Re: Postgres slave not catching up (on 9.2)

From: Ruben Domingo Gaspar Aparicio
Subject: Re: Postgres slave not catching up (on 9.2)
Date: ,
Msg-id: 6FCBD86BDC789C43AC696969EC34022801093E6947@CERNXCHG44.cern.ch
(view: Whole thread, Raw)
In response to: Re: Postgres slave not catching up (on 9.2)  (Andres Freund)
List: pgsql-performance

Tree view

Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Robert Klemme, )
  Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Andres Freund, )
  Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )
 Re: Postgres slave not catching up (on 9.2)  (Ruben Domingo Gaspar Aparicio, )

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
exclusivemode: 

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



pgsql-performance by date:

From: Ruben Domingo Gaspar Aparicio
Date:
Subject: Re: Postgres slave not catching up (on 9.2)
From: Patrick Krecker
Date:
Subject: CTE query plan ignores selective index