Re: Postgres slave not catching up (on 9.2)
| От | Ruben Domingo Gaspar Aparicio |
|---|---|
| Тема | Re: Postgres slave not catching up (on 9.2) |
| Дата | |
| Msg-id | 6FCBD86BDC789C43AC696969EC34022801093E6947@CERNXCHG44.cern.ch обсуждение исходный текст |
| Ответ на | Re: Postgres slave not catching up (on 9.2) (Andres Freund <andres@2ndquadrant.com>) |
| Список | 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 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 по дате отправления:
Предыдущее
От: Scott MarloweДата:
Сообщение: Re: Small performance regression in 9.2 has a big impact