Re: pg_dump in a production environment

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(at)g2switchworks(dot)com>
> Date: May 23, 2005 3:18:33 PM CDT
> To: "Thomas F. O'Connell" <tfo(at)sitening(dot)com>
> Cc: PgSQL General <pgsql-general(at)postgresql(dot)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(at)zeut(dot)net>
> Date: May 23, 2005 3:18:18 PM CDT
> To: "Thomas F. O'Connell" <tfo(at)sitening(dot)com>
> Cc: PgSQL General <pgsql-general(at)postgresql(dot)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(at)svana(dot)org>
> Date: May 23, 2005 3:25:23 PM CDT
> To: "Thomas F. O'Connell" <tfo(at)sitening(dot)com>
> Cc: PgSQL General <pgsql-general(at)postgresql(dot)org>
> Subject: Re: [GENERAL] pg_dump in a production environment
> Reply-To: Martijn van Oosterhout <kleptog(at)svana(dot)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,