From: Richard Huxton <dev@archonet.com>
To: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
Cc: pgsql-performance@postgresql.org
Subject: Re: DB responce during DB dump
Date: Wed, 25 Jan 2006 12:44:45 +0000
Message-ID: <43D772BD.8080809@archonet.com> (raw)
In-Reply-To: <20060125150134.55589806.eugrid@fpm.kubsu.ru>
References: <GJEAKHIALCNFPPGBGEKNEEBDDGAA.dan@centrifugesolutions.com>
<20060125150134.55589806.eugrid@fpm.kubsu.ru>
Evgeny Gridasov wrote:
> Hi, everybody!
>
> I experience problems with backing up one of my Postgresql 8.1.2 installations.
> The problem is that when I do DB backup, all queries begin to run very slow =(
> The database only grows in its size (~20Gb today), and the number of transactions increases every month.
> A year ago such slow down was OK, but today it is unacceptable.
>
> I found out that pg_dump dramatically increases hdd I/O and because of this most of all
> queries begin to run slower. My application using this DB server is time-critical, so
> any kind of slow down is critical.
>
> I've written a perl script to limit pg_dump output bandwidth, a simple traffic shaper,
> which runs as: pg_dumpall -c -U postgres | limit_bandwidth.pl | bzip2 > pgsql_dump.bz2
> The limit_bandwidth.pl script limits pipe output at 4Mb/sec rate, which seems to be ok.
>
> Is there any other solution to avoid this problem?
That's an interesting solution, and I'd guess people might like to see
it posted to the list if it's not too big.
Also, there's no reason you have to dump from the same machine, you can
do so over the network which should reduce activity a little bit.
Basically though, it sounds like you either need more disk I/O or a
different approach.
Have you looked into using PITR log-shipping or replication (e.g. slony)
to have an off-machine backup?
--
Richard Huxton
Archonet Ltd
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: dev@archonet.com, eugrid@fpm.kubsu.ru
Subject: Re: DB responce during DB dump
In-Reply-To: <43D772BD.8080809@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox