pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Jeff Frost <jeff@pgexperts.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andres Freund <andres@2ndquadrant.com>
Cc: Soni M <diptatapa@gmail.com>
Cc: Heikki Linnakangas <hlinnakangas@vmware.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Postgres Replaying WAL slowly
Date: Mon, 30 Jun 2014 12:42:19 -0700
Message-ID: <C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com> (raw )
In-Reply-To: <20300.1404156750@sss.pgh.pa.us >
References: <CAAMgDXm5Y9QPELKvCGBrFvPcuR0ENVA1tEYRcazhDSxs6xnX5w@mail.gmail.com >
<53AFCEE3.4080009@vmware.com >
<CAAMgDXnKknBDk+Zb3BXau1cKV7y1A0vd9CJpX=vkSTXVGBrZwA@mail.gmail.com >
<53B10C4C.8090701@vmware.com >
<CAAMgDX=c8_6tutCDNiu38Z4wguKMGbhqznVNyMiOC5PZBpn1Fw@mail.gmail.com >
<53B18CE0.4060501@vmware.com >
<20140630171416.GK21422@awork2.anarazel.de >
<CAAMgDXnT8cQbwxcNXfFKONrRVAKoGhR-3FWYSX_5gJcFrEMPMQ@mail.gmail.com >
<8DA8C140-818D-4184-AAE6-CA37278B983C@pgexperts.com >
<20140630183908.GT26930@awork2.anarazel.de >
<0A7B96D4-DB10-49A8-A268-F28960F2E137@pgexperts.com >
<F3E1168D-8D49-407E-A8B7-B3ED81C93DCB@pgexperts.com >
<20300.1404156750@sss.pgh.pa.us >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On Jun 30, 2014, at 12:32 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Jeff Frost <jeff@pgexperts.com> writes:
>> Sampling pg_locks on the primary shows ~50 locks with ExclusiveLock mode:
>
>> mode | count
>> --------------------------+-------
>> AccessExclusiveLock | 11
>> AccessShareLock | 2089
>> ExclusiveLock | 46
>> RowExclusiveLock | 81
>> RowShareLock | 17
>> ShareLock | 4
>> ShareUpdateExclusiveLock | 5
>
> That's not too helpful if you don't pay attention to what the lock is on;
> it's likely that all the ExclusiveLocks are on transactions' own XIDs,
> which isn't relevant to the standby's behavior. The AccessExclusiveLocks
> are probably interesting though --- you should look to see what those
> are on.
You're right about the ExclusiveLocks.
Here's how the AccessExclusiveLocks look:
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted
----------+----------+------------+------+-------+------------+---------------+---------+------------+----------+--------------------+-------+---------------------+---------
relation | 111285 | 3245291551 | | | | | | | | 233/170813 | 23509 | AccessExclusiveLock | t
relation | 111285 | 3245292820 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292833 | | | | | | | | 173/1723993 | 23407 | AccessExclusiveLock | t
relation | 111285 | 3245287874 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
relation | 111285 | 3245292836 | | | | | | | | 173/1723993 | 23407 | AccessExclusiveLock | t
relation | 111285 | 3245292774 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292734 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292827 | | | | | | | | 173/1723993 | 23407 | AccessExclusiveLock | t
relation | 111285 | 3245288540 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
relation | 111285 | 3245292773 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292775 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292743 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292751 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245288669 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
relation | 111285 | 3245292817 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245288657 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
object | 111285 | | | | | | 2615 | 1246019760 | 0 | 233/170813 | 23509 | AccessExclusiveLock | t
relation | 111285 | 3245292746 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245287876 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
relation | 111285 | 3245292739 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292826 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292825 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245292832 | | | | | | | | 173/1723993 | 23407 | AccessExclusiveLock | t
relation | 111285 | 3245292740 | | | | | | | | 5/22498235 | 23427 | AccessExclusiveLock | t
relation | 111285 | 3245287871 | | | | | | | | 133/3818415 | 23348 | AccessExclusiveLock | t
(25 rows)
And if you go fishing in pg_class for any of the oids, you don't find anything:
SELECT s.procpid,
s.query_start,
n.nspname,
c.relname,
l.mode,
l.granted,
s.current_query
FROM pg_locks l,
pg_class c,
pg_stat_activity s,
pg_namespace n
WHERE l.relation = c.oid
AND l.pid = s.procpid
AND c.relnamespace = n.oid
AND l.mode = 'AccessExclusiveLock';
procpid | query_start | nspname | relname | mode | granted | current_query
---------+-------------+---------+---------+------+---------+---------------
(0 rows)
Temp tables maybe?
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
view thread (33+ messages) latest in thread
Message-ID: <C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com>
Permalink: ../C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com/
Also on: postgresql.org/message-id/C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: jeff@pgexperts.com, tgl@sss.pgh.pa.us, andres@2ndquadrant.com, diptatapa@gmail.com, hlinnakangas@vmware.com
Subject: Re: Postgres Replaying WAL slowly
In-Reply-To: <C64129CF-D034-4311-AD32-09EF048C3E93@pgexperts.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox