-- this is the third time I've tried sending this and I never saw it get
through to the list. Sorry if multiple copies show up.
Hi all,
I've been lurking using the web archives for a while and haven't found
an answer that seems to answer my questions about pg_dump.
We have a 206GB data warehouse running on version 8.0.3. The server is
somewhat underpowered in terms of CPU: (1) 2.8 GHz Xeon 4GB Ram and a
single HBA to our SAN (IBM DS4300). We in the process of migrating to a
new server that we've repurposed from our production OLTP database (8)
2.0 GHz Xeon, 16GB Ram and dual HBAs to the same SAN running version 8.1.
Independant of that move, we still need to get by on the old system and
I'm concerned that even on the new system, pg_dump will still perform
poorly. I can't do a full test because we're also taking advantage of
the table partitioning in 8.1 so we're not doing a dump and restore.
We backup the database using:
pg_dump -Fc -cv ${CURDB} > ${BACKDIR}/${CURDB}-${DATE}.bak
There a three different LUNs allocated to the old warehouse on the SAN -
data, wal and a dump area for the backups. The SAN has two controllers
(only 128MB of cache per) and the data is on one controller while the
WAL and dump area are on the other. Still a single HBA though.
Creating the compressed backup of this database takes 12 hours. We start
at 6PM and it's done a little after 1AM, just in time for the next day's
load. The load itself takes about 5 hours.
I've watched the backup process and I/O is not a problem. Memory isn't a
problem either. It seems that we're CPU bound but NOT in I/O wait. The
server is a dedicated PGSQL box.
Here are our settings from the conf file:
maintenance_work_mem = 524288
work_mem = 1048576 ( I know this is high but you should see some of our
sorts and aggregates)
shared_buffers = 50000
effective_cache_size = 450000
wal_buffers = 64
checkpoint_segments = 256
checkpoint_timeout = 3600
We're inserting around 3mil rows a night if you count staging, info, dim
and fact tables. The vacuum issue is a whole other problem but right now
I'm concerned about just the backup on the current hardware.
I've got some space to burn so I could go to an uncompressed backup and
compress it later during the day.
If there are any tips anyone can provide I would greatly appreciate it.
I know that the COPY performance was bumped up in 8.1 but I'm stuck on
this 8.0 box for a while longer.
Thanks,
John E. Vincent