Thread: Weird problem that enormous locks
Hi,
The configuration information is listed at the end.
I met this problem last weekend. The presentation was that, the db locks became enormous, up to 8.3k, and the db hanged there. About half an hour to one hour later, it recovered: the locks became 1 or 2 hundreds, which was its average level. It happened every 5-8 hours.
I checked the log, but nothing interesting. The log about dead lock happened several times a day, and not when hanging. I had a cron job running every minute to record the locks using the command below:
select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start, pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age, age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and substr(pg_class.relname,1,3) != 'pg_' order by query_start;
The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are only AccessShareLock and RowExclusiveLock.
After suffering from that for whole weekend, I restarted postgresql, and my service, and reduced a bit db pressure by disabling some service, and it didn't happen again till now.
The possible reason I think of is that someone was reindexing index, which is almost impossible; or the hardware problem, which is also little possible.
Have any one experienced that, or any suggestion on researching/debugging?
The configuration information:
System: Ubuntu server 10.04.2
Postgresql version: 8.4.8-0ubuntu0.10.04
CPU: Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (24 cores)
Disk: Fusion IO drive
Memory: 32G
Postgresql configuration:
max_connection = 800
shared_buffers = 2000MB
effective_cache_size = 14000MB
autovacuum = off
--
BR,
Tony Wang
On 13/07/2011 12:52 AM, Tony Wang wrote: > Have any one experienced that, or any suggestion on researching/debugging? > Capture the contents of pg_catalog.pg_stat_activity whenever your cron job notices high lock counts. That'll give you some more information to work with. POST Newspapers 276 Onslow Rd, Shenton Park Ph: 08 9381 3088 Fax: 08 9388 2258 ABN: 50 008 917 717 http://www.postnewspapers.com.au/
On Wed, Jul 13, 2011 at 08:40, Craig Ringer <craig@postnewspapers.com.au> wrote:
On 13/07/2011 12:52 AM, Tony Wang wrote:Capture the contents of pg_catalog.pg_stat_activity whenever your cron job notices high lock counts. That'll give you some more information to work with.Have any one experienced that, or any suggestion on researching/debugging?
Thanks, but the cron job query has already joined pg_stat_activity table
POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/
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)
On Wed, Jul 13, 2011 at 00:52, Tony Wang <wwwjfy@gmail.com> wrote:
Hi,The configuration information is listed at the end.I met this problem last weekend. The presentation was that, the db locks became enormous, up to 8.3k, and the db hanged there. About half an hour to one hour later, it recovered: the locks became 1 or 2 hundreds, which was its average level. It happened every 5-8 hours.I checked the log, but nothing interesting. The log about dead lock happened several times a day, and not when hanging. I had a cron job running every minute to record the locks using the command below:select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start, pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age, age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and substr(pg_class.relname,1,3) != 'pg_' order by query_start;The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are only AccessShareLock and RowExclusiveLock.After suffering from that for whole weekend, I restarted postgresql, and my service, and reduced a bit db pressure by disabling some service, and it didn't happen again till now.The possible reason I think of is that someone was reindexing index, which is almost impossible; or the hardware problem, which is also little possible.Have any one experienced that, or any suggestion on researching/debugging?The configuration information:System: Ubuntu server 10.04.2Postgresql version: 8.4.8-0ubuntu0.10.04CPU: Intel(R) Xeon(R) CPU X5650 @ 2.67GHz (24 cores)Disk: Fusion IO driveMemory: 32GPostgresql configuration:max_connection = 800shared_buffers = 2000MBeffective_cache_size = 14000MBautovacuum = off--BR,Tony Wang
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. 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. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Thu, Jul 14, 2011 at 10:01, John R Pierce <pierce@hogranch.com> wrote:
On 07/13/11 6:55 PM, Tony Wang wrote:i have postgres servers that run for months and even years without problems.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)
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.
--
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
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
On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> 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@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.
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 07/13/11 8:47 PM, Tony Wang wrote:
> 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.
that query should be quite fast. is it part of a larger transaction? is
there any possibility of multiple sessions/connections accessing the
same player.id?
it would be interesting to identify the process that issued the
exclusive lock and determine what query/queries its made. if its not
apparent in pg_stat_activity, perhaps enable logging of all DDL
commands, and check the logs.
if there's a lot of active queries (you ahve 800 connections)
select count(*),current_query from pg_stat_activity group by
current_query order by count(*) desc;
can help you make sense of them.
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.com> wrote:
On 07/13/11 8:47 PM, Tony Wang wrote:that query should be quite fast. is it part of a larger transaction? is there any possibility of multiple sessions/connections accessing the same player.id?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.
That's possible, but I think only one row will be locked for a while, but not thousands of locks for an hour. It's rare that thousands of users update the value at once.
it would be interesting to identify the process that issued the exclusive lock and determine what query/queries its made. if its not apparent in pg_stat_activity, perhaps enable logging of all DDL commands, and check the logs.
yeah, I've made the log_statement to "all" now. Previously, it only logged slow queries more than 50ms. I could know something from logs if it happens again (hope not).
if there's a lot of active queries (you ahve 800 connections)
select count(*),current_query from pg_stat_activity group by current_query order by count(*) desc;
that's helpful, thanks.
can help you make sense of them.
--
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
Once time I've read 9.x PostgreSQL locks everything before offset, if You execute select for update offset. Do you call such query at least once? It's the way why we think about having 9.x server.
------------------------
Regards,
Radoslaw Smogura
(mobile)
------------------------
Regards,
Radoslaw Smogura
(mobile)
From: Tony Wang
Sent: 14 lipca 2011 07:00
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Weird problem that enormous locks
On Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.com> wrote:
On 07/13/11 8:47 PM, Tony Wang wrote:that query should be quite fast. is it part of a larger transaction? is there any possibility of multiple sessions/connections accessing the same player.id?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.
That's possible, but I think only one row will be locked for a while, but not thousands of locks for an hour. It's rare that thousands of users update the value at once.
it would be interesting to identify the process that issued the exclusive lock and determine what query/queries its made. if its not apparent in pg_stat_activity, perhaps enable logging of all DDL commands, and check the logs.
yeah, I've made the log_statement to "all" now. Previously, it only logged slow queries more than 50ms. I could know something from logs if it happens again (hope not).
if there's a lot of active queries (you ahve 800 connections)
select count(*),current_query from pg_stat_activity group by current_query order by count(*) desc;
that's helpful, thanks.
can help you make sense of them.
--
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
Thanks, I've checked the "for update". No such queries there.
On Thu, Jul 14, 2011 at 15:36, Radoslaw Smogura <rsmogura@softperience.eu> wrote:
Once time I've read 9.x PostgreSQL locks everything before offset, if You execute select for update offset. Do you call such query at least once? It's the way why we think about having 9.x server.
------------------------
Regards,
Radoslaw Smogura
(mobile)
From: Tony Wang
Sent: 14 lipca 2011 07:00
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Weird problem that enormous locksOn Thu, Jul 14, 2011 at 12:35, John R Pierce <pierce@hogranch.com> wrote:On 07/13/11 8:47 PM, Tony Wang wrote:that query should be quite fast. is it part of a larger transaction? is there any possibility of multiple sessions/connections accessing the same player.id?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.That's possible, but I think only one row will be locked for a while, but not thousands of locks for an hour. It's rare that thousands of users update the value at once.
it would be interesting to identify the process that issued the exclusive lock and determine what query/queries its made. if its not apparent in pg_stat_activity, perhaps enable logging of all DDL commands, and check the logs.yeah, I've made the log_statement to "all" now. Previously, it only logged slow queries more than 50ms. I could know something from logs if it happens again (hope not).
if there's a lot of active queries (you ahve 800 connections)
select count(*),current_query from pg_stat_activity group by current_query order by count(*) desc;that's helpful, thanks.
can help you make sense of them.
--
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
On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote: > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> wrote: > 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." You need to figure out what part of your app, or maybe a rogue developer etc is throwing an exclusive lock.
On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> wrote:> It's a game server, and the queries are updating users' money, as normal.You need to figure out what part of your app, or maybe a rogue
> 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."
developer etc is throwing an exclusive lock.
Yeah, that's what I'm trying to do
On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote: > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> >> > wrote: >> > 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." >> >> You need to figure out what part of your app, or maybe a rogue >> developer etc is throwing an exclusive lock. > > Yeah, that's what I'm trying to do Cool. In your first post you said: > select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start, > pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age, > age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left > outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and > substr(pg_class.relname,1,3) != 'pg_' order by query_start; > The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are > only AccessShareLock and RowExclusiveLock. So what did / does current_query say when it's happening? If it says you don't have access permission then run that query as root when it happens again.
On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
>> > wrote:
>> > 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."
>>
>> You need to figure out what part of your app, or maybe a rogue
>> developer etc is throwing an exclusive lock.
>
> Yeah, that's what I'm trying to doCool. In your first post you said:So what did / does current_query say when it's happening? If it says
> select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start,
> pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left
> outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and
> substr(pg_class.relname,1,3) != 'pg_' order by query_start;
> The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are
> only AccessShareLock and RowExclusiveLock.
you don't have access permission then run that query as root when it
happens again.
As I said, it's normal update like "UPDATE player SET money = money + 100 WHERE id=12345", but there are quite many
On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote: > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com> >> > wrote: >> >> >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> >> >> > wrote: >> >> > 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." >> >> >> >> You need to figure out what part of your app, or maybe a rogue >> >> developer etc is throwing an exclusive lock. >> > >> > Yeah, that's what I'm trying to do >> >> Cool. In your first post you said: >> >> > select pg_class.relname, pg_locks.mode, pg_locks.granted, >> > pg_stat_activity.current_query, pg_stat_activity.query_start, >> > pg_stat_activity.xact_start as transaction_start, >> > age(now(),pg_stat_activity.query_start) as query_age, >> > age(now(),pg_stat_activity.xact_start) as transaction_age, >> > pg_stat_activity.procpid from pg_stat_activity,pg_locks left >> > outer join pg_class on (pg_locks.relation = pg_class.oid) where >> > pg_locks.pid=pg_stat_activity.procpid and >> > substr(pg_class.relname,1,3) != 'pg_' order by query_start; >> >> > The only special thing I can find is that there were a lot >> > ExclusiveLock, while it's normal the locks are >> > only AccessShareLock and RowExclusiveLock. >> >> So what did / does current_query say when it's happening? If it says >> you don't have access permission then run that query as root when it >> happens again. > > As I said, it's normal update like "UPDATE player SET money = money + 100 > WHERE id=12345", but there are quite many A regular update like that can't get a full exclusive lock by itself, there'd have to be a previous query in the same transaction that took out an explicit lock. Is it possible for you to set up query logging such that you can track connections to see which one does that in the future? Were there more than 1 exclusive lock (now row exclusive, but just plain exclusive)?
On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com> wrote:
A regular update like that can't get a full exclusive lock by itself,On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
>> > wrote:
>> >>
>> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
>> >> > wrote:
>> >> > 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."
>> >>
>> >> You need to figure out what part of your app, or maybe a rogue
>> >> developer etc is throwing an exclusive lock.
>> >
>> > Yeah, that's what I'm trying to do
>>
>> Cool. In your first post you said:
>>
>> > select pg_class.relname, pg_locks.mode, pg_locks.granted,
>> > pg_stat_activity.current_query, pg_stat_activity.query_start,
>> > pg_stat_activity.xact_start as transaction_start,
>> > age(now(),pg_stat_activity.query_start) as query_age,
>> > age(now(),pg_stat_activity.xact_start) as transaction_age,
>> > pg_stat_activity.procpid from pg_stat_activity,pg_locks left
>> > outer join pg_class on (pg_locks.relation = pg_class.oid) where
>> > pg_locks.pid=pg_stat_activity.procpid and
>> > substr(pg_class.relname,1,3) != 'pg_' order by query_start;
>>
>> > The only special thing I can find is that there were a lot
>> > ExclusiveLock, while it's normal the locks are
>> > only AccessShareLock and RowExclusiveLock.
>>
>> So what did / does current_query say when it's happening? If it says
>> you don't have access permission then run that query as root when it
>> happens again.
>
> As I said, it's normal update like "UPDATE player SET money = money + 100
> WHERE id=12345", but there are quite many
there'd have to be a previous query in the same transaction that took
out an explicit lock. Is it possible for you to set up query logging
such that you can track connections to see which one does that in the
future?
Yeah, and I also wonder when will an ExclusiveLock acquired.
I set up query logging after that, that'll be really big file.
Were there more than 1 exclusive lock (now row exclusive, but just
plain exclusive)?
There were many such locks (not row exclusive) updating different player id.
On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy@gmail.com> wrote: > On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com> >> > wrote: >> >> >> >> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> >> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe >> >> > <scott.marlowe@gmail.com> >> >> > wrote: >> >> >> >> >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote: >> >> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com> >> >> >> > wrote: >> >> >> > 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." >> >> >> >> >> >> You need to figure out what part of your app, or maybe a rogue >> >> >> developer etc is throwing an exclusive lock. >> >> > >> >> > Yeah, that's what I'm trying to do >> >> >> >> Cool. In your first post you said: >> >> >> >> > select pg_class.relname, pg_locks.mode, pg_locks.granted, >> >> > pg_stat_activity.current_query, pg_stat_activity.query_start, >> >> > pg_stat_activity.xact_start as transaction_start, >> >> > age(now(),pg_stat_activity.query_start) as query_age, >> >> > age(now(),pg_stat_activity.xact_start) as transaction_age, >> >> > pg_stat_activity.procpid from pg_stat_activity,pg_locks left >> >> > outer join pg_class on (pg_locks.relation = pg_class.oid) where >> >> > pg_locks.pid=pg_stat_activity.procpid and >> >> > substr(pg_class.relname,1,3) != 'pg_' order by query_start; >> >> >> >> > The only special thing I can find is that there were a lot >> >> > ExclusiveLock, while it's normal the locks are >> >> > only AccessShareLock and RowExclusiveLock. >> >> >> >> So what did / does current_query say when it's happening? If it says >> >> you don't have access permission then run that query as root when it >> >> happens again. >> > >> > As I said, it's normal update like "UPDATE player SET money = money + >> > 100 >> > WHERE id=12345", but there are quite many >> >> A regular update like that can't get a full exclusive lock by itself, >> there'd have to be a previous query in the same transaction that took >> out an explicit lock. Is it possible for you to set up query logging >> such that you can track connections to see which one does that in the >> future? > > Yeah, and I also wonder when will an ExclusiveLock acquired. > I set up query logging after that, that'll be really big file. > >> >> Were there more than 1 exclusive lock (now row exclusive, but just >> plain exclusive)? > > There were many such locks (not row exclusive) updating different player id. How many just plain exclusive locks were there?
On Fri, Jul 15, 2011 at 10:42, Scott Marlowe <scott.marlowe@gmail.com> wrote:
How many just plain exclusive locks were there?On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 10:05, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> > On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com>
>> > wrote:
>> >>
>> >> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> >> > On Fri, Jul 15, 2011 at 01:13, Scott Marlowe
>> >> > <scott.marlowe@gmail.com>
>> >> > wrote:
>> >> >>
>> >> >> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> >> >> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
>> >> >> > wrote:
>> >> >> > 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."
>> >> >>
>> >> >> You need to figure out what part of your app, or maybe a rogue
>> >> >> developer etc is throwing an exclusive lock.
>> >> >
>> >> > Yeah, that's what I'm trying to do
>> >>
>> >> Cool. In your first post you said:
>> >>
>> >> > select pg_class.relname, pg_locks.mode, pg_locks.granted,
>> >> > pg_stat_activity.current_query, pg_stat_activity.query_start,
>> >> > pg_stat_activity.xact_start as transaction_start,
>> >> > age(now(),pg_stat_activity.query_start) as query_age,
>> >> > age(now(),pg_stat_activity.xact_start) as transaction_age,
>> >> > pg_stat_activity.procpid from pg_stat_activity,pg_locks left
>> >> > outer join pg_class on (pg_locks.relation = pg_class.oid) where
>> >> > pg_locks.pid=pg_stat_activity.procpid and
>> >> > substr(pg_class.relname,1,3) != 'pg_' order by query_start;
>> >>
>> >> > The only special thing I can find is that there were a lot
>> >> > ExclusiveLock, while it's normal the locks are
>> >> > only AccessShareLock and RowExclusiveLock.
>> >>
>> >> So what did / does current_query say when it's happening? If it says
>> >> you don't have access permission then run that query as root when it
>> >> happens again.
>> >
>> > As I said, it's normal update like "UPDATE player SET money = money +
>> > 100
>> > WHERE id=12345", but there are quite many
>>
>> A regular update like that can't get a full exclusive lock by itself,
>> there'd have to be a previous query in the same transaction that took
>> out an explicit lock. Is it possible for you to set up query logging
>> such that you can track connections to see which one does that in the
>> future?
>
> Yeah, and I also wonder when will an ExclusiveLock acquired.
> I set up query logging after that, that'll be really big file.
>
>>
>> Were there more than 1 exclusive lock (now row exclusive, but just
>> plain exclusive)?
>
> There were many such locks (not row exclusive) updating different player id.
There were 2519 RowExclusiveLock and 85 ExclusiveLock
On 07/14/11 7:58 PM, Tony Wang wrote: > > There were 2519 RowExclusiveLock and 85 ExclusiveLock how could 800 max_connections have 2519 row locks ? do you update multiple different rows in the same transaction? -- john r pierce N 37, W 122 santa cruz ca mid-left coast
On Fri, Jul 15, 2011 at 12:38, John R Pierce <pierce@hogranch.com> wrote:
On 07/14/11 7:58 PM, Tony Wang wrote:how could 800 max_connections have 2519 row locks ? do you update multiple different rows in the same transaction?
There were 2519 RowExclusiveLock and 85 ExclusiveLock
the row locks includes indexes locks, also some updates related to two tables. Roughly, about 700+ unique RowExclusiveLock there
--
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
Simple and obvious question right now do You call commit after transaction? If yes do you use any query or connection pooler?
------------------------
Regards,
Radoslaw Smogura
(mobile)
------------------------
Regards,
Radoslaw Smogura
(mobile)
From: Tony Wang
Sent: 15 lipca 2011 03:51
To: Scott Marlowe
Cc: PostgreSQL
Subject: Re: [GENERAL] Weird problem that enormous locks
On Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:
>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
>> > wrote:
>> > 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."
>>
>> You need to figure out what part of your app, or maybe a rogue
>> developer etc is throwing an exclusive lock.
>
> Yeah, that's what I'm trying to doCool. In your first post you said:So what did / does current_query say when it's happening? If it says
> select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start,
> pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left
> outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and
> substr(pg_class.relname,1,3) != 'pg_' order by query_start;
> The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are
> only AccessShareLock and RowExclusiveLock.
you don't have access permission then run that query as root when it
happens again.
As I said, it's normal update like "UPDATE player SET money = money + 100 WHERE id=12345", but there are quite many
Weird that I receive your each message twice.
On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmogura@softperience.eu> wrote:
Simple and obvious question right now do You call commit after transaction? If yes do you use any query or connection pooler?
Yes. connection pool is used as application level, not db level.
no commit after transaction is possible (I'm trying to check the logic), I just cannot imagine it happened for so many users at the same time, and then calmed down for long time, and came again.
I found the query I used to log locks would miss locks that relname is null. will add that, though no idea why it's null
------------------------
Regards,
Radoslaw Smogura
(mobile)
From: Tony Wang
Sent: 15 lipca 2011 03:51
To: Scott Marlowe
Cc: PostgreSQL
Subject: Re: [GENERAL] Weird problem that enormous locksOn Fri, Jul 15, 2011 at 08:22, Scott Marlowe <scott.marlowe@gmail.com> wrote:On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang <wwwjfy@gmail.com> wrote:
> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>>
>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang <wwwjfy@gmail.com> wrote:>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce <pierce@hogranch.com>
>> > wrote:
>> > 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."
>>
>> You need to figure out what part of your app, or maybe a rogue
>> developer etc is throwing an exclusive lock.
>
> Yeah, that's what I'm trying to doCool. In your first post you said:So what did / does current_query say when it's happening? If it says
> select pg_class.relname, pg_locks.mode, pg_locks.granted, pg_stat_activity.current_query, pg_stat_activity.query_start,
> pg_stat_activity.xact_start as transaction_start, age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_stat_activity.xact_start) as transaction_age, pg_stat_activity.procpid from pg_stat_activity,pg_locks left
> outer join pg_class on (pg_locks.relation = pg_class.oid) where pg_locks.pid=pg_stat_activity.procpid and
> substr(pg_class.relname,1,3) != 'pg_' order by query_start;
> The only special thing I can find is that there were a lot ExclusiveLock, while it's normal the locks are
> only AccessShareLock and RowExclusiveLock.
you don't have access permission then run that query as root when it
happens again.As I said, it's normal update like "UPDATE player SET money = money + 100 WHERE id=12345", but there are quite many
On Fri, Jul 15, 2011 at 4:36 AM, Tony Wang <wwwjfy@gmail.com> wrote: > Weird that I receive your each message twice. > On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmogura@softperience.eu> > wrote: >> >> Simple and obvious question right now do You call commit after >> transaction? If yes do you use any query or connection pooler? > > Yes. connection pool is used as application level, not db level. > no commit after transaction is possible (I'm trying to check the logic), I > just cannot imagine it happened for so many users at the same time, and then > calmed down for long time, and came again. > I found the query I used to log locks would miss locks that relname is null. > will add that, though no idea why it's null They're likely exclusive locks on a transaction, which are normal.
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: > Weird that I receive your each message twice. > > On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote: > >> Simple and obvious question right now do You call commit after >> transaction? If yes do you use any query or connection pooler? > > Yes. connection pool is used as application level, not db level. > no commit after transaction is possible (Im trying to check the > logic), I just cannot imagine it happened for so many users at the > same time, and then calmed down for long time, and came again. > > I found the query I used to log locks would miss locks that relname > is > null. will add that, though no idea why its null > > >> ------------------------ >> Regards, >> Radoslaw Smogura >> (mobile) >> ------------------------- >> From: Tony Wang >> Sent: 15 lipca 2011 03:51 >> To: Scott Marlowe >> Cc: PostgreSQL >> >> Subject: Re: [GENERAL] Weird problem that enormous locks >> >> On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote: >> >>> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote: >>>> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe >>> > wrote: >>>>> >>>>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote: >>> >>>>> > On Thu, Jul 14, 2011 at 10:35, John R Pierce >>> >> > wrote: >>>>> > Its 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 >>> [5]), its >>> >> > said >>>>> > about the Exclusive "This lock mode is not automatically >>> acquired on >>>>> > user >>>>> > tables by any PostgreSQL command." >>>>> >>>>> You need to figure out what part of your app, or maybe a rogue >>> >> developer etc is throwing an exclusive lock. >>>> >>>> Yeah, thats what Im trying to do >>> >>> Cool. In your first post you said: >>> >>>> select pg_class.relname, pg_locks.mode, pg_locks.granted, >>> pg_stat_activity.current_query, pg_stat_activity.query_start, >>>> pg_stat_activity.xact_start as transaction_start, >>> age(now(),pg_stat_activity.query_start) as query_age, >>> > age(now(),pg_stat_activity.xact_start) as transaction_age, >>> pg_stat_activity.procpid from pg_stat_activity,pg_locks left >>>> outer join pg_class on (pg_locks.relation = pg_class.oid) where >>> pg_locks.pid=pg_stat_activity.procpid and >>> > substr(pg_class.relname,1,3) != pg_ order by query_start; >>> >>>> The only special thing I can find is that there were a lot >>> ExclusiveLock, while its normal the locks are >>>> only AccessShareLock and RowExclusiveLock. >>> >>> So what did / does current_query say when its happening? If it >>> says >>> you dont have access permission then run that query as root when >>> it >>> happens again. >> >> As I said, its normal update like "UPDATE player SET money = money + >> 100 WHERE id=12345", but there are quite many > > > > Links: > ------ > [1] mailto:wwwjfy@gmail.com > [2] mailto:scott.marlowe@gmail.com > [3] mailto:wwwjfy@gmail.com > [4] mailto:pierce@hogranch.com > [5] http://www.postgresql.org/docs/8.4/static/explicit-locking.html > [6] mailto:scott.marlowe@gmail.com > [7] mailto:rsmogura@softperience.eu Actually I don't know what pool You use (I think PHP - I don't know much about this), but I imagine following, If You don't use auto commit or commit: 1. User A updates moneys, gets connections C1, locks his row, no commit 2. User A updates moneys again, gets connection C2, but C1 still holds lock. Regards, Radosław Smogura
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: > Weird that I receive your each message twice. Once message You get from mailing list, one because You are (B)CC.
On Fri, Jul 15, 2011 at 18:50, Radosław Smogura <rsmogura@softperience.eu> wrote:
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:Actually I don't know what pool You use (I think PHP - I don't know much about this), but I imagine following, If You don't use auto commit or commit:Weird that I receive your each message twice.On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote:no commit after transaction is possible (Im trying to check theSimple and obvious question right now do You call commit after
transaction? If yes do you use any query or connection pooler?
Yes. connection pool is used as application level, not db level.null. will add that, though no idea why its null
logic), I just cannot imagine it happened for so many users at the
same time, and then calmed down for long time, and came again.
I found the query I used to log locks would miss locks that relname is
------------------------On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote:
Regards,
Radoslaw Smogura
(mobile)
-------------------------
From: Tony Wang
Sent: 15 lipca 2011 03:51
To: Scott Marlowe
Cc: PostgreSQL
Subject: Re: [GENERAL] Weird problem that enormous locksOn Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote:> wrote:On Fri, Jul 15, 2011 at 01:13, Scott Marlowe>> > wrote:
On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote:> On Thu, Jul 14, 2011 at 10:35, John R Pierce> Its a game server, and the queries are updating users money,as[5]), itsid => normal.
> The sql is like "UPDATE player SET money = money + 100 where
>> > 12345".the indexes.> The locks were RowExclusiveLock for the table "player" and"player",> The
> weird thing is there was another ExclusiveLock for the table
>> > i.e.ExclusiveLock.> "player" got two locks, one RowExclusiveLock and one(http://www.postgresql.org/docs/8.4/static/explicit-locking.html> In the postgresql documentation
>
>> > saidacquired on> about the Exclusive "This lock mode is not automatically>> developer etc is throwing an exclusive lock.> user
> tables by any PostgreSQL command."
You need to figure out what part of your app, or maybe a rogue
Yeah, thats what Im trying to doExclusiveLock, while its normal the locks are
Cool. In your first post you said:select pg_class.relname, pg_locks.mode, pg_locks.granted,pg_stat_activity.current_query, pg_stat_activity.query_start,pg_stat_activity.xact_start as transaction_start,age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_stat_activity.xact_start) as transaction_age,
pg_stat_activity.procpid from pg_stat_activity,pg_locks leftouter join pg_class on (pg_locks.relation = pg_class.oid) wherepg_locks.pid=pg_stat_activity.procpid and
> substr(pg_class.relname,1,3) != pg_ order by query_start;The only special thing I can find is that there were a lotonly AccessShareLock and RowExclusiveLock.
So what did / does current_query say when its happening? If it
says
you dont have access permission then run that query as root when
it
happens again.
As I said, its normal update like "UPDATE player SET money = money +
100 WHERE id=12345", but there are quite many
Links:
------
[1] mailto:wwwjfy@gmail.com
[2] mailto:scott.marlowe@gmail.com
[3] mailto:wwwjfy@gmail.com
[4] mailto:pierce@hogranch.com
[5] http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6] mailto:scott.marlowe@gmail.com
[7] mailto:rsmogura@softperience.eu
1. User A updates moneys, gets connections C1, locks his row, no commit
2. User A updates moneys again, gets connection C2, but C1 still holds lock.
Regards,
Radosław Smogura
Any connection pool behaves similarly. The connection C1 surely will be committed and returned after the operation finished. Having said that, the ONLY possible reason is some transactions hanged holding the locks, and cause others cannot work any more, and the "ExclusiveLock" is not a problem, right?
The interesting thing is, I didn't find any timeout/exception after the "lock" period ended in postgresql log, only long query time.
On Fri, Jul 15, 2011 at 18:52, Radosław Smogura <rsmogura@softperience.eu> wrote:
On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:Once message You get from mailing list, one because You are (B)CC.Weird that I receive your each message twice.
gmail should be clever enough handling that, at lease I didn't receive twice this time.
On Fri, Jul 15, 2011 at 5:08 AM, Tony Wang <wwwjfy@gmail.com> wrote: > On Fri, Jul 15, 2011 at 18:52, Radosław Smogura <rsmogura@softperience.eu> > wrote: >> >> On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: >>> >>> Weird that I receive your each message twice. >> >> Once message You get from mailing list, one because You are (B)CC. > > gmail should be clever enough handling that, at lease I didn't receive twice > this time. I'm on gmail and I only get the one copy in these convos.
On Fri, 15 Jul 2011 19:07:45 +0800, Tony Wang wrote: > On Fri, Jul 15, 2011 at 18:50, Radosław Smogura wrote: > >> On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: >> >>> Weird that I receive your each message twice. >>> >>> On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote: >>> >>>> Simple and obvious question right now do You call commit after >>>> transaction? If yes do you use any query or connection pooler? >>> >>> Yes. connection pool is used as application level, not db level. >>> no commit after transaction is possible (Im trying to check the >>> >>> logic), I just cannot imagine it happened for so many users at >>> the >>> same time, and then calmed down for long time, and came again. >>> >>> I found the query I used to log locks would miss locks that >>> relname is >>> null. will add that, though no idea why its null >>> >>> >>>> ------------------------ >>>> Regards, >>>> Radoslaw Smogura >>>> (mobile) >>>> ------------------------- >>>> From: Tony Wang >>>> Sent: 15 lipca 2011 03:51 >>>> To: Scott Marlowe >>>> Cc: PostgreSQL >>>> >>>> Subject: Re: [GENERAL] Weird problem that enormous locks >>>> >>>> On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote: >>>> >>>>> On Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote: >>>>> >>>>>> On Fri, Jul 15, 2011 at 01:13, Scott Marlowe >>>>> > wrote: >>>>> >>>>>>> On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote: >>>>> >>>>> ; On Thu, Jul 14, 2011 at 10:35, John R Pierce >>>>>> >> > wrote: >>>>> #ccc solid;padding-left:1ex"> > Its a game server, and the >>>>> queries are updating users money, >>>>> >>>>> as >>>>> class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px >>>>>> #ccc solid;padding-left:1ex"> > normal. >>>>>> > The sql is like "UPDATE player SET money = money + 100 >>>>>> where >>>>> blockquote> id = >>>>> >> > 12345". >>>>> the indexes. >>>>> 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> > The >>>>> > weird thing is there was another ExclusiveLo >>>>> >>>>>> ockquote class="gmail_quote" style="margin:0 0 0 >>>>>> .8ex;border-left:1px #ccc solid;padding-left:1ex"> >>>>> uot;player" got two locks, one RowExclusiveLock and one >>>>> ExclusiveLock. >>>>> kquote> acquired on >>>>> c solid;padding-left:1ex"> > user >>>>> > tables by any PostgreSQL command." >>>>> >>>>> You need to figure out what part of your app, or maybe a >>>>> rogue >>>>> >> developer et >>>>> >>>>>> order-left:1px #ccc solid;padding-left:1ex"> >>>>>> Yeah, thats what Im trying to do >>>>>> >>>>>> Cool. In your first post you said: >>>>> "gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc >>>>> solid;padding-left:1ex"> select pg_class.relname, >>>>> pg_locks.mode, pg_locks.granted, >>>>> pg_stat_activity.current_query, >>>>> pg_stat_activity.query_start, >>>>> pg_stat_activity.xact_start as transaction_start, >>>>> age(now(),pg_stat_activity.query_start) as query_age, >>>>> > age(now(),pg_st >>>>> >>>>>> ,pg_locks left >>>>>> outer join pg_class on (pg_locks.relation = pg_class.oid) >>>>>> where >>>>> e> pg_locks.pid=pg_stat_activity.procpid and >>>>> > substr(pg_class.relname,1,3) != pg_ order by query_start; >>>>> >>>>> cial thing I can find is that there were a lot >>>>>> ExclusiveLock, while its normal the locks are >>>>>> only AccessShareLock and RowEx >>>>> br> >>>>> So what did / does current_query say when its happening? If >>>>> it >>>>> says >>>>> you dont have access permission then run that query as root >>>>> when >>>>> it >>>>> happens again. >>>>> >>>>>> >>>>> >>>>>> >>>>> >>>>>> >>>>> >>>>>> >>>>> >>>>>> >>>>> >>>>>> >>>> >>>> As I said, its normal update like "UPDATE player SET money = >>>> money + >>>> >>>> 100 WHERE id=12345", but there are quite many >>> >>> Links: >>> ------ >>> [1] mailto:wwwjfy@gmail.com [2] >>> [2] mailto:scott.marlowe@gmail.com [3] >>> [3] mailto:wwwjfy@gmail.com [4] >>> [4] mailto:pierce@hogranch.com [5] >>> [5] >>> http://www.postgresql.org/docs/8.4/static/explicit-locking.html >>> [6] >>> [6] mailto:scott.marlowe@gmail.com [7] >>> [7] mailto:rsmogura@softperience.eu [8] >> Actually I dont know what pool You use (I think PHP - I dont know >> much about this), but I imagine following, If You dont use auto >> commit or commit: >> 1. User A updates moneys, gets connections C1, locks his row, no >> commit >> 2. User A updates moneys again, gets connection C2, but C1 still >> holds lock. >> Regards, >> Radosław Smogura > > Any connection pool behaves similarly. The connection C1 surely will > be committed and returned after the operation finished. Having said > that, the ONLY possible reason is some transactions hanged holding > the > locks, and cause others cannot work any more, and the "ExclusiveLock" > is not a problem, right? > The interesting thing is, I didnt find any timeout/exception after > the > "lock" period ended in postgresql log, only long query time. No. It's depend on pooler, application server and transaction manager, for example there are possibilities to return connection which is not associated with transaction manager, so You still need to manually commit or rollback at the end of business logic. You may return C1 to poll, and I believe Your application makes this, but transaction may be uncommited. Watch your query log if You have COMMIT or ROLLBACK there, You may as well add tracking of connection id to associate query flow per connection; or check If you have auto commit turned on. Regards, Radosław Smogura
On Friday, July 15, 2011 3:52:13 am Radosław Smogura wrote: > On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote: > > Weird that I receive your each message twice. > > Once message You get from mailing list, one because You are (B)CC. If it is continues to be a problem go to : http://www.postgresql.org/mailpref/pgsql-general Log in and set the eliminatecc setting. -- Adrian Klaver adrian.klaver@gmail.com
On Fri, Jul 15, 2011 at 19:47, Radosław Smogura <rsmogura@softperience.eu> wrote:
On Fri, 15 Jul 2011 19:07:45 +0800, Tony Wang wrote:On Fri, Jul 15, 2011 at 18:50, Radosław Smogura wrote:No. It's depend on pooler, application server and transaction manager, for example there are possibilities to return connection which is not associated with transaction manager, so You still need to manually commit or rollback at the end of business logic. You may return C1 to poll, and I believe Your application makes this, but transaction may be uncommited. Watch your query log if You have COMMIT or ROLLBACK there, You may as well add tracking of connection id to associate query flow per connection; or check If you have auto commit turned on.On Fri, 15 Jul 2011 18:36:19 +0800, Tony Wang wrote:Actually I dont know what pool You use (I think PHP - I dont knowWeird that I receive your each message twice.
On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura wrote:Simple and obvious question right now do You call commit after
transaction? If yes do you use any query or connection pooler?
Yes. connection pool is used as application level, not db level.
no commit after transaction is possible (Im trying to check the
logic), I just cannot imagine it happened for so many users at
the
same time, and then calmed down for long time, and came again.
I found the query I used to log locks would miss locks that
relname is
null. will add that, though no idea why its null
------------------------
Regards,
Radoslaw Smogura
(mobile)
-------------------------
From: Tony Wang
Sent: 15 lipca 2011 03:51
To: Scott Marlowe
Cc: PostgreSQL
Subject: Re: [GENERAL] Weird problem that enormous locks
On Fri, Jul 15, 2011 at 08:22, Scott Marlowe wrote:#ccc solid;padding-left:1ex"> > Its a game server, and theOn Thu, Jul 14, 2011 at 6:01 PM, Tony Wang wrote:On Fri, Jul 15, 2011 at 01:13, Scott Marlowe> wrote:On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang wrote:
; On Thu, Jul 14, 2011 at 10:35, John R Pierce>> > wrote:class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px
queries are updating users money,
as#ccc solid;padding-left:1ex"> > normal.blockquote> id =
> The sql is like "UPDATE player SET money = money + 100
where
>> > 12345".
the indexes.
0 .8ex;border-left:1px #ccc solid;padding-left:1ex"> > The
> weird thing is there was another ExclusiveLoockquote class="gmail_quote" style="margin:0 0 0uot;player" got two locks, one RowExclusiveLock and one
.8ex;border-left:1px #ccc solid;padding-left:1ex">
ExclusiveLock.
kquote> acquired on
c solid;padding-left:1ex"> > user
> tables by any PostgreSQL command."
You need to figure out what part of your app, or maybe a
rogue
>> developer etorder-left:1px #ccc solid;padding-left:1ex">"gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc
Yeah, thats what Im trying to do
Cool. In your first post you said:
solid;padding-left:1ex"> select pg_class.relname,
pg_locks.mode, pg_locks.granted,
pg_stat_activity.current_query,
pg_stat_activity.query_start,
pg_stat_activity.xact_start as transaction_start,
age(now(),pg_stat_activity.query_start) as query_age,
> age(now(),pg_ste> pg_locks.pid=pg_stat_activity.procpid and,pg_locks left
outer join pg_class on (pg_locks.relation = pg_class.oid)
where
> substr(pg_class.relname,1,3) != pg_ order by query_start;cial thing I can find is that there were a lotbr>ExclusiveLock, while its normal the locks are
only AccessShareLock and RowEx
So what did / does current_query say when its happening? If
it
says
you dont have access permission then run that query as root
when
it
happens again.
As I said, its normal update like "UPDATE player SET money =
money +
100 WHERE id=12345", but there are quite many[7] mailto:rsmogura@softperience.eu [8]
Links:
------
[1] mailto:wwwjfy@gmail.com [2]
[2] mailto:scott.marlowe@gmail.com [3]
[3] mailto:wwwjfy@gmail.com [4]
[4] mailto:pierce@hogranch.com [5]
[5]
http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6]
[6] mailto:scott.marlowe@gmail.com [7]
much about this), but I imagine following, If You dont use auto
commit or commit:
1. User A updates moneys, gets connections C1, locks his row, no
commit
2. User A updates moneys again, gets connection C2, but C1 still
holds lock.
Regards,
Radosław SmoguraThe interesting thing is, I didnt find any timeout/exception after the
Any connection pool behaves similarly. The connection C1 surely will
be committed and returned after the operation finished. Having said
that, the ONLY possible reason is some transactions hanged holding the
locks, and cause others cannot work any more, and the "ExclusiveLock"
is not a problem, right?
"lock" period ended in postgresql log, only long query time.
I meant I'm sure the pooler will do that, when a request ends.
Regards,
Radosław Smogura
On Fri, Jul 15, 2011 at 18:44, Scott Marlowe <scott.marlowe@gmail.com> wrote:
On Fri, Jul 15, 2011 at 4:36 AM, Tony Wang <wwwjfy@gmail.com> wrote:They're likely exclusive locks on a transaction, which are normal.
> Weird that I receive your each message twice.
> On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <rsmogura@softperience.eu>
> wrote:
>>
>> Simple and obvious question right now do You call commit after
>> transaction? If yes do you use any query or connection pooler?
>
> Yes. connection pool is used as application level, not db level.
> no commit after transaction is possible (I'm trying to check the logic), I
> just cannot imagine it happened for so many users at the same time, and then
> calmed down for long time, and came again.
> I found the query I used to log locks would miss locks that relname is null.
> will add that, though no idea why it's null
Thanks for the info. May miss something without such rows.