Re: pg_dump in a production environment
| От | Thomas F. O'Connell |
|---|---|
| Тема | Re: pg_dump in a production environment |
| Дата | |
| Msg-id | 935448BA-6C88-4CED-984F-184B60E8D06E@sitening.com обсуждение исходный текст |
| Ответ на | pg_dump in a production environment ("Thomas F. O'Connell" <tfo@sitening.com>) |
| Ответы |
Re: pg_dump in a production environment
(Chris Kratz <chris.kratz@vistashare.com>)
|
| Список | pgsql-general |
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™
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
From: Scott Marlowe <smarlowe@g2switchworks.com>Date: May 23, 2005 3:18:33 PM CDTTo: "Thomas F. O'Connell" <tfo@sitening.com>Cc: PgSQL General <pgsql-general@postgresql.org>Subject: Re: [GENERAL] pg_dump in a production environmentBasically, it sounds like postgresql is doing a lot of very longsequential scans to do this backup. HAve you done a vacuum fulllately? It could be that you've got a lot of table bloat that's makingthe seq scans take so long.You could be I/O saturated already, and the backup is just pushing youover the edge of the performance knee.I do a 'vacuum analyze verbose' and see if you need more fsm setup foryour regular vacuums to keep up.
From: "Matthew T. O'Connor" <matthew@zeut.net>Date: May 23, 2005 3:18:18 PM CDTTo: "Thomas F. O'Connell" <tfo@sitening.com>Cc: PgSQL General <pgsql-general@postgresql.org>Subject: Re: [GENERAL] pg_dump in a production environmentCould 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
From: Martijn van Oosterhout <kleptog@svana.org>Date: May 23, 2005 3:25:23 PM CDTTo: "Thomas F. O'Connell" <tfo@sitening.com>Cc: PgSQL General <pgsql-general@postgresql.org>Subject: Re: [GENERAL] pg_dump in a production environmentReply-To: Martijn van Oosterhout <kleptog@svana.org>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 onechewing all your memory? i.e what's really causing the problem...Hope this helps,
В списке pgsql-general по дате отправления: