pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_dump slow
7+ messages / 3 participants
[nested] [flat]
* pg_dump slow
@ 2005-11-30 13:35 Franklin Haut <franklin.haut@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Franklin Haut @ 2005-11-30 13:35 UTC (permalink / raw)
To: pgsql-performance
Hi
i´m using PostgreSQL on windows 2000, the pg_dump take around 50 minutes
to do backup of 200Mb data ( with no compression, and 15Mb with
compression), but in windows XP does not pass of 40 seconds... :(
This happens with 8.1 and version 8.0, somebody passed for the same
situation?
It will be that a configuration in the priorities of the exists
processes ? in Windows XP the processing of schemes goes 70% and
constant accesses to the HardDisk, while that in windows 2000 it does
not pass of 3%.
thanks
Franklin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump slow
@ 2005-11-30 13:56 Ron <rjpeace@earthlink.net>
parent: Franklin Haut <franklin.haut@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Ron @ 2005-11-30 13:56 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; pgsql-performance
At 08:35 AM 11/30/2005, Franklin Haut wrote:
>Hi
>
>i´m using PostgreSQL on windows 2000, the pg_dump take around 50 minutes
>to do backup of 200Mb data ( with no compression, and 15Mb with
>compression),
Compression is reducing the data to 15/200= 3/40= 7.5% of original size?
>but in windows XP does not pass of 40 seconds... :(
You mean that 40 secs in pg_dump under Win XP
crashes, and therefore you have a WinXP problem?
Or do you mean that pg_dump takes 40 secs to
complete under WinXP and 50 minutes under W2K and
therefore you have a W2K problem?
In fact, either 15MB/40secs= 375KBps or
200MB/40secs= 5MBps is _slow_, so there's a problem under either platform!
>This happens with 8.1 and version 8.0, somebody
>passed for the same situation?
>
>It will be that a configuration in the priorities of the exists
>processes ? in Windows XP the processing of schemes goes 70% and
>constant accesses to the HardDisk, while that in windows 2000 it does
>not pass of 3%.
Assuming Win XP completes the dump, the first thing to do is
*don't use W2K*
M$ has stopped supporting it in anything but absolutely minimum fashion anyway.
_If_ you are going to use an M$ OS you should be using WinXP.
(You want to pay licensing fees for your OS, but
you are using free DB SW? Huh? If you are
trying to save $$$, use Open Source SW like Linux
or *BSD. pg will perform better under it, and it's cheaper!)
Assuming that for some reason you can't/won't
migrate to a non-M$ OS, the next problem is the
slow HD IO you are getting under WinXP.
What is the HW involved here? Particularly the
HD subsystem and the IO bus(es) it is plugged into?
For some perspective, Raw HD average IO rates for
even reasonably modern 7200rpm HD's is in the
~50MBps per HD range. Top of the line 15Krpm
SCSI and FC HD's have raw average IO rates of
just under 80MBps per HD as of this post.
Given that most DB's are not on 1 HD (if you DB
_is_ on only 1 HD, change that ASAP before you
lose data...), for anything other than a 2 HD
RAID 1 set I'd expect raw HD average IO rates to be at least 100MBps.
If you are getting >= 100MBps of average HD IO,
you should be getting > 5MBps during pg_dump, and certainly > 375MBps!
Ron
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump slow
@ 2005-11-30 16:56 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2005-11-30 16:56 UTC (permalink / raw)
To: Ron <rjpeace@earthlink.net>; +Cc: pgsql-performance; Franklin Haut <franklin.haut@gmail.com>
> At 08:35 AM 11/30/2005, Franklin Haut wrote:
> >Hi
> >
> >i´m using PostgreSQL on windows 2000, the pg_dump take around 50 minutes
> >to do backup of 200Mb data ( with no compression, and 15Mb with
> >compression),
>
> Compression is reducing the data to 15/200= 3/40= 7.5% of original size?
>
> >but in windows XP does not pass of 40 seconds... :(
>
> You mean that 40 secs in pg_dump under Win XP
> crashes, and therefore you have a WinXP problem?
>
> Or do you mean that pg_dump takes 40 secs to
> complete under WinXP and 50 minutes under W2K and
> therefore you have a W2K problem?
I think he is saying the time to dump does not take more than 40 seconds, but I'm not sure.
> In fact, either 15MB/40secs= 375KBps or
> 200MB/40secs= 5MBps is _slow_, so there's a problem under either platform!
5 mb/sec dump output from psql is not terrible or even bad, depending on hardware.
> >not pass of 3%.
> Assuming Win XP completes the dump, the first thing to do is
> *don't use W2K*
XP is not a server platform. Next level up is 2003 server. Many organizations still have 2k deployed. About half of my servers still run it. Anyways, the 2k/xp issue does not explain why there is a performance problem.
> M$ has stopped supporting it in anything but absolutely minimum fashion
> anyway.
> _If_ you are going to use an M$ OS you should be using WinXP.
> (You want to pay licensing fees for your OS, but
> you are using free DB SW? Huh? If you are
> trying to save $$$, use Open Source SW like Linux
> or *BSD. pg will perform better under it, and it's cheaper!)
I would like to see some benchmarks supporting those claims. No comment on licensing issue, but there are many other factors in considering server platform than licensing costs. That said, there were several win32 specific pg performance issues that were rolled up into the 8.1 release. So for win32 you definitely want to be running 8.1.
> Assuming that for some reason you can't/won't
> migrate to a non-M$ OS, the next problem is the
> slow HD IO you are getting under WinXP.
Problem is almost certainly not related to disk unless there is a imminent disk failure. Could be TCP/IP issue (are you running pg_dump from remote box?), or possibly a network driver issue or some other weird software issue. Can you determine if disk is running normally with respect to other applications? Is this a fresh win2k install? A LSP, virus scanner, backup software, or some other garbage can really ruin your day.
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* pg_dump slow
@ 2005-12-01 12:29 Franklin Haut <franklin.haut@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Franklin Haut @ 2005-12-01 12:29 UTC (permalink / raw)
To: 'Ron' <rjpeace@earthlink.net>; pgsql-performance
I Maked a new install on machine this night, and the same results, on
console localhost
Windows 2000 Server
Version 5.00.2195
PG Version 8.1
Franklin
>Franlin: are you making pg_dump from local or remote box and is this a
>clean install? Try fresh patched win2k install and see what happens.
He claimed this was local, not network. It is certainly an
intriguing possibility that W2K and WinXP handle bytea
differently. I'm not competent to comment on that however.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump slow
@ 2005-12-01 16:15 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2005-12-01 16:15 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-performance
> >Franlin: are you making pg_dump from local or remote box and is this
a
> >clean install? Try fresh patched win2k install and see what happens.
> He claimed this was local, not network. It is certainly an
> intriguing possibility that W2K and WinXP handle bytea
> differently. I'm not competent to comment on that however.
can you make small extraction of this file (~ 100 rows), zip to file and
send to me off list? I'll test it vs. a 2000 and xp server and try to
reproduce your results.
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump slow
@ 2005-12-02 16:28 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2005-12-02 16:28 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-performance
>
> That was the command used to restore a database
>
> pg_restore.exe -i -h localhost -p 5432 -U postgres -d temp2 -v
> "D:\d\temp.bkp"
>
> The database was created before using LATIN1 charset
>
> With 100 rows you can´t feel the test, then I decided send the whole
> table.
>
> Very Thanks
>
> Franklin Haut
How are you dumping out your archive? I confirmed unreasonably slow dump with pg_dump -Z temp2 > temp2.bkp on windows 2000 server. I normally use bzip to compress my dumps.
Can you measure time to dump uncompressed and also with bzip and compare?
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: pg_dump slow
@ 2005-12-02 16:33 Merlin Moncure <merlin.moncure@rcsonline.com>
0 siblings, 0 replies; 7+ messages in thread
From: Merlin Moncure @ 2005-12-02 16:33 UTC (permalink / raw)
To: Franklin Haut <franklin.haut@gmail.com>; +Cc: pgsql-performance
> How are you dumping out your archive? I confirmed unreasonably slow
dump
> with pg_dump -Z temp2 > temp2.bkp on windows 2000 server. I normally
use
> bzip to compress my dumps.
>
> Can you measure time to dump uncompressed and also with bzip and
compare?
>
> Merlin
oops...cancel that. I was dumping the wrong database. Dumping your
table from localhost on a dual Opteron win2k server took a few seconds
with Z=0 and Z=9.
Merlin
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-12-02 16:33 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-11-30 13:35 pg_dump slow Franklin Haut <franklin.haut@gmail.com>
2005-11-30 13:56 ` Ron <rjpeace@earthlink.net>
2005-11-30 16:56 Re: pg_dump slow Merlin Moncure <merlin.moncure@rcsonline.com>
2005-12-01 12:29 pg_dump slow Franklin Haut <franklin.haut@gmail.com>
2005-12-01 16:15 Re: pg_dump slow Merlin Moncure <merlin.moncure@rcsonline.com>
2005-12-02 16:28 Re: pg_dump slow Merlin Moncure <merlin.moncure@rcsonline.com>
2005-12-02 16:33 Re: pg_dump slow Merlin Moncure <merlin.moncure@rcsonline.com>
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