Re: Having trouble with backups (was: Re: Crash Recovery)

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(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC