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(at)hogranch(dot)com
> <mailto:pierce(at)hogranch(dot)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