Geekly articles weekly

Eine Möglichkeit, das Workload-Profil abzurufen und den Verlauf in PostgreSQL zu warten

Fortsetzung des Artikels " Versuch, ein Analogon von ASH für PostgreSQL zu erstellen ".

Der Artikel wird in bestimmten Abfragen und Beispielen betrachtet und angezeigt. Welche nützlichen Informationen können mithilfe des Präsentationsverlaufs pg_stat_activity abgerufen werden?
Warnung
Aufgrund der Neuheit des Themas und des unvollständigen Testzeitraums kann der Artikel Fehler enthalten. Kritik und Kommentare werden nachdrücklich ermutigt und erwartet.

Daten eingeben


Verlauf anzeigen 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 Zieldatenbank gefüllt. Die interessanteste und nützlichste Spalte in der Tabelle ist natürlich queryid .

Verlauf der Pg_stat_activity-Ansicht


archive_pg_stat_activity
 CREATE TABLE archive_pg_stat_activity ( timepoint timestamp without time zone, datid oid, datname name, pid integer, usesysid oid, usename name, application_name text, client_addr inet, client_hostname text, client_port integer, backend_start timestamp without time zone, xact_start timestamp without time zone, query_start timestamp without time zone, state_change timestamp without time zone, wait_event_type text, wait_event text, state text, backend_xid xid, backend_xmin xid, query text, backend_type text, queryid bigint );

Die Tabelle ist eine uhrpartitionierte history_pg_stat_activity-Tabelle (Weitere Informationen finden Sie unter pg_stat_statements + pg_stat_activity + loq_query = pg_ash? Und hier ist ein Versuch, ein Analogon von ASH für PostgreSQL zu erstellen.)

Impressum


CLUSTER-CPU-ZEIT (SYSTEM + KUNDEN)


Anfrage
 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 WARTET ZEIT


Anfrage
 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

Summe der pg_stat_statements-Werte


Anfrage
  --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 - Total Query Runtime


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

Beispiel
 SQL DBTIME : 136:49:36

SQL CPU TIME Zeit der CPU, die für die Ausführung von Abfragen aufgewendet wurde


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

Beispiel
 SQL CPU TIME : 27:40:15

SQL WAITINGS TIME - Gesamtwartezeit für Abfragen


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

Beispiel
 SQL WAITINGS TIME : 30:04:09

Die folgenden Abfragen sind trivial und um Platz zu sparen, werden Implementierungsdetails weggelassen:

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

Wir kommen zum interessantesten Abschnitt

WARTUNGSSTATIK


TOP 10 WARTUNGEN NACH GESAMTWARTUNG FÜR KUNDENPROZESSE


Anfrage
 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 WARTUNGEN NACH GESAMTWARTUNG 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 GESAMTWARTUNG FÜR KUNDENPROZESSE


Anfrage
 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

Beispiel
  + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  Dauer |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Sperre |  Transaktions-ID |  08: 16: 47 |  6.05
 |  2 |  IO |  DataFileRead |  06: 13: 41 |  4.55
 |  3 |  Zeitüberschreitung |  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 |  Sperre |  Tupel |  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 GESAMTWARTZEIT FÜR SYSTEMPROZESSE


Anfrage
 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

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

WARTETYPEN NACH GESAMTWARTUNG FÜR KUNDENPROZESSE


Anfrage
 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 |  Dauer |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Sperre |  08: 18: 19 |  6.07
 |  2 |  IO |  06: 16: 01 |  4.58
 |  3 |  Zeitüberschreitung |  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 Erwartungen, für Systemprozesse und individuelle Anforderungen.

WARTEN AUF SYSTEMPROZESSE


Anfrage
 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_type |  Datenbankname |  wait_event_type |  wait_event |  Dauer
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  logischer Replikationsstarter |  |  Aktivität |  LogicalLauncherMain |  10:43:28
 |  2 |  Autovakuum-Starter |  |  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 |  Autovakuumarbeiter |  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 |  Autovakuumarbeiter |  tdb1 |  LWLock |  WALWriteLock |  00:00:02
 |  21 |  Walwriter |  |  IO |  WALInitSync |  00:00:02
 |  22 |  Autovakuumarbeiter |  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 |  Autovakuumarbeiter |  tdb1 |  IO |  WALWrite |  00:00:01
 |  28 |  Checkpointer |  |  IO |  DataFileSync |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------

WARTEN AUF SQL - Erwartungen für einzelne Abfragen von queryid


Anfrage
 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 |  Datenbankname |  wait_event_type |  wait_event |  Wartezeiten |  insgesamt
 |  |  |  |  |  |  Dauer |  Dauer
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  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 |  Sperre |  Transaktions-ID |  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 |  Sperre |  Tupel |  00: 00: 35 |  04:14:15
 |  20 |  389015618226997618 |  tdb1 |  LWLock |  WALWriteLock |  00: 00: 02 |  04:14:15
 |  21 |  389015618226997618 |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  04:14:15
 |  22 |  389015618226997618 |  tdb1 |  LWLock |  SyncScanLock |  00: 00: 01 |  04:14:15
 |  23 |  389015618226997618 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |  04:14:15
 |  24 |  734234407411547467 |  tdb1 |  Client |  ClientRead |  00: 00: 11 |
 |  25 |  734234407411547467 |  tdb1 |  LWLock |  buffer_mapping |  00: 00: 05 |
 |  26 |  734234407411547467 |  tdb1 |  IO |  DataFileRead |  00: 00: 02 |
 |  27 |  1237430309438971376 |  tdb1 |  LWLock |  buffer_mapping |  00: 02: 18 |  02:45:40
 |  28 |  1237430309438971376 |  tdb1 |  IO |  DataFileRead |  00: 00: 27 |  02:45:40
 |  29 |  1237430309438971376 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  02:45:40
 |  30 |  2404820632950544954 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |
 |  31 |  2515308626622579467 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |
 |  32 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_mapping |  00: 03: 08 |  02:18:21
 |  33 |  4710212362688288619 |  tdb1 |  IO |  DataFileRead |  00: 00: 22 |  02:18:21
 |  34 |  4710212362688288619 |  tdb1 |  Client |  ClientRead |  00: 00: 06 |  02:18:21
 |  35 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_io |  00: 00: 02 |  02:18:21
 |  36 |  9150846928388977274 |  tdb1 |  IO |  DataFileRead |  00: 01: 19 |
 |  37 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_mapping |  00: 00: 34 |
 |  38 |  9150846928388977274 |  tdb1 |  Client |  ClientRead |  00: 00: 10 |
 |  39 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_io |  00: 00: 01 |
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------

CLIENT SQL STATICTICS - TOP-Abfragen


Anfragen für einen erneuten Empfang sind jedoch trivial und aus Platzgründen nicht gegeben.

Beispiele
  + --------------------------------------------- -----------------------------------
 |  CLIENT SQL sortiert nach verstrichener Zeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  verstrichene Zeit |  Anrufe |  % dbtime |  % CPU |  % IO |  Datenbankname |  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 geordnet nach CPU-Zeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CPU-Zeit |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  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 sortiert nach Benutzer-E / A-Wartezeit
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  io_wait time |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  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 sortiert nach Shared Buffers Reads
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Puffer liest |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  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 sortiert nach Disk Reads Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Lesezeit |  Anrufe |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  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 sortiert nach Ausführungen
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Anrufe |  Zeilen |  % dbtime | total_time |  % CPU |  % IO |  Datenbankname |  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


Mithilfe der übermittelten Anforderungen und der daraus resultierenden Berichterstellung erhalten Sie ein vollständigeres Bild zur Analyse und Lösung der Probleme einer Leistungsminderung für einzelne Anforderungen und den gesamten Cluster als Ganzes.

Entwicklung


Bisher sind die Entwicklungspläne wie folgt:

  • Ergänzen Sie die Berichterstellung mit einem Sperrverlauf. Anfragen werden getestet und in Kürze eingereicht.
  • Verwenden Sie die Erweiterung TimescaleDB, um den Verlauf von pg_stat_activity und pg_locks zu speichern.
  • Bereiten Sie eine Batch-Lösung auf Github für den Masseneinsatz auf Produktionsbasis vor.

Fortsetzung folgt…

Source: https://habr.com/ru/post/de467575/

More articles:


All Articles