Bah. It's the stuff in the format translation path and conversion to/from datums that is the bottleneck.
We sped up COPY TO recently by a factor of 10 using similar approaches to what we did for COPY FROM in the past. There's a format conversion that is the culprit. We routinely get about 12 MB/s of heap insertion rate per CPU core and it's CPU bound. You can peek in on what's happening using gstack on Linux, or the gdb "attach and print stacktrace" approach for a crude profile. - Luke On 2/19/08 1:36 PM, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > On Tue, 19 Feb 2008 13:21:46 -0800 > "Joshua D. Drake" <[EMAIL PROTECTED]> wrote: > >> Were their any thoughts on this? I am also finding that backing up >> this database is rudely slow with the same type of odd metrics >> (almost zero (or zero) iowait). We can saturate a CPU but the CPU is >> certainly not pushing the data to disk as fast as it could. > > Further on this. We have tested on RHEL4 and RHEL5. Their are two > machines, each with 32GB of ram. I have four of these in the RHEL 5 > machine: > > processor : 0 > vendor_id : AuthenticAMD > cpu family : 15 > model : 65 > model name : Dual-Core AMD Opteron(tm) Processor 8216 > stepping : 2 > cpu MHz : 2411.132 > cache size : 1024 KB > physical id : 0 > siblings : 2 > core id : 0 > cpu cores : 2 > fpu : yes > fpu_exception : yes > cpuid level : 1 > wp : yes > flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge > mca cmov pat pse36 clflush mmx fxsr sse sse2 ht syscall nx mmxext > fxsr_opt rdtscp lm 3dnowext 3dnow pni cx16 lahf_lm cmp_legacy svm > extapic cr8_legacy bogomips : 4823.59 TLB size : 1024 4K > pages clflush size : 64 cache_alignment : 64 > address sizes : 40 bits physical, 48 bits virtual > power management: ts fid vid ttp tm stc > > Here is the mini specs: > > # 5U Q524 - Quad Opteron 24 SCSI > # Tyan S4885G3NR 800 Series CPU Opteron 800 > # AMD Opteron 880 - 2.4GHz 2Core x 4 > # 32GB - DDR ECC REG 400MHz (16x2GB) x 1 (16 x 2GB 3200 ECC REG Smart > Modular (32GB) # HD 1: 73GB SCSI 15K RPM x 24 > # PCI 1: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB > # MegaRaid LSIiTBBU01 Battery - Order #: LSI00009 > # PCI 2: MegaRaid SCSI 320-2E - 2ch, U320, PCI Express, 128MB > # MegaRaid LSIiTBBU01 Battery - Order #: LSI00009 > # DVD-ROM/Sony 3.5 Inch Floppy Drive > > The configuration is: > > / RAID 1 > / xlogs RAID 1 > /data1 10 drives RAID 10 > /data2 10 drives RAID 10 > > The thing that is frustrating here, is it appears that PostgreSQL just > can't utilize the hardware. I *know* it can because we have larger > machines in production that use PostgreSQL happily. However when I have > 220G backups taking 8 hours and restores taking 11 hours, I begin to > wonder where the bottleneck is. > > Assuming 25 megs a second per drive (random write) on data1 and data2 > we should be pushing 250M a second. Just to be insanely conservative > let's cut that in half to 125M per second. That means every 10 seconds > we should do ~ 1G. That means every minute we should to ~ 6G, which > means 360G an hour. > > Granted we have index creation and everything else going on but 11 > hours and no IO chewing? > > As a note these are reproducible on both machines regardless of RHEL5 > or RHEL4. > > I know there are much stats here but I have provided them in previous > posts on this thread. Perhaps someone sees a red flag in the hardware? > > > Sincerely, > > Joshua D. Drake > - -- > The PostgreSQL Company since 1997: http://www.commandprompt.com/ > PostgreSQL Community Conference: http://www.postgresqlconference.org/ > Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate > PostgreSQL SPI Liaison | SPI Director | PostgreSQL political pundit > > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.6 (GNU/Linux) > > iD8DBQFHu0vyATb/zqfZUUQRAiuYAJ9ut6i/cPv2MYc8RO2+wNw09M5/WwCfUaGY > sAkFt+S14i0kFMn6mz9juBw= > =TNys > -----END PGP SIGNATURE----- > ---------------------------(end of broadcast)--------------------------- TIP > 9: In versions below 8.0, the planner will ignore your desire to choose > an index scan if your joining column's datatypes do not match ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
