ProHoster > Blogg > Administration > En av metoderna för att fÄ en arbetsbelastningsprofil och vÀntetidshistorik i PostgreSQL

En av metoderna för att fÄ en arbetsbelastningsprofil och vÀntetidshistorik i PostgreSQL

FortsÀttningen av artikeln "Ett försök att skapa en analog till ASH för PostgreSQL «.

I artikeln kommer vi att diskutera och visa med specifika förfrÄgningar och exempel vilken anvÀndbar information man kan fÄ frÄn historiken av visningen pg_stat_activity.

Varning.
PÄ grund av Àmnets nyhet och det ofullstÀndiga testperioden kan artikeln innehÄlla fel. Kritik och kommentarer vÀlkomnas och förvÀntas.

Indata

Historik över visningen 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 );

Tabellen fylls i varje timme, med hjÀlp av dblink till mÄldatabasen. Den mest intressanta och anvÀndbara kolumnen i tabellen Àr sjÀlvklart queryid.

Historik över visningen pg_stat_activity

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

Tabellen Ă€r en tidssektionerad version av history_pg_stat_activity (LĂ€s mer hĂ€r — pg_stat_statements + pg_stat_activity + loq_query = pg_ash? och hĂ€r — Försök att skapa en analog till ASH för PostgreSQL.)

Utdata

CLUSTER CPU TIME (SYSTEM + CLIENTS )

FrÄga

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 ;

Exempel

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

CLUSTER WAITINGS TIME

FrÄga

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 ;

Exempel

CLUSTER WAITINGS TIME : 30:12:49

Totala pg_stat_statements vÀrden

FrÄga

 --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 execution time of requests

FrÄga

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

Exempel

SQL DBTIME : 136:49:36

SQL CPU TIME — CPU time spent on executing requests

FrÄga

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 ;

Exempel

SQL CPU TIME : 27:40:15

SQL WAITINGS TIME — total waiting time for requests

FrÄga

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 ;

Exempel

SQL WAITINGS TIME : 30:04:09

The following queries are trivial and for space-saving purposes, implementation details are omitted:

Exempel

| SQL IOTIME : 19:44:50
| SQL READ TIME : 19:44:32
| SQL WRITE TIME : 00:00:17
|
| SQL CALLS : 12188248
-------------------------------------------------------------
| SQL SHARED BLOCKS READS : 7997039120
| SQL SHARED BLOCKS HITS : 8868286092
| SQL SHARED BLOCKS HITS/READS % : 110.89
| SQL SHARED BLOCKS DIRTIED : 419945
| SQL SHARED BLOCKS WRITTEN : 19857
|
| SQL TEMPORARY BLOCKS READS : 7836169
| SQL TEMPORARY BLOCKS WRITTEN : 10683938

Now we move to the most interesting section

WAITINGS STATISTICS

TOP 10 WAITINGS BY TOTAL WAIT TIME FOR CLIENTS PROCESSES

FrÄga

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

Exempel

+------------------------------------------------------------------------------------
| TOP 10 VENTANDE MED SUMMA VENTETID FÖR SYSTEMPROCESSER
+-----+------------------------------+--------------------+--------------------
|    #|               vent_typ|          vent|            varaktighet
+-----+------------------------------+--------------------+--------------------
|    1|                      Aktivitet| LogicalLauncherMain|            10:43:28
|    2|                      Aktivitet|      AutoVacuumMain|            10:42:49
|    3|                      Aktivitet|       WalWriterMain|            10:28:53
|    4|                      Aktivitet|    CheckpointerMain|            10:23:50
|    5|                      Aktivitet|        BgWriterMain|            09:11:59
|    6|                      Aktivitet|   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 WAITINGS BY TOTAL WAIT TIME FOR CLIENTS PROCESSES

FrÄga

VÄLJ 
  vent_typ , vent ,
  get_clients_waiting_duration( vent_typ , vent , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as varaktighet
FRÅN 
  activity_hist.archive_pg_stat_activity aa
DÄR 
  tidpunkt MELLAN pg_stat_history_begin+(current_hour_diff * interval '1 hour') OCH pg_stat_history_end+(current_hour_diff * interval '1 hour') OCH backend_typ = 'client backend' OCH vent_typ ÄR INTE NULL 
GRUPPERA EFTER vent_typ, vent
ORDNA EFTER 3 DESC
BEGRÄNSA 10

Exempel

+-----+------------------------------+--------------------+--------------------+----------
|    #|               vent_typ|          vent|            varaktighet|  % dbtid
+-----+------------------------------+--------------------+--------------------+----------
|    1|                          LĂ„s|       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|                          LĂ„s|               tuple|            00:01:32|      0.02
|    8|                        Klient|          ClientRead|            00:01:19|      0.02
|    9|                            IO|         BufFileRead|            00:00:37|      0.01
|   10|                        LWLock|      buffer_content|            00:00:08|         0
+-----+------------------------------+--------------------+--------------------+----------

VENTATYPS SAMMANSTÄLLNING ENLIGT TOTAL VENTETID FÖR SYSTEMPROCESSER

FrÄga

VÄLJ 
  vent_typ ,
  get_system_waiting_type_duration( vent_typ , pg_stat_history_begin+(current_hour_diff * interval '1 hour') , pg_stat_history_end+(current_hour_diff * interval '1 hour') ) as varaktighet
FRÅN
  activity_hist.archive_pg_stat_activity aa
DÄR 
  tidpunkt MELLAN pg_stat_history_begin+(current_hour_diff * interval '1 hour') OCH pg_stat_history_end+(current_hour_diff * interval '1 hour') OCH  backend_typ != 'client backend' OCH vent_typ ÄR INTE NULL 
GRUPPERA EFTER vent_typ
ORDNA EFTER 2 DESC

Exempel

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

VÄNTA TYPER EFTER TOTAL VÄNTETID, FÖR KLIENTPROCESSER

FrÄga

VÄLJ 
  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
FRÅN
  activity_hist.archive_pg_stat_activity aa
DÄR 
  timepoint MELLAN pg_stat_history_begin+(current_hour_diff * interval '1 hour') OCH pg_stat_history_end+(current_hour_diff * interval '1 hour') OCH backend_type = 'client backend' OCH wait_event_type ÄR INTE NULL 
GRUPPERA EFTER wait_event_type
ORDERA EFTER 2 DESC

Exempel

+-----+------------------------------+--------------------+--------------------
|    #|               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|                        Klient|            00:01:19|                0.02
|    6|                           IPC|            00:00:04|                   0
+-----+------------------------------+--------------------+--------------------

VÀntetider för systemprocesser och enskilda förfrÄgningar.

VÄNTNINGAR FÖR SYSTEMPROCESSER

FrÄga

VÄLJ 
  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 
FRÅN 
  activity_hist.archive_pg_stat_activity aa
DÄR 
  timepoint MELLAN pg_stat_history_begin+(current_hour_diff * interval '1 hour') OCH pg_stat_history_end+(current_hour_diff * interval '1 hour') OCH backend_type != 'client backend' OCH wait_event_type ÄR INTE NULL 
GRUPPERA EFTER backend_type , datname , wait_event_type , wait_event
ORDERA EFTER 5 DESC

Exempel

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

VĂ€ntar pĂ„ SQL — vĂ€ntetider för individuella förfrĂ„gningar efter queryid

FrÄga

VÄLJ 
queryid, datnamn, 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')) som varaktighet 
FRÅN 
  activity_hist.archive_pg_stat_activity aa
DÄR 
  timepoint MELLAN pg_stat_history_begin + (current_hour_diff * interval '1 hour') OCH pg_stat_history_end + (current_hour_diff * interval '1 hour') OCH backend_type = 'client backend' OCH wait_event_type ÄR INTE NULL OCH queryid ÄR INTE NULL 
GRUPPERA EFTER queryid, datnamn, wait_event_type, wait_event
ORDNA EFTER 1, 5 FALLANDE

Exempel

+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    #|                  queryid|    dbname|     wait_event_type|          wait_event|            waitings|               total
|     |                         |          |                    |                    |            duration|            duration
+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------
|    1|     -8247416849404883188|      tdb1|              Klient|          KlientLĂ€sning|            00:00:02|
|    2|     -6572922443698419129|      tdb1|              Klient|          KlientLĂ€sning|            00:00:05|
|    3|     -6572922443698419129|      tdb1|                  IO|        DataFilLĂ€sning|            00:00:01|
|    4|     -5917408132400665328|      tdb1|              Klient|          KlientLĂ€sning|            00:00:04|
|    5|     -4091009262735781873|      tdb1|              Klient|          KlientLĂ€sning|            00:00:03|
|    6|     -1473395109729441239|      tdb1|              Klient|          KlientLĂ€sning|            00:00:01|
|    7|        28942442626229688|      tdb1|                  IO|        BufFilSkrivning|            00:01:34|            00:46:06
|    8|        28942442626229688|      tdb1|              LWLock|      buffer_mapping|            00:01:05|            00:46:06
|    9|        28942442626229688|      tdb1|                  IO|        DataFilLĂ€sning|            00:00:44|            00:46:06
|   10|        28942442626229688|      tdb1|                  IO|         BufFilLĂ€sning|            00:00:37|            00:46:06
|   11|        28942442626229688|      tdb1|              LWLock|           buffer_io|            00:00:35|            00:46:06
|   12|        28942442626229688|      tdb1|              Klient|          KlientLĂ€sning|            00:00:05|            00:46:06
|   13|        28942442626229688|      tdb1|                 IPC| MessageKöMottagning|            00:00:03|            00:46:06
|   14|        28942442626229688|      tdb1|                 IPC|    BgArbetareAvstÀngning|            00:00:01|            00:46:06
|   15|       389015618226997618|      tdb1|                LĂ„s|       transactionid|            03:55:09|            04:14:15
|   16|       389015618226997618|      tdb1|                  IO|        DataFilLĂ€sning|            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|                LĂ„s|               tuple|            00:00:35|            04:14:15
|   20|       389015618226997618|      tdb1|              LWLock|        WALWriteLock|            00:00:02|            04:14:15
|   21|       389015618226997618|      tdb1|                  IO|       DataFilSkrivning|            00:00:01|            04:14:15
|   22|       389015618226997618|      tdb1|              LWLock|        SyncScanLock|            00:00:01|            04:14:15
|   23|       389015618226997618|      tdb1|              Klient|          KlientLĂ€sning|            00:00:01|            04:14:15
|   24|       734234407411547467|      tdb1|              Klient|          KlientLĂ€sning|            00:00:11|
|   25|       734234407411547467|      tdb1|              LWLock|      buffer_mapping|            00:00:05|
|   26|       734234407411547467|      tdb1|                  IO|        DataFilLĂ€sning|            00:00:02|
|   27|      1237430309438971376|      tdb1|              LWLock|      buffer_mapping|            00:02:18|            02:45:40
|   28|      1237430309438971376|      tdb1|                  IO|        DataFilLĂ€sning|            00:00:27|            02:45:40
|   29|      1237430309438971376|      tdb1|              Klient|          KlientLĂ€sning|            00:00:02|            02:45:40
|   30|      2404820632950544954|      tdb1|              Klient|          KlientLĂ€sning|            00:00:01|
|   31|      2515308626622579467|      tdb1|              Klient|          KlientLĂ€sning|            00:00:02|
|   32|      4710212362688288619|      tdb1|              LWLock|      buffer_mapping|            00:03:08|            02:18:21
|   33|      4710212362688288619|      tdb1|                  IO|        DataFilLĂ€sning|            00:00:22|            02:18:21
|   34|      4710212362688288619|      tdb1|              Klient|          KlientLĂ€sning|            00:00:06|            02:18:21
|   35|      4710212362688288619|      tdb1|              LWLock|           buffer_io|            00:00:02|            02:18:21
|   36|      9150846928388977274|      tdb1|                  IO|        DataFilLĂ€sning|            00:01:19|
|   37|      9150846928388977274|      tdb1|              LWLock|      buffer_mapping|            00:00:34|
|   38|      9150846928388977274|      tdb1|              Klient|          KlientLĂ€sning|            00:00:10|
|   39|      9150846928388977274|      tdb1|              LWLock|           buffer_io|            00:00:01|
+-----+-------------------------+----------+--------------------+--------------------+--------------------+--------------------

KLIENT SQL STATISTIK — TOP-förfrĂ„gningar

FörfrÄgningarna för att hÀmta Àr, som alltid, triviala och för att spara plats tas de inte upp.

Exempel

+------------------------------------------------------------------------------------
| KLIENT SQL sorterad efter förfluten tid
+--------------------+----------+----------+----------+----------+----------+--------------------
|        förfluten tid|     samtal|  % dbtid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+--------------------
|            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 sorterad efter CPU-tid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            cpu tid|     samtal|  % dbtid|total_tid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            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 sorterad efter AnvÀndar I/O VÀntetid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|        io_vÀntetid|     samtal|  % dbtid|total_tid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            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 sorterad efter Delade Buffrar LĂ€sningar
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|       buffrar lÀsningar|     samtal|  % dbtid|total_tid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|          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 sorterad efter DisklÀsningar Tid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|           lÀstid|     samtal|  % dbtid|total_tid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|            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 sorterad efter Exekveringar
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|               samtal|      rader|  % dbtid|total_tid|     % CPU|      % IO|    databasnamn|             frÄgaid
+--------------------+----------+----------+----------+----------+----------+----------+--------------------
|                 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
+--------------------+----------+----------+----------+----------+----------+----------+--------------------

Resultat

Genom att anvÀnda de angivna frÄgorna och den erhÄllna rapporten kan man fÄ en mer komplett bild för analys och problemlösning av prestandadegradering för individuella frÄgor och hela klustret totalt.

Utveckling

Planerna för utveckling framöver Àr följande:

  • Komplettera rapporteringen med historik över lĂ„sningar. FrĂ„gorna testas och kommer att presenteras inom kort.
  • AnvĂ€nda tillĂ€gget TimescaleDB för att lagra historik över pg_stat_activity och pg_locks.
  • Förbereda en paketlösning pĂ„ github för massdistribution pĂ„ produktionsdatabaser.

FortsĂ€ttning följer


KĂ€lla: habr.com

Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar đŸ”„ Köp pĂ„litlig webbhotell med DDoS-skydd, VPS VDS-servrar | ProHoster