Re: [GENERAL] Weird problem that enormous locks

[Craig Ringer]

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/

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Wed, Jul 13, 2011 at 08:40, Craig Ringer wrote:

> 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.
>

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/ 
>

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

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  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.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
>

Re: [GENERAL] Weird problem that enormous locks

[John R Pierce]

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 pierceN 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Thu, Jul 14, 2011 at 10:01, John R Pierce  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.


>
> --
> john r pierceN 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
>

Re: [GENERAL] Weird problem that enormous locks

[John R Pierce]

On 07/13/11 7:16 PM, Tony Wang wrote:
On Thu, Jul 14, 2011 at 10:01, John R Pierce > 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 doingIf 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 pierceN 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Thu, Jul 14, 2011 at 10:35, John R Pierce  wrote:

> On 07/13/11 7:16 PM, Tony Wang wrote:
>
>  On Thu, Jul 14, 2011 at 10:01, John R Pierce > pie...@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 doingIf you had that many
> exclusive_locks,  just what were the queries making these locks doing?
>

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 pierceN 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
>

Re: [GENERAL] Weird problem that enormous locks

[John R Pierce]

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 pierceN 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Thu, Jul 14, 2011 at 12:35, John R Pierce  wrote:

> 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?
>
>
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 pierceN 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
>

Re: [GENERAL] Weird problem that enormous locks

[Radoslaw Smogura]

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)

-Original Message-
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  wrote:

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?




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 pierceN 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

Thanks, I've checked the "for update". No such queries there.

On Thu, Jul 14, 2011 at 15:36, Radoslaw Smogura 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 locks
>
>
> On Thu, Jul 14, 2011 at 12:35, John R Pierce  wrote:
>
>> 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?
>>
>>
> 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 pierceN 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<http://www.postgresql.org/mailpref/pgsql-general>
>>
>
>

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

On Wed, Jul 13, 2011 at 9:47 PM, Tony Wang  wrote:
> On Thu, Jul 14, 2011 at 10:35, John R Pierce  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.

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

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:
> > 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

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

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:
>> > 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.

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

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:
> >> > 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

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang  wrote:
> 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:
>> >> > 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)?

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 10:05, Scott Marlowe wrote:

> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang  wrote:
> > 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:
> >> >> > 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.

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang  wrote:
> On Fri, Jul 15, 2011 at 10:05, Scott Marlowe 
> wrote:
>>
>> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang  wrote:
>> > 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:
>> >> >> > 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?

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 10:42, Scott Marlowe wrote:

> On Thu, Jul 14, 2011 at 8:28 PM, Tony Wang  wrote:
> > On Fri, Jul 15, 2011 at 10:05, Scott Marlowe 
> > wrote:
> >>
> >> On Thu, Jul 14, 2011 at 7:51 PM, Tony Wang  wrote:
> >> > 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 <
> pie...@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?
>

There were 2519 RowExclusiveLock and 85 ExclusiveLock

Re: [GENERAL] Weird problem that enormous locks

[John R Pierce]

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 pierceN 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 12:38, John R Pierce  wrote:

> 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?
>
>
the row locks includes indexes locks, also some updates related to two
tables. Roughly, about 700+ unique RowExclusiveLock there


>
>
>
> --
> john r pierceN 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
>

Re: [GENERAL] Weird problem that enormous locks

[Radoslaw Smogura]

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)

-Original Message-
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:
>> > 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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

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 (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 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:
>> >> > 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
>

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

On Fri, Jul 15, 2011 at 4:36 AM, 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 (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.

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

Re: [GENERAL] Weird problem that enormous locks

[Radosław Smogura]

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:www...@gmail.com
[2] mailto:scott.marl...@gmail.com
[3] mailto:www...@gmail.com
[4] mailto:pie...@hogranch.com
[5] http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6] mailto:scott.marl...@gmail.com
[7] mailto:rsmog...@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

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

Re: [GENERAL] Weird problem that enormous locks

[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.

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

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:
>>>>
>>>>> > 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<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:www...@gmail.com
>> [2] mailto:scott.marl...@gmail.com
>> [3] mailto:www...@gmail.com
>> [4] mailto:pie...@hogranch.com
>> [5] 
>> http://www.postgresql.org/**docs/8.4/static/explicit-**locking.html<http://www.postgresql.org/docs/8.4/static/explicit-locking.html>
>> [6] mailto:scott.marl...@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
>

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.

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 18:52, 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.
>

gmail should be clever enough handling that, at lease I didn't receive twice
this time.

Re: [GENERAL] Weird problem that enormous locks

[Scott Marlowe]

On Fri, Jul 15, 2011 at 5:08 AM, Tony Wang  wrote:
> On Fri, Jul 15, 2011 at 18:52, 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.
>
> 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.

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

Re: [GENERAL] Weird problem that enormous locks

[Radosław Smogura]

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:www...@gmail.com [2]
[2] mailto:scott.marl...@gmail.com [3]
[3] mailto:www...@gmail.com [4]
[4] mailto:pie...@hogranch.com [5]
[5]
http://www.postgresql.org/docs/8.4/static/explicit-locking.html
[6]
[6] mailto:scott.marl...@gmail.com [7]
[7] mailto:rsmog...@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


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

Re: [GENERAL] Weird problem that enormous locks

[Adrian Klaver]

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.kla...@gmail.com

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

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 19:47, Radosław Smogura 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:
>>
>>  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
>>>>>>
>&g

Re: [GENERAL] Weird problem that enormous locks

[Tony Wang]

On Fri, Jul 15, 2011 at 18:44, Scott Marlowe wrote:

> On Fri, Jul 15, 2011 at 4:36 AM, Tony Wang  wrote:
> > Weird that I receive your each message twice.
> > On Fri, Jul 15, 2011 at 15:33, Radoslaw Smogura <
> rsmog...@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.
>

Thanks for the info. May miss something without such rows.