ProHoster > blog > administratie > Eén methode voor het verkrijgen van een werklastprofiel en wachtgeschiedenis in PostgreSQL

Eén methode voor het verkrijgen van een werklastprofiel en wachtgeschiedenis in PostgreSQL

Vervolg van het artikel “Een poging om een ​​ASH-analoog voor PostgreSQL te maken ".

In het artikel wordt met behulp van specifieke vragen en voorbeelden onderzocht en getoond welke nuttige informatie kan worden verkregen met behulp van de geschiedenis van de weergave pg_stat_activity.

Waarschuwing.
Vanwege de nieuwheid van het onderwerp en de onvoltooide testperiode kan het artikel fouten bevatten. Kritiek en commentaar zijn zeer welkom en worden verwacht.

Invoergegevens

pg_stat_statements inzendingsgeschiedenis

pg_stat_geschiedenis

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 );

De tabel wordt elk uur gevuld met behulp van dblink naar de doeldatabase. De meest interessante en nuttige kolom in de tabel natuurlijk zoekopdrachtid.

pg_stat_activity geschiedenis bekijken

archive_pg_stat_activiteit

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
);

De tabel is een history_pg_stat_activity tabel, opgedeeld per uur (meer details hier - pg_stat_statements + pg_stat_activity + loq_query = pg_ash? en hier - Een poging om een ​​analoog van ASH voor PostgreSQL te creëren.)

Uitvoer

CLUSTER CPU-TIJD (SYSTEEM + CLIENTEN)

onderzoek

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 ;

Voorbeeld

CLUSTER CPU TIME (SYSTEM + CLIENTS ) : 28:37:46

CLUSTER WACHTTIJD

onderzoek

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 ;

Voorbeeld

CLUSTER WAITINGS TIME : 30:12:49

Totaal pg_stat_statements-waarden

onderzoek

 --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 - totale uitvoeringstijd van query's

onderzoek

dbtime_total = interval '1 millisecond' * pg_total_stat_history_rec.total_time ;

Voorbeeld

SQL DBTIME : 136:49:36

SQL CPU TIME - CPU-tijd besteed aan het uitvoeren van query's

onderzoek

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 ;

Voorbeeld

SQL CPU TIME : 27:40:15

SQL WACHTTIJD - totale wachttijd voor queries

onderzoek

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 ;

Voorbeeld

SQL WAITINGS TIME : 30:04:09

De volgende vragen zijn triviaal en implementatiedetails zijn weggelaten om ruimte te besparen:

Voorbeeld

| 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

Laten we verder gaan naar het meest interessante gedeelte

WACHTEN STATISCH

TOP 10 WACHTEN OP TOTAAL WACHTTIJD VOOR KLANTENPROCESSEN

onderzoek

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

Voorbeeld

+--------------------------------------------- ---------------------------------- | TOP 10 WACHTINGEN NAAR TOTALE WACHTTIJD VOOR SYSTEEMPROCESSEN +-----+---------------------------+-- -----------------+------------------- | #| wait_event_type| wacht_gebeurtenis| duur +-----+--------------------------+----------- -- -------+---------- | 1| Activiteit| LogischeLauncherMain| 10:43:28 | 2| Activiteit| AutoVacuümHoofd| 10:42:49 | 3| Activiteit| WalWriterMain| 10:28:53 | 4| Activiteit| CheckpointerMain| 10:23:50 | 5| Activiteit| BgWriterMain| 09:11:59 | 6| Activiteit| BgWriterHibernate| 01:37:46 | 7| IO| BufFileWrite| 00:02:35 | 8| LWLock| buffer_mapping| 00:01:54 | 9| IO| GegevensbestandLezen| 00:01:23 | 10| IO| WALSchrijf| 00:00:59 +-----+---------------------------+------- -------------+-----------

TOP 10 WACHTEN OP TOTAAL WACHTTIJD VOOR KLANTENPROCESSEN

onderzoek

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

Voorbeeld

+----------+---------------------------+--------- --- ---------+----------+---------- | #| wait_event_type| wacht_gebeurtenis| duur| % dbtijd +-----+----------------------------+---------- ----------+----------+---------- | 1| Vergrendel| transactie-id| 08:16:47| 6.05 | 2| IO| GegevensbestandLezen| 06:13:41| 4.55 | 3| Time-out| PgSlaap| 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| Vergrendel| tupel| 00:01:32| 0.02 | 8| Klant| KlantLees| 00:01:19| 0.02 | 9| IO| BufFileRead| 00:00:37| 0.01 | 10| LWLock| buffer_inhoud| 00:00:08| 0 +-----+--------------------------+----------- -- -------+-----------+----------

WACHTTYPEN OP TOTALE WACHTTIJD, VOOR SYSTEEMPROCESSEN

onderzoek

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

Voorbeeld

+----------+---------------------------+--------- --- -------- | #| wait_event_type| duur +-----+--------------------------+----------- -- ------- | 1| Activiteit| 53:08:45 | 2| IO| 00:06:24 | 3| LWLock| 00:03:02 +-----+---------------------------+------- -------------

WACHTTYPEN OP TOTALE WACHTTIJD, VOOR KLANTENPROCESSEN

onderzoek

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

Voorbeeld

+----------+---------------------------+--------- --- ---------+---------- | #| wait_event_type| duur| % dbtijd +-----+----------------------------+---------- ----------+------------------- | 1| Vergrendel| 08:18:19| 6.07 | 2| IO| 06:16:01| 4.58 | 3| Time-out| 02:53:21| 2.11 | 4| LWLock| 00:58:12| 0.71 | 5| Klant| 00:01:19| 0.02 | 6| IPC| 00:00:04| 0 +-----+--------------------------+----------- -- -------+--------------------

Wachttijden voor systeemprocessen en individuele verzoeken.

WACHTEN OP SYSTEEMPROCESSEN

onderzoek

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

Voorbeeld

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 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
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

WACHTEN OP SQL - wacht op individuele zoekopdrachten per queryid

onderzoek

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

Voorbeeld

+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    #|                  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 STATICS - TOP-query's

Verzoeken om ontvangst zijn wederom triviaal en worden niet gegeven om ruimte te besparen.

Примеры

+------------------------------------------------------------------------------------
| 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Totaal

Met behulp van de ingediende query's en de resulterende rapportage kunt u een completer beeld krijgen voor het analyseren en oplossen van problemen met prestatievermindering voor individuele query's en het hele cluster als geheel.

Ontwikkeling

Tot nu toe zijn de ontwikkelingsplannen als volgt:

  • Vul de rapportage aan met blokkeringsgeschiedenis. Aanvragen worden getest en zullen binnenkort worden ingediend.
  • Gebruik de TimescaleDB-extensie om de geschiedenis van pg_stat_activity en pg_locks op te slaan.
  • Bereid een pakketoplossing voor op github voor massa-implementatie op productiebasissen.

Wordt vervolgd ...

Bron: www.habr.com

Voeg een reactie