Re: Postgres Replaying WAL slowly

[Jeff Frost <jeff@xxxxxxxxxxxxx>]

On Jun 30, 2014, at 1:15 PM, Andres Freund <andres@xxxxxxxxxxxxxxx> wrote:

On 2014-06-30 12:57:56 -0700, Jeff Frost wrote:

On Jun 30, 2014, at 12:54 PM, Matheus de Oliveira <matioli.matheus@xxxxxxxxx> wrote:

On Mon, Jun 30, 2014 at 4:42 PM, Jeff Frost <jeff@xxxxxxxxxxxxx> 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?