Re: Postgres slave not catching up (on 9.2)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance





[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux