设置:多个运行mod_wsgi、Apache和连接到运行Postgres 8.3.6的共享数据库的pgbouncer的Web服务器。应用程序正在运行Django。
我们看到的是:在DB上出现了'空闲事务'查询,这些查询会长时间挂起。为了查看它们,我会运行类似以下命令:
SELECT query_start, procpid, client_addr, current_query FROM pg_stat_activity
WHERE query_start < NOW() - interval '5 minutes';
大多数结果当然只是pgbouncer保持打开以供使用的IDLE连接,但有时会出现这些旧的“IDLE in transaction”查询。我理解这意味着有一个查询事务正在等待某些东西,或者有一个BEGIN但尚未达到COMMIT或ROLLBACK的事务。
我的下一步是尝试使用pg_locks确定进程正在等待什么:
select pg_class.relname, pg_locks.transactionid, pg_locks.mode,
pg_locks.granted as "g", pg_stat_activity.current_query,
pg_stat_activity.query_start,
age(now(),pg_stat_activity.query_start) as "age",
pg_stat_activity.procpid
from pg_stat_activity,pg_locks
left outer join pg_class on (pg_locks.relation = pg_class.oid)
where pg_locks.pid=pg_stat_activity.procpid
and pg_stat_activity.procpid = <AN IDLE TRANSACTION PROCESS>
order by query_start;
很多时候,我得到的结果看起来像这样:
relname | transactionid | mode | g | current_query | query_start | age | client_addr | procpid
---------+---------------+-----------------+---+-----------------------+------------------------------+-----------------+----------------+---------
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | ExclusiveLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
| | AccessShareLock | t | <IDLE> in transaction | 2010-07-22 15:33:11.48136-04 | 00:23:35.029045 | 192.168.100.99 | 1991
(10 rows)
我不确定如何阅读这个(我猜这源自于我对pg_locks的理解并不是很深)。没有relname,所以它是在等待什么东西吗?我认为如果granted是'true',那么就已经获得了锁。由于所有这些结果都被授予了,那么pg_locks正在显示它拥有的锁而不是正在等待的锁吗?
现在我通过重新启动Apache来“解决”这个问题,这似乎可以使事务开始运转,但显然这不是一个真正的解决方案。我希望Postgres能够告诉我在哪里查找解决方法,特别是因为Django应该自动管理其连接和事务。