Re: Postgres Replaying WAL slowly

On Jun 30, 2014, at 1:15 PM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:

> On 2014-06-30 12:57:56 -0700, Jeff Frost wrote:
>>
>> On Jun 30, 2014, at 12:54 PM, Matheus de Oliveira <matioli(dot)matheus(at)gmail(dot)com> wrote:
>>
>>>
>>> On Mon, Jun 30, 2014 at 4:42 PM, Jeff Frost <jeff(at)pgexperts(dot)com> wrote:
>>> And if you go fishing in pg_class for any of the oids, you don't find anything:
>>>
>>> That is probably because you are connected in the wrong database. Once you connect to the database of interest, you don't even need to query pg_class, just cast relation attribute to regclass:
>>>
>>> SELECT relation::regclass, ...
>>> FROM pg_locks WHERE database = (SELECT oid FROM pg_database WHERE datname = current_database());
>>>
>>
>> Yah, i thought about that too, but verified I am in the correct DB. Just for clarity sake:
>
> So these are probably relations created in uncommitted
> transactions. Possibly ON COMMIT DROP temp tables?

That would make sense. There are definitely quite a few of those being used.

Another item of note is the system catalogs are quite bloated:

schemaname | tablename | tbloat | wastedmb | idxbloat | wastedidxmb
------------+--------------+--------+----------+----------+-------------
pg_catalog | pg_attribute | 3945 | 106.51 | 2770 | 611.28
pg_catalog | pg_class | 8940 | 45.26 | 4420 | 47.89
pg_catalog | pg_type | 4921 | 18.45 | 5850 | 81.16
pg_catalog | pg_depend | 933 | 10.23 | 11730 | 274.37
pg_catalog | pg_index | 3429 | 8.36 | 3920 | 24.24
pg_catalog | pg_shdepend | 983 | 2.67 | 9360 | 30.56
(6 rows)

Would that cause the replica to spin on StandbyReleaseLocks?