Thread: Connecting process to PostgreSQL works stunningly slow
Hi, We have a problem with PostgreSQL 8.1 on a production server (Windows 2003 Server, Xeon 3GHz, 1GB)); application placed on it run for a month without problems. It was great. But yesterday... Any connection to the server, from application or pgAdmin (both previously working fine), is slow. I mean, I have to wait *minutes* for even login to succeed. Even when connecting from the same machine, with pgAdmin, with no users connected to it. Restarting the service is slow, opening the database is slow, backup is slow. Sometimes minutes, I mean. Like there was something wrong with listening for connections - sometimes I got an information that server on [my_ip_here:my_port_here] is not listening. The machine runs other services, but there is no heavy load on it and users do not have any problems with e.g. (don't shoot) MS SQL Server. Since I use IPs only, DNS issues should not be involved. There were no changes to current configuration, autovacuum is on and it seems to work, according to the log (runs every 60s). There are two tiny tables (no more than a hundred rows each, barely a database), there are only updates on them (no more than a few hundreds a day), I know it's weird but that's how it should work. I googled and searched on Groups. I'm rather new with PostgreSQL and dealing with the crashed production server is not nice :) Tommorrow I will try connection without md5 and look if the drive is OK (defragmentation?), but maybe someone has any idea, what else could I check? It is very depressing, since the database is so tiny... If nothing else helps, I will of course pg_dump, reinstall PostgreSQL server and wait for a month or so *gulp*. Thanks in advance, Bart Golda
Bart Golda wrote: > Hi, > > We have a problem with PostgreSQL 8.1 on a production server (Windows > 2003 Server, Xeon 3GHz, 1GB)); application placed on it run for a month > without problems. It was great. But yesterday... > > Any connection to the server, from application or pgAdmin (both > previously working fine), is slow. I mean, I have to wait *minutes* for > even login to succeed. Even when connecting from the same machine, with > pgAdmin, with no users connected to it. Restarting the service is slow, > opening the database is slow, backup is slow. Sometimes minutes, I > mean. Like there was something wrong with listening for connections - > sometimes I got an information that server on [my_ip_here:my_port_here] > is not listening. The machine runs other services, but there is no > heavy load on it and users do not have any problems with e.g. (don't > shoot) MS SQL Server. Firstly, make sure PostgreSQL is logging everything you'd like. Connections in particular might be interesting. Secondly, check to see if you have a lot of activity with PostgreSQL on: 1. CPU 2. Disk I/O If not, check your anti-virus system - if it's checking on every file access that PG makes that'll grind things to a halt. -- Richard Huxton Archonet Ltd
Thanks for hints! In case of anyone having similar problem - it can be easily solved. Windows 2003 Server deserves painful death! It just stopped answering pings and most of the network traffic was terribly slow (MS SQL Server worked, though... strange). Restarting the Windows server helped - everything works now. We could not do it before (very important users, MS SQL Server, other wonderful things). I suppose the problem was not in any way connected with PostgreSQL. Someone told me that many W2003S configurations (especially with some server services running) require weekly restart to work well. Just in case, PostgreSQL server will be probably moved in some time to Solaris or some *nix... Thanks, Bart Golda