ProHoster > Blog > Administration > Eine der Methoden zur Analyse von Arbeitslastprofilen und Wartehistorien in PostgreSQL.

Eine der Methoden zur Analyse von Arbeitslastprofilen und Wartehistorien in PostgreSQL.

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

In diesem Artikel wird erlÀutert und anhand konkreter Abfragen und Beispiele gezeigt, welche wertvollen Informationen durch die Historie der Darstellung pg_stat_activity gewonnen werden können.

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

Eingabedaten

Die Historie der Darstellung 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 );

Die Tabelle wird stĂŒndlich mit dblink zur Ziel-Datenbank gefĂŒllt. Die interessanteste und nĂŒtzlichste Spalte in der Tabelle ist selbstverstĂ€ndlich das. queryid.

Die Historie der Darstellung pg_stat_activity.

archive_pg_stat_activity

ERSTELLEN SIE DIE TABELLE archive_pg_stat_activity
(
  zeitpunkt timestamp ohne Zeitzone,
  datid             oid, 
  datname           name,
  pid               integer,
  usesysid          oid,
  usename           name,
  anwendungsname    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,
  statusÀnderung    timestamp ohne Zeitzone,
  wart_event_typ    text,                     
  wart_event        text,                   
  status            text,                  
  backend_xid       xid,                 
  backend_xmin      xid,                
  abfrage           text,               
  backend_typ       text,
  queryid           bigint
);

Die Tabelle ist eine stĂŒndlich partitionierte Tabelle history_pg_stat_activity (Weitere Informationen hier – pg_stat_statements + pg_stat_activity + log_query = pg_ash? und hier — Versuch, ein Äquivalent zu ASH fĂŒr PostgreSQL zu erstellen.)

Ausgabe

CLUSTER CPU ZEIT (SYSTEM + CLIENTS)

Abfrage

MIT 
 t ALS
 (
	SELECT 		
			date_trunc('Sekunde', zeitpunkt)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	zeitpunkt ZWISCHEN pg_stat_history_begin+(current_hour_diff * intervall '1 Stunde') UND pg_stat_history_end+(current_hour_diff * intervall '1 Stunde')  UND 
			( aa.wart_event_typ IST NULL  ) UND
			aa.status = 'aktiv'
 )
 SELECT count(*) 
 IN cpu_total
 AUS t ;

Beispiel

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

CLUSTER WARTUNGSZEITEN

Abfrage

MIT 
 t ALS
 (
	SELECT 		
			date_trunc('Sekunde', zeitpunkt)
	FROM 	activity_hist.archive_pg_stat_activity aa
	WHERE 	zeitpunkt ZWISCHEN pg_stat_history_begin+(current_hour_diff * intervall '1 Stunde') UND pg_stat_history_end+(current_hour_diff * intervall '1 Stunde')  UND 
			( aa.wart_event_typ IST NICHT NULL  ) UND
			aa.status = 'aktiv'
 )
 SELECT count(*) 
 IN cpu_total
 AUS t ;

Beispiel

CLUSTER WARTUNGSZEIT : 30:12:49

Gesamtwerte der pg_stat_statements

Abfrage

 --GESAMT pg_stat
  SELECT 
    SUM(calls) AS anzahl, SUM(total_time) AS gesamtzeit, SUM(rows) AS zeilen ,
	SUM(shared_blks_hit) AS geteilte_blöcke_getroffen, SUM(shared_blks_read) AS geteilte_blöcke_gelesen ,
	SUM(shared_blks_dirtied) AS geteilte_blöcke_verÀndert, SUM(shared_blks_written) AS geteilte_blöcke_geschrieben , 
    SUM(local_blks_hit) AS lokale_blöcke_getroffen, SUM(local_blks_read) AS lokale_blöcke_gelesen , 
	SUM(local_blks_dirtied) AS lokale_blöcke_verÀndert, SUM(local_blks_written) AS lokale_blöcke_geschrieben,
	SUM(temp_blks_read) AS temporÀre_blöcke_gelesen, SUM(temp_blks_written) AS temporÀre_blöcke_geschrieben , 
	SUM(blk_read_time) AS block_lesezeit, SUM(blk_write_time) AS block_schreibzeit
  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 — gesamte AusfĂŒhrungszeit der Abfragen

Abfrage

dbtime_total = intervall '1 millisekunde' * pg_total_stat_history_rec.gesamtzeit ;

Beispiel

SQL DBTIME : 136:49:36

SQL CPU TIME — CPU-Zeit, die zur AusfĂŒhrung von Abfragen verwendet wird

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 * intervall '1 stunde') AND pg_stat_history_end+(current_hour_diff * intervall '1 stunde')  AND 
			( aa.wait_event_type IS NULL  ) AND
			backend_type = 'client backend' AND 
			aa.state = 'aktiv'
 )
 SELECT count(*) 
 INTO cpu_total
 FROM t ;

Beispiel

SQL CPU TIME : 27:40:15

SQL WAITINGS TIME — gesamte Wartezeit fĂŒr Abfragen

Abfrage

MIT 
 t ALS
 (
	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 ;

Beispiel

SQL WARTUNGSDAUER : 30:04:09

Die folgenden Abfragen sind trivial und aus PlatzgrĂŒnden werden die Implementierungsdetails weggelassen:

Beispiel

| SQL IOTIME : 19:44:50
| SQL LIESEZEIT : 19:44:32
| SQL SCHREIBZEIT : 00:00:17
|
| SQL AUFRUFE : 12188248
-------------------------------------------------------------
| SQL GETEILTE BLOCKLESUNGEN : 7997039120
| SQL GETEILTE BLOCKTREFFER : 8868286092
| SQL GETEILTE BLOCKTREFFER/LESEN % : 110,89
| SQL GETEILTE BLOCKS MÜLLE : 419945
| SQL GETEILTE BLOCKS GESCHREIBEN : 19857
|
| SQL TEMPORÄRE BLOCKLESUNGEN : 7836169
| SQL TEMPORÄRE BLOCKS GESCHREIBEN : 10683938

Kommen wir zum spannendsten Abschnitt

WARTUNGSSTATISTIKEN

TOP 10 WARTUNGEN NACH GESAMTWARTUNGSDAUER FÜR KLIENTENPROZESSE

Abfrage

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

Beispiel

+------------------------------------------------------------------------------------
| TOP 10 WARTEZEITEN NACH GESAMTWARTEZEIT FÜR SYSTEMPROZESSE
+-----+------------------------------+--------------------+--------------------
|    #|               warte_event_typ|          warte_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 GESAMTWARTUNGSDAUER FÜR KLIENTENPROZESSE

Abfrage

WÄHLEN SIE 
  warte_event_typ , warte_event ,
  get_clients_waiting_duration( warte_event_typ , warte_event , pg_stat_history_begin+(current_hour_diff * INTERVAL '1 STUNDE') , pg_stat_history_end+(current_hour_diff * INTERVAL '1 STUNDE') ) AS dauer
FROM 
  activity_hist.archive_pg_stat_activity aa
WHERE 
  timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * INTERVAL '1 STUNDE') AND pg_stat_history_end+(current_hour_diff * INTERVAL '1 STUNDE') AND backend_typ = 'client backend' AND warte_event_typ IS NOT NULL 
GROUP BY warte_event_typ, warte_event
ORDER BY 3 DESC
LIMIT 10

Beispiel

+-----+------------------------------+--------------------+--------------------+----------
|    #|               Warteereignisart|          Warteereignis|            Dauer|  % dbzeit
+-----+------------------------------+--------------------+--------------------+----------
|    1|                          Sperre|       Transaktions-ID|            08:16:47|      6.05
|    2|                            IO|        DatenDateiLesen|            06:13:41|      4.55
|    3|                       Timeout|             PgSleep|            02:53:21|      2.11
|    4|                        LWLock|      Puffer-Zuordnung|            00:40:42|       0.5
|    5|                        LWLock|           Puffer_IO|            00:17:17|      0.21
|    6|                            IO|        BufDateiSchreiben|            00:01:34|      0.02
|    7|                          Sperre|               Tupel|            00:01:32|      0.02
|    8|                        Client|          ClientLesen|            00:01:19|      0.02
|    9|                            IO|         BufDateiLesen|            00:00:37|      0.01
|   10|                        LWLock|      Puffer-Inhalt|            00:00:08|         0
+-----+------------------------------+--------------------+--------------------+----------

WARTETYPEN NACH GESAMTER WARTEDAUER, FÜR SYSTEMPROZESSE

Abfrage

SELECT 
  warteereignisart,
  get_system_waiting_type_duration( warteereignisart, 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 
  zeitpunkt BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') AND  backend_typ != 'client backend' AND warteereignisart IS NOT NULL 
GROUP BY warteereignisart
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
+-----+------------------------------+--------------------

WARTETYPEN NACH GESAMTEM WARTETEMPO FÜR KLIENTENPROZESSE

Abfrage

WÄHLEN 
  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
VON
  activity_hist.archive_pg_stat_activity aa
WO 
  timepoint BETWEEN 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 IS NOT NULL 
GROUP BY wait_event_type
ORDER BY 2 DESC

Beispiel

+-----+------------------------------+--------------------+--------------------
|    #|               wait_event_type|            duration|            % dbtime
+-----+------------------------------+--------------------+--------------------
|    1|                          Sperre|            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.

WARTEN FÜR SYSTEMPROZESSE

Abfrage

WÄHLEN 
  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 dauer 
VON 
  activity_hist.archive_pg_stat_activity aa
WO 
  timepoint 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 
GROUP BY backend_type , datname , wait_event_type , wait_event
ORDER BY 5 DESC

Beispiel

+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    #|                 backend_type|    dbname|     wait_event_type|            wait_event|            duration
+-----+-----------------------------+----------+--------------------+----------------------+--------------------
|    1| logische Replikation Starter|          |            AktivitÀt|   LogicalLauncherMain|            10:43:28
|    2|               Autovacuum-Launcher|          |            AktivitÀt|        AutoVacuumMain|            10:42:49
|    3|                        walwriter|          |            AktivitÀt|         WalWriterMain|            10:28:53
|    4|                     Checkpointer|          |            AktivitÀt|      CheckpointerMain|            10:23:50
|    5|                  Hintergrundschreiber|          |            AktivitÀt|          BgWriterMain|            09:11:59
|    6|                  Hintergrundschreiber|          |            AktivitÀt|     BgWriterHibernate|            01:37:46
|    7|                    Parallelarbeiter|      tdb1|                  IO|          BufFileWrite|            00:02:35
|    8|                    Parallelarbeiter|      tdb1|              LWLock|        buffer_mapping|            00:01:41
|    9|                    Parallelarbeiter|      tdb1|                  IO|          DataFileRead|            00:01:22
|   10|                    Parallelarbeiter|      tdb1|                  IO|           BufFileRead|            00:00:59
|   11|                        walwriter|          |                  IO|              WALWrite|            00:00:57
|   12|                    Parallelarbeiter|      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|                     Checkpointer|          |                  IO|         DataFileWrite|            00:00:11
|   16|                        walwriter|          |              LWLock|          WALWriteLock|            00:00:09
|   17|                     Checkpointer|          |              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|                     Checkpointer|          |                  IO| ControlFileSyncUpdate|            00:00:01
|   24|                  Hintergrundschreiber|          |                  IO|              WALWrite|            00:00:01
|   25|                  Hintergrundschreiber|          |                  IO|         DataFileFlush|            00:00:01
|   26|                     Checkpointer|          |                  IO|         SLRUFlushSync|            00:00:01
|   27|                  Autovacuum-Arbeiter|      tdb1|                  IO|              WALWrite|            00:00:01
|   28|                     Checkpointer|          |                  IO|          DataFileSync|            00:00:01
+-----+-----------------------------+----------+--------------------+----------------------+--------------------

WARTEN AUF SQL — Wartezeiten fĂŒr einzelne Abfragen nach queryid

Abfrage

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

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 Abfragen zur Erfassung sind wiederum trivial und werden zur Platzersparnis nicht wiedergegeben.

Beispiele

+------------------------------------------------------------------------------------
| KLIENT 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
+--------------------+----------+----------+----------+----------+----------+--------------------
| KLIENT SQL nach CPU-Zeit sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu zeit|     Aufrufe|  % dbzeit|gesamt_zeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLIENT SQL nach Benutzer-I/O-Wartezeit sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_wartezeit|     Aufrufe|  % dbzeit|gesamt_zeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLIENT SQL nach gemeinsam genutzten Pufferlesungen sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       pufferlesungen|     Aufrufe|  % dbzeit|gesamt_zeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLIENT SQL nach Lesezeiten von Festplatten sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           lesezeit|     Aufrufe|  % dbzeit|gesamt_zeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
| KLIENT SQL nach AusfĂŒhrungen sortiert
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               Aufrufe|      zeilen|  % dbzeit|gesamt_zeit|     % 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Zusammenfassung

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

Entwicklung

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

  • Die Berichterstattung um die Historie der Sperren ergĂ€nzen. Die Abfragen werden getestet und in KĂŒrze bereitgestellt.
  • Die Erweiterung TimescaleDB zur Speicherung der Historie von pg_stat_activity und pg_locks nutzen.
  • Ein Batch-Lösungspaket auf GitHub vorbereiten, um eine massenhafte Bereitstellung auf Produktionsdatenbanken zu ermöglichen.

Die Fortsetzung folgt


Quelle: habr.com

Kaufen Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server đŸ”„ Kaufen Sie zuverlĂ€ssiges Hosting fĂŒr Websites mit DDoS-Schutz, VPS VDS-Server | ProHoster