On Apr 29, 2008, at 4:54 PM, Dan Armbrust wrote:
I have an app that we were load testing - it maintains a pool of
connections to PostgreSQL - 8.3.1
Suddenly, after running fine for weeks, the app hung - unable to get a
free connection from the pool.
select * from pg_stat_activity;" shows me that most of my connections
in a COMMIT phase:
03:05:37.73064-05 | 2008-04-24 03:05:38.419796-05 | 2008-04-24
02:11:53.908518-05 | 127.0.0.1 | 53807
16385 | ispaaa | 953 | 16384 | pslogin | COMMIT | f
| 2008-04-24
While some are in a SELECT:
16385 | ispaaa | 1181 | 16384 | pslogin | select
dynamichos0_.ethernetmacaddr as ethernet1_0_, dynamichos0_.ipaddr as
ipaddr0_, dynamichos0_.cpemac as cpemac0_, dynamichos0_.regtime as
regtime0_, dynamichos0_.leasetime as leasetime0_,
dynamichos0_.last_updated as last5_0_ from iphost dynamichos0_, cpe
cpe1_ where dynamichos0_.cpemac=cpe1_.cpemac and 1=1 and
dynamichos0_.ethernetmacaddr=$1 and dynamichos0_.cpemac=$2 and
cpe1_.regBaseId=$3 and dynamichos0_.ipaddr<>$4 | f |
2008-04-24 03:05:37.734041-05 | 2008-04-24 03:05:38.405854-05 |
2008-04-24 02:41:54.413337-05 | 127.0.0.1 | 55363
Perhaps VACUUM had something to do with it?:
16385 | ispaaa | 8956 | 16384 | pslogin | delete from iphost
where leasetime<$1 | f | 2008-04-24 18:43:29.920069-05 |
2008-04-24 18:43:30.116435-05 | 2008-04-24 18:41:59.071032-05 |
127.0.0.1 |
49069 16385 | ispaaa | 1618 | 10 | postgres | autovacuum:
VACUUM public.iphost | f | 2008-04-24 03:05:13.212436-05 |
2008-04-24 03:05:13.212436-05 | 2008-04-24 03:05:12.526611-05 |
|
Where should I begin to look for the source of this problem?
Thanks for any info,
Well, you can look in pg_locks to see if there are outstanding locks
waiting on already granted conflicting locks. This isn't a deadlock
situation, though, Postgres will detect those and kill one of the
offending processes so that the others can finish (it'll leave a log
message about it, too). My guess is that you've got some long running
write/ddl query that's go a heavy lock on iphost or you have a LOT of
queries that need heavy locks hitting the table at once. How large is
iphost? How many of those deletes have you got going on it? Do you
also have concurrent updates running against it? Do you have any ddl
queries running against it (alter tables, index builds/drops, etc...)?
Erik Jones
DBA | Emma®
erik@xxxxxxxxxx
800.595.4401 or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
