ProHoster > Blog > Administration > Eine der Methoden zur Erfassung des Arbeitslastprofils und der Wartegeschichte in PostgreSQL

Eine der Methoden zur Erfassung des Arbeitslastprofils und der Wartegeschichte in PostgreSQL

Fortsetzung des Artikels „Versuch, ein Analogon zu ASH fĂŒr PostgreSQL zu erstellen «.

Im Artikel wird an konkreten Abfragen und Beispielen gezeigt, welche nĂŒtzlichen Informationen durch die Historie der Ansicht pg_stat_activity gewonnen werden können.

Warnung.
Aufgrund der Neuheit des Themas und des unvollendeten Testzeitraums kann der Artikel Fehler enthalten. Kritik und Anmerkungen sind jederzeit willkommen und werden erwartet.

Eingabedaten

Historie der Ansicht pg_stat_statements

pg_stat_history

CREATE TABLE pg_stat_history ( 
id SERIAL, 
snapshot_timestamp  timestamp ohne Zeitzone, 
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 );

Die Tabelle wird jede Stunde gefĂŒllt, indem dblink auf die Ziel-Datenbank verwendet wird. Die interessanteste und nĂŒtzlichste Spalte in der Tabelle ist offensichtlich queryid.

Historie der Ansicht pg_stat_activity

archive_pg_stat_activity

CREATE TABLE archive_pg_stat_activity
(
  timepoint timestamp ohne Zeitzone,
  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 ohne Zeitzone,
  xact_start        timestamp ohne Zeitzone,
  query_start       timestamp ohne Zeitzone,
  state_change      timestamp ohne Zeitzone,
  wait_event_type   text,                     
  wait_event        text,                   
  state             text,                  
  backend_xid       xid,                 
  backend_xmin      xid,                
  query             text,               
  backend_type      text,
  queryid           bigint
);

Die Tabelle stellt eine stundenweise partitionierte Tabelle history_pg_stat_activity dar (Mehr dazu hier — pg_stat_statements + pg_stat_activity + log_query = pg_ash? und hier — Ein Versuch, ein Analogon von ASH fĂŒr PostgreSQL zu erstellen.)

Ausgabe

CLUSTER CPU TIME (SYSTEM + CLIENTS )

Abfrage

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 ;

Beispiel

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

CLUSTER WAITINGS TIME

Abfrage

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 ;

Beispiel

CLUSTER WAITINGS TIME : 30:12:49

Gesamte Werte von pg_stat_statements

Abfrage

 --GESAMT pg_stat
  WÄHLE 
    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
  IN 
    pg_total_stat_history_rec
  AUS 
    pg_stat_history
  WO 
 	snapshot_timestamp ZWISCHEN pg_stat_history_begin UND pg_stat_history_end UND 
	queryid IST NULL;

SQL DBTIME — Gesamtdauer der AnfrageausfĂŒhrung

Abfrage

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

Beispiel

SQL DBTIME : 136:49:36

SQL CPU TIME - CPU-Zeit, die fĂŒr die AusfĂŒhrung von Anfragen aufgewendet wird

Abfrage

MIT 
 t ALS
 (
	WÄHLE 		
			date_trunc('second', timepoint)
	AUS 	activity_hist.archive_pg_stat_activity aa
	WO 	timepoint ZWISCHEN pg_stat_history_begin+(current_hour_diff * intervall '1 hour') UND pg_stat_history_end+(current_hour_diff * intervall '1 hour') UND 
			( aa.wait_event_type IST NULL ) UND
			backend_type = 'client backend' UND 
			aa.state = 'active'
 )
 WÄHLE count(*) 
 IN 
 cpu_total
 AUS t ;

Beispiel

SQL CPU TIME : 27:40:15

SQL WAITINGS TIME — Gesamtdauer der Wartezeiten fĂŒr Abfragen

Abfrage

MIT 
 t ALS
 (
	WÄHLE 		
			date_trunc('second', timepoint)
	AUS 	activity_hist.archive_pg_stat_activity aa
	WO 	timepoint ZWISCHEN pg_stat_history_begin+(current_hour_diff * intervall '1 hour') UND pg_stat_history_end+(current_hour_diff * intervall '1 hour') UND 
			( aa.wait_event_type IST NICHT NULL ) UND
			aa.state = 'active' UND 
		backend_type = 'client backend'
 )
 WÄHLE count(*) 
 IN 
 waiting_total
 AUS t ;

Beispiel

SQL WAITINGS TIME : 30:04:09

Die folgenden Abfragen sind trivial und werden zum Platzsparen in den Implementierungsdetails ausgelassen:

Beispiel

| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL ANFRAGEN : 12188248
-------------------------------------------------------------
| SQL GETEILTE BLOCKLESUNGEN : 7997039120
| SQL GETEILTE BLOCKTREFFER : 8868286092
| SQL GETEILTE BLOCKTREFFER/LESEN % : 110.89
| SQL GETEILTE BLOCKVERDRECKTE : 419945
| SQL GETEILTE BLOCKEINGESCHRIEBEN : 19857
|
| SQL TEMPORÄRE BLOCKLESUNGEN : 7836169
| SQL TEMPORÄRE BLOCKEINGESCHRIEBEN : 10683938

Kommen wir zum interessanteren Abschnitt

WARTUNGSTATISTIKEN

TOP 10 WARTUNGEN NACH GESAMTER WARTEDAUER FÜR KLIENTPROZESSE

Abfrage

WÄHLE 
  wait_event_type, wait_event ,
  get_system_waiting_duration( wait_event_type , wait_event ,pg_stat_history_begin+(current_hour_diff * intervall '1 hour') ,pg_stat_history_end+(current_hour_diff * intervall '1 hour') ) als dauer 
AUS
  activity_hist.archive_pg_stat_activity aa
WO 
  timepoint ZWISCHEN pg_stat_history_begin+(current_hour_diff * intervall '1 hour') UND pg_stat_history_end+(current_hour_diff * intervall '1 hour') UND backend_type != 'client backend' UND wait_event_type IST NICHT NULL 
GRUPPIERE NACH 
  wait_event_type, wait_event
BESTELLE NACH 3 ABSTEIGEND
LIMIT 10

Beispiel

+------------------------------------------------------------------------------------
| TOP 10 WARTEZEITEN NACH GESAMTER WARTEZEIT FÜR SYSTEMPROZESSE
+-----+------------------------------+--------------------+--------------------
|    #|               wait_event_type|          wait_event|            dauer
+-----+------------------------------+--------------------+--------------------
|    1|                      AktivitÀt| LogicalLauncherMain|            10:43:28
|    2|                      AktivitÀt|      AutoVacuumMain|            10:42:49
|    3|                      AktivitÀt|       WalWriterMain|            10:28:53
|    4|                      AktivitÀt|    CheckpointerMain|            10:23:50
|    5|                      AktivitÀt|        BgWriterMain|            09:11:59
|    6|                      AktivitÀt|   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 WARTUNGEN NACH GESAMTER WARTEDAUER FÜR KLIENTPROZESSE

Abfrage

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

Beispiel

+-----+------------------------------+--------------------+--------------------+----------
|    #|               wait_event_type|          wait_event|            dauer|  % dbtime
+-----+------------------------------+--------------------+--------------------+----------
|    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
+-----+------------------------------+--------------------+--------------------+----------

WARTETYPEN NACH GESAMTER WARTEZEIT FÜR SYSTEMPROZESSE

Abfrage

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

Beispiel

+-----+------------------------------+--------------------
|    #|               wait_event_type|            duration
+-----+------------------------------+--------------------
|    1|                      AktivitÀt|            53:08:45
|    2|                            IO|            00:06:24
|    3|                        LWLock|            00:03:02
+-----+------------------------------+--------------------

WARTUNGSTYPEN NACH GESAMT-WARTEDAUER FÜR CLIENTPROZESSE

Abfrage

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

Beispiel

+-----+------------------------------+--------------------+--------------------
|    #|               wait_event_type|            duration|            % dbtime
+-----+------------------------------+--------------------+--------------------
|    1|                          Lock|            08:18:19|                6.07
|    2|                            IO|            06:16:01|                4.58
|    3|                       Timeout|            02:53:21|                2.11
|    4|                        LWLock|            00:58:12|                0.71
|    5|                        Client|            00:01:19|                0.02
|    6|                           IPC|            00:00:04|                   0
+-----+------------------------------+--------------------+--------------------

Dauer der Wartezeiten fĂŒr Systemprozesse und einzelne Anfragen.

WARTUNGEN FÜR SYSTEMPROZESSE

Abfrage

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

Beispiel

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 backend_typ|    dbname|     wait_event_typ|            wait_event|            duration
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    1| logische Replikationsstarter|          |            AktivitÀt|   LogicalLauncherMain|            10:43:28
|    2|          Autovacuum-Launcher|          |            AktivitÀt|        AutoVacuumMain|            10:42:49
|    3|                    walwriter|          |            AktivitÀt|         WalWriterMain|            10:28:53
|    4|                 PrĂŒfpunkt|          |            AktivitĂ€t|      CheckpointerMain|            10:23:50
|    5|            Hintergrundschreiber|          |            AktivitÀt|          BgWriterMain|            09:11:59
|    6|            Hintergrundschreiber|          |            AktivitÀt|     BgWriterHibernate|            01:37:46
|    7|              paralleler Arbeiter|      tdb1|                  IO|          BufFileWrite|            00:02:35
|    8|              paralleler Arbeiter|      tdb1|              LWLock|        buffer_mapping|            00:01:41
|    9|              paralleler Arbeiter|      tdb1|                  IO|          DataFileRead|            00:01:22
|   10|              paralleler Arbeiter|      tdb1|                  IO|           BufFileRead|            00:00:59
|   11|                    walwriter|          |                  IO|              WALWrite|            00:00:57
|   12|              paralleler Arbeiter|      tdb1|              LWLock|             buffer_io|            00:00:47
|   13|            Autovacuum-Arbeiter|      tdb1|              LWLock|        buffer_mapping|            00:00:13
|   14|            Hintergrundschreiber|          |                  IO|         DataFileWrite|            00:00:12
|   15|                 PrĂŒfpunkt|          |                  IO|         DataFileWrite|            00:00:11
|   16|                    walwriter|          |              LWLock|          WALWriteLock|            00:00:09
|   17|                 PrĂŒfpunkt|          |              LWLock|          WALWriteLock|            00:00:06
|   18|            Hintergrundschreiber|          |              LWLock|          WALWriteLock|            00:00:06
|   19|                    walwriter|          |                  IO|          WALInitWrite|            00:00:02
|   20|            Autovacuum-Arbeiter|      tdb1|              LWLock|          WALWriteLock|            00:00:02
|   21|                    walwriter|          |                  IO|           WALInitSync|            00:00:02
|   22|            Autovacuum-Arbeiter|      tdb1|                  IO|          DataFileRead|            00:00:01
|   23|                 PrĂŒfpunkt|          |                  IO| ControlFileSyncUpdate|            00:00:01
|   24|            Hintergrundschreiber|          |                  IO|              WALWrite|            00:00:01
|   25|            Hintergrundschreiber|          |                  IO|         DataFileFlush|            00:00:01
|   26|                 PrĂŒfpunkt|          |                  IO|         SLRUFlushSync|            00:00:01
|   27|            Autovacuum-Arbeiter|      tdb1|                  IO|              WALWrite|            00:00:01
|   28|                 PrĂŒfpunkt|          |                  IO|          DataFileSync|            00:00:01
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

WARTEN AUF SQL — Wartezeiten fĂŒr bestimmte Anfragen nach queryid

Abfrage

WÄHLEN 
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 dauer 
VON 
activity_hist.archive_pg_stat_activity aa 
WO 
zeitpunkt ZWISCHEN pg_stat_history_begin + (current_hour_diff * interval '1 hour') UND pg_stat_history_end + (current_hour_diff * interval '1 hour') UND backend_type = 'client backend' UND wait_event_type IST NICHT NULL UND queryid IST NICHT NULL 
GRUPPIEREN NACH queryid, datname, wait_event_type, wait_event 
BESTELLEN NACH 1, 5 DESC

Beispiel

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

KUNDEN-SQL-STATISTIKEN — TOP-Anfragen

Die Anfragen zur Erlangung sind, wieder einmal, trivial und werden aus PlatzgrĂŒnden nicht aufgefĂŒhrt.

Beispiele

+------------------------------------------------------------------------------------
| CLIENT SQL nach verstrichener Zeit sortiert
+--------------------+----------+----------+----------+----------+----------+--------------------
|        verstrichene Zeit|     Aufrufe|  % dbzeit|     % 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 nach CPU-Zeit sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu Zeit|     Aufrufe|  % dbzeit|gesamtzeit|     % 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 nach Benutzereingabe/I/O Wartezeit sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_wartezeit|     Aufrufe|  % dbzeit|gesamtzeit|     % 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 nach Lesezugriffen auf gemeinsam genutzte Puffer sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       pufferzugriffe|     Aufrufe|  % dbzeit|gesamtzeit|     % 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 nach Lesezugriffszeiten sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           lesezeit|     Aufrufe|  % dbzeit|gesamtzeit|     % 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 nach AusfĂŒhrungen sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               aufrufe|      zeilen|  % dbzeit|gesamtzeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Fazit

Durch die Verwendung der bereitgestellten Anfragen und der erhaltenen Berichterstattung kann ein umfassenderes Bild fĂŒr die Analyse und Lösung von Leistungsdegradationsproblemen fĂŒr einzelne Anfragen und den gesamten Cluster insgesamt gewonnen werden.

Entwicklung

Die zukĂŒnftigen EntwicklungsplĂ€ne sind wie folgt:

  • Die Berichterstattung um die Geschichte der Sperrungen erweitern. Die Anfragen werden getestet und in naher Zukunft bereitgestellt.
  • Die Erweiterung TimescaleDB zur Speicherung der Historie von pg_stat_activity und pg_locks verwenden.
  • Ein Paketlösungs-Paket auf github vorbereiten, um eine massenhafte Bereitstellung auf Produktionsdatenbanken zu ermöglichen.

Fortsetzung folgt


Quelle: habr.com

60GB SSD 8Gb DDR4