Re: Having trouble with backups (was: Re: Crash Recovery) - Mailing list pgsql-performance

From Curt Sampson
Subject Re: Having trouble with backups (was: Re: Crash Recovery)
Date
Msg-id Pine.NEB.4.51.0301251316090.596@angelic.cynic.net
Whole thread Raw
In response to Re: Having trouble with backups (was: Re: Crash Recovery)  (Carlos Moreno <moreno@mochima.com>)
List pgsql-performance
On Fri, 24 Jan 2003, Carlos Moreno wrote:


> The server is a Dual Athlon 1.8GHz, with 1GB of RAM,
> running Linux 7.3, and approx. 250MB for shared buffers.
> ...
> In case this tells you something...  The size of the
> backup files (in plain ASCII) are around 300MB  (the
> command is "nice pg_dump -c -f file.sql dbname").

I was going to ask you to check your disk I/O statistics, but that tells
me that disk I/O is probably not the problem. If the ASCII dump file
(I assume by "plain ASCII" you mean uncompressed as well) is only 300
MB, your database size is likely well under 100 MB. In which case the
entire database ought to be residing in the buffer cache, and you should
see maximum CPU utilisation during the dump, and not too much disk
I/O. (This is, however, assuming that that's the only database on your
machine. You don't have another 250 GB database that gets lots of random
access hiding there, do you? :-))

On a big machine like that, with such a small database, you should be
able to do a dump in a couple of minutes with little noticable impact on
the performance of clients.

I would probably start with carefully tracing what your clients are doing
during backup, and where they're blocking.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

pgsql-performance by date:

Previous
From: Kevin Brown
Date:
Subject: Re: Mount options for Ext3?
Next
From: Tom Lane
Date:
Subject: WAL replay logic (was Re: Mount options for Ext3?)