Продължение на статията ««.
Статията ще разгледа и ще покаже с конкретни запитвания и примери - каква полезна информация може да се получи чрез историята на представянето pg_stat_activity.
Предупреждение.
Поради новостта на темата и незавършения тестов период, статията може да съдържа грешки. Критиката и коментарите са добре дошли и очаквани.
Входни данни
История на представянето pg_stat_statements
pg_stat_history
CREATE TABLE pg_stat_history (
id SERIAL,
snapshot_timestamp timestamp без времева зона,
database_id integer,
dbid oid,
userid oid,
queryid bigint,
query text,
calls bigint,
total_time double precision,
min_time double precision,
max_time double precision,
mean_time double precision,
stddev_time double precision,
rows bigint,
shared_blks_hit bigint,
shared_blks_read bigint,
shared_blks_dirtied bigint,
shared_blks_written bigint,
local_blks_hit bigint,
local_blks_read bigint,
local_blks_dirtied bigint,
local_blks_written bigint,
temp_blks_read bigint,
temp_blks_written bigint,
blk_read_time double precision,
blk_write_time double precision,
baseline_id integer );Таблицата се запълва всеки час, използвайки dblink към целевата база данни. Най-интересният и полезен стълб в таблицата е, разбира се queryid.
История на представянето pg_stat_activity
archive_pg_stat_activity
CREATE TABLE archive_pg_stat_activity
(
timepoint timestamp без времева зона,
datid oid,
datname name,
pid integer,
usesysid oid,
usename name,
application_name text,
client_addr inet,
client_hostname text,
client_port integer,
backend_start timestamp без времева зона,
xact_start timestamp без времева зона,
query_start timestamp без времева зона,
state_change timestamp без времева зона,
wait_event_type text,
wait_event text,
state text,
backend_xid xid,
backend_xmin xid,
query text,
backend_type text,
queryid bigint
);Таблицата представлява секционирана по часове таблица history_pg_stat_activity (повече информация тук — и тук —
Изходни данни
КЛУСТЕР ВРЕМЕ ЗА ЦПУ (СИСТЕМА + КЛИЕНТИ )
Запитване
С
t AS
(
SELECT
date_trunc('second', timepoint)
FROM activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
( aa.wait_event_type IS NULL ) AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;Пример
КЛУСТЕР ВРЕМЕ ЗА ЦПУ (СИСТЕМА + КЛИЕНТИ ): 28:37:46КЛУСТЕР ВРЕМЕ ЗА ОЧАКВАНИЯ
Запитване
С
t AS
(
SELECT
date_trunc('second', timepoint)
FROM activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
( aa.wait_event_type IS NOT NULL ) AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;Пример
КЛУСТЕР ВРЕМЕ ЗА ОЧАКВАНИЯ : 30:12:49Общо стойности pg_stat_statements
Запитване
--TOTAL pg_stat
SELECT
SUM(calls) AS calls, SUM(total_time) AS total_time, SUM(rows) AS rows ,
SUM(shared_blks_hit) AS shared_blks_hit,SUM(shared_blks_read) AS shared_blks_read ,
SUM(shared_blks_dirtied) AS shared_blks_dirtied,SUM(shared_blks_written) AS shared_blks_written ,
SUM(local_blks_hit) AS local_blks_hit , SUM(local_blks_read) AS local_blks_read ,
SUM(local_blks_dirtied) AS local_blks_dirtied , SUM(local_blks_written) AS local_blks_written,
SUM(temp_blks_read) AS temp_blks_read, SUM(temp_blks_written) AS temp_blks_written ,
SUM(blk_read_time) AS blk_read_time , SUM(blk_write_time) AS blk_write_time
INTO
pg_total_stat_history_rec
FROM
pg_stat_history
WHERE
snapshot_timestamp BETWEEN pg_stat_history_begin AND pg_stat_history_end AND
queryid IS NULL;SQL DBTIME — общото време за изпълнение на заявките
Запитване
dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;Пример
SQL DBTIME : 136:49:36SQL CPU TIME — времето на CPU, изразходвано за изпълнение на заявките
Запитване
С WITH
t AS
(
SELECT
date_trunc('second', timepoint)
FROM activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
( aa.wait_event_type IS NULL ) AND
backend_type = 'client backend' AND
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;Пример
SQL CPU TIME : 27:40:15SQL WAITINGS TIME — общото време на изчакване за заявките
Запитване
С WITH
t AS
(
SELECT
date_trunc('second', timepoint)
FROM activity_hist.archive_pg_stat_activity aa
WHERE timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND
( aa.wait_event_type IS NOT NULL ) AND
aa.state = 'active' AND
backend_type = 'client backend'
)
SELECT count(*)
INTO waiting_total
FROM t ;Пример
SQL WAITINGS TIME : 30:04:09Следващите заявки са тривиални и за икономия на място детайлите на изпълнението са пропуснати:
Пример
| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL CALLS : 12188248
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 7997039120
| SQL SHARED BLOCKS HITS : 8868286092
| SQL SHARED BLOCKS HITS/READS % : 110.89
| SQL SHARED BLOCKS DIRTIED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938
Преминаваме към най-интригуващата част на раздела
СТАТИСТИКА ЗА ИЗЧАКВАНИЯ
ТОП 10 ИЗЧАКВАНИЯ ПО ОБЩО ВРЕМЕ ЗА ИЗЧАКВАНЕ ЗА КЛИЕНТСКИ ПРОЦЕСИ
Запитване
SELECT
wait_event_type , wait_event ,
get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * interval '1 hour') ,pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration
FROM
activity_hist.archive_pg_stat_activity aa
WHERE
timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL
GROUP BY
wait_event_type, wait_event
ORDER BY 3 DESC
LIMIT 10Пример
+------------------------------------------------------------------------------------ | ТОП 10 ЗАБАВЯНЕЙ СЪС СЪОТВЕТНАТА ВРЕМЕ ЗА СИСТЕМНИ ПРОЦЕСИ +-----+------------------------------+--------------------+-------------------- | #| вид на събитие при изчакване| изчакване на събитие| продължителност +-----+------------------------------+--------------------+-------------------- | 1| Дейност| LogicalLauncherMain| 10:43:28 | 2| Дейност| AutoVacuumMain| 10:42:49 | 3| Дейност| WalWriterMain| 10:28:53 | 4| Дейност| CheckpointerMain| 10:23:50 | 5| Дейност| BgWriterMain| 09:11:59 | 6| Дейност| BgWriterHibernate| 01:37:46 | 7| Вход/Изход| BufFileWrite| 00:02:35 | 8| LWLock| buffer_mapping| 00:01:54 | 9| Вход/Изход| DataFileRead| 00:01:23 | 10| Вход/Изход| WALWrite| 00:00:59 +-----+------------------------------+--------------------+--------------------
ТОП 10 ИЗЧАКВАНИЯ ПО ОБЩО ВРЕМЕ ЗА ИЗЧАКВАНЕ ЗА КЛИЕНТСКИ ПРОЦЕСИ
Запитване
ИЗБЕРЕТЕ
вид на събитие при изчакване, изчакване на събитие,
get_clients_waiting_duration( вид на събитие при изчакване, изчакване на събитие, pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as продължителност
OT
activity_hist.archive_pg_stat_activity aa
КЪДЕ
timepoint МЕЖДУ pg_stat_history_begin+(current_hour_diff * interval '1 hour') И pg_stat_history_end+(current_hour_diff * interval '1 hour') И тип на обратно изход = 'клиентски обратно изход' И wait_event_type IS NOT NULL
ГРУПИРАНЕ ПО вид на събитие при изчакване, изчакване на събитие
НАРЕЖДАНЕ ПО 3 НАМАЛЯВАНЕ
LIMIT 10Пример
+-----+------------------------------+--------------------+--------------------+---------- | #| вид на събитие при изчакване| изчакване на събитие| продължителност| % dbtime +-----+------------------------------+--------------------+--------------------+---------- | 1| Заключване| transactionid| 08:16:47| 6.05 | 2| Вход/Изход| DataFileRead| 06:13:41| 4.55 | 3| Времева закъснялост| PgSleep| 02:53:21| 2.11 | 4| LWLock| buffer_mapping| 00:40:42| 0.5 | 5| LWLock| buffer_io| 00:17:17| 0.21 | 6| Вход/Изход| BufFileWrite| 00:01:34| 0.02 | 7| Заключване| tuple| 00:01:32| 0.02 | 8| Клиент| ClientRead| 00:01:19| 0.02 | 9| Вход/Изход| BufFileRead| 00:00:37| 0.01 | 10| LWLock| buffer_content| 00:00:08| 0 +-----+------------------------------+--------------------+--------------------+----------
ТЕРОРИ НА ЗАБАВЯНЕ СЪС СЪОТВЕТНАТА ВРЕМЕ, ЗА СИСТЕМНИ ПРОЦЕСИ
Запитване
ИЗБЕРЕТЕ
вид на събитие при изчакване,
get_system_waiting_type_duration( вид на събитие при изчакване, pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as продължителност
OT
activity_hist.archive_pg_stat_activity aa
КЪДЕ
timepoint МЕЖДУ pg_stat_history_begin+(current_hour_diff * interval '1 hour') И pg_stat_history_end+(current_hour_diff * interval '1 hour') И тип на обратно изход != 'клиентски обратно изход' И wait_event_type IS NOT NULL
ГРУПИРАНЕ ПО вид на събитие при изчакване
НАРЕЖДАНЕ ПО 2 НАРЕЖДАНЕПример
+-----+------------------------------+-------------------- | #| wait_event_type| duration +-----+------------------------------+-------------------- | 1| Activity| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock| 00:03:02 +-----+------------------------------+--------------------
ТИПОВЕ НА ОЧАКВАНЕ ПО ОБЩО ВРЕМЕ ЗА ОЧАКВАНЕ, ЗА ПРОЦЕСИ НА КЛИЕНТА
Запитване
SELECT
wait_event_type ,
get_clients_waiting_type_duration( wait_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration
FROM
activity_hist.archive_pg_stat_activity aa
WHERE
timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type = 'client backend' AND wait_event_type IS NOT NULL
GROUP BY wait_event_type
ORDER BY 2 DESCПример
+-----+------------------------------+--------------------+-------------------- | #| wait_event_type| duration| % dbtime +-----+------------------------------+--------------------+-------------------- | 1| Lock| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| Timeout| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| Client| 00:01:19| 0.02 | 6| IPC| 00:00:04| 0 +-----+------------------------------+--------------------+--------------------
Продължителности на чакане за системни процеси и отделни заявки.
ОЧАКВАНИЯ ЗА СИСТЕМНИ ПРОЦЕСИ
Запитване
SELECT
backend_type , datname , wait_event_type , wait_event , get_backend_type_waiting_duration( backend_type , wait_event_type , wait_event , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as duration
FROM
activity_hist.archive_pg_stat_activity aa
WHERE
timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND backend_type != 'client backend' AND wait_event_type IS NOT NULL
GROUP BY backend_type , datname , wait_event_type , wait_event
ORDER BY 5 DESCПример
+-----+-----------------------------+----------+--------------------+----------------------+-------------------- | #| backend_type| dbname| wait_event_type| wait_event| duration +-----+-----------------------------+----------+--------------------+----------------------+-------------------- | 1| logical replication launcher| | Activity| LogicalLauncherMain| 10:43:28 | 2| autovacuum launcher| | Activity| AutoVacuumMain| 10:42:49 | 3| walwriter| | Activity| WalWriterMain| 10:28:53 | 4| checkpointer| | Activity| CheckpointerMain| 10:23:50 | 5| background writer| | Activity| BgWriterMain| 09:11:59 | 6| background writer| | Activity| BgWriterHibernate| 01:37:46 | 7| parallel worker| tdb1| IO| BufFileWrite| 00:02:35 | 8| parallel worker| tdb1| LWLock| buffer_mapping| 00:01:41 | 9| parallel worker| tdb1| IO| DataFileRead| 00:01:22 | 10| parallel worker| tdb1| IO| BufFileRead| 00:00:59 | 11| walwriter| | IO| WALWrite| 00:00:57 | 12| parallel worker| tdb1| LWLock| buffer_io| 00:00:47 | 13| autovacuum worker| tdb1| LWLock| buffer_mapping| 00:00:13 | 14| background writer| | IO| DataFileWrite| 00:00:12 | 15| checkpointer| | IO| DataFileWrite| 00:00:11 | 16| walwriter| | LWLock| WALWriteLock| 00:00:09 | 17| checkpointer| | LWLock| WALWriteLock| 00:00:06 | 18| background writer| | LWLock| WALWriteLock| 00:00:06 | 19| walwriter| | IO| WALInitWrite| 00:00:02 | 20| autovacuum worker| tdb1| LWLock| WALWriteLock| 00:00:02 | 21| walwriter| | IO| WALInitSync| 00:00:02 | 22| autovacuum worker| tdb1| IO| DataFileRead| 00:00:01 | 23| checkpointer| | IO| ControlFileSyncUpdate| 00:00:01 | 24| background writer| | IO| WALWrite| 00:00:01 | 25| background writer| | IO| DataFileFlush| 00:00:01 | 26| checkpointer| | IO| SLRUFlushSync| 00:00:01 | 27| autovacuum worker| tdb1| IO| WALWrite| 00:00:01 | 28| checkpointer| | IO| DataFileSync| 00:00:01 +-----+-----------------------------+----------+--------------------+----------------------+--------------------
ОЧАКВАНИЯ ЗА SQL — изчаквания за отделни заявки по queryid
Запитване
ИЗБЕРЕТЕ
queryid, datname, wait_event_type, wait_event, get_query_waiting_duration(queryid, wait_event_type, wait_event, pg_stat_history_begin + (current_hour_diff * интервал '1 час'), pg_stat_history_end + (current_hour_diff * интервал '1 час')) AS duration
ОТ
activity_hist.archive_pg_stat_activity aa
КЪДЕ
timepoint между pg_stat_history_begin + (current_hour_diff * интервал '1 час') И pg_stat_history_end + (current_hour_diff * интервал '1 час') И backend_type = 'клиентски бекенд' И wait_event_type НЕ Е NULL И queryid НЕ Е NULL
ГРУПИРАЙТЕ ПО queryid, datname, wait_event_type, wait_event
НАРЕДИ ПО 1, 5 ОПАДАЮЩО Пример
+-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | #| queryid| dbname| wait_event_type| wait_event| waitings| total | | | | | | duration| duration +-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | 1| -8247416849404883188| tdb1| Client| ClientRead| 00:00:02| | 2| -6572922443698419129| tdb1| Client| ClientRead| 00:00:05| | 3| -6572922443698419129| tdb1| IO| DataFileRead| 00:00:01| | 4| -5917408132400665328| tdb1| Client| ClientRead| 00:00:04| | 5| -4091009262735781873| tdb1| Client| ClientRead| 00:00:03| | 6| -1473395109729441239| tdb1| Client| ClientRead| 00:00:01| | 7| 28942442626229688| tdb1| IO| BufFileWrite| 00:01:34| 00:46:06 | 8| 28942442626229688| tdb1| LWLock| buffer_mapping| 00:01:05| 00:46:06 | 9| 28942442626229688| tdb1| IO| DataFileRead| 00:00:44| 00:46:06 | 10| 28942442626229688| tdb1| IO| BufFileRead| 00:00:37| 00:46:06 | 11| 28942442626229688| tdb1| LWLock| buffer_io| 00:00:35| 00:46:06 | 12| 28942442626229688| tdb1| Client| ClientRead| 00:00:05| 00:46:06 | 13| 28942442626229688| tdb1| IPC| MessageQueueReceive| 00:00:03| 00:46:06 | 14| 28942442626229688| tdb1| IPC| BgWorkerShutdown| 00:00:01| 00:46:06 | 15| 389015618226997618| tdb1| Lock| transactionid| 03:55:09| 04:14:15 | 16| 389015618226997618| tdb1| IO| DataFileRead| 03:23:09| 04:14:15 | 17| 389015618226997618| tdb1| LWLock| buffer_mapping| 00:12:09| 04:14:15 | 18| 389015618226997618| tdb1| LWLock| buffer_io| 00:10:18| 04:14:15 | 19| 389015618226997618| tdb1| Lock| tuple| 00:00:35| 04:14:15 | 20| 389015618226997618| tdb1| LWLock| WALWriteLock| 00:00:02| 04:14:15 | 21| 389015618226997618| tdb1| IO| DataFileWrite| 00:00:01| 04:14:15 | 22| 389015618226997618| tdb1| LWLock| SyncScanLock| 00:00:01| 04:14:15 | 23| 389015618226997618| tdb1| Client| ClientRead| 00:00:01| 04:14:15 | 24| 734234407411547467| tdb1| Client| ClientRead| 00:00:11| | 25| 734234407411547467| tdb1| LWLock| buffer_mapping| 00:00:05| | 26| 734234407411547467| tdb1| IO| DataFileRead| 00:00:02| | 27| 1237430309438971376| tdb1| LWLock| buffer_mapping| 00:02:18| 02:45:40 | 28| 1237430309438971376| tdb1| IO| DataFileRead| 00:00:27| 02:45:40 | 29| 1237430309438971376| tdb1| Client| ClientRead| 00:00:02| 02:45:40 | 30| 2404820632950544954| tdb1| Client| ClientRead| 00:00:01| | 31| 2515308626622579467| tdb1| Client| ClientRead| 00:00:02| | 32| 4710212362688288619| tdb1| LWLock| buffer_mapping| 00:03:08| 02:18:21 | 33| 4710212362688288619| tdb1| IO| DataFileRead| 00:00:22| 02:18:21 | 34| 4710212362688288619| tdb1| Client| ClientRead| 00:00:06| 02:18:21 | 35| 4710212362688288619| tdb1| LWLock| buffer_io| 00:00:02| 02:18:21 | 36| 9150846928388977274| tdb1| IO| DataFileRead| 00:01:19| | 37| 9150846928388977274| tdb1| LWLock| buffer_mapping| 00:00:34| | 38| 9150846928388977274| tdb1| Client| ClientRead| 00:00:10| | 39| 9150846928388977274| tdb1| LWLock| buffer_io| 00:00:01| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
КЛИЕНТСКАЯ SQL СТАТИСТИКА — ТОП запитвания
Запитвания за получаване отново, са тривиални и за спестяване на място, не са посочени.
Примери
+------------------------------------------------------------------------------------ | КЛИЕНТСКИ SQL по време на изчакване +--------------------+----------+----------+----------+----------+----------+-------------------- | време на изчакване| повиквания| % време на базата данни| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+-------------------- | 04:14:15| 19| 3.1| 10.83| 11.52| tdb1| 389015618226997618 | 02:45:40| 746| 2.02| 4.23| 0.08| tdb1| 1237430309438971376 | 02:18:21| 749| 1.69| 3.39| 0.1| tdb1| 4710212362688288619 | 00:46:06| 375| 0.56| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+-------------------- | КЛИЕНТСКИ SQL по време на CPU +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | време на CPU| повиквания| % време на базата данни|време на изпълнение| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:59:49| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 01:10:12| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:56:15| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:15:35| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | КЛИЕНТСКИ SQL по време на изчакване на I/O от потребителя +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | време на изчакване на I/O| повиквания| % време на базата данни|време на изпълнение| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 03:23:10| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:02:54| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:00:27| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 00:00:22| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | КЛИЕНТСКИ SQL по четене на споделени буфери +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | четения от буфери| повиквания| % време на базата данни|време на изпълнение| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 1056388566| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 11709251| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 3439004| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 3373330| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | КЛИЕНТСКИ SQL по време на четене от диск +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | време на четене| повиквания| % време на базата данни|време на изпълнение| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 02:16:30| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 | 00:04:50| 375| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 00:01:10| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 00:00:57| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | КЛИЕНТСКИ SQL по извиквания +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | повиквания| редове| % време на базата данни|време на изпълнение| % CPU| % IO| име на базата данни| id на запитването +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 749| 749| 1.69| 02:18:21| 3.39| 0.1| tdb1| 4710212362688288619 | 746| 746| 2.02| 02:45:40| 4.23| 0.08| tdb1| 1237430309438971376 | 375| 0| 0.56| 00:46:06| 0.94| 0.41| tdb1| 28942442626229688 | 19| 19| 3.1| 04:14:15| 10.83| 11.52| tdb1| 389015618226997618 +--------------------+----------+----------+----------+----------+----------+----------+--------------------
Резюме
Използвайки представените заявки и получените отчети, можем да получим по-пълна картина за анализ и решаване на проблеми с деградацията на производителността по отделни заявки и целия клъстер в целом.
Развитие
Докато плановете за развитие са следните:
- Да се добави отчетност за историята на блокировките. Заявките бяха тествани и ще бъдат представени скоро.
- Да се използва разширение TimescaleDB за съхранение на историята на pg_stat_activity и pg_locks.
- Да се подготви пакетно решение в github за масово разгръщане на продукционния бази.
Продължението следва…
Източник: habr.com
