ProHoster > Blog > Administrácia > Pokus o vytvorenie analógu ASH pre PostgreSQL

Pokus o vytvorenie analógu ASH pre PostgreSQL

Vyhlásenie o probléme

Na optimalizáciu dopytov PostgreSQL je veľmi potrebná schopnosť analyzovať históriu aktivít, najmä čakania, zámky a štatistiky tabuliek.

Dostupné príležitosti

Nástroj na analýzu historického pracovného zaťaženia alebo „AWR for Postgres“: veľmi zaujímavé riešenie, ale neexistuje žiadna história pg_stat_activity a pg_locks.

rozšírenie pgsentinel :
«Všetky nahromadené informácie sú uložené iba v pamäti RAM a množstvo spotrebovanej pamäte je regulované počtom posledných uložených záznamov.

Pridá sa pole queryid – rovnaké queryid z rozšírenia pg_stat_statements (vyžaduje sa predinštalácia).«

To by, samozrejme, veľmi pomohlo, ale najproblematickejšia vec je prvý bod.“Všetky nahromadené informácie sú uložené iba v pamäti RAM “, t.j. dochádza k vplyvu na cieľovú základňu. Okrem toho chýba história zámkov a štatistiky tabuľky. Tie. riešenie je vo všeobecnosti neúplné: “Zatiaľ neexistuje žiadny hotový balík na inštaláciu. Odporúča sa stiahnuť si zdroje a zostaviť knižnicu sami. Najprv musíte nainštalovať balík „devel“ pre váš server a nastaviť cestu na pg_config v premennej PATH.".

Vo všeobecnosti je tu veľa rozruchu a v prípade serióznych produkčných databáz nemusí byť možné so serverom nič robiť. Musíme opäť prísť s niečím vlastným.

Varovanie.

Vzhľadom na pomerne veľký objem a neukončenú dobu testovania má článok najmä informačný charakter, skôr ako súbor téz a priebežných výsledkov.
Podrobnejší materiál bude pripravený neskôr, po častiach

Návrh požiadaviek na riešenie

Je potrebné vyvinúť nástroj, ktorý vám umožní ukladať:

históriu zobrazenia pg_stat_activity
História uzamknutia relácie pomocou zobrazenia pg_locks

Požiadavka na riešenie–minimalizovať dopad na cieľovú databázu.

Všeobecná myšlienka– agent zberu údajov sa nespustí v cieľovej databáze, ale v monitorovacej databáze ako systémová služba. Áno, určitá strata údajov je možná, ale nie je to kritické pre vykazovanie, ale nemá to žiadny vplyv na cieľovú databázu z hľadiska pamäte a miesta na disku. A v prípade použitia fondu pripojení je dopad na používateľské procesy minimálny.

Etapy implementácie

1.Servisné stoly

Na ukladanie tabuliek sa používa samostatná schéma, aby sa nekomplikovala analýza použitých hlavných tabuliek.

DROP SCHEMA IF EXISTS activity_hist ;
CREATE SCHEMA activity_hist AUTHORIZATION monitor ;

Dôležité: Schéma sa nevytvára v cieľovej databáze, ale v monitorovacej databáze.

históriu zobrazenia pg_stat_activity

Tabuľka sa používa na uloženie aktuálnych snímok pohľadu pg_stat_activity

activity_hist.history_pg_stat_activity :

--ACTIVITY_HIST.HISTORY_PG_STAT_ACTIVITY
DROP TABLE IF EXISTS activity_hist.history_pg_stat_activity;
CREATE TABLE activity_hist.history_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 with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,  
  queryid           bigint
);

Na urýchlenie vkladania – žiadne indexy ani obmedzenia.

Na uloženie samotnej histórie sa používa rozdelená tabuľka:

activity_hist.archive_pg_stat_activity :

DROP TABLE IF EXISTS activity_hist.archive_pg_stat_activity;
CREATE TABLE activity_hist.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 with time zone ,
  xact_start        timestamp with time zone ,
  query_start       timestamp with time zone ,
  state_change      timestamp with time zone ,
  wait_event_type   text ,                     
  wait_event        text ,                   
  state             text ,                  
  backend_xid       xid  ,                 
  backend_xmin      xid  ,                
  query             text ,               
  backend_type      text ,
  queryid           bigint
)
PARTITION BY RANGE (timepoint);

Keďže v tomto prípade nie sú žiadne požiadavky na rýchlosť vkladania, boli vytvorené niektoré indexy na urýchlenie tvorby zostáv.

História blokovania relácií

Tabuľka sa používa na ukladanie aktuálnych snímok zámkov relácie:

activity_hist.history_locking :

--ACTIVITY_HIST.HISTORY_LOCKING
DROP TABLE IF EXISTS activity_hist.history_locking;
CREATE TABLE activity_hist.history_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean
);

Na urýchlenie vkladania neexistujú žiadne indexy ani obmedzenia.

Na uloženie samotnej histórie sa používa rozdelená tabuľka:

activity_hist.archive_locking:

DROP TABLE IF EXISTS activity_hist.archive_locking;
CREATE TABLE activity_hist.archive_locking
(
	timepoint timestamp without time zone ,
	locktype text ,
	relation oid ,
	mode text ,
	tid xid ,
	vtid text ,
	pid integer ,
	blocking_pids integer[] ,
	granted boolean	
)
PARTITION BY RANGE (timepoint);

Keďže v tomto prípade nie sú žiadne požiadavky na rýchlosť vkladania, boli vytvorené niektoré indexy na urýchlenie tvorby zostáv.

2.Vyplnenie aktuálnej histórie

Na priame zhromažďovanie snímok zobrazenia sa používa bash skript, ktorý spúšťa funkciu plpgsql.

get_current_activity.sh

#!/bin/bash
#########################################################
#get_current_activity.sh

ERROR_FILE='/home/demon/get_current_activity'$(date +%Y%m%d-)'T'$(date +%H)$(date +%M)$(date +%S)
host=$1
s_name=$2
s_pass=$3

psql  -A -t -q -v ON_ERROR_STOP=1 -c "SELECT activity_hist.get_current_activity( '$host' , '$s_name' , '$s_pass' )" >/dev/null 2>$ERROR_FILE

line_count=`cat $ERROR_FILE | wc -l`
if [[ $line_count != '0' ]];
then
    rm -f /home/demon/*.err >/dev/null 2>/dev/null
	cp $ERROR_FILE $ERROR_FILE'.err' >/dev/null 2>/dev/null  
fi
rm $ERROR_FILE >/dev/null 2>/dev/null
exit 0

plpgsql Funkcia dblink pristupuje k pohľadom v cieľovej databáze a vkladá riadky do servisných tabuliek v monitorovacej databáze.

get_current_activity.sql

CREATE OR REPLACE FUNCTION activity_hist.get_current_activity( current_host text , current_s_name text , current_s_pass text ) RETURNS BOOLEAN AS $$
DECLARE 
  database_rec record;
  dblink_str text ;
BEGIN   

	EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||current_host||' port=5432 dbname=postgres'||
	                                         ' user='||current_s_name||' password='||current_s_pass|| ' '')';



--------------------------------------------------------------------
--GET pg_stat_activity stats
	INSERT INTO activity_hist.history_pg_stat_activity
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			datid             , 
			datname           ,
			pid               ,
			usesysid              ,
			usename              ,
			application_name     ,
			client_addr          ,
			client_hostname      ,
			client_port       ,
			backend_start         ,
			xact_start            ,
			query_start           ,
			state_change          ,
			wait_event_type    ,                     
			wait_event         ,                   
			state              ,                  
			backend_xid         ,                 
			backend_xmin        ,                
			query              ,               
			backend_type   			
		FROM pg_stat_activity
		') 
		AS t (
		    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 with time zone ,
			xact_start        timestamp with time zone ,
			query_start       timestamp with time zone ,
			state_change      timestamp with time zone ,
			wait_event_type   text ,                     
			wait_event        text ,                   
			state             text ,                  
			backend_xid       xid  ,                 
			backend_xmin      xid  ,                
			query             text ,               
			backend_type      text 			
		)
	);

---------------------------------------	
--ACTIVITY_HIST.HISTORY_LOCKING	
	INSERT INTO activity_hist.history_locking
	(
		SELECT * FROM dblink('LINK1',
			'SELECT 
			now() , 
			lock.locktype,
			lock.relation,
			lock.mode,
			lock.transactionid as tid,
			lock.virtualtransaction as vtid,
			lock.pid,
			pg_blocking_pids(lock.pid), 
			lock.granted
			FROM 	pg_catalog.pg_locks lock LEFT JOIN pg_catalog.pg_database db ON db.oid = lock.database
			WHERE NOT lock.pid = pg_backend_pid()	
		') 
		AS t (
			timepoint timestamp without time zone ,
			locktype text ,
			relation oid , 
			mode text ,
			tid xid ,
			vtid text ,
			pid integer ,
			blocking_pids integer[] ,
			granted boolean
		)
	);
	PERFORM dblink_disconnect('LINK1');
	
	RETURN TRUE ;
END
$$ LANGUAGE plpgsql;

Na zhromažďovanie snímok zobrazenia sa používa služba systemd a dva skripty:

pg_current_activity.service

# /etc/systemd/system/pg_current_activity.service
[Unit]
Description=Collect history of pg_stat_activity , pg_locks 
Wants=pg_current_activity.timer

[Service]
Type=forking
StartLimitIntervalSec=0
ExecStart=/home/postgres/pgutils/demon/get_current_activity.sh 10.124.70.40 postgres postgres

[Install]
WantedBy=multi-user.target

pg_current_activity.timer

# /etc/systemd/system/pg_current_activity.timer
[Unit]
Description=Run pg_current_activity.sh every 1 second
Requires=pg_current_activity.service

[Timer]
Unit=pg_current_activity.service
OnCalendar=*:*:0/1
AccuracySec=1

[Install]
WantedBy=timers.target

Priraďte práva k skriptom:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Začnime službu:
# systemctl daemon-reload
# systemctl štart pg_current_activity.service

História zobrazení sa teda zhromažďuje vo forme snímok sekundy po sekunde. Samozrejme, ak všetko zostane tak, ako je, stoly sa veľmi rýchlo zväčšia a viac či menej produktívna práca sa znemožní.

Je potrebné zorganizovať archiváciu údajov.

3. História archivácie

Na archiváciu sa používa archív rozdelených tabuliek*.

Nové oddiely sa vytvárajú každú hodinu, zatiaľ čo staré údaje sa z tabuliek histórie* odstraňujú, takže veľkosť tabuliek histórie* sa príliš nemení a rýchlosť vkladania časom neklesá.

Vytváranie nových sekcií vykonáva funkcia plpgsql activity_hist.archive_current_activity. Algoritmus práce je veľmi jednoduchý (na príklade sekcie pre tabuľku archive_pg_stat_activity).

Vytvorte a vyplňte novú sekciu

EXECUTE format(
'CREATE TABLE ' || partition_name || 
' PARTITION OF activity_hist.archive_pg_stat_activity FOR VALUES FROM ( %L ) TO ( %L ) ' , 
to_char(date_trunc('year', partition_min_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_min_range ),'MM')||'-'||
to_char(date_trunc('day', partition_min_range ),'DD')||' '||
to_char(date_trunc('hour', partition_min_range ),'HH24')||':00', 
to_char(date_trunc('year', partition_max_range ),'YYYY')||'-'||
to_char(date_trunc('month', partition_max_range ),'MM')||'-'||
to_char(date_trunc('day', partition_max_range ),'DD')||' '||
to_char(date_trunc('hour', partition_max_range ),'HH24')||':00'
);

INSERT INTO activity_hist.archive_pg_stat_activity
(
	SELECT 	* 
	FROM 	activity_hist.history_pg_stat_activity
	WHERE 	timepoint BETWEEN partition_min_range AND partition_max_range 		
);

Vytváranie indexov

EXECUTE format	(
'CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint )' 
);

EXECUTE format	('CREATE INDEX '||index_name||
' ON '||partition_name||' ( wait_event_type , backend_type , timepoint , queryid )' 
);

Odstraňujú sa staré údaje z tabuľky history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Samozrejme, z času na čas sú staré sekcie vymazané ako zbytočné.

Základné prehľady

Vlastne, prečo sa to všetko robí? Ak chcete získať správy veľmi nejasne pripomínajúce AWR spoločnosti Oracle.

Je dôležité dodať, že na prijímanie správ je potrebné vytvoriť prepojenie medzi pohľadmi pg_stat_activity a pg_stat_statements. Tabuľky sú prepojené pridaním stĺpca 'queryid' do tabuliek 'history_pg_stat_activity', 'archive_pg_stat_activity'. Spôsob pridávania hodnoty stĺpca presahuje rozsah tohto článku a je opísaný tu − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

CELKOVÝ ČAS CPU PRE DOTAZY

Žiadosť :

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' 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 datname != 'postgres' AND	( aa.wait_event_type IS NULL  ) ANDaa.state = 'active'
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
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 datname != 'postgres' AND ( ha.wait_event_type IS NULL  )AND ha.state = 'active'
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Príklad:

-------------------------------------------------------------------
| TOTAL CPU TIME FOR QUERIES : 07:47:36
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            04:28:58
|   2|                                        |            01:07:29
|   3|                     1237430309438971376|            00:59:38
|   4|                     4710212362688288619|            00:50:48
|   5|                       28942442626229688|            00:15:50
|   6|                     9150846928388977274|            00:04:46
|   7|                    -6572922443698419129|            00:00:06
|   8|                                        |            00:00:01
+----+----------------------------------------+--------------------

CELKOVÝ ČAS ČAKANIA NA OTÁZKY

Žiadosť :

WITH hist AS
(
SELECT 
	aa.query ,aa.queryid ,			
	count(*) * interval '1 second' 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 datname != 'postgres' AND
	( aa.wait_event_type IS NOT NULL  ) 
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
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 datname != 'postgres' AND				
	( ha.wait_event_type IS NOT NULL  )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Príklad:

-------------------------------------------------------------------
| TOTAL WAITINGS TIME FOR QUERIES : 21:55:04
+----+----------------------------------------+--------------------
|   #|                                 queryid|            duration
+----+----------------------------------------+--------------------
|   1|                      389015618226997618|            16:19:05
|   2|                                        |            03:47:04
|   3|                     8085340880788646241|            00:40:20
|   4|                     4710212362688288619|            00:13:35
|   5|                     9150846928388977274|            00:12:25
|   6|                       28942442626229688|            00:11:32
|   7|                     1237430309438971376|            00:09:45
|   8|                     2649515222348904837|            00:09:37
|   9|                                        |            00:03:45
|  10|                     3167065002719415275|            00:02:20
|  11|                     5731212217001535134|            00:02:13
|  12|                     8304755792398128062|            00:01:31
|  13|                     2649515222348904837|            00:00:59
|  14|                     2649515222348904837|            00:00:22
|  15|                                        |            00:00:12
|  16|                     3422818749220588372|            00:00:08
|  17|                    -5730801771815999400|            00:00:03
|  18|                    -1473395109729441239|            00:00:02
|  19|                     2404820632950544954|            00:00:02
|  20|                    -6572922443698419129|            00:00:02
|  21|                     2369289265278398647|            00:00:01
|  22|                      180077086776069052|            00:00:01
+----+----------------------------------------+--------------------

ČAKANIA NA OTÁZKY

Žiadosti:

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event 
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 datname != 'postgres' AND
	aa.wait_event IS NOT NULL 
GROUP BY aa.wait_event_type , aa.wait_event
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
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 datname != 'postgres' AND
	ha.wait_event IS NOT NULL 
GROUP BY ha.wait_event_type , ha.wait_event		
)
SELECT 	wait_event_type , wait_event 
FROM hist
GROUP BY wait_event_type , wait_event
ORDER BY 1 ASC,2 ASC

----------------------------------------------------------------------

WITH hist AS
(
SELECT 
	aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid ,			
	count(*) * interval '1 second' 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 datname != 'postgres' AND
	( aa.wait_event_type = waitings_stat_rec.wait_event_type AND aa.wait_event = waitings_stat_rec.wait_event )
GROUP BY aa.wait_event_type , aa.wait_event , aa.query ,aa.queryid		
UNION 
SELECT 
	ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid,
	count(*) * interval '1 second' AS duration 
FROM 	activity_hist.history_pg_stat_activity_for_reports ha
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 datname != 'postgres' AND				
	( ha.wait_event_type = waitings_stat_rec.wait_event_type AND ha.wait_event = waitings_stat_rec.wait_event )
GROUP BY ha.wait_event_type , ha.wait_event , ha.query ,ha.queryid		
)
SELECT 	query , queryid , SUM( duration ) as duration 
FROM hist
GROUP BY  query , queryid 
ORDER BY 3 DESC

Príklad:

------------------------------------------------
| WAITINGS FOR QUERIES
+-----------------------------------------------
|                      wait_event_type = Client|
|                       wait_event = ClientRead|
|                        Total time  = 00:46:56|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1| 8085340880788646241|            00:40:20
|    2|                    |            00:03:45
|    3| 5731212217001535134|            00:01:53
|    4|                    |            00:00:12
|    5| 9150846928388977274|            00:00:09
|    6| 3422818749220588372|            00:00:08
|    7| 1237430309438971376|            00:00:06
|    8|   28942442626229688|            00:00:05
|    9| 4710212362688288619|            00:00:05
|   10|-5730801771815999400|            00:00:03
|   11| 8304755792398128062|            00:00:02
|   12|-6572922443698419129|            00:00:02
|   13|-1473395109729441239|            00:00:02
|   14| 2404820632950544954|            00:00:02
|   15|  180077086776069052|            00:00:01
|   16| 2369289265278398647|            00:00:01

+-----------------------------------------------
|                          wait_event_type = IO|
|                      wait_event = BufFileRead|
|                        Total time  = 00:00:38|
------------------------------------------------
|    #|             queryid|            duration
+-----+--------------------+--------------------
|    1|   28942442626229688|            00:00:38

+-----------------------------------------------

HISTÓRIA UZAMKNUTÝCH PROCESOV

Žiadosť:

SELECT 
MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype 	 
FROM 
	activity_hist.archive_locking al 
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
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
UNION
SELECT 
	MIN(date_trunc('second',timepoint)) AS started , 
	count(*) * interval '1 second' as duration ,
	pid , blocking_pids , relation , mode , locktype
FROM 
	activity_hist.history_locking 
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
	NOT granted AND 
	locktype = 'relation' 
GROUP BY pid , blocking_pids , relation , mode , locktype			
ORDER BY 1

Príklad:

-------------------------------------------------- -------------------------------------------------- ---------------------------------- | HISTÓRIA UZAMKNUTÝCH PROCESOV +-----+----------+-----+-------- --+------------ --------+--------------------+----- --------------- +-------------------- | #| pid| začalo| trvanie| blocking_pids| vzťah| režim| typ zámku +----------+----------+-----+---------- +---------- -----------+-----------+------- -------------+------ --------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccessShareLock| vzťah | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| vzťah | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccessShareLock| vzťah | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccessShareLock| vzťah | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| vzťah | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| vzťah | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccessShareLock| vzťah | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccessShareLock| vzťah | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| vzťah | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccessShareLock| vzťah | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| vzťah | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccessShareLock| vzťah | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| vzťah | | | | | 28376| |

HISTÓRIA BLOKOVACÍCH PROCESOV

Žiadosti:

SELECT 
blocking_pids 
FROM 
	activity_hist.archive_locking al 
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
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
UNION
SELECT 
	blocking_pids 
FROM 
	activity_hist.history_locking 
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
	NOT granted AND 
	locktype = 'relation' 
GROUP BY blocking_pids 		
ORDER BY 1

---------------------------------------------------------------

SELECT 
	pid , usename , application_name , datname ,
	MIN(date_trunc('second',timepoint)) as started , 
	count(*) * interval '1 second' as duration ,		 
	state , 
	query
				FROM  	activity_hist.archive_pg_stat_activity
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				UNION
				SELECT 
					pid , usename , application_name , datname ,
					MIN(date_trunc('second',timepoint)) as started , 
					count(*) * interval '1 second' as duration ,		 
					state , 
					query
				FROM  	activity_hist.history_pg_stat_activity_for_reports
				WHERE 	pid= current_pid AND 
						timepoint BETWEEN pg_stat_history_begin+(current_hour_diff * interval '1 hour') AND pg_stat_history_end+(current_hour_diff * interval '1 hour') 						 
				GROUP BY pid , usename , application_name , 
						datname , 
						state_change, 
						state , 
						query 
				ORDER BY 5 , 1

Príklad:

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- HISTÓRIA BLOKOVACÍCH PROCESOV +----+----------+-------- ---+--------------------+----------+-------------- ------+--------------------+----------------------- -------+---------------------------------------- | #| pid| používateľské meno| názov_aplikácie| dátový názov| začalo| trvanie| štát| dotaz +----------+----------+----------+-----+ --------- ------------------------+------------------ --+------ --------------------------+---------------- ------- ------------------ | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| nečinný| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| nečinný v transakcii| začať; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| nečinný v transakcii| tabuľka uzamknutia wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| nečinný| zaviazať sa; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| nečinný v transakcii| začať; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| nečinný v transakcii| tabuľka uzamknutia wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| nečinný| zaviazať sa; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| aktívny| select test_del();

rozvoj.

Zobrazované základné dotazy a výsledné zostavy už značne uľahčujú život pri analýze incidentov výkonu.
Na základe základných dopytov môžete získať správu, ktorá sa nejasne podobá na AWR spoločnosti Oracle.
Príklad súhrnnej správy

+-------------------------------------------------- ------------------------------------ | KONSOLIDOVANÁ SPRÁVA O ČINNOSTI A ČAKANÍ.

Pokračovanie nabudúce. Ďalším v poradí je vytvorenie histórie zámkov (pg_stat_locks), detailnejší popis procesu plnenia tabuliek.

Zdroj: hab.com

Pridať komentár