Нийтлэлийн үргэлжлэл "
Энэхүү нийтлэл нь pg_stat_activity харагдацын түүхийг ашиглан ямар хэрэгтэй мэдээллийг олж авах боломжтойг тодорхой асуултууд болон жишээнүүдийг ашиглан судалж, харуулах болно.
Анхааруулга
Сэдвийн шинэлэг зүйл, туршилтын хугацаа бүрэн бус байгаа тул нийтлэлд алдаа гарч болзошгүй. Шүүмжлэл, санал хүсэлтийг хүлээн авч, хүлээж байна.
Оролтын мэдээлэл
pg_stat_statements илгээсэн түүх
pg_stat_history
CREATE TABLE pg_stat_history (
id SERIAL,
snapshot_timestamp timestamp without time zone,
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 without time zone,
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 without time zone,
xact_start timestamp without time zone,
query_start timestamp without time zone,
state_change timestamp without time zone,
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 хүснэгт юм (Дэлгэрэнгүй мэдээллийг эндээс -
Гаралт
КЛАСТРЫН 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
aa.state = 'active'
)
SELECT count(*)
INTO cpu_total
FROM t ;
Жишээ нь:
CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46
КЛАСТЕР ХҮЛЭЭХ ЦАГ
Хүсэлт гаргах
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'
)
SELECT count(*)
INTO cpu_total
FROM t ;
Жишээ нь:
CLUSTER WAITINGS TIME : 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) 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:36
SQL CPU-ийн ХУГАЦАА - Асуулга гүйцэтгэхэд зарцуулсан 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:15
SQL ХҮЛЭЭЛГЭЭНИЙ ХУГАЦАА - асуулга хүлээх нийт хугацаа
Хүсэлт гаргах
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 DIRTED : 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| IO| BufFileWrite| 00:02:35 | 8| LWLock| buffer_mapping| 00:01:54 | 9| IO| DataFileRead| 00:01:23 | 10| IO| WALWrite| 00:00:59 +-----+-----------------------------------+------- -------------+-----------
ҮЙЛЧЛҮҮЛЭГЧДИЙН ҮЙЛ АЖИЛЛАГААНЫ НИЙТ ХҮЛЭЭЛТИЙН ХҮЛЭЭЛТИЙН ШИЛДЭГ 10 ХҮЛЭЭЛТ
Хүсэлт гаргах
SELECT
wait_event_type , wait_event ,
get_clients_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
Жишээ нь:
+----------+----------------------------+--------- --- ---------+----------+---------- | #| хүлээх_үйл явдлын_төрөл| хүлээх_үйл явдал| үргэлжлэх хугацаа| % dbtime +-----+------------------------------+---------- ----------+----------+---------- | 1| Түгжих| гүйлгээ| 08:16:47| 6.05 | 2| IO| 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| IO| BufFileWrite| 00:01:34| 0.02 | 7| Түгжих| tuple| 00:01:32| 0.02 | 8| Үйлчлүүлэгч| ClientRead| 00:01:19| 0.02 | 9| IO| BufFileRead| 00:00:37| 0.01 | 10| LWLock| buffer_content| 00:00:08| 0 +-----+----------------------------+----------- -- -------+-----------+----------
СИСТЕМИЙН ПРОЦЕССИЙГ НИЙТ ХҮЛЭЭЛГЭЭНИЙ ХУГАЦААР ХҮЛЭЭЛГИЙН ТӨРЛҮҮД
Хүсэлт гаргах
SELECT
wait_event_type ,
get_system_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
Жишээ нь:
+----------+----------------------------+--------- --- -------- | #| хүлээх_үйл явдлын_төрөл| үргэлжлэх хугацаа +-----+----------------------------+----------- -- ------- | 1| Үйл ажиллагаа| 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
Жишээ нь:
+----------+----------------------------+--------- --- ---------+---------- | #| хүлээх_үйл явдлын_төрөл| үргэлжлэх хугацаа| % dbtime +-----+------------------------------+---------- ----------+-------------------- | 1| Түгжих| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| Хугацаа дууссан| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| Үйлчлүүлэгч| 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-ээр бие даасан асуулга хүлээж байна
Хүсэлт гаргах
SELECT
queryid , datname , wait_event_type , wait_event , get_query_waiting_duration( queryid , 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 AND queryid IS NOT NULL
GROUP BY queryid , datname , wait_event_type , wait_event
ORDER BY 1 , 5 DESC
Жишээ нь:
+-----+-------------------------+----------+--------------------+--------------------+--------------------+-------------------- | #| 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| +-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
CLIENT SQL STATICTICS - ШИЛДЭГ асуулга
Хүлээн авах хүсэлт нь дахин өчүүхэн бөгөөд орон зай хэмнэх үүднээс өгдөггүй.
жишээ
+------------------------------------------------------------------------------------ | CLIENT SQL ordered by Elapsed Time +--------------------+----------+----------+----------+----------+----------+-------------------- | elapsed time| calls| % dbtime| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by CPU Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | cpu time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by User I/O Wait Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | io_wait time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Shared Buffers Reads +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | buffers reads| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Disk Reads Time +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | read time| calls| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | CLIENT SQL ordered by Executions +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | calls| rows| % dbtime|total_time| % CPU| % IO| dbname| queryid +--------------------+----------+----------+----------+----------+----------+----------+-------------------- | 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 +--------------------+----------+----------+----------+----------+----------+----------+--------------------
Үр дүн
Ирүүлсэн асуулга болон үр дүнгийн тайланг ашигласнаар та тусдаа асуулга болон бүхэлд нь кластерын гүйцэтгэлийн доройтлын асуудлыг шинжлэх, шийдвэрлэх талаар илүү бүрэн дүр зургийг авах боломжтой.
Хөгжил
Одоогийн байдлаар хөгжлийн төлөвлөгөө нь дараах байдалтай байна.
- Мэдээллийг блоклосон түүхээр нэмнэ үү. Хүсэлтийг шалгаж байгаа бөгөөд удахгүй илгээнэ.
- pg_stat_activity болон pg_locks түүхийг хадгалахын тулд TimescaleDB өргөтгөлийг ашиглана уу.
- Үйлдвэрлэлийн бааз дээр бөөнөөр нь байршуулахын тулд github дээр багц шийдлийг бэлтгэ.
Үргэлжлэл бий…
Эх сурвалж: www.habr.com