ProHoster > Blog > Beheer > Een van de methoden om een werklastprofiel en wachthistorie in PostgreSQL te verkrijgen

Een van de methoden om een werklastprofiel en wachthistorie in PostgreSQL te verkrijgen

Vervolg op het artikel Ā«Poging om een analoog van ASH voor PostgreSQL te creĆ«ren Ā«.

In dit artikel wordt besproken en aan de hand van specifieke verzoeken en voorbeelden getoond welke nuttige informatie kan worden verkregen met behulp van de weergavegeschiedenis pg_stat_activity.

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

Invoergegevens

Weergavegeschiedenis pg_stat_statements

pg_stat_history

CREATE TABLE pg_stat_history ( 
id SERIAL, 
snapshot_timestamp timestamp zonder tijdzone, 
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 doel-database. De meest interessante en nuttige kolom in de tabel is natuurlijk queryid.

Weergavegeschiedenis pg_stat_activity

archive_pg_stat_activity

CREATE TABLE archive_pg_stat_activity
(
  timepoint timestamp zonder tijdzone,
  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 zonder tijdzone,
  xact_start        timestamp zonder tijdzone,
  query_start       timestamp zonder tijdzone,
  state_change      timestamp zonder tijdzone,
  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 op uren gepartitioneerde tabel van history_pg_stat_activity (Meer hierover ā€” pg_stat_statements + pg_stat_activity + loq_query = pg_ash? en hier ā€” Poging om een analoog ASH voor PostgreSQL te creĆ«ren.)

Uitvoer

CLUSTER CPU TIJD (SYSTEEM + CLIƋNTS )

Verzoek

MET 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint TUSSEN pg_stat_history_begin+(current_hour_diff * interval '1 uur') EN pg_stat_history_end+(current_hour_diff * interval '1 uur')  EN 
			( aa.wait_event_type IS NULL  ) EN
			aa.state = 'active'
 )
 SELECT count(*) 
 IN cpu_total
 VAN t ;

Voorbeeld

CLUSTER CPU TIJD (SYSTEEM + CLIƋNTS ) : 28:37:46

CLUSTER WACHTTIJDEN

Verzoek

MET 
 t AS
 (
	SELECT 		
			date_trunc('second', timepoint)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	timepoint TUSSEN pg_stat_history_begin+(current_hour_diff * interval '1 uur') EN pg_stat_history_end+(current_hour_diff * interval '1 uur')  EN 
			( aa.wait_event_type IS NIET NULL  ) EN
			aa.state = 'active'
 )
 SELECT count(*) 
 IN cpu_total
 VAN t ;

Voorbeeld

CLUSTER WACHTTIJDEN : 30:12:49

Totaal pg_stat_statements waarden

Verzoek

 --TOTALE 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 ā€” totale uitvoeringstijd van queries

Verzoek

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 queries

Verzoek

MET 
 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 WAITINGS TIME ā€” totale wachttijd voor queries

Verzoek

MET 
 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 queries zijn triviaal en voor de besparing van ruimte worden implementatiedetails weggelaten:

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 DIRTIED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938

Laten we doorgaan naar het meest interessante gedeelte

WACHTSTATISTIEKEN

TOP 10 WACHTEN OP BASIS VAN TOTALE WACHTTIJD VOOR KLANTPROCESSEN

Verzoek

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 WACHTTIJDEN OP BASIS VAN DE TOTALE WACHTTIJD VOOR SYSTEEMPROCESSEN
+-----+------------------------------+--------------------+--------------------
|    #|               wacht_event_type|          wacht_event|            duur
+-----+------------------------------+--------------------+--------------------
|    1|                      Activiteit| LogicalLauncherMain|            10:43:28
|    2|                      Activiteit|      AutoVacuumMain|            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|        DataFileRead|            00:01:23
|   10|                            IO|            WALWrite|            00:00:59
+-----+------------------------------+--------------------+--------------------

TOP 10 WACHTEN OP BASIS VAN TOTALE WACHTTIJD VOOR KLANTPROCESSEN

Verzoek

SELECT 
  wacht_event_type , wacht_event ,
  get_clients_waiting_duration( wacht_event_type , wacht_event , pg_stat_history_begin+(current_hour_diff * interval '1 uur') , pg_stat_history_end+(current_hour_diff * interval '1 uur') ) as duur
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 uur') AND pg_stat_history_end+(current_hour_diff * interval '1 uur') AND backend_type = 'client backend' AND wacht_event_type IS NOT NULL 
GROUP BY wacht_event_type, wacht_event
ORDER BY 3 DESC
LIMIT 10

Voorbeeld

+-----+------------------------------+--------------------+--------------------+----------
|    #|               wacht_event_type|          wacht_event|            duur|  % dbtijd
+-----+------------------------------+--------------------+--------------------+----------
|    1|                          Lock|       transactionid|            08:16:47|      6.05
|    2|                            IO|        DataFileRead|            06:13:41|      4.55
|    3|                       Timeout|             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|                          Lock|               tuple|            00:01:32|      0.02
|    8|                        Client|          ClientRead|            00:01:19|      0.02
|    9|                            IO|         BufFileRead|            00:00:37|      0.01
|   10|                        LWLock|      buffer_content|            00:00:08|         0
+-----+------------------------------+--------------------+--------------------+----------

WACHTTYPE OP BASIS VAN DE TOTALE WACHTTIJD VOOR SYSTEEMPROCESSEN

Verzoek

SELECT 
  wacht_event_type ,
  get_system_waiting_type_duration( wacht_event_type , pg_stat_history_begin+(current_hour_diff * interval '1 uur') , pg_stat_history_end+(current_hour_diff * interval '1 uur') ) as duur
FROM
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 uur') AND pg_stat_history_end+(current_hour_diff * interval '1 uur') AND  backend_type != 'client backend' AND wacht_event_type IS NOT NULL 
GROUP BY wacht_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
+-----+------------------------------+--------------------

WACHTTYPE OP TOTALE WACHTTIJD, VOOR CLIƋNTPROCESSEN

Verzoek

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

Wachtduur voor systematische processen en afzonderlijke verzoeken.

WACHTEN VOOR SYSTEEMPROCESSEN

Verzoek

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 duur 
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| logische replicatie launcher|          |            Activiteit|   LogicalLauncherMain|            10:43:28
|    2|          autovacuum launcher|          |            Activiteit|        AutoVacuumMain|            10:42:49
|    3|                    walwriter|          |            Activiteit|         WalWriterMain|            10:28:53
|    4|                 checkpointer|          |            Activiteit|      CheckpointerMain|            10:23:50
|    5|            achtergrondschrijver|          |            Activiteit|          BgWriterMain|            09:11:59
|    6|            achtergrondschrijver|          |            Activiteit|     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|            achtergrondschrijver|          |                  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|            achtergrondschrijver|          |              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|            achtergrondschrijver|          |                  IO|              WALWrite|            00:00:01
|   25|            achtergrondschrijver|          |                  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 ā€” wachttijden voor afzonderlijke verzoeken op queryid

Verzoek

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 STATISTIEKEN ā€” TOP zoekopdrachten

De aanvragen voor het verkrijgen zijn opnieuw triviaal en worden, ter besparing van ruimte, niet weergegeven.

Voorbeelden

+------------------------------------------------------------------------------------
| KLANT SQL gesorteerd op Verstreken Tijd
+--------------------+----------+----------+----------+----------+----------+--------------------
|        verstreken tijd|     oproepen|  % dbtijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+--------------------
| KLANT SQL gesorteerd op CPU Tijd
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu tijd|     oproepen|  % dbtijd|totale_tijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLANT SQL gesorteerd op Gebruiker I/O Wacht Tijd
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_wacht tijd|     oproepen|  % dbtijd|totale_tijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLANT SQL gesorteerd op Gedeelde Buffers Lezingen
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       buffers lezingen|     oproepen|  % dbtijd|totale_tijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLANT SQL gesorteerd op Schijf Lezingen Tijd
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           lees tijd|     oproepen|  % dbtijd|totale_tijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLANT SQL gesorteerd op Uitvoeringen
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               oproepen|      rijen|  % dbtijd|totale_tijd|     % CPU|      % IO|    dbnaam|             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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Conclusie

Met behulp van de gepresenteerde verzoeken en de verkregen rapportage kan een completer beeld voor analyse en probleemoplossing van prestatie-degradatie van afzonderlijke verzoeken en het cluster als geheel worden verkregen.

Ontwikkeling

De plannen voor ontwikkeling zijn als volgt:

  • De rapportage aanvullen met een geschiedenis van blokkades. Verzoeken worden getest en zullen binnenkort worden gepresenteerd.
  • Gebruik de TimescaleDB-extensie voor het opslaan van de geschiedenis van pg_stat_activity en pg_locks.
  • Bereid een batchoplossing voor op GitHub voor massale implementatie op productie-databases.

Wordt vervolgdā€¦

Bron: habr.com

Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers šŸ”„ Koop betrouwbare webhosting met bescherming tegen DDoS, VPS VDS servers | ProHoster