Postgresql 8.4 偶尔因 JDBC 访问而挂起
我有一个数据库密集型应用程序，它运行多个小时并使用多个线程，所有线程都通过 JDBC 与 Postgresql 通信。我看到的症状是，偶尔（每次“运行”中一到三次）我会遇到一个或多个卡住的 JDBC 连接，这些连接似乎正在等待数据库的响应，但似乎永远在等待。线程转储如下：
"Thread-4367355" daemon prio=6 tid=0x04920c00 nid=0x1e88 runnable [0x04bef000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:255)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1165)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
- locked <0x2c023e10> (a org.postgresql.core.v3.QueryExecutorImpl)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
我本来会想到某种锁定问题，但很多时候只有一个线程被卡住。我在这种状态下看到的至少一个查询是 REINDEX，因此该查询可能需要相当长的时间。为了找到解决方案，我将 JDBC 驱动程序从 8.4 升级到 9.1，但问题仍然出现。 Postgresql 日志中也没有任何异常。有任何进一步诊断的想法（除了使用 pg_locks 之外）？
I have a very database-intensive application which runs for many hours and uses multiple threads, all talking to Postgresql via JDBC. The symptom I'm seeing is that occasionally (one to three times out of every "run") I wind up with one or more stuck JDBC connections, which seem to be waiting on a response from the database but seem to be waiting forever. The thread dump is as follows:
"Thread-4367355" daemon prio=6 tid=0x04920c00 nid=0x1e88 runnable [0x04bef000]
java.lang.Thread.State: RUNNABLE
at java.net.SocketInputStream.socketRead0(Native Method)
at java.net.SocketInputStream.read(SocketInputStream.java:129)
at org.postgresql.core.VisibleBufferedInputStream.readMore(VisibleBufferedInputStream.java:135)
at org.postgresql.core.VisibleBufferedInputStream.ensureBytes(VisibleBufferedInputStream.java:104)
at org.postgresql.core.VisibleBufferedInputStream.read(VisibleBufferedInputStream.java:73)
at org.postgresql.core.PGStream.ReceiveChar(PGStream.java:255)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1165)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
- locked <0x2c023e10> (a org.postgresql.core.v3.QueryExecutorImpl)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:337)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:329)
I would have thought of some kind of locking issue except that many times only ONE thread is stuck. At least one of the queries I've seen in this state was a REINDEX, so it is possible that the query is taking a fair bit of time. In hopes of finding a solution, I upgraded the JDBC driver from 8.4 to 9.1 but the problem still occurs. There is nothing unusual in the Postgresql logs either. Any ideas for further diagnostics (other than using pg_locks)?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
有一个明显的事情您可以尝试：将 PostgreSQL 本身也更新到版本 9.1。
您还可以记录所有 长时间运行陈述，这可能会给你一个线索。
或者任何适合您的阈值。
我不知道如何解释线程转储，但这一行看起来很奇怪：
锁定了什么？你会注意到它拼写错误“at a org.postgresql.cor...”。这是复制粘贴的产物还是原始消息？如果是这样，可能有助于找到起源。
There is the obvious thing you could try: to update PostgreSQL itself to version 9.1, too.
You could also log all long running statements, that might give you a clue.
Or whatever threshold suits you.
I don't know how to interpret the thread dump, but this line looks peculiar:
What is locked? And you notice how it misspells "at a org.postgresql.cor...". Is that a copy-paste artefact or the original message? If so, might help to find the origin.