Hackers,
One of our clients is seeing an unusual issue with statements which are
waiting going into sleep forever and never completing. This first
e-mail is for a "has anyone else seen this problem before?" while we try
to collect additional information for a diagnosis.
This problem only happens under load and only when performing load tests
that insert large data (250 Kib) into bytea columns. It's takes a
couple hours but we've been able to reproduce the issue with a 100%
success rate. Sometime the locked query shows up as '<BIND>' in the
pg_stat_activity sometimes it's a complicated query using a multi-table
left outer join, other times is a simple select. The only thing in
common is that there is never a corresponding entry for that statement
in the pg_locks table, and if you drop the connection the query goes away.
An strace on the process shows it to be in RECV, and otherwise doing
nothing. We have not been able to run GDB because it takes a couple
hours of running a heavy load test to cause the issue. While memory is
heavily used during the test, there is no swapping during the test which
would indicate Linux memory management as the culprit.
We can reproduce the issue on 8.1.11 and 8.1.23. Currently we are
working on testing it on 9.0 and seeing if we can reproduce the issue.
We compiled the postgres from the Redhat source RPM. The only
modification that we make is the config file.
The OS is Centos 5.4 32bit.
Hardware:
IBM 3650
2 x Dual Core Intel Xeon 5160 @ 3.00 GHz
16 GB memory
6 x 146 GB SAS 10K RPM in RAID-5
Please note that while we can reproduce the issue, access to the test
system is fairly restrictive and test runs take a while, so I'd like to
get requests for additional information-collecting all at once if possible.
-- -- Josh Berkus PostgreSQL Experts Inc.
http://www.pgexperts.com