On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@xxxxxxxxxxxx> wrote:
On 07/13/11 7:16 PM, Tony Wang wrote: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?
On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@xxxxxxxxxxxx <mailto:pierce@xxxxxxxxxxxx>> 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.
It's a game server, and the queries are updating users' money, as normal. The sql is like "UPDATE player SET money = money + 100 where id = 12345". The locks were RowExclusiveLock for the table "player" and the indexes. The weird thing is there was another ExclusiveLock for the table "player", i.e. "player" got two locks, one RowExclusiveLock and one ExclusiveLock.
In the postgresql documentation (http://www.postgresql.org/docs/8.4/static/explicit-locking.html), it's said about the Exclusive "This lock mode is not automatically acquired on user tables by any PostgreSQL command."
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)
Mostly update players' info, and another table called items for the items ownership for users.
As I listed, I'm using ubuntu 10.04. I didn't find useful messages there. Does oomkiller means out of memory killer? from the munin graph, the memory usage is quite normal.
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.
Yeah, that's what I planned to do next.
Thanks for your concerns! :)
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
