Thread: pg_dump slow
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
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
Hi, Yes, my problem is that the pg_dump takes 40 secs to complete under WinXP and 50 minutes under W2K! The same database, the same hardware!, only diferrent Operational Systems. The hardware is: Pentium4 HT 3.2 GHz 1024 Mb Memory HD 120Gb SATA Im has make again the test, and then real size of database is 174Mb (avaliable on pg_admin, properties) and the file size of pg_dump is 18Mb ( with command line pg_dump -i -F c -b -v -f "C:\temp\BackupTest.bkp" NameOfDatabase ). The time was equal in 40 seconds on XP and 50 minutes on W2K, using PG 8.1 Unhappyly for some reasons I cannot use other platforms, I need use PG on Windows, and must be W2K. Is strange to have a so great difference in the time of execution of dump, therefore the data are the same ones and the archive is being correctly generated in both OS. Franklin -----Mensagem original----- De: Ron [mailto:rjpeace@earthlink.net] Enviada em: quarta-feira, 30 de novembro de 2005 10:57 Para: Franklin Haut; pgsql-performance@postgresql.org Assunto: Re: [PERFORM] pg_dump slow 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
> 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 myservers 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 factorsin considering server platform than licensing costs. That said, there were several win32 specific pg performanceissues 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 yourunning pg_dump from remote box?), or possibly a network driver issue or some other weird software issue. Can you determineif 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
Franklin Haut wrote: > Hi, > > Yes, my problem is that the pg_dump takes 40 secs to complete under > WinXP and 50 minutes under W2K! The same database, the same hardware!, > only diferrent Operational Systems. > > The hardware is: > Pentium4 HT 3.2 GHz > 1024 Mb Memory > HD 120Gb SATA There have been reports of very slow network performance on Win2k systems with the default configuration. You'll have to check the archives for details I'm afraid. This might apply to you. If you're happy that doesn't affect you then I'd look at the disk system - perhaps XP has newer drivers than Win2k. What do the MS performance-charts show is happening? Specifically, CPU and disk I/O. -- Richard Huxton Archonet Ltd
Complementing... The test was maked at the same machine ( localhost ) at Command-Prompt, no client´s connected, no concurrent processes only PostgreSQL running. In windows XP, exists much access to the processor (+- 70%) and HD (I see HD Led allways on), while in the W2K almost without activity of processor (3%)and little access to the HardDisk (most time of the led HD is off). Look, the database has 81 Tables, one of these, has 2 fields ( one integer and another ByteA ), these table as 5.150 Records. I´m Dumpped only this table and the file size is 7Mb (41% of total (17MB is the total)) was very slow.... Then I Maked Backup of the others tables was fast! So i´m conclused that pg_dump and pg_restore is very slow when manipulates ByteA type on W2K!, is this possible ? Franklin -----Mensagem original----- De: Merlin Moncure [mailto:merlin.moncure@rcsonline.com] Enviada em: quarta-feira, 30 de novembro de 2005 13:57 Para: Ron Cc: pgsql-performance@postgresql.org; Franklin Haut Assunto: RE: [PERFORM] pg_dump slow > 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
At 12:27 PM 11/30/2005, Richard Huxton wrote: >Franklin Haut wrote: >>Hi, >>Yes, my problem is that the pg_dump takes 40 secs to complete under >>WinXP and 50 minutes under W2K! The same database, the same hardware!, >>only diferrent Operational Systems. >>The hardware is: Pentium4 HT 3.2 GHz >> 1024 MB Memory Get the RAM up to at least 4096MB= 4GB for a DB server. 4 1GB DIMMs or 2 2GB DIMMS are ~ the same $$ as a HD (~$250-$300 US) and well worth the expense. >> HD 120GB SATA "b" is "bit". "B" is "Byte". I made the correction. You have =1= HD? and you are using it for everything: OS, pq, swap, etc? Very Bad Idea. At the very least, a DB server should have the OS on separate spindles from pg, and pg tables should be on something like a 4 HD RAID 10. At the very least. DB servers are about HDs. Lots and lots of HDs compared to anything outside the DB realm. Start thinking in terms of at least 6+ HD's attached to the system in question (I've worked on system with literally 100's). Usually only a few of these are directly attached to the DB server and most are attached by LAN or FC. But the point remains: DBs and DB servers eat HDs in prodigious quantities. >There have been reports of very slow network performance on Win2k >systems with the default configuration. You'll have to check the >archives for details I'm afraid. This might apply to you. Unless you are doing IO across a network, this issue will not apply to you. By default W2K systems often had a default TCP/IP packet size of 576B and a tiny RWIN. Optimal for analog modems talking over noisy POTS lines, but horrible for everything else Packet size needs to be boosted to 1500B, the maximum. RWIN should be boosted to _at least_ the largest number <= 2^16 that you can use without TCP scaling. Benchmark network IO rates. Then TCP scaling should be turned on and RWIN doubled and network IO benched again. Repeat until there is no performance benefit to doubling RWIN or you run out of RAM that you can afford to toss at the problem or you hit the max for RWIN (very doubtful). >If you're happy that doesn't affect you then I'd look at the disk >system - perhaps XP has newer drivers than Win2k. I'll reiterate: Do _not_ run a production DB server on W2K. M$ has obsoleted the platform and that it is not supported _nor_ any of reliable, secure, etc. etc. A W2K based DB server, particularly one with a connection to the Internet, is a ticking time bomb at this point. Get off W2K as a production platform ASAP. Take to your CEO/Dean/whatever you call your Fearless Leader if you have to. Economically and probably performance wise, it's best to use an Open Source OS like Linux or *BSD. However, if you must use M$, at least use OS's that M$ is actively supporting. Despite M$ marketing propaganda and a post in this thread to the contrary, you =CAN= often run a production DB server under WinXP and not pay M$ their usurious licensing fees for W2003 Server or any of their other products with "server" in the title. How much RAM and how many CPUs you want in your DB server is the main issue. For a 1P, <= 4GB RAM vanilla box, WinXp will work just fine. >What do the MS performance-charts show is happening? Specifically, >CPU and disk I/O. His original post said ~3% CPU under W2K and ~70% CPU under WinXP Ron