Puzzling full database lock

From: Christopher Opena <counterveil(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Puzzling full database lock
Date: 2012-02-02 00:38:31
Message-ID: CAFOrgqfDgJBnJE087vyFvST1r+EpqQLzA5UGGeT47xHwYdon+A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello folks,

We've been running into some very strange issues of late with our
PostgreSQL database(s). We have an issue where a couple of queries push
high CPU on a few of our processors and the entire database locks (reads,
writes, console cannot be achieved unless the high CPU query procs are
killed). Further investigation shows ~59% total cpu usage (we have 16
total cores), low io, and mid-to-low memory usage (we have 74GB of memory,
shared_buffers=16GB). We had previously seen some high io problems but
those turned out to be unconnected and ultimately solved, yet we are still
seeing a complete lock of the DB occasionally as previously described.

The queries themselves are not any different than normal usage on other
databases; they are pulling back a little more data but there's nothing
that stands out about them as far as query construction.

One thing that we aren't sure of is whether or not we are running into a
general connection pooling issue. Our typical number of postgresql
processes fluctuates between 1,400 and 1,600 - most of which are idle - as
we have a number of application servers all connecting to a central
read/write master (the master replicates out to a secondary via streaming
replication). We have max_processes set to 3,000 after tweaking some
kernel memory parameters so at least we know we aren't exceeding that, but
is there a practical "real world" limit or issue with setting this too high?

Ultimately, the problem we're seeing is a full read/write lock on a system
that is apparently at medium usage levels once we got rid of our high io
red herring. Honestly I'm a little stumped as to where to look next; is
there some specific metric I might be missing here?

Any help is greatly appreciated,
-Chris.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Carlos Mennens 2012-02-02 00:44:24 Re: Puzzling full database lock
Previous Message Chris Travers 2012-02-01 23:43:28 Re: Issue with CREATE EXTENSION tablefuncKreiter