Re: Weird problem that enormous locks

On 07/13/11 7:16 PM, Tony Wang wrote:

On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@hogranch.com <mailto:pierce@hogranch.com>> wrote:

   On 07/13/11 6:55 PM, Tony Wang wrote:

       Could I consider it a hardware problem, or postgresql running
       too long which causes problems? (It ran about half a month,
       however, it ran much longer than that without problems)


   i have postgres servers that run for months and even years without
   problems.


Yeah, same for me.


   based on what I see in your original posting, there's no way
   anyone on this list could possibly guess what is happening on your
   server.


Sorry but is there anything I'm missing? I just want to know any possible situation can cause high locks. The server runs for more than a year, and I didn't do any related update recently and it just happened.

If I run into locking problems, the first thing *I* do is look at pg_stat_activity to see what sort of queries are active, and relate the transaction OIDs to the pg_locks and the queries to figure out whats locking on what, which it appears your join is doing....    If you had that many exclusive_locks,  just what were the queries making these locks doing?

We don't know what sort of schema you have, what kind of queries your applications make, etc etc etc.   were there any hardware events related to storage in the kernel message buffer (displayed by dmesg (1) on most unix and linux systems) ?   If linux, has the oomkiller run amok? (this also should be logged in dmesg)

800 concurrent connections is a very large number for a server that has at most a dozen cores. (you say you have x5650, thats a 6 core processor, which supports at most 2 sockets, for 12 cores total.  these 12 cores support hyperthreading, which allows 24 total threads).  With 24 hardware threads and 800 queries running, you'd have 33 queries contending for each CPU, which likely will result in LOWER total performance than if you tried to execute fewer queries at once.    If most of those connections are idle at a given time, you likely should consider using a connection pooler with a lot fewer max_connections, say, no more than 100 or so.

--
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general