Обсуждение: 8.0 beta1 and XP SP2
Hi, I'm wondering if anyone has tried running PgSQL on XP SP2? I have a problem with it - quite a big one. I have a perl DBI script which is a migration tool from old MySQL database format to a new PgSQL database. So here is a quick summary of the situation. MySQL (win32) - perl - PgSQL (Linux server) Runs fine, full speed, the script finishes its work in 20 minutes. Next, I move PgSQL to the windows side. MySQL (win32) - perl - PgSQL (win32, XP SP1) Runs fine, just a bit slower (like 25 minutes) In both previous cases there were 100% CPU load, HD activity, etc. - two databases raping poor windows XP workstation. Next, after SP2 installation - that's here the fun comes. The process can take hours to complete. Actually, I never had a patience to see it finished - last time I aborted the process after 2 hours and the damn thing just finished about 50% of its job. I switched PgSQL back to Linux without changing anything else - no performance problems. I uninstalled SP2 and checked again - all is ok, all goes full speed, 25 minutes for task completion. Reinstalled SP2 - and the problem came back. So for me it's quite obvious that SP2 has screwed up PgSQL in some way. I've tried several web forums, IRC - so far nobody seems to get into the same situation since I'm likely to be the only PgSQL user stupid enough to try SP2 :) Now some details on what script does. It's, like I said, a migration script - it takes data from one database, changes it slightly and puts it to another one, using simple inserts. Nothing special. Although pg_restore seems to work just fine even under SP2, no performance problems. There's more. I've tried PowerGres database as well. It experiences the same performance hit, no difference at all. Some details on the system - WinXP Pro, P4 2.4Ghz, 1GB RAM, 200GB SATA Seagate drive. I've disabled that firewall thingy and data execution prevention - again, nothing has changed. I'm out of ideas... Thanks for any input -- Oleg Letsinsky, Software Developer
Oleg Letsinsky wrote: >Hi, > >I'm wondering if anyone has tried running PgSQL on XP SP2? > >I have a problem with it - quite a big one. I have a perl DBI script >which is a migration tool from old MySQL database format to a new PgSQL >database. So here is a quick summary of the situation. > >MySQL (win32) - perl - PgSQL (Linux server) Runs fine, full speed, the >script finishes its work in 20 minutes. > >Next, I move PgSQL to the windows side. > >MySQL (win32) - perl - PgSQL (win32, XP SP1) Runs fine, just a bit slower >(like 25 minutes) > >In both previous cases there were 100% CPU load, HD activity, etc. - two >databases raping poor windows XP workstation. > >Next, after SP2 installation - that's here the fun comes. The process can >take hours to complete. Actually, I never had a patience to see it >finished - last time I aborted the process after 2 hours and the damn >thing just finished about 50% of its job. I switched PgSQL back to Linux >without changing anything else - no performance problems. I uninstalled >SP2 and checked again - all is ok, all goes full speed, 25 minutes for >task completion. Reinstalled SP2 - and the problem came back. So for me >it's quite obvious that SP2 has screwed up PgSQL in some way. I've >tried several web forums, IRC - so far nobody seems to get into the >same situation since I'm likely to be the only PgSQL user stupid enough >to try SP2 :) > > What is the CPU load under SP2? >Now some details on what script does. It's, like I said, a migration >script - it takes data from one database, changes it slightly and puts it >to another one, using simple inserts. Nothing special. Although >pg_restore seems to work just fine even under SP2, no performance >problems. > > If pg_restore works prehaps it is not a PostgreSQL problem.... >There's more. I've tried PowerGres database as well. It experiences the >same performance hit, no difference at all. > >Some details on the system - WinXP Pro, P4 2.4Ghz, 1GB RAM, 200GB SATA >Seagate drive. I've disabled that firewall thingy and data execution >prevention - again, nothing has changed. I'm out of ideas... > > Try mod your script to dump back to the mysql database. Check if you get weird performance issues doing that. I think assuming it is PostgreSQL at this point is jumping the gun. Maybe active states perl doesn't run so well on SP2, or it could even be mysql. >Thanks for any input > > Pleasure Regards Justin
> Hi, > > I'm wondering if anyone has tried running PgSQL on XP SP2? > > I have a problem with it - quite a big one. I have a perl DBI > script which is a migration tool from old MySQL database > format to a new PgSQL database. So here is a quick summary of > the situation. > > MySQL (win32) - perl - PgSQL (Linux server) Runs fine, full > speed, the script finishes its work in 20 minutes. > > Next, I move PgSQL to the windows side. > > MySQL (win32) - perl - PgSQL (win32, XP SP1) Runs fine, just > a bit slower (like 25 minutes) > > In both previous cases there were 100% CPU load, HD > activity, etc. - two databases raping poor windows XP workstation. Are both MySQL and PgSQL Win32 on the same machine, or two different machines? > Next, after SP2 installation - that's here the fun comes. The > process can take hours to complete. Actually, I never > had a patience to see it finished - last time I aborted > the process after 2 hours and the damn thing just finished > about 50% of its job. I switched PgSQL back to Linux without > changing anything else - no performance problems. I uninstalled > SP2 and checked again - all is ok, all goes full speed, 25 > minutes for task completion. Reinstalled SP2 - and the > problem came back. So for me it's quite obvious that SP2 has > screwed up PgSQL in some way. I've tried several web > forums, IRC - so far nobody seems to get into the same > situation since I'm likely to be the only PgSQL user stupid > enough to try SP2 :) Is the machine still pegged at 100% CPU, or is the CPU idle? What about the I/O? > Now some details on what script does. It's, like I said, a > migration script - it takes data from one database, changes > it slightly and puts it to another one, using simple inserts. > Nothing special. Although pg_restore seems to work just fine > even under SP2, no performance problems. Does your script run with just one connection, or does it open new connections often? > There's more. I've tried PowerGres database as well. It > experiences the same performance hit, no difference at all. Interesting. > Some details on the system - WinXP Pro, P4 2.4Ghz, 1GB RAM, > 200GB SATA Seagate drive. I've disabled that firewall thingy > and data execution prevention - again, nothing has changed. > I'm out of ideas... With disable firewall I assume you mean disable completely, and not just set "postgres can do whatevever it wants"? If not, disable it completely :-) Are you running any antivirus on the machine? If so, try uninstalling that too. I've seen AV products behave pretty differently on SP1 and SP2... //Magnus
Hi Magnus and everyone, > Are both MySQL and PgSQL Win32 on the same machine, or two different > machines? They are on the same machine. > Is the machine still pegged at 100% CPU, or is the CPU idle? What about > the I/O? Sorry for not mentioning this before - after SP2 installation CPU utilization has dropped almost to zero. Even task manager consumes more CPU than both BD's. But the HD activity is still quite high. I can send perfmon log for CPU/Memory/Network/Physical Disk to anyone who interested. 5 phases: database setup through psql/idle time/my script works for a couple of minutes/idle/database restored through pg_restore. Just 26 kb in bzip'ed form :) > Does your script run with just one connection, or does it open new > connections often? No, sure it creates DB connections on the start. TCPView confirms this - no connection has been made since the start. >> Some details on the system - WinXP Pro, P4 2.4Ghz, 1GB RAM, 200GB SATA >> Seagate drive. I've disabled that firewall thingy and data execution >> prevention - again, nothing has changed. I'm out of ideas... > With disable firewall I assume you mean disable completely, and not just > set "postgres can do whatevever it wants"? If not, disable it completely > :-) I even stopped and disabled the firewall service. Doesn't help much. > Are you running any antivirus on the machine? If so, try uninstalling > that too. I've seen AV products behave pretty differently on SP1 and > SP2... Tried that as well. Uninstalled TCPView Pro. Removed QoS from network connections. Still nothing. -- Oleg Letsinsky, Software Developer
Hi Justin, > Try mod your script to dump back to the mysql database. Check if you > get weird performance issues doing that. I think assuming it is > PostgreSQL at this point is jumping the gun. Maybe active states perl > doesn't run so well on SP2, or it could even be mysql. I've tried cygwin perl as well. But the point is that I need just to change the PgSQL connection string in the script so it points to my linux machine - and the performance is back. Please see my reply to Magnus, I added some new info there as well. -- Oleg Letsinsky, Software Developer
> -----Original Message----- > From: pgsql-hackers-win32-owner@postgresql.org > [mailto:pgsql-hackers-win32-owner@postgresql.org] On Behalf > Of Oleg Letsinsky > Sent: 27 August 2004 11:40 > To: postgresql > Subject: Re: [pgsql-hackers-win32] 8.0 beta1 and XP SP2 > > Hi Justin, > > > Try mod your script to dump back to the mysql database. > Check if you > > get weird performance issues doing that. I think assuming it is > > PostgreSQL at this point is jumping the gun. Maybe active > states perl > > doesn't run so well on SP2, or it could even be mysql. > > I've tried cygwin perl as well. But the point is that I need > just to change the PgSQL connection string in the script so > it points to my linux machine - and the performance is back. In your reply to Magnus though, you said that the win32 PostgreSQL and MySQL are on the same machine - so you almost certainly would get much better performance off-loading one of the DBMS's to a separate box. Regards, Dave
Hi Dave, > In your reply to Magnus though, you said that the win32 PostgreSQL and > MySQL are on the same machine - so you almost certainly would get much > better performance off-loading one of the DBMS's to a separate box. Sure, but unlikely that 10x faster. And the performance on a single Win32 box were *not* problematic until the SP2. I hope it's clear enough now. -- Oleg Letsinsky, Software Developer
> > Are both MySQL and PgSQL Win32 on the same machine, or two > different > > machines? > > They are on the same machine. Ok. Grasping at some straws here, this could possiblyi be related to the context switch storm reported on unix platforms. Can you check the counter for context switches in perfmon? (It's under "System"). It shouldn't be directly related since it's just one postgres backend, but still. And compare this value to when running with SP1. > > Is the machine still pegged at 100% CPU, or is the CPU idle? What > > about the I/O? > > Sorry for not mentioning this before - after SP2 installation > CPU utilization has dropped almost to zero. Even task manager > consumes more CPU than both BD's. But the HD activity is > still quite high. I can send perfmon log for > CPU/Memory/Network/Physical Disk to anyone who interested. 5 > phases: database setup through psql/idle time/my script works > for a couple of minutes/idle/database restored through pg_restore. > Just 26 kb in bzip'ed form :) Does the machine otherwise appear sluggish when doing things that are I/O bound, or can you do things like copy a large file at about the same speed as when idle? Simple way to check if the I/O system is saturated. I don't see why that would change with SP2, but... > > Does your script run with just one connection, or does it open > > new connections often? > > No, sure it creates DB connections on the start. TCPView > confirms this - no connection has been made since the start. Ok. There goes that idea. > >> Some details on the system - WinXP Pro, P4 2.4Ghz, 1GB RAM, 200GB > >> SATA Seagate drive. I've disabled that firewall thingy and data > >> execution prevention - again, nothing has changed. I'm > out of ideas... > > > With disable firewall I assume you mean disable completely, and not > > just set "postgres can do whatevever it wants"? If not, disable it > > completely > > :-) > > I even stopped and disabled the firewall service. Doesn't help much. Ok. Try disabling the pgsql stats collector. Again, don't directly see why it should have an effect, but if you're somehow getting lookup problems for localhost or so. Can someone else confirm either that they also see a slowdown on SP2, or that they do *not*? Specifically when loading a bunch of data is best, but in general for "heavy load" situation would be enough. //Magnus
> -----Original Message----- > From: pgsql-hackers-win32-owner@postgresql.org > [mailto:pgsql-hackers-win32-owner@postgresql.org] On Behalf > Of Magnus Hagander > Sent: 27 August 2004 12:02 > To: oleg@ourmx.com; PgSQL Win32 developers > Subject: Re: [pgsql-hackers-win32] 8.0 beta1 and XP SP2 > > Can someone else confirm either that they also see a slowdown > on SP2, or that they do *not*? Specifically when loading a > bunch of data is best, but in general for "heavy load" > situation would be enough. This is *very* far from scientific, but, I tried loading the same dumpfile on 2 systems: Box 1: Slackware Linux Dual PIII 1GHz 15K RPM U160 SCSI Disk 1GB RAM PostgreSQL 7.3.2 roughly tuned (yes, I know it needs upgrading) 6-8 other PostgreSQL users. Load time: 24 seconds. Box 2: Windows XP Pro SP2 Centrino 2.7GHz 7200 RPM IDE disk (2.5") 1GB RAM PostgreSQL 8.0 Dev out of the box 0 other users, but runnning various other apps. Load time: 31 seconds. I can't honestly say I've noticed any real slowdown since SP2 with the exception of DNS lookups which are now dog slow, however, our ISP added access controls to their servers at about the same time I upgraded so it may be that. Regards, Dave.
Użytkownik Oleg Letsinsky napisał:
>Reinstalled SP2 - and the problem came back. So for me
>it's quite obvious that SP2 has screwed up PgSQL in some way. I've
>tried several web forums, IRC - so far nobody seems to get into the
>same situation since I'm likely to be the only PgSQL user stupid enough
>to try SP2 :)
>
>
Hi Oleg,
Try this:
1. Click *Start*, click *Run*, type sysdm.cpl, and then click *OK* to
open Control Panel.
2. Click the *Advanced *tab, click *Performance*, and then click
*Settings*.
3. In* Performance Options*, click the *Data Execution Prevention* tab.
4. Click* Turn on DEP for all programs and services except for those
I select*, and then click * Add*.
5. In the *Open *dialog box, locate and then click the application.
6. Click *Open*, click *Apply*, and then click *OK*. When you are
prompted to restart your system, click *OK*.
Maybe there you'll find the solution:
http://support.microsoft.com/default.aspx?kbid=875351
http://support.microsoft.com/default.aspx?kbid=884130
http://support.microsoft.com/default.aspx?kbid=842242
http://support.microsoft.com/default.aspx?kbid=875357
I'll wait with SP2, it seems to be not really usable.
Best regards
Rony
On Sat, Aug 28, 2004 at 05:08:50PM +0200, Ronald Kuczek wrote: > > Hi Oleg, Hi > > Try this: > > 1. Click *Start*, click *Run*, type sysdm.cpl, and then click *OK* to > open Control Panel. > 2. Click the *Advanced *tab, click *Performance*, and then click > *Settings*. > 3. In* Performance Options*, click the *Data Execution Prevention* tab. > 4. Click* Turn on DEP for all programs and services except for those > I select*, and then click * Add*. > 5. In the *Open *dialog box, locate and then click the application. > 6. Click *Open*, click *Apply*, and then click *OK*. When you are > prompted to restart your system, click *OK*. No, thanks. I've found the reason just yesterday (well, kind of) - installed XP on a clean machine, applied SP2 and tested everything. No problem. I just think that my installation is screwed - after just 3 months of existence. Damn... Like in haiku: "Yesterday it is working. Today it is not. Windows is like that." I guess I'm too much of a wuss to get into the trouble of reinstalling it again, so I'm back in Linux totally. Sorry for troubles, guys.