ProHoster > Блог > басқарма > PostgreSQL жүйесінде жұмыс жүктемесі профилін және күту тарихын алудың бір әдісі

PostgreSQL жүйесінде жұмыс жүктемесі профилін және күту тарихын алудың бір әдісі

Мақаланың жалғасы «PostgreSQL үшін ASH аналогын жасау әрекеті «.

Мақалада нақты сұраулар мен мысалдар арқылы 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 арқылы сағат сайын толтырылады. Кестедегі ең қызықты және пайдалы баған, әрине сұрау коды.

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 кестесі (Толығырақ осында - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? және мұнда - PostgreSQL үшін ASH аналогын жасау әрекеті.)

Шығару

КЛАСТЕРЛІК 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 УАҚЫТЫ – сұрауларды орындауға кеткен процессордың уақыты

Сұрау

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 КҮТУ +-----+----------------------------------------+-- ------------------+-------------------- | #| күту_оқиғасының_түрі| wait_event| ұзақтығы +-----+----------------------------+----------- -- -------+---------- | 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

Мысал:

+----------+----------------------------+--------- --- ---------+----------+---------- | #| күту_оқиғасының_түрі| wait_event| ұзақтығы| % 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| Құлыптау| кортеж| 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
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

WAITINGS FOR 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

пікір қалдыру