Geekly articles weekly

Salah satu cara untuk mendapatkan profil beban kerja dan menunggu sejarah di PostgreSQL

Kelanjutan artikel " Mencoba membuat analog ASH untuk PostgreSQL ".

Artikel akan dipertimbangkan dan ditampilkan pada pertanyaan dan contoh spesifik - informasi bermanfaat apa yang dapat diperoleh dengan menggunakan riwayat presentasi pg_stat_activity.
Peringatan
Karena kebaruan topik dan periode pengujian yang tidak lengkap, artikel mungkin mengandung kesalahan. Kritik dan komentar sangat didorong dan diharapkan.

Masukkan data


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

Tabel diisi setiap jam menggunakan dblink ke database target. Kolom yang paling menarik dan bermanfaat dalam tabel, tentu saja, adalah queryid .

Pg_stat_activity melihat riwayat


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

Tabelnya adalah tabel history_pg_stat_activity jam-dipartisi (Untuk lebih jelasnya lihat pg_stat_statements + pg_stat_activity + loq_query = pg_ash? Dan di sini ada upaya untuk membuat analog ASH untuk PostgreSQL.)

Jejak


CLUSTER CPU TIME (SYSTEM + CLIENTS)


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

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

WAKTU TUNGGU KLUB


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

Contoh
 CLUSTER WAITINGS TIME : 30:12:49

Total nilai pg_stat_statements


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


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

Contoh
 SQL DBTIME : 136:49:36

SQL CPU WAKTU waktu dari CPU yang dihabiskan untuk mengeksekusi query


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

Contoh
 SQL CPU TIME : 27:40:15

SQL WAITINGS TIME - Total Waktu Tunggu untuk Permintaan


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

Contoh
 SQL WAITINGS TIME : 30:04:09

Pertanyaan berikut sepele dan untuk menghemat ruang, detail implementasi dihilangkan:

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

Kami melewati bagian paling menarik

STATISTIK TUNGGU


TOP 10 MENUNGGU OLEH TOTAL TUNGGU WAKTU UNTUK PROSES KLIEN


Minta
 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

Contoh
  + ------------------------------------------------- -----------------------------------
 |  TOP 10 MENUNGGU OLEH TOTAL TUNGGU WAKTU UNTUK PROSES SISTEM
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  wait_event |  durasi
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Aktivitas |  LogicalLauncherMain |  10:43:28
 |  2 |  Aktivitas |  AutoVacuumMain |  10:42:49
 |  3 |  Aktivitas |  WalWriterMain |  10:28:53
 |  4 |  Aktivitas |  CheckpointerMain |  10:23:50
 |  5 |  Aktivitas |  BgWriterMain |  09:11:59
 |  6 |  Aktivitas |  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 MENUNGGU OLEH TOTAL TUNGGU WAKTU UNTUK PROSES KLIEN


Minta
 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

Contoh
  + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  durasi |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Kunci |  transactionid |  08:16: 47 |  6.05
 |  2 |  IO |  DataFileRead |  06: 13: 41 |  4.55
 |  3 |  Batas waktu |  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 |  Kunci |  tuple |  00: 01: 32 |  0,02
 |  8 |  Klien |  ClientRead |  00:01:19 |  0,02
 |  9 |  IO |  BufFileRead |  00.00: 37 |  0,01
 |  10 |  LWLock |  buffer_content |  00:00: 08 |  0
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------

JENIS TUNGGU OLEH TOTAL TUNGGU WAKTU, UNTUK PROSES SISTEM


Minta
 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

Contoh
  + ----- + ------------------------------ + ------------ --------
 |  # |  wait_event_type |  durasi
 + ----- + ------------------------------ + ------------ --------
 |  1 |  Aktivitas |  53:08:45
 |  2 |  IO |  00:06:24
 |  3 |  LWLock |  00:03:02
 + ----- + ------------------------------ + ------------ --------

JENIS TUNGGU OLEH TOTAL TUNGGU WAKTU, UNTUK PROSES KLIEN


Minta
 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

Contoh
  + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  durasi |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Kunci |  08:18: 19 |  6.07
 |  2 |  IO |  06: 16: 01 |  4.58
 |  3 |  Batas waktu |  02: 53: 21 |  2.11
 |  4 |  LWLock |  00: 58: 12 |  0,71
 |  5 |  Klien |  00:01:19 |  0,02
 |  6 |  IPC |  00.00: 04 |  0
 + ----- + ------------------------------ + ------------ -------- + --------------------

Durasi harapan, untuk proses sistem dan permintaan individu.

MENUNGGU PROSES SISTEM


Minta
 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

Contoh
  + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  durasi
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  peluncur replikasi logis |  |  Aktivitas |  LogicalLauncherMain |  10:43:28
 |  2 |  peluncur autovacuum |  |  Aktivitas |  AutoVacuumMain |  10:42:49
 |  3 |  walwriter |  |  Aktivitas |  WalWriterMain |  10:28:53
 |  4 |  checkpointer |  |  Aktivitas |  CheckpointerMain |  10:23:50
 |  5 |  penulis latar belakang |  |  Aktivitas |  BgWriterMain |  09:11:59
 |  6 |  penulis latar belakang |  |  Aktivitas |  BgWriterHibernate |  01:37:46
 |  7 |  pekerja paralel |  tdb1 |  IO |  BufFileWrite |  00:02:35
 |  8 |  pekerja paralel |  tdb1 |  LWLock |  buffer_mapping |  00:01:41
 |  9 |  pekerja paralel |  tdb1 |  IO |  DataFileRead |  00:01:22
 |  10 |  pekerja paralel |  tdb1 |  IO |  BufFileRead |  00:00:59
 |  11 |  walwriter |  |  IO |  WALWrite |  00:00:57
 |  12 |  pekerja paralel |  tdb1 |  LWLock |  buffer_io |  00:00:47
 |  13 |  pekerja autovacuum |  tdb1 |  LWLock |  buffer_mapping |  00:00:13
 |  14 |  penulis latar belakang |  |  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 |  penulis latar belakang |  |  LWLock |  WALWriteLock |  00:00:06
 |  19 |  walwriter |  |  IO |  WALInitWrite |  00:00:02
 |  20 |  pekerja autovacuum |  tdb1 |  LWLock |  WALWriteLock |  00:00:02
 |  21 |  walwriter |  |  IO |  WALInitSync |  00:00:02
 |  22 |  pekerja autovacuum |  tdb1 |  IO |  DataFileRead |  00:00:01
 |  23 |  checkpointer |  |  IO |  ControlFileSyncUpdate |  00:00:01
 |  24 |  penulis latar belakang |  |  IO |  WALWrite |  00:00:01
 |  25 |  penulis latar belakang |  |  IO |  DataFileFlush |  00:00:01
 |  26 |  checkpointer |  |  IO |  SLRUFlushSync |  00:00:01
 |  27 |  pekerja autovacuum |  tdb1 |  IO |  WALWrite |  00:00:01
 |  28 |  checkpointer |  |  IO |  DataFileSync |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------

MENUNGGU SQL - harapan untuk permintaan individu oleh queryid


Minta
 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

Contoh
  + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  # |  queryid |  dbname |  wait_event_type |  wait_event |  menunggu |  total
 |  |  |  |  |  |  durasi |  durasi
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------
 |  1 |  -8247416849404883188 |  tdb1 |  Klien |  ClientRead |  00.00: 02 |
 |  2 |  -6572922443698419129 |  tdb1 |  Klien |  ClientRead |  00:00:05 |
 |  3 |  -6572922443698419129 |  tdb1 |  IO |  DataFileRead |  00:00: 01 |
 |  4 |  -5917408132400665328 |  tdb1 |  Klien |  ClientRead |  00.00: 04 |
 |  5 |  -4091009262735781873 |  tdb1 |  Klien |  ClientRead |  00:00: 03 |
 |  6 |  -1473395109729441239 |  tdb1 |  Klien |  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 |  Klien |  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 |  Kunci |  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 |  Kunci |  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 |  Klien |  ClientRead |  00:00: 01 |  04:14:15
 |  24 |  734234407411547467 |  tdb1 |  Klien |  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 |  Klien |  ClientRead |  00.00: 02 |  02:45:40
 |  30 |  2404820632950544954 |  tdb1 |  Klien |  ClientRead |  00:00: 01 |
 |  31 |  2515308626622579467 |  tdb1 |  Klien |  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 |  Klien |  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 |  Klien |  ClientRead |  00:00:10 |
 |  39 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_io |  00:00: 01 |
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + --------------------

KLIEN SQL STATICTICS - TOP query


Namun, permintaan untuk menerima lagi sepele dan menghemat ruang, tidak diberikan.

Contohnya
  + ------------------------------------------------- -----------------------------------
 |  CLIENT SQL dipesan oleh Elapsed Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  waktu yang telah berlalu |  panggilan |  % dbtime |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  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 dipesan oleh CPU Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  waktu cpu |  panggilan |  % dbtime | total_time |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  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 dipesan oleh Pengguna I / O Waktu Tunggu
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  io_unggu waktu |  panggilan |  % dbtime | total_time |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  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 dipesan oleh Shared Buffers Reads
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  buffer berbunyi |  panggilan |  % dbtime | total_time |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  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 dipesan oleh Disk Reads Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  baca waktu |  panggilan |  % dbtime | total_time |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  02: 16: 30 |  19 |  3.1 |  04: 14: 15 |  10.83 |  11.52 |  tdb1 |  389015618226997618
 |  00:10: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 dipesan oleh Eksekusi
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  panggilan |  baris |  % dbtime | total_time |  % CPU |  % IO |  dbname |  kueri
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  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
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------

Ringkasan


Dengan menggunakan permintaan yang diajukan dan pelaporan yang dihasilkan, Anda bisa mendapatkan gambaran yang lebih lengkap untuk menganalisis dan memecahkan masalah penurunan kinerja untuk permintaan individual dan seluruh kluster secara keseluruhan.

Pengembangan


Sejauh ini, rencana pengembangan adalah sebagai berikut:

  • Pelaporan pelengkap dengan riwayat kunci. Permintaan sedang diuji dan akan dikirimkan segera.
  • Gunakan ekstensi TimescaleDB untuk menyimpan sejarah pg_stat_activity dan pg_locks.
  • Siapkan solusi batch di github untuk penyebaran massal di basis produksi.

Dilanjutkan ...

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

More articles:


All Articles