Re: PostgreSQL 7.1 on SMP: FreeBSD 4.3 || Linux 2.4.x?

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
bigdelta 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)
>>

_____________________________________________________________