| From: | Ruben Domingo Gaspar Aparicio <Ruben(dot)Gaspar(dot)Aparicio(at)cern(dot)ch> |
|---|---|
| To: | Andres Freund <andres(at)2ndquadrant(dot)com> |
| Cc: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>, "dbondemand-admin (DB On Demand administrators)" <dbondemand-admin(at)cern(dot)ch> |
| Subject: | Re: Postgres slave not catching up (on 9.2) |
| Date: | 2014-11-28 16:21:43 |
| Message-ID: | 6FCBD86BDC789C43AC696969EC34022801093E6947@CERNXCHG44.cern.ch |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
Hi Andres,
Sorry for my delay to reply. Here below my replies:
> I have a couple of questions:
> 1) Is the standby actually used for querying? Is it possible that replay
> frequently conflicts with active queries? As you don't have
> hot_standby_feedback enabled that seems quite possible.
Nowadays we don't manage to have a decent lag so the standby is not use at all. No clients connect to it.
> 2) Is the startup process on the standby CPU or IO bound?
The servers is almost idle. I don't see any bottle neck either on CPU or IO.
> 3) Does the workload involve loads of temporary tables or generally
> transactions locking lots of tables exclusively in one transaction?
We have monitored the master for a couple of days we haven't detected any "create temp table" statement.
For the locks I see it's also not the case in my opinion, at a given point in time I don't see many tables lock in exclusive mode:
puppetdb=# SELECT locktype, relation::regclass, mode, transactionid AS tid, datname,
virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
ON db.oid = l.database WHERE NOT pid = pg_backend_pid();
locktype | relation | mode | tid | datname | vtid | pid | granted
---------------+----------------------------------------------+------------------+-----------+----------+------------+--------+---------
relation | resource_params_cache_pkey | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | resource_params_cache | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalog_resources_pkey | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalog_resources_exported_true | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalog_resources_resource | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalog_resources_type | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalog_resources_type_title | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalog_resources | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalogs_transaction_uuid | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | idx_catalogs_transaction_uuid | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_certname_key | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_certname_key | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_hash_key | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_hash_key | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_pkey | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs_pkey | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | catalogs | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | certnames_pkey | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | certnames_pkey | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
relation | certnames | AccessShareLock | | puppetdb | 5/3099716 | 54422 | t
relation | certnames | RowExclusiveLock | | puppetdb | 5/3099716 | 54422 | t
virtualxid | | ExclusiveLock | | | 5/3099716 | 54422 | t
relation | resource_params_cache_pkey | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | resource_params_cache | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalog_resources_pkey | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalog_resources_exported_true | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalog_resources_resource | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalog_resources_type | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalog_resources_type_title | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalog_resources | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalogs_transaction_uuid | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | idx_catalogs_transaction_uuid | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_certname_key | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_certname_key | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_hash_key | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_hash_key | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_pkey | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs_pkey | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | catalogs | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | certnames_pkey | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | certnames_pkey | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
relation | certnames | AccessShareLock | | puppetdb | 20/2901642 | 100098 | t
relation | certnames | RowExclusiveLock | | puppetdb | 20/2901642 | 100098 | t
virtualxid | | ExclusiveLock | | | 20/2901642 | 100098 | t
relation | idx_catalogs_transaction_uuid | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | catalogs_certname_key | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | catalogs_hash_key | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | catalogs_pkey | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | catalogs | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | certnames_pkey | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | certnames_pkey | RowExclusiveLock | | puppetdb | 21/2767248 | 13044 | t
relation | certnames | AccessShareLock | | puppetdb | 21/2767248 | 13044 | t
relation | certnames | RowExclusiveLock | | puppetdb | 21/2767248 | 13044 | t
virtualxid | | ExclusiveLock | | | 21/2767248 | 13044 | t
relation | edges | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | resource_params_cache_pkey | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | resource_params_cache | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | resource_params_cache | RowShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalog_resources_pkey | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalog_resources_pkey | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_exported_true | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_exported_true | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_resource | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_resource | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_type | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_type | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_type_title | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalog_resources_type_title | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalog_resources | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalog_resources | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalogs_transaction_uuid | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | idx_catalogs_transaction_uuid | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_certname_key | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_certname_key | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_hash_key | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_hash_key | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_pkey | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs_pkey | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | catalogs | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | certnames_pkey | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | certnames_pkey | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
relation | certnames | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | certnames | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
virtualxid | | ExclusiveLock | | | 27/1597400 | 77873 | t
relation | certnames | RowShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | edges | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
transactionid | | ExclusiveLock | 191755866 | | 27/1597400 | 77873 | t
transactionid | | ExclusiveLock | 191755880 | | 20/2901642 | 100098 | t
relation | edges_certname_source_target_type_unique_key | AccessShareLock | | puppetdb | 27/1597400 | 77873 | t
relation | edges_certname_source_target_type_unique_key | RowExclusiveLock | | puppetdb | 27/1597400 | 77873 | t
transactionid | | ExclusiveLock | 191755874 | | 5/3099716 | 54422 | t
transactionid | | ExclusiveLock | 191755883 | | 21/2767248 | 13044 | t
(95 rows)
Jus to comment that we are running a DBaaS, we don't know much about the apps running on our servers.
Thank you,
Ruben
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Patrick Krecker | 2014-12-02 20:59:23 | CTE query plan ignores selective index |
| Previous Message | Scott Marlowe | 2014-11-26 17:21:29 | Re: Small performance regression in 9.2 has a big impact |