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@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
