PostgreSQL 7.1 on SMP: FreeBSD 4.3 || Linux 2.4.x?
I have a dual-CPU (PIII 600MHz), 512MB, IDE-HD box. A couple of months ago I was using Linux 2.4.3 on this box and was getting good performance on a custom network-server/db application I am running. Basically, I am getting constant network traffic to the machine, storing transmitted information in memory until some threshold size, and then forking off a process to transfer the shared memory to PostgreSQL database. I saw better performance doing this than dumping to the database right away as the data was coming in. The application takes advantage of optimized SQL statements, vacuum analyze, indexes, persistent db connections, bound values, delayed transaction commits, etc..
Recently, I moved the box to FreeBSD 4.3. There were no hardware changes to the SMP box, no changes to the application software, and no changes to the database schema or configuration (no fsync, buffers, shared memory, etc). Soft updates are on for the FFS, and under Linux I was using the standard ext fs (not a journaling one).
However, I am seeing a degredation in performance. Sadly, I didn't take detailed measurements under Linux 2.4.3, but I am seeing the postgres processes that do the insert/updates (on the order of 1000 rows every couple of minutes) take a lot longer than before. With Linux I had been seeing the postgres process that did those inserts/updates last for about 20 seconds (say 50-100tps). With FreeBSD I am seeing the process last for 1.5 - 2 minutes (say approx. 10-20tps).
Whew. Now for the question part. I understand that SMP support under Linux is a lot more fine-grained than FreeBSD in the 2.4.x kernels. Can this be causing my database performance degredation? When the postgres process is writing those thousands of rows to various tables, I am still getting a constant flow of network traffic coming into the ethernet ports. The amount of data transfer is very low per connection, but the frequency of connections is high (for example, 100 connections per second, but 100 bytes per connection). I don't have a clear understanding of kernel space issues, but could the net traffic be generating interrupts that cause one of the CPUs to lock in kernel space, effectively prohibiting the other CPU (or other processes) from concurrently working the postgres stuff? My understanding is that under FreeBSD, if a kernel system call happens on one CPU, it gets a big kernel lock and the other CPU spins and waits...? Whereas under Linux, might both processes (postgres and network server) be truly working concurrently? Ideally, it would be nice to bind CPU0 to handle the network stuff while CPU1 could work on the disk I/O for the postgres stuff. I know it's not as simple as that, but is Linux 2.4.x better suited to doing this application than FreeBSD 4.x?
On a side note, my intention is not to instigate the *BSD vs. Linux arguments. I have been very pleased with FreeBSD (security, minimalism, extremely robust & stable, etc.). However, I have a very specific application where their SMP differences might be exploited for better db performance.
Thanks for any info/help you might be able to provide.
--Ben
_____________________________________________________________
yup. the only difference i forgot to mention is that on the freebsd system I "--enable-debug"'ged when I configured postgres. But the debug-level is set to 0. I know that impacts performance a bit, but I doubt it would account for the big delta I'm seeing.
--Ben
--- "Andrew Snow" <andrew@modulus.org>wrote:
Stupid question possibly, but did you enable SoftUpdates on the FreeBSD
file system?- Andrew
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Benjamin Franks
Sent: Friday, 28 September 2001 7:19 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] PostgreSQL 7.1 on SMP: FreeBSD 4.3 || Linux 2.4.x?I have a dual-CPU (PIII 600MHz), 512MB, IDE-HD box. A couple
of months ago I was using Linux 2.4.3 on this box and was
getting good performance on a custom network-server/db
application I am running. Basically, I am getting constant
network traffic to the machine, storing transmitted
information in memory until some threshold size, and then
forking off a process to transfer the shared memory to
PostgreSQL database. I saw better performance doing this
than dumping to the database right away as the data was
coming in. The application takes advantage of optimized SQL
statements, vacuum analyze, indexes, persistent db
connections, bound values, delayed transaction commits, etc..Recently, I moved the box to FreeBSD 4.3. There were no
hardware changes to the SMP box, no changes to the
application software, and no changes to the database schema
or configuration (no fsync, buffers, shared memory, etc).
Soft updates are on for the FFS, and under Linux I was using
the standard ext fs (not a journaling one).However, I am seeing a degredation in performance. Sadly, I
didn't take detailed measurements under Linux 2.4.3, but I am
seeing the postgres processes that do the insert/updates (on
the order of 1000 rows every couple of minutes) take a lot
longer than before. With Linux I had been seeing the
postgres process that did those inserts/updates last for
about 20 seconds (say 50-100tps). With FreeBSD I am seeing
the process last for 1.5 - 2 minutes (say approx. 10-20tps).Whew. Now for the question part. I understand that SMP
support under Linux is a lot more fine-grained than FreeBSD
in the 2.4.x kernels. Can this be causing my database
performance degredation? When the postgres process is
writing those thousands of rows to various tables, I am still
getting a constant flow of network traffic coming into the
ethernet ports. The amount of data transfer is very low per
connection, but the frequency of connections is high (for
example, 100 connections per second, but 100 bytes per
connection). I don't have a clear understanding of kernel
space issues, but could the net traffic be generating
interrupts that cause one of the CPUs to lock in kernel
space, effectively prohibiting the other CPU (or other
processes) from concurrently working the postgres stuff? My
understanding is that under FreeBSD, if a kernel system call
happens on one CPU, it gets a big kernel lock and the other
CPU spins and waits...? Whereas under Linux, might both
proce! sses (postgres and network server) be truly working
concurrently? Ideally, it would be nice to bind CPU0 to
handle the network stuff while CPU1 could work on the disk
I/O for the postgres stuff. I know it's not as simple as
that, but is Linux 2.4.x better suited to doing this
application than FreeBSD 4.x?On a side note, my intention is not to instigate the *BSD vs.
Linux arguments. I have been very pleased with FreeBSD
(security, minimalism, extremely robust & stable, etc.).
However, I have a very specific application where their SMP
differences might be exploited for better db performance.Thanks for any info/help you might be able to provide.
--Ben
_____________________________________________________________
---------------------------(end of
broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
_____________________________________________________________
Import Notes
Resolved by subject fallback
yes, vacuum analyze is used constantly.
--Ben
--- Fernando Schapachnik <fschapachnik@vianetworks.com.ar>wrote:
Have you vacuum analyzed after the restore?En un mensaje anterior, Benjamin Franks escribi=F3:
I have a dual-CPU (PIII 600MHz), 512MB, IDE-HD box. A couple of months a=
go I was using Linux 2.4.3 on this box and was getting good performance on =
a custom network-server/db application I am running. Basically, I am getti=
ng constant network traffic to the machine, storing transmitted information=
in memory until some threshold size, and then forking off a process to tra=
nsfer the shared memory to PostgreSQL database. I saw better performance d=
oing this than dumping to the database right away as the data was coming in=
. The application takes advantage of optimized SQL statements, vacuum anal=
yze, indexes, persistent db connections, bound values, delayed transaction =
commits, etc..=20
Recently, I moved the box to FreeBSD 4.3. There were no hardware changes=to the SMP box, no changes to the application software, and no changes to =
the database schema or configuration (no fsync, buffers, shared memory, etc=
). Soft updates are on for the FFS, and under Linux I was using the standa=
rd ext fs (not a journaling one).=20=20=20
However, I am seeing a degredation in performance. Sadly, I didn't take d=etailed measurements under Linux 2.4.3, but I am seeing the postgres proces=
ses that do the insert/updates (on the order of 1000 rows every couple of m=
inutes) take a lot longer than before. With Linux I had been seeing the po=
stgres process that did those inserts/updates last for about 20 seconds (sa=
y 50-100tps). With FreeBSD I am seeing the process last for 1.5 - 2 minute=
s (say approx. 10-20tps).=20
Whew. Now for the question part. I understand that SMP support under Li=nux is a lot more fine-grained than FreeBSD in the 2.4.x kernels. Can this=
be causing my database performance degredation? When the postgres process=
is writing those thousands of rows to various tables, I am still getting a=
constant flow of network traffic coming into the ethernet ports. The amou=
nt of data transfer is very low per connection, but the frequency of connec=
tions is high (for example, 100 connections per second, but 100 bytes per c=
onnection). I don't have a clear understanding of kernel space issues, but =
could the net traffic be generating interrupts that cause one of the CPUs t=
o lock in kernel space, effectively prohibiting the other CPU (or other pro=
cesses) from concurrently working the postgres stuff? My understanding is =
that under FreeBSD, if a kernel system call happens on one CPU, it gets a b=
ig kernel lock and the other CPU spins and waits...? Whereas under Linux, =
might both proce!sses (postgres and network server) be truly working concurrently? Ideal=
ly, it would be nice to bind CPU0 to handle the network stuff while CPU1 co=
uld work on the disk I/O for the postgres stuff. I know it's not as simple=
as that, but is Linux 2.4.x better suited to doing this application than F=
reeBSD 4.x?=20
On a side note, my intention is not to instigate the *BSD vs. Linux argum=ents. I have been very pleased with FreeBSD (security, minimalism, extreme=
ly robust & stable, etc.). However, I have a very specific application whe=
re their SMP differences might be exploited for better db performance.=20
Thanks for any info/help you might be able to provide.
=20
--Ben
=20
_____________________________________________________________
=20
=20
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)Fernando P. Schapachnik
Planificaci=F3n de red y tecnolog=EDa
VIA NET.WORKS ARGENTINA S.A.
fschapachnik@vianetworks.com.ar
Tel.: (54-11) 4323-3381
_____________________________________________________________
Import Notes
Resolved by subject fallback
We belive that PostgreSQL 7.2 will have much better SMP performance.
It should be released in 2-3 months.
I have a dual-CPU (PIII 600MHz), 512MB, IDE-HD box. A couple
of months ago I was using Linux 2.4.3 on this box and was getting
good performance on a custom network-server/db application I am
running. Basically, I am getting constant network traffic to
the machine, storing transmitted information in memory until
some threshold size, and then forking off a process to transfer
the shared memory to PostgreSQL database. I saw better performance
doing this than dumping to the database right away as the data
was coming in. The application takes advantage of optimized
SQL statements, vacuum analyze, indexes, persistent db connections,
bound values, delayed transaction commits, etc..
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026