Обсуждение: 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 gettinggood performance on a custom network-server/db application I am running. Basically, I am getting constant networktraffic to the machine, storing transmitted information in memory until some threshold size, and then forking offa process to transfer the shared memory to PostgreSQL database. I saw better performance doing this than dumping to thedatabase 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 areon 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 amseeing the postgres processes that do the insert/updates (on the order of 1000 rows every couple of minutes) take a lotlonger than before. With Linux I had been seeing the postgres process that did those inserts/updates last for about 20seconds (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 the2.4.x kernels. Can this be causing my database performance degredation? When the postgres process is writing those thousandsof 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, 100connections per second, but 100 bytes per connection). I don't have a clear understanding of kernel space issues, butcould the net traffic be generating interrupts that cause one of the CPUs to lock in kernel space, effectively prohibitingthe other CPU (or other processes) from concurrently working the postgres stuff? My understanding is that underFreeBSD, 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 postgresstuff. I know it's not as simple as that, but is Linux 2.4.x better suited to doing this application than FreeBSD4.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 differencesmight be exploited for better db performance. Thanks for any info/help you might be able to provide. --Ben _____________________________________________________________
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
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) >> _____________________________________________________________
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 _____________________________________________________________