pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedDB responce during DB dump
5+ messages / 3 participants
[nested] [flat]
* DB responce during DB dump
@ 2006-01-25 12:01 Evgeny Gridasov <eugrid@fpm.kubsu.ru>
0 siblings, 1 reply; 5+ messages in thread
From: Evgeny Gridasov @ 2006-01-25 12:01 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DB responce during DB dump
@ 2006-01-25 12:44 Richard Huxton <dev@archonet.com>
parent: Evgeny Gridasov <eugrid@fpm.kubsu.ru>
0 siblings, 2 replies; 5+ messages in thread
From: Richard Huxton @ 2006-01-25 12:44 UTC (permalink / raw)
To: Evgeny Gridasov <eugrid@fpm.kubsu.ru>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DB responce during DB dump
@ 2006-01-25 16:21 Tom Lane <tgl@sss.pgh.pa.us>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2006-01-25 16:21 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Evgeny Gridasov <eugrid@fpm.kubsu.ru>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> Evgeny Gridasov wrote:
>> I've written a perl script to limit pg_dump output bandwidth,
>> ...
>> 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.
Years ago there was some experimentation with dump-rate throttling logic
inside pg_dump itself --- there's still a comment about it in pg_dump.c.
The experiment didn't seem very successful, which is why it never got to
be a permanent feature. I'm curious to know why this perl script is
doing a better job than we were able to do inside pg_dump.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DB responce during DB dump
@ 2006-01-25 16:43 Evgeny Gridasov <eugrid@fpm.kubsu.ru>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Evgeny Gridasov @ 2006-01-25 16:43 UTC (permalink / raw)
To: pgsql-performance
All I was trying to achieve is to limit I/O rate done by pg_dump.
The script is a very simple pipe rate limitter and nothing more:
it reads input, but outputs data no more than at rate specified.
I guess it helps because even if pg_dump outputs data at 20 mb/sec,
the script won't be able to read it at rate higher than output rate. Pipe
buffer is not infinitive, so pg_dump output rate and hard disk reads become
almost equal the input rate of my perl script.
On Wed, 25 Jan 2006 11:21:58 -0500
Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Years ago there was some experimentation with dump-rate throttling logic
> inside pg_dump itself --- there's still a comment about it in pg_dump.c.
> The experiment didn't seem very successful, which is why it never got to
> be a permanent feature. I'm curious to know why this perl script is
> doing a better job than we were able to do inside pg_dump.
--
Evgeny Gridasov
Software Engineer
I-Free, Russia
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: DB responce during DB dump
@ 2006-01-25 16:47 Evgeny Gridasov <eugrid@fpm.kubsu.ru>
parent: Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Evgeny Gridasov @ 2006-01-25 16:47 UTC (permalink / raw)
To: pgsql-performance
Ok, It's VERY simple =) here:
http://deepcore.i-free.ru/simple_shaper.pl
I could dump it to a spare machine, but I don't have one.
Current DB server is 2xXEON / 4GbRAM / RAID10 (4 SCSI HDD). Performance is excellent, except during backups.
I wanted to set up some kind of replication but it's useless - I don't have a spare machine now, may be in future...
On Wed, 25 Jan 2006 12:44:45 +0000
Richard Huxton <dev@archonet.com> wrote:
>
> 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?
--
Evgeny Gridasov
Software Engineer
I-Free, Russia
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-01-25 16:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-01-25 12:01 DB responce during DB dump Evgeny Gridasov <eugrid@fpm.kubsu.ru>
2006-01-25 12:44 ` Richard Huxton <dev@archonet.com>
2006-01-25 16:21 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-01-25 16:43 ` Evgeny Gridasov <eugrid@fpm.kubsu.ru>
2006-01-25 16:47 ` Evgeny Gridasov <eugrid@fpm.kubsu.ru>
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