Re: Postgres Replaying WAL slowly

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?