PG_DUMP very slow because of STDOUT ??

Hi,

I am new to the mailing list (as a writer … reading since a long time), but am using PostgreSQL in our company for quite some time now.

Lately, we rolled out some new servers with vastly improved hardware (8 disk RAID 1+0, instead of old 2 disk RAID 1 etc. etc.), new OS (Ubuntu 10.04 server instead of Debian Lenny) and newer PostgreSQL version (8.4.4 instead of 8.3.8). And everything seemed to be much better – as expected – until, well until I have seen the first full backup run-times.

For example:

-          One of our old machines needed approximately 2 ½ hours to make a dump (with “–Fc –compress=9”) of our approximately 100 GByte DB.

-          One of our new machines with the copy of the same 100 GByte DB needed 6 ½ to 7 hours to dump the same DB.

First check of our MRTG monitoring showed, that during the backup, the machine was not saturated in any way by load (IO Wait were below 5%), CPUs were almost idling so you got the feeling, that machine was sleeping … But of course it did the backup, only that it was very slow.

No I further investigated. I also did some detailed monitoring with collect (http://collectl.sourceforge.net/), seeing, that the machine often was doing some slow reading and every 20-30 seconds wrote down a few Mbytes on the backup disk. So, almost everything suggests that we are very very far from some hardware limitations. I also checked some file write/ reads on the RAID, and could confirm, that plain DD of some multy GByte file only took a snap.

OK, then I checked what PG_DUMP was doing. In the process list of PostgreSQL (viewed via pgAdmin), I see how PG_DUMP works (didn’t care much about it until now). In does a COPY to STDOUT. Now I wanted to narrow down the problem, and tried to reproduce that COPY statement of one of our 8 GByte DBs (that table only has INT, BIGINT, BOOLEAN fields ! So no potential BLOB problem!). And now comes the interesting part.

-          When I did “COPY public.bigtable (column1, column2, column3) TO '/var/tmp/test.dump';” it is FAST. It writes down the dump file of 3 GBytes in about 2 ½ minutes (as expected with the hardware)!

-          When I did “time sudo -u postgres /var/software/pgsql1/bin/psql -h /tmp/pgsql1 -p 4306 -d  database_name -f copy-test.sql > /var/tmp/test.dump” (and copy-test.sql has : “COPY public.cachereleaseartist (releasebyterritoryid, artistid, isdisplayartist, artistroleclusterid, artistroletypeid, iscontainedinrelease, iscontainedinresource) TO STDOUT;” ) … I couldn’t wait until it ended .. after 20 minutes the test.dump file was merely at 1 Gbyte (far from the target size of 3 Gbyte).

I also monitored both statements via collect, and could confirm that the direct COPY to file made some reasonable IO activity, while the COPY via STDOUT was idling like mad, with some occasional bursts and falling asleep again. This would also make clear, why PG_DUMP is so dog slow. If it gets its data via STDOUT at that speed, then it falls asleep too ….

And to make things even worse: I did the same test on our old hardware too. And BOTH, COPY to file directly AND COPY via STDOUT war equally fast (took 5 ½ minutes … but OK, that’s an old iron compared to the new one)!!! No difference between file and STDOUT (as expected)!

Now I ask, whats going on here ???? Why is COPY via STDOUT so much slower on out new machine?

-          Has anything changed between PostgreSQL 8.3.8 and PostgreSQL 8.4.4 in this respect? Maybe some STDOUT buffer flushing from COPY? Buffer handling? Buffer size???

-          Is maybe the OS to blame? As I told, we changed from Debian Lenny (Kernel 2.6.26-2) to Ubuntu 10.04 Server (Kernel 2.6.32-22) (both 64-bit)? And if yes, would there be ways for PostgreSQL to adapt to the new OSs new behavior in STDOUT (if there is such thing)???

And for the Hardware:

-          Old machine was an 8 Core with 16 GByte RAM BUT only one RAID 1 (software!) with two disks for everything (I know, that was a little bit meager).

-          New machine has 8 Core (with hyper-threading 16), 24 GByte RAM, and a RAID 1+0 with 8 disks for Data only, and XLOG/Backup/OS on  a second RAID 1 with 2 disks, with hardware raid controller and battery backed cache  (so, obviously, this machine should be faster than the old one)

Can someone shed some light on this STDOUT madness? Or give me some directions/hints in which I could further research?

Thank you very much!

Andras Fabian

Atrada Trading Network AG