Search Postgresql Archives

Re: pg_dump in a production environment

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxxxxxx>
Date: May 23, 2005 3:18:33 PM CDT
To: "Thomas F. O'Connell" <tfo@xxxxxxxxxxxx>
Cc: PgSQL General <pgsql-general@xxxxxxxxxxxxxx>
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.

From: "Matthew T. O'Connor" <matthew@xxxxxxxx>
Date: May 23, 2005 3:18:18 PM CDT
To: "Thomas F. O'Connell" <tfo@xxxxxxxxxxxx>
Cc: PgSQL General <pgsql-general@xxxxxxxxxxxxxx>
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

From: Martijn van Oosterhout <kleptog@xxxxxxxxx>
Date: May 23, 2005 3:25:23 PM CDT
To: "Thomas F. O'Connell" <tfo@xxxxxxxxxxxx>
Cc: PgSQL General <pgsql-general@xxxxxxxxxxxxxx>
Subject: Re: pg_dump in a production environment
Reply-To: Martijn van Oosterhout <kleptog@xxxxxxxxx>


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,

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux