DB responce during DB dump - Mailing list pgsql-performance

From Evgeny Gridasov
Subject DB responce during DB dump
Msg-id 20060125150134.55589806.eugrid@fpm.kubsu.ru
Whole thread Raw
In response to Inconsistant query plan  ("Daniel Gish")
Responses Re: DB responce during DB dump  (Richard Huxton)
List pgsql-performance
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?

Evgeny Gridasov
Software Engineer
I-Free, Russia

pgsql-performance by date:

From: Alessandro Baretta
Subject: Re: Inconsistant query plan
From: Richard Huxton
Subject: Re: DB responce during DB dump