| Okay, I collated the three replies I got below for ease in replying.
I vacuum full analyze and reindexdb approximately once a month, but I use pg_autovacuum as a matter of ongoing maintenance, and it seems to hit equilibrium pretty well and seems to prevent bloat. The last time I checked a vacuum analyze verbose, I had plenty of FSM to spare. The data grows, but it doesn't seem to grow so quickly that I'd already be out of FSM space.
I actually run pg_dump from a remote machine, so I/O contention on the partition with $PGDATA shouldn't be an issue.
And here is the actual command:
pg_dump -h <host> -F c <database> > <dumpfile>
Pretty basic, although it is compressing.
As far as I can tell, the postmaster handling the dump request takes up quite a bit of CPU, but not itself to the point where the database should be unusable under ordinary circumstances. E.g., when a query/backend eats up that much CPU, it doesn't prevent further access.
I'm suspicious more of something involving locks than of CPU.
Oh, and one other small(ish) detail: the dumping client is using a 7.4.8 installation, whereas the server itself is 7.4.6.
-tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 615-260-0005 Date: May 23, 2005 3:18:33 PM CDT Subject: Re: pg_dump in a production environment
Basically, it sounds like postgresql is doing a lot of very long sequential scans to do this backup. HAve you done a vacuum full lately? It could be that you've got a lot of table bloat that's making the seq scans take so long.
You could be I/O saturated already, and the backup is just pushing you over the edge of the performance knee.
I do a 'vacuum analyze verbose' and see if you need more fsm setup for your regular vacuums to keep up.
Date: May 23, 2005 3:18:18 PM CDT Subject: Re: pg_dump in a production environment
Could this be an I/O saturation issue like the one the vacuum delay settings are supposed to help with? Perhaps we could either extend the vacuum delay settings to effect pg_dump, or make new option to pg_dump that would have it slow down the dump.
BTW, have you tried running pg_dump from a separate machine? Or even just making sure that the dump file is being written to a different disk drive than PostgreSQL is running on. All that disk write activity is bound to slow the system down.
Matthew
Date: May 23, 2005 3:25:23 PM CDT Subject: Re: pg_dump in a production environment
What's you pg_dump command? Some options may take a lot of memory.
If you list the processes while this is going on, do you see one chewing all your memory? i.e what's really causing the problem...
Hope this helps,
|
