Re: Postgres Replaying WAL slowly

On Jun 30, 2014, at 12:32 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Jeff Frost <jeff(at)pgexperts(dot)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?