Re: 8.3 / 8.2.6 restore comparison

-----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-----