Postgres Pro
Downloads
Home   >   mailing lists

Re: pg_dump in a production environment - Mailing list pgsql-general

From Thomas F. O'Connell
Subject Re: pg_dump in a production environment
Date
Msg-id 935448BA-6C88-4CED-984F-184B60E8D06E@sitening.com
Whole thread Raw
In response to pg_dump in a production environment  ("Thomas F. O'Connell" <tfo@sitening.com>)
Responses Re: pg_dump in a production environment
List pgsql-general
Tree view
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


From: Scott Marlowe <smarlowe@g2switchworks.com>
Date: May 23, 2005 3:18:33 PM CDT
To: "Thomas F. O'Connell" <tfo@sitening.com>
Cc: PgSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] 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.

From: "Matthew T. O'Connor" <matthew@zeut.net>
Date: May 23, 2005 3:18:18 PM CDT
To: "Thomas F. O'Connell" <tfo@sitening.com>
Cc: PgSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] 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

From: Martijn van Oosterhout <kleptog@svana.org>
Date: May 23, 2005 3:25:23 PM CDT
To: "Thomas F. O'Connell" <tfo@sitening.com>
Cc: PgSQL General <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] pg_dump in a production environment
Reply-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 one
chewing all your memory? i.e what's really causing the problem...

Hope this helps,

pgsql-general by date:

Previous
From: Scott Frankel
Date:
Subject: Re: urgent: another postmaster
Next
From: "Thomas F. O'Connell"
Date:
Subject: Re: pg_dump in a production environment