ProHoster > blog > administratie > Een poging om een ​​ASH-analoog voor PostgreSQL te maken

Een poging om een ​​ASH-analoog voor PostgreSQL te maken

Formulering van het probleem

Om PostgreSQL-query's te optimaliseren, is de mogelijkheid om de activiteitengeschiedenis te analyseren, met name wachttijden, vergrendelingen en tabelstatistieken, zeer vereist.

Beschikbare mogelijkheden

Historische werklastanalysetool of "AWR voor Postgres": een zeer interessante oplossing, maar er is geen geschiedenis van pg_stat_activity en pg_locks.

pgsentinel-extensie :
«Alle verzamelde informatie wordt alleen in RAM opgeslagen en de verbruikte hoeveelheid geheugen wordt geregeld door het aantal laatst opgeslagen records.

Het queryid-veld is toegevoegd - dezelfde queryid uit de pg_stat_statements-extensie (vooraf geïnstalleerde versie vereist).«

Dit zou natuurlijk veel helpen, maar het meest lastige is het eerste punt.Alle verzamelde informatie wordt alleen in RAM opgeslagen ”, d.w.z. er is impact op de doelgroep. Bovendien is er geen slotgeschiedenis en tabelstatistieken. Die. de oplossing is over het algemeen onvolledig: “Er is nog geen kant-en-klaar pakket voor installatie. Er wordt voorgesteld om de bronnen te downloaden en de bibliotheek zelf samen te stellen. U moet eerst het “devel”-pakket voor uw server installeren en het pad instellen op pg_config in de PATH-variabele.'.

Over het algemeen is er veel gedoe, en bij serieuze productiedatabases is het misschien niet mogelijk om iets met de server te doen. We moeten weer iets eigens verzinnen.

Waarschuwing.

Vanwege het vrij grote volume en vanwege de onvolledige testperiode is het artikel voornamelijk bedoeld voor informatieve doeleinden, eerder als een reeks scripties en tussenresultaten.
Meer gedetailleerd materiaal zal later in delen worden voorbereid

Ontwerp eisen voor de oplossing

Het is noodzakelijk om een ​​tool te ontwikkelen waarmee u het volgende kunt opslaan:

pg_stat_activity geschiedenis bekijken
Sessievergrendelingsgeschiedenis met behulp van de pg_locks-weergave

Oplossing vereiste–de impact op de doeldatabase minimaliseren.

Algemeen idee– de gegevensverzamelingsagent wordt niet in de doeldatabase gestart, maar in de monitoringdatabase als een systemd-service. Ja, enig gegevensverlies is mogelijk, maar dit is niet van cruciaal belang voor de rapportage, maar er is geen impact op de doeldatabase in termen van geheugen en schijfruimte. En bij het gebruik van een connectiepool is de impact op gebruikersprocessen minimaal.

Stadia van implementatie

1.Servicetafels

Er wordt een apart schema gebruikt om tabellen op te slaan, om de analyse van de gebruikte hoofdtabellen niet te bemoeilijken.

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

Belangrijk: Het schema wordt niet in de doeldatabase gemaakt, maar in de monitoringdatabase.

pg_stat_activity geschiedenis bekijken

Er wordt een tabel gebruikt om huidige momentopnamen van de pg_stat_activity-weergave op te slaan

activiteit_hist.history_pg_stat_activiteit:

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

Om het invoegen te versnellen - geen indexen of beperkingen.

Om de geschiedenis zelf op te slaan, wordt een gepartitioneerde tabel gebruikt:

activiteit_hist.archive_pg_stat_activiteit:

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

Omdat er in dit geval geen vereisten zijn voor de invoegsnelheid, zijn er enkele indexen gemaakt om het maken van rapporten te versnellen.

Sessieblokkeringsgeschiedenis

Er wordt een tabel gebruikt om huidige momentopnamen van sessievergrendelingen op te slaan:

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

Om het invoegen te versnellen, zijn er bovendien geen indexen of beperkingen.

Om de geschiedenis zelf op te slaan, wordt een gepartitioneerde tabel gebruikt:

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

Omdat er in dit geval geen vereisten zijn voor de invoegsnelheid, zijn er enkele indexen gemaakt om het maken van rapporten te versnellen.

2. De huidige geschiedenis invullen

Om weergavesnapshots rechtstreeks te verzamelen, wordt een bash-script gebruikt dat de plpgsql-functie uitvoert.

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 De dblink-functie heeft toegang tot views in de doeldatabase en voegt rijen in servicetabellen in de monitoringdatabase in.

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;

Om weergavemomentopnamen te verzamelen, worden de systemd-service en twee scripts gebruikt:

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

Rechten toewijzen aan scripts:
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Laten we de dienst starten:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Zo wordt de geschiedenis van weergaven verzameld in de vorm van snapshots van seconde tot seconde. Als alles blijft zoals het is, zullen de tafels natuurlijk zeer snel in omvang toenemen en zal min of meer productief werk onmogelijk worden.

Het is noodzakelijk om de archivering van gegevens te organiseren.

3. Geschiedenis archiveren

Voor archivering wordt gebruik gemaakt van gepartitioneerd tabellenarchief*.

Er worden elk uur nieuwe partities gemaakt, terwijl oude gegevens uit de geschiedenistabellen worden verwijderd. De grootte van de geschiedenistabellen verandert dus niet veel en de invoegsnelheid neemt niet af in de loop van de tijd.

Het aanmaken van nieuwe secties wordt uitgevoerd door de plpgsql-functie Activity_hist.archive_current_activity. Het werkalgoritme is heel eenvoudig (met behulp van het voorbeeld van de sectie voor de tabel archive_pg_stat_activity).

Maak een nieuw gedeelte aan en vul het in

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

Indexen maken

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

Oude gegevens verwijderen uit de tabel history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Natuurlijk worden van tijd tot tijd oude secties verwijderd omdat ze overbodig zijn.

Basisrapporten

Waarom wordt dit eigenlijk allemaal gedaan? Om rapporten te verkrijgen die heel vaag doen denken aan Oracle's AWR.

Het is belangrijk om toe te voegen dat u, om rapporten te ontvangen, een verbinding moet opbouwen tussen de weergaven pg_stat_activity en pg_stat_statements. De tabellen zijn gekoppeld door een 'queryid'-kolom toe te voegen aan de tabellen 'history_pg_stat_activity' en 'archive_pg_stat_activity'. De methode voor het toevoegen van een kolomwaarde valt buiten het bestek van dit artikel en wordt hier beschreven − pg_stat_statements + pg_stat_activity + loq_query = pg_ash? .

TOTALE CPU-TIJD VOOR QUERIES

Verzoek :

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

Voorbeeld:

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

TOTALE WACHTTIJD VOOR VRAGEN

Verzoek :

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

Voorbeeld :

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

WACHTEN OP VRAGEN

Verzoeken:

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

Voorbeeld:

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

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

VERGRENDELDE PROCESGESCHIEDENIS

Verzoek:

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

Voorbeeld:

--------------------------------------------- --------------------------------------------- ------------------------------- | VERGRENDELDE PROCESGESCHIEDENIS +-----+----------+-----+-------- --+----------- --------+-------------------+----- --------------- +------------------- | #| pid| begonnen| duur| blocking_pids| relatie| modus| type slot +----------+----------+-----+---------- +--------- -----------+-----------+------- ------------+----- -------------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| ToegangDelenLock| relatie | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| ToegangDelenLock| relatie | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| ToegangDelenLock| relatie | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| ToegangDelenLock| relatie | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ToegangDelenLock| relatie | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ToegangDelenLock| relatie | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| ToegangDelenLock| relatie | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| ToegangDelenLock| relatie | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| ToegangDelenLock| relatie | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| ToegangDelenLock| relatie | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccessExclusiveLock| relatie | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| ToegangDelenLock| relatie | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccessExclusiveLock| relatie | | | | | 28376| |

GESCHIEDENIS VAN BLOKKERINGSPROCESSEN

Verzoeken:

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

Voorbeeld:

--------------------------------------------- --------------------------------------------- --------------------------------------------- ---------------------- GEBLOKKEERDE PROCESSEN GESCHIEDENIS +----+----------+------- ---+------------------+----------+------------- ------+-------------------+-------------------- - -------+------------------------------------ | #| pid| gebruikersnaam| applicatienaam| datnaam| begonnen| duur| staat| vraag +----------+----------+----------+-----+ --------- -+-------------------+---------------- --+------ -------------------------+--------------- ------- ----------------- | 1| 26211| tuser| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| inactief| | 2| 26211| tuser| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| inactief in transactie| beginnen; | 3| 26211| tuser| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| inactief in transactie| vergrendel tabel wafer_data; | 4| 26211| tuser| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| inactief| verbinden; | 5| 26211| tuser| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| inactief in transactie| beginnen; | 6| 26211| tuser| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| inactief in transactie| vergrendel tabel wafer_data; | 7| 26211| tuser| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| inactief| verbinden; | 8| 26211| tuser| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| actief| selecteer test_del();

Ontwikkeling.

De getoonde basisvragen en de daaruit voortvloeiende rapporten maken het leven al veel eenvoudiger bij het analyseren van prestatie-incidenten.
Op basis van basisquery's kunt u een rapport krijgen dat vaag lijkt op de AWR van Oracle.
Samenvattend rapport voorbeeld

+--------------------------------------------- ---------------------------------- | GECONSOLIDEERD RAPPORT VOOR ACTIVITEITEN EN WACHTEN.

Wordt vervolgd. De volgende stap is het aanmaken van een vergrendelingsgeschiedenis (pg_stat_locks), een meer gedetailleerde beschrijving van het proces van het vullen van tabellen.

Bron: www.habr.com

Voeg een reactie