Re: How do I track down a possible locking problem?

Herouth Maoz <herouth(at)unicell(dot)co(dot)il> wrote:

> I have a production system using Postgresql 9.1.2.

That's asking for trouble.  There have been many bugs fixed in 9.1
since 2011-12-05, including security vulnerabilities and (more to
the point) bugs which caused vacuum processes to interact poorly
with tables used as queues.  You should really drop in the latest
9.1 minor release.  It does not require any conversion of the data.

http://www.postgresql.org/support/versioning/

> The database in production is very busy with millions of writes
> per hour. Could there be a situation in which a particular
> connection gets "starved" while other connections are able to run
> queries without noticeable delay?

If the issue has not been solved, you might want to read this page
and post to the pgsql-performance list, providing the suggested
information:

http://wiki.postgresql.org/wiki/SlowQueryQuestions

In particular, the number of cores and the setting for
max_connections might suggest a possible cause.  But rather than
guessing in advance of the facts, I suggest monitoring data in the
queue to spot a lingering entry, and capturing the contents of
pg_stat_activity and pg_locks while that condition esists, along
with a couple minutes of output from `vmstat 1`.  That along with
the general information suggested on the above page may allow a
proper diagnosis.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company