ENC: RES: [PERFORM] pg_dump slow - Solution
Hi, Finally i found the problem of slow backup/restore, i´m only instaled de Windows 2000 Service Pack 4... :) Thanks to all Franklin -Mensagem original- De: Richard Huxton [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 30 de novembro de 2005 14:28 Para: Franklin Haut Cc: 'Ron'; pgsql-performance@postgresql.org Assunto: Re: RES: [PERFORM] pg_dump slow 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] pg_dump slow
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] pg_dump slow
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[PERFORM] pg_dump slow
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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] pg_dump slow
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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PERFORM] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
RES: [PERFORM] pg_dump slow
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:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
RES: [PERFORM] pg_dump slow
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:[EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: [PERFORM] pg_dump slow
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: RES: [PERFORM] pg_dump slow
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 wrong. default MTU for windows 2000 server is 1500, as was NT4. http://support.microsoft.com/?id=140375 However tweaking rwin is certainly something to look at. 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. wrong again. WIN2k gets free security hotfixes and paid support until 2010. http://www.microsoft.com/windows2000/support/lifecycle/ 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. wrong again!! There is every reason to believe win2k is *more* secure than win2003 sever because it is a more stable platform. This of course depends on what other services are running, firewall issues, etc etc. 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. I encourage use of open source software. However encouraging other people to spontaneously switch hardware/software platforms (especially when they just stated when win2k is a requirement) is just or at least not helpful. 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 you are on a roll here. You must not be aware of 10 connection limit for win2k pro and winxp pro. http://winhlp.com/WxConnectionLimit.htm There are hackerish ways of getting around this which are illegal. Cheating to get around this by pooling connections via tcp proxy for example is also against EULA (and, in my opinion, unethical). 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. Now, who is guilty of propaganda here? Also, your comments regarding hard disks while correct in the general sense are not helpful. This is clearly not a disk bandwidth problem. 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 Slow performance in extraction of bytea column strongly suggests tcp/ip. issue. I bet if you blanked out bytea column pg_dump will be fast. 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. Merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: RES: [PERFORM] pg_dump slow
At 05:13 PM 11/30/2005, Merlin Moncure wrote: 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 wrong. default MTU for windows 2000 server is 1500, as was NT4. http://support.microsoft.com/?id=140375 LOL. Good to see it is now. I got bit by the problem when it wasn't. However tweaking rwin is certainly something to look at. 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. wrong again. WIN2k gets free security hotfixes and paid support until 2010. http://www.microsoft.com/windows2000/support/lifecycle/ I've _lived_ what I'm talking about. I've built some of the largest M$ installations in existence at the time of their deployment. Type of Support Availability Mainstream * Paid-per-incident support * Free hotfix support June 30, 2005 Extended * Hourly support * Paid hotfix support June 30, 2010 Security hotfixes Free to all customers through March 31, 2010 From your own source. And good luck getting M$ to give you free support for anything except what _They_ consider to be Their Fault without paying them a boatload of . The standard M$ party line at this point is Upgrade, Upgrade, Upgrade. ...Or pay us so much to do it that we feel it makes economic sense for M$ to Play Ball. 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. wrong again!! There is every reason to believe win2k is *more* secure than win2003 sever because it is a more stable platform. This of course depends on what other services are running, firewall issues, etc etc. You evidently do not have a very serious background in network or systems security or professional experience would tell you that your above sentence is dangerously misguided. Reality is that platforms stay marginally secure _only_ by constant patching of newly discovered exploits and never ceasing vigilance looking for new exploits to patch. Regardless of platform. Obsoleted platforms are at greater risk because the White Hats are no longer paying as much attention to them and the Black Hats are basically opportunistic parasites. They play with anything and everything they can get their hands on in the hopes of finding exploitable security flaws. 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. I encourage use of open source software. However encouraging other people to spontaneously switch hardware/software platforms (especially when they just stated when win2k is a requirement) is just or at least not helpful. 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 you are on a roll here. You must not be aware of 10 connection limit for win2k pro and winxp pro. http://winhlp.com/WxConnectionLimit.htm I'm excruciatingly aware of the 10 connection limit AND how stupid it is. It's one of the reasons, along with what M$ thought they could get away with charging to increase it, M$ got thrown out of my server rooms. Also, we are talking about a _DB_ server. Not a web server or some other function that deals with relatively light load per connection. Just how many open active DB connections do want active concurrently? Not Many. Once all the HW is being utilized to full capacity, DBs get best performance by being asked to do as little as possible concurrently beyond that. Long before you will want more than 10 active open DB connections banging on modest HW you are going to want a queuing system in front of the DB in order to smooth behavior.By the time you _really_ need to support lots of open active DB connections, you will in a position to spend more money (and probably will have to on both better HW and better SW). There are hackerish ways of getting around this which are illegal. Cheating to get around this by pooling connections via tcp proxy for example is also against EULA (and, in my opinion, unethical). I'm sorry you evidently feel your income stream is threatened, but there is no way that is either immoral or illegal for anyone to use the industry standard layered architecture of having a DB connection layer separate from a Queuing system. M$MQ is provided _specifically_ for
