Re: 8.3 / 8.2.6 restore comparison

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" <jd(at)commandprompt(dot)com> wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Tue, 19 Feb 2008 13:21:46 -0800
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> 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