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=3Dcpe1_.cpemac and 1=3D1 and
> dynamichos0_.ethernetmacaddr=3D$1 and dynamichos0_.cpemac=3D$2 and
> cpe1_.regBaseId=3D$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=20=20
waiting on already granted conflicting locks. This isn't a deadlock=20=20
situation, though, Postgres will detect those and kill one of the=20=20
offending processes so that the others can finish (it'll leave a log=20=20
message about it, too). My guess is that you've got some long running=20=
=20
write/ddl query that's go a heavy lock on iphost or you have a LOT
of=20=20
queries that need heavy locks hitting the table at once. How large
is=20=20
iphost? How many of those deletes have you got going on it? Do you=20=20
also have concurrent updates running against it? Do you have any
ddl=20=20
queries running against it (alter tables, index builds/drops, etc...)?
Erik Jones
DBA | Emma=AE
erik@[EMAIL PROTECTED]
or 615.292.5888
615.292.0777 (fax)
Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com
--=20
Sent via pgsql-general mailing list (pgsql-general@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
|
