ProHoster > Blog > administration > Une tentative de créer un analogue ASH pour PostgreSQL

Une tentative de créer un analogue ASH pour PostgreSQL

Formulation du problème

Pour optimiser les requêtes PostgreSQL, la capacité d'analyser l'historique des activités, en particulier les attentes, les verrous et les statistiques des tables, est indispensable.

Opportunités disponibles

Outil d'analyse de la charge de travail historique ou "AWR pour Postgres": une solution très intéressante, mais il n'y a pas d'historique de pg_stat_activity et pg_locks.

extension pgsentinel :
«Toutes les informations accumulées sont stockées uniquement dans la RAM et la quantité de mémoire consommée est régulée par le nombre des derniers enregistrements stockés.

Le champ queryid est ajouté - le même queryid de l'extension pg_stat_statements (pré-installation requise).«

Bien sûr, cela aiderait beaucoup, mais le plus gênant est le premier point.Toutes les informations accumulées sont stockées uniquement dans la RAM ", c'est à dire. il y a un impact sur la base cible. De plus, il n'y a pas d'historique de verrouillage ni de statistiques de table. Ceux. la solution est généralement incomplète : «Il n'existe pas encore de package prêt à l'emploi pour l'installation. Il est suggéré de télécharger les sources et d'assembler la bibliothèque vous-même. Vous devez d'abord installer le package « devel » pour votre serveur et définir le chemin vers pg_config dans la variable PATH. ».

En général, il y a beaucoup de bruit, et dans le cas de bases de données de production sérieuses, il peut ne pas être possible de faire quoi que ce soit avec le serveur. Nous devons à nouveau trouver quelque chose qui nous est propre.

Avertissement

En raison du volume assez important et de la période de test incomplète, l'article est principalement destiné à des fins d'information, plutôt qu'à un ensemble de thèses et de résultats intermédiaires.
Un matériel plus détaillé sera préparé plus tard, en partie

Ébauche d'exigences pour la solution

Il est nécessaire de développer un outil qui permette de stocker :

pg_stat_activity afficher l'historique
Historique de verrouillage de session à l'aide de la vue pg_locks

Exigence de solution–minimiser l’impact sur la base de données cible.

Idée générale– l'agent de collecte de données n'est pas lancé dans la base de données cible, mais dans la base de données de surveillance en tant que service systemd. Oui, une certaine perte de données est possible, mais cela n'est pas critique pour le reporting, mais il n'y a aucun impact sur la base de données cible en termes de mémoire et d'espace disque. Et dans le cas de l'utilisation d'un pool de connexions, l'impact sur les processus utilisateur est minime.

Étapes de mise en œuvre

1.Tableaux de services

Un schéma distinct est utilisé pour stocker les tables, afin de ne pas compliquer l'analyse des principales tables utilisées.

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

Important : Le schéma n'est pas créé dans la base de données cible, mais dans la base de données de surveillance.

pg_stat_activity afficher l'historique

Une table est utilisée pour stocker les instantanés actuels de la vue 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
);

Pour accélérer l'insertion - pas d'index ni de restrictions.

Pour stocker l'historique lui-même, une table partitionnée est utilisée :

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

Comme dans ce cas il n'y a aucune exigence en matière de vitesse d'insertion, certains index ont été créés pour accélérer la création de rapports.

Historique des blocages de sessions

Une table est utilisée pour stocker les instantanés actuels des verrous de session :

activité_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
);

De plus, pour accélérer l’insertion, il n’y a aucun index ni restriction.

Pour stocker l'historique lui-même, une table partitionnée est utilisée :

activité_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);

Comme dans ce cas il n'y a aucune exigence en matière de vitesse d'insertion, certains index ont été créés pour accélérer la création de rapports.

2.Remplir l'historique actuel

Pour collecter directement des instantanés de vue, un script bash est utilisé pour exécuter la fonction 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 La fonction dblink accède aux vues de la base de données cible et insère des lignes dans les tables de service de la base de données de surveillance.

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;

Pour collecter des instantanés de vue, le service systemd et deux scripts sont utilisés :

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

Attribuez des droits aux scripts :
# chmod 755 pg_current_activity.timer
# chmod 755 pg_current_activity.service

Commençons le service :
# systemctl daemon-reload
# systemctl démarre pg_current_activity.service

Ainsi, l’historique des vues est collecté sous forme d’instantanés seconde par seconde. Bien entendu, si tout est laissé tel quel, les tables vont très vite augmenter en taille et un travail plus ou moins productif deviendra impossible.

Il est nécessaire d'organiser l'archivage des données.

3. Archivage de l'historique

Pour l'archivage, des tables partitionnées archive* sont utilisées.

De nouvelles partitions sont créées toutes les heures, tandis que les anciennes données sont supprimées des tables d'historique*, de sorte que la taille des tables d'historique* ne change pas beaucoup et que la vitesse d'insertion ne se dégrade pas avec le temps.

La création de nouvelles sections est effectuée par la fonction plpgsqlactivity_hist.archive_current_activity. L'algorithme de travail est très simple (en utilisant l'exemple de la section pour la table archive_pg_stat_activity).

Créer et remplir une nouvelle section

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

Création d'index

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

Suppression des anciennes données de la table history_pg_stat_activity

DELETE 
FROM 	activity_hist.history_pg_stat_activity
WHERE 	timepoint < partition_max_range;

Bien entendu, de temps en temps, les anciennes sections sont supprimées car inutiles.

Rapports de base

Au fait, pourquoi tout cela est-il fait ? Pour obtenir des rapports rappelant très vaguement l'AWR d'Oracle.

Il est important d'ajouter que pour recevoir des rapports, vous devez établir une connexion entre les vues pg_stat_activity et pg_stat_statements. Les tables sont liées en ajoutant une colonne 'queryid' aux tables 'history_pg_stat_activity', 'archive_pg_stat_activity'. La méthode d'ajout d'une valeur de colonne dépasse le cadre de cet article et est décrite ici - pg_stat_statements + pg_stat_activity + loq_query = pg_ash ? .

TEMPS CPU TOTAL POUR LES REQUÊTES

Demande :

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

Exemple:

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

TEMPS D'ATTENTE TOTAL POUR LES REQUÊTES

Demande :

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

Exemple:

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

ATTENTES DE REQUÊTES

Demandes :

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

Exemple:

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

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

HISTORIQUE DES PROCESSUS VERROUILLÉS

Demande:

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

Exemple:

-------------------------------------------------- -------------------------------------------------- --------------------------------- | HISTORIQUE DES PROCESSUS VERROUILLÉS +-----+----------+-----+-------- --+------------ ---------+----------+----- --------------- +------------------- | #| pid| commencé | durée| blocage_pids| relation| mode| type de verrouillage +----------+----------+-----+---------- +--------- -----------+-----------+------- -------------+--------- ---------- | 1| 26224| 2019-09-02 19:32:16| 00:01:45| {26211}| 16541| AccèsShareLock| relation | 2| 26390| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccèsShareLock| relation | 3| 26391| 2019-09-02 19:34:03| 00:00:53| {26211}| 16541| AccèsShareLock| relation | 4| 26531| 2019-09-02 19:35:27| 00:00:12| {26211}| 16541| AccèsShareLock| relation | 5| 27284| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccèsShareLock| relation | 6| 27283| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccèsShareLock| relation | 7| 27286| 2019-09-02 19:44:02| 00:00:19| {27276}| 16541| AccèsShareLock| relation | 8| 27423| 2019-09-02 19:45:24| 00:00:12| {27394}| 16541| AccèsShareLock| relation | 9| 27648| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccèsShareLock| relation | 10| 27650| 2019-09-02 19:48:06| 00:00:20| {27647}| 16541| AccèsShareLock| relation | 11| 27735| 2019-09-02 19:49:08| 00:00:06| {27650}| 16541| AccèsExclusiveLock| relation | 12| 28380| 2019-09-02 19:56:03| 00:01:56| {28379}| 16541| AccèsShareLock| relation | 13| 28379| 2019-09-02 19:56:03| 00:00:01| 28377| 16541| AccèsExclusiveLock| relation | | | | | 28376| |

HISTORIQUE DES PROCESSUS DE BLOCAGE

Demandes :

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

Exemple:

-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ----------------------- HISTORIQUE DES PROCESSUS DE BLOCAGE +----+--------------+------- ---+----------+----------+-------------- ---+------------------------+-------------------------- - -------+-------------------------------------------- | #| pid| nom d'utilisateur| nom_application| nom de données| commencé| durée| état| requête +----------+----------+--------------+---------+ --------- -----------+------------------ --+------ --------------------------+---------------- ------- ----------------- | 1| 26211| utilisateur| psql| tdb1| 2019-09-02 19:31:54| 00:00:04| inactif | | 2| 26211| utilisateur| psql| tdb1| 2019-09-02 19:31:58| 00:00:06| inactif dans la transaction | commencer; | 3| 26211| utilisateur| psql| tdb1| 2019-09-02 19:32:16| 00:01:45| inactif dans la transaction | verrouiller la table wafer_data ; | 4| 26211| utilisateur| psql| tdb1| 2019-09-02 19:35:54| 00:01:23| inactif | commettre; | 5| 26211| utilisateur| psql| tdb1| 2019-09-02 19:38:46| 00:00:02| inactif dans la transaction | commencer; | 6| 26211| utilisateur| psql| tdb1| 2019-09-02 19:38:54| 00:00:08| inactif dans la transaction | verrouiller la table wafer_data ; | 7| 26211| utilisateur| psql| tdb1| 2019-09-02 19:39:08| 00:42:42| inactif | commettre; | 8| 26211| utilisateur| psql| tdb1| 2019-09-03 07:12:07| 00:00:52| actif| sélectionnez test_del();

Développement.

Les requêtes de base affichées et les rapports qui en résultent facilitent déjà grandement la vie lors de l'analyse des incidents de performances.
Sur la base de requêtes de base, vous pouvez obtenir un rapport qui ressemble vaguement à l'AWR d'Oracle.
Exemple de rapport récapitulatif

+------------------------------------------------- ----------------------------------- | RAPPORT CONSOLIDÉ D'ACTIVITÉ ET D'ATTENTE.

À suivre. Vient ensuite la création d'un historique de verrouillage (pg_stat_locks), une description plus détaillée du processus de remplissage des tables.

Source: habr.com

Ajouter un commentaire