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

yes, vacuum analyze is used constantly.
--Ben

--- Fernando Schapachnik <fschapachnik(at)vianetworks(dot)com(dot)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(at)postgresql(dot)org)
>
>
>
>Fernando P. Schapachnik
>Planificaci=F3n de red y tecnolog=EDa
>VIA NET.WORKS ARGENTINA S.A.
>fschapachnik(at)vianetworks(dot)com(dot)ar
>Tel.: (54-11) 4323-3381

_____________________________________________________________