🥇Surveillance des performances des requêtes PostgreSQL. Partie 1 — reporting

Ingénieur — du latin, cela signifie inspiré.
L'ingénieur peut tout faire. (c) R.Diesel.
Épigraphes.

Ou l'histoire de la raison pour laquelle un administrateur de base de données devrait se souvenir de son passé de programmeur.

Préface

Tous les noms ont été modifiés. Les coïncidences sont fortuites. Le matériel représente uniquement l'opinion personnelle de l'auteur.

Avertissement sur les garanties : Il n'y aura pas de description détaillée et précise des tables et scripts utilisés dans le cycle d'articles prévu. Les matériaux ne pourront pas être utilisés immédiatement 'EN L'ÉTAT'.
Tout d'abord, en raison du volume important de matériel,
deuxièmement, en raison de l'adéquation avec la base de production du client réel.
C'est pourquoi les articles ne présenteront que des idées et des descriptions de manière très générale.
Peut-être qu'à l'avenir, le système évoluera vers le partage sur GitHub, ou peut-être pas. Le temps le dira.

Début de l'histoire - «Tu te souviens comment tout a commencé ?».
Ce qui en est sorti, en termes très généraux - «La synthèse comme l'une des méthodes d'amélioration de la performance de PostgreSQL»

Pourquoi tout cela m'intéresse-t-il ?

Eh bien, d'abord pour ne pas oublier, en me rappelant les bons jours à la retraite.
Deuxièmement, pour systématiser ce qui a été écrit. Car parfois, je commence moi-même à me perdre et à oublier certaines parties.

Et surtout, qui sait, cela pourrait être utile à quelqu'un et l'aider à ne pas réinventer la roue ou à ne pas se manger les doigts. En d'autres termes, améliorer son karma (pas au sens Habr). Car, ce qui est le plus précieux dans ce monde, ce sont les idées. L'essentiel est de trouver une idée. La concrétiser est une question purement technique.

Alors, commençons doucement…

Énoncé du problème.

Il y a :

Base de données PostgreSQL (10.5), de type de charge mixte (OLTP+DSS), de charge moyenne à faible, située dans le cloud AWS.
La surveillance de la base de données est absente, la surveillance de l'infrastructure est assurée par les outils standards d'AWS dans une configuration minimale.

Exigences :

Surveiller les performances et l'état de la base de données, trouver et disposer d'informations initiales pour optimiser les requêtes lourdes à la base de données.

Introduction ou analyse des options de solution

Pour commencer, essayons d'examiner les options de solution du point de vue d'une analyse comparative des avantages et des désagréments pour l'ingénieur, tandis que les bénéfices et les pertes pour la direction peuvent être traités par ceux qui sont responsables selon l'organigramme.

Option 1 - «Travailler à la demande»

Nous laissons tout comme c'est. Si le client n'est pas satisfait de la performance de la base de données ou de l'application, il informera les ingénieurs DBA par e-mail ou en créant un incident dans le système de tickets.
L'ingénieur, après avoir reçu l'alerte, se penchera sur le problème, proposera une solution ou reportera le problème, espérant que tout se résoudra de lui-même, et de toute façon, tout sera bientôt oublié.
Gâteaux et beignets, bleus et bossesGâteaux et beignets :
1. Il n'est pas nécessaire de faire quoi que ce soit de superflu.
2. Il y a toujours la possibilité de se défiler et de feinter.
3. Une montagne de temps que l'on peut utiliser à sa guise.
Bleus et bosses :
1. Tôt ou tard, le client se posera des questions sur la nature de l'existence et la justice universelle dans ce monde, et se demandera encore : pourquoi paye-t-il de l'argent ? Les conséquences sont toujours les mêmes — la seule question est quand le client s'ennuiera et se résignera à dire au revoir. Et le gagne-pain se videra. C'est triste.
2. Le développement de l'ingénieur — zéro.
3. Difficultés dans la planification du travail et de la charge.

Option 2 - «On danse avec des tambourins, on vend et on chausse.»

Point 1-Pourquoi avons-nous besoin d'un système de surveillance, nous traiterons toutes les demandes. Nous exécutons une multitude de demandes vers le dictionnaire de données et les vues dynamiques, activons divers compteurs, compilons tout dans des tableaux, et analysons périodiquement les listes et tableaux. En fin de compte, nous avons de beaux graphiques ou pas très beaux, des tableaux, des rapports. L'essentiel est d'avoir toujours plus.
Point 2-Nous générons de l'activité — nous lançons l'analyse de tout cela.
Point 3-Nous préparons un document, que nous appelons simplement — «comment organiser notre base de données».
Point 4-Le client, voyant toute cette magnificence de graphiques et de chiffres, est dans une naïve confiance enfantine — voilà, maintenant tout va fonctionner, bientôt. Et il se sépare facilement et sans douleur de ses ressources financières. La direction est aussi convaincue — nos ingénieurs travaillent d'arrache-pied. La charge est à son maximum.
Point 5-Répéter régulièrement le Point 1.
Gâteaux et beignets, bleus et bossesGâteaux et beignets :
1. La vie des managers et des ingénieurs est simple, prévisible et pleine d'activité. Tout bourdonne, tout le monde est occupé.
2. La vie du client n'est pas mal non plus — il est toujours sûr qu'il suffit de patienter un peu et tout se mettra en place. Ça ne s'arrange pas, eh bien, que faire — c'est un monde injuste, peut-être dans une prochaine vie — il aura de la chance.
Bleus et bosses :
1. Tôt ou tard, il y aura un fournisseur plus rapide offrant un service similaire, qui fera la même chose, mais à un prix légèrement inférieur. Et si le résultat est le même, pourquoi payer plus ? Ce qui, encore une fois, entraînera la disparition de cette source de revenus.
2. C'est ennuyeux. Tout aussi ennuyeux que n'importe quelle activité peu significative.
3. Comme dans l'option précédente, il n'y a pas de développement. Mais pour un ingénieur, un inconvénient est que, contrairement à la première option, ici il faut constamment générer des bases de données d'informations. Et cela prend du temps. Un temps qui pourrait être utilisé de manière bénéfique pour soi-même. Car si on ne prend pas soin de soi, personne ne le fera.

Option 3 - Il n'est pas nécessaire de réinventer la roue, il faut l'acheter et rouler.

Les ingénieurs d'autres entreprises ne mangent pas de pizza en buvant de la bière pour rien (ah, les bons vieux temps de Saint-Pétersbourg des années 90). Utilisons des systèmes de surveillance qui sont déjà conçus, testés et fonctionnent, et qui apportent en fait un bénéfice (au moins à leurs créateurs).
Gâteaux et beignets, bleus et bossesGâteaux et beignets :
1. Il n'est pas nécessaire de perdre du temps à créer ce qui a déjà été créé. Prenez et utilisez.
2. Les systèmes de surveillance ne sont pas conçus par des idiots et ils sont bien sûr utiles.
3. Les systèmes de surveillance fonctionnels fournissent généralement des informations filtrées pertinentes.
Bleus et bosses :
1. Dans ce cas particulier, l'ingénieur n'est pas un ingénieur, mais simplement un utilisateur d'un produit d'autrui. Ou un utilisateur.
2. Il faut convaincre le client de la nécessité d'acheter quelque chose qu'il ne veut, en réalité, pas comprendre ni devoir ; et en plus, le budget annuel a été approuvé et ne changera pas. Ensuite, il faut allouer des ressources distinctes et configurer pour un système particulier. Donc, d'abord il faut payer, payer et encore payer. Et le client est avare. C'est la norme de cette vie.

Que faire - Tchernychevski ? Ta question est tout à fait pertinente. (c)

Dans ce cas précis et la situation actuelle, on peut agir un peu différemment — et pourquoi ne pas créer notre propre système de surveillance.

Eh bien, pas vraiment un système au sens plein du terme, c'est trop ambitieux et présomptueux, mais au moins simplifier la tâche et recueillir plus d'informations pour résoudre les incidents de performance. Afin de ne pas se retrouver dans une situation où il faut dire : « Va là où je ne sais pas où, trouve ce que je ne sais pas quoi ».

Quels sont les avantages et les inconvénients de cette option :

Avantages :
1. C'est intéressant. Enfin, au moins plus intéressant que les incessantes « shrink datafile, alter tablespace, etc. »
2. Ce sont de nouvelles compétences et un nouveau développement. Cela donnera tôt ou tard des récompenses bien méritées.
Inconvénients :
1. Il faudra travailler. Travailler beaucoup.
2. Il faudra régulièrement expliquer le sens et les perspectives de toute l'activité.
3. Il faudra céder à quelque chose, car la seule ressource dont dispose l'ingénieur — le temps — est limitée par l'univers.
4. Ce qui est le plus terrible et le plus désagréable — c'est qu'on peut finir par obtenir quelque chose comme « Ni souriceau, ni grenouille, mais une bête inconnue ».

Qui ne risque rien n'a rien.
Alors — le plus intéressant commence.

L'idée générale — schématiquement

(L'illustration est tirée de l'article «La synthèse comme l'une des méthodes d'amélioration de la performance de PostgreSQL»)

Explication :

Dans la base cible, l'extension standard PostgreSQL — « pg_stat_statements » — est installée.
Dans la base de données de surveillance, nous créons un ensemble de tables de service pour stocker l'historique de pg_stat_statements à ses débuts et pour configurer les métriques et la surveillance par la suite.
Sur l'hôte de surveillance, nous créons un ensemble de scripts bash, y compris pour générer des incidents dans le système de tickets.

Tables de service

Pour commencer, voici un ERD schématique simplifié de ce qui a été réalisé :

Description succincte des tablesendpoint — hôte, point de connexion à l'instance
database — paramètres de la base de données
pg_stat_history — table historique pour stocker des instantanés temporels de la vue pg_stat_statements de la base de données cible
metric_glossary — glossaire des métriques de performance
metric_config — configuration des métriques individuelles
metric — métrique spécifique pour la requête surveillée
metric_alert_history — historique des alertes de performance
log_query — table de service pour stocker les enregistrements analysés du fichier journal PostgreSQL chargé depuis AWS
baseline — paramètres de la période temporelle utilisée comme référence
checkpoint — configuration des métriques de vérification de l'état de la base de données
checkpoint_alert_history — historique des alertes des métriques de vérification de l'état de la base de données
pg_stat_db_queries — table de service des requêtes actives
activity_log — table de service du journal d'activité
trap_oid — table de service de configuration du trap

Étape 1 — nous collectons des informations statistiques sur la performance et obtenons des rapports

La table est utilisée pour stocker les informations statistiques pg_stat_history
Structure de la table pg_stat_history

                                          Table "public.pg_stat_history"
       Column        |            Type             |                          Modifiers
---------------------+-----------------------------+-------------------------------------------
 id                  | integer                     | not null default nextval('pg_stat_history_id_seq'::regclass)
 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                     |
Indexes:
    "pg_stat_history_pkey" PRIMARY KEY, btree (id)
    "database_idx" btree (database_id)
    "queryid_idx" btree (queryid)
    "snapshot_timestamp_idx" btree (snapshot_timestamp)
Foreign-key constraints:
    "database_id_fk" FOREIGN KEY (database_id) REFERENCES database(id) ON DELETE CASCADE

Comme on peut le voir, la table représente simplement des données cumulatives de la vue pg_stat_statements dans la base de données cible.

L'utilisation de cette table est très simple

pg_stat_history représentera des statistiques cumulées sur l'exécution des requêtes pour chaque heure. Au début de chaque heure, après le remplissage de la table, les statistiques pg_stat_statements sont réinitialisées à l'aide de pg_stat_statements_reset().
Remarque : les statistiques sont collectées pour les requêtes ayant une durée d'exécution supérieure à 1 seconde.
Remplissage de la table pg_stat_history

--pg_stat_history.sql
CREATE OR REPLACE FUNCTION pg_stat_history( ) RETURNS boolean AS $$
DECLARE
  endpoint_rec record ;
  database_rec record ;
  pg_stat_snapshot record ;
  current_snapshot_timestamp timestamp without time zone;
BEGIN
  current_snapshot_timestamp = date_trunc('minute',now());  
  
  FOR endpoint_rec IN SELECT * FROM endpoint 
  LOOP
    FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
	  LOOP
	    
		RAISE NOTICE 'NOUVEAU DOSSIER EN COURS DE CRÉATION';
		
		--Se connecter à la base de données cible	  
	    EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||endpoint_rec.host||' dbname='||database_rec.name||' user=USER password=PASSWORD '')';
 
        RAISE NOTICE 'hôte % et dbname % ',endpoint_rec.host,database_rec.name;
		RAISE NOTICE 'Création d’un instantané de pg_stat_statements pour la base de données %',database_rec.name;
		
		SELECT 
	      *
		INTO 
		  pg_stat_snapshot
	    FROM dblink('LINK1',
	      'SELECT 
	       dbid , SUM(calls),SUM(total_time),SUM(rows) ,SUM(shared_blks_hit) ,SUM(shared_blks_read) ,SUM(shared_blks_dirtied) ,SUM(shared_blks_written) , 
           SUM(local_blks_hit) , SUM(local_blks_read) , SUM(local_blks_dirtied) , SUM(local_blks_written) , SUM(temp_blks_read) , SUM(temp_blks_written) , SUM(blk_read_time) , SUM(blk_write_time)
	       FROM pg_stat_statements WHERE dbid=(SELECT oid from pg_database where datname=current_database() ) 
		   GROUP BY dbid
  	      '
	               )
	      AS t
	       ( dbid oid , calls bigint , 
  	         total_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	  
	       );
		 
		INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid , calls  ,total_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	    VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );		   
		  
        RAISE NOTICE 'Création d’un instantané de pg_stat_statements pour les requêtes ayant un temps minimum supérieur à 1000ms';
	
        FOR pg_stat_snapshot IN
          --Toutes les requêtes avec un temps_max supérieur à 1000 ms
	      SELECT 
	        *
	      FROM dblink('LINK1',
	        'SELECT 
	         dbid , userid ,queryid,query,calls,total_time,min_time ,max_time,mean_time, stddev_time ,rows ,shared_blks_hit ,
			 shared_blks_read ,shared_blks_dirtied ,shared_blks_written , 
             local_blks_hit , local_blks_read , local_blks_dirtied , 
			 local_blks_written , temp_blks_read , temp_blks_written , blk_read_time , 
			 blk_write_time
	         FROM pg_stat_statements 
			 WHERE dbid=(SELECT oid from pg_database where datname=current_database() AND min_time >= 1000 ) 
  	        '

	                  )
	        AS t
	         ( 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	  
	         )
	    LOOP
		  INSERT INTO pg_stat_history
          ( 
		    snapshot_timestamp  ,database_id  ,
			dbid ,userid  , queryid  , query  , calls  ,total_time ,min_time ,max_time ,mean_time ,stddev_time ,
            rows ,shared_blks_hit  ,shared_blks_read  ,shared_blks_dirtied  ,shared_blks_written ,local_blks_hit , 	 	
            local_blks_read,local_blks_dirtied,local_blks_written,temp_blks_read,temp_blks_written, 	
            blk_read_time, blk_write_time 
		  )		  
	      VALUES
	      (
	       current_snapshot_timestamp ,
		   database_rec.id ,
	       pg_stat_snapshot.dbid ,pg_stat_snapshot.userid ,pg_stat_snapshot.queryid,pg_stat_snapshot.query,pg_stat_snapshot.calls,
	       pg_stat_snapshot.total_time,pg_stat_snapshot.min_time ,pg_stat_snapshot.max_time,pg_stat_snapshot.mean_time, pg_stat_snapshot.stddev_time ,
	       pg_stat_snapshot.rows ,pg_stat_snapshot.shared_blks_hit ,pg_stat_snapshot.shared_blks_read ,pg_stat_snapshot.shared_blks_dirtied ,pg_stat_snapshot.shared_blks_written , 
           pg_stat_snapshot.local_blks_hit , pg_stat_snapshot.local_blks_read , pg_stat_snapshot.local_blks_dirtied , pg_stat_snapshot.local_blks_written , 
	       pg_stat_snapshot.temp_blks_read , pg_stat_snapshot.temp_blks_written , pg_stat_snapshot.blk_read_time , pg_stat_snapshot.blk_write_time 	   
	      );
		  
        END LOOP;

        PERFORM dblink_disconnect('LINK1');  
				
	  END LOOP ;--FOR database_rec IN SELECT * FROM database WHERE endpoint_id = endpoint_rec.id 
    
  END LOOP;

RETURN TRUE;  
END
$$ LANGUAGE plpgsql;

En conséquence, après un certain laps de temps dans le tableau pg_stat_history nous aurons un ensemble de captures du contenu du tableau pg_stat_statements de la base de données cible.

En fait, le reporting

En utilisant des requêtes simples, on peut obtenir des rapports assez utiles et intéressants.

Données agrégées sur une période donnée

Requête

SELECT 
  database_id , 
  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
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY database_id ;

Temps DB

to_char(interval '1 millisecond' * pg_total_stat_history_rec.total_time, 'HH24:MI:SS.MS')

Temps I/O

to_char(interval '1 millisecond' * ( pg_total_stat_history_rec.blk_read_time + pg_total_stat_history_rec.blk_write_time ), 'HH24:MI:SS.MS')

TOP10 SQL par total_time

Requête

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(total_time)  AS total_time  	
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT 
GROUP BY queryid 
ORDER BY 3 DESC 
LIMIT 10

-------------------------------------------------------------------------------------
| TOP10 SQL PAR TEMPS D'EXÉCUTION TOTAL
|   #|    queryid|      appels|    % des appels|                total_time (ms) |  % dbtime
+----+-----------+-----------+-----------+--------------------------------+----------
|   1|  821760255|          2|     .00001|00:03:23.141(    203141.681 ms.)|      5.42
|   2| 4152624390|          2|     .00001|00:03:13.929(    193929.215 ms.)|      5.17
|   3| 1484454471|          4|     .00001|00:02:09.129(    129129.057 ms.)|      3.44
|   4|  655729273|          1|     .00000|00:02:01.869(    121869.981 ms.)|      3.25
|   5| 2460318461|          1|     .00000|00:01:33.113(     93113.835 ms.)|      2.48
|   6| 2194493487|          4|     .00001|00:00:17.377(     17377.868 ms.)|       .46
|   7| 1053044345|          1|     .00000|00:00:06.156(      6156.352 ms.)|       .16
|   8| 3644780286|          1|     .00000|00:00:01.063(      1063.830 ms.)|       .03

TOP10 SQL par temps I/O total

Requête

SELECT 
  queryid , 
  SUM(calls) AS calls ,
  SUM(blk_read_time + blk_write_time)  AS io_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY  queryid 
ORDER BY 3 DESC 
LIMIT 10

----------------------------------------------------------------------------------------
| TOP10 SQL PAR TEMPS I/O TOTAL
|   #|    identifiant de requête|      appels|    % d'appels|                   temps I/O (ms)|% temps I/O db
+----+-----------+-----------+-----------+--------------------------------+-------------
|   1| 4152624390|          2|     .00001|00:08:31.616(    511616.592 ms.)|        31.06
|   2|  821760255|          2|     .00001|00:08:27.099(    507099.036 ms.)|        30.78
|   3|  655729273|          1|     .00000|00:05:02.209(    302209.137 ms.)|        18.35
|   4| 2460318461|          1|     .00000|00:04:05.981(    245981.117 ms.)|        14.93
|   5| 1484454471|          4|     .00001|00:00:39.144(     39144.221 ms.)|         2.38
|   6| 2194493487|          4|     .00001|00:00:18.182(     18182.816 ms.)|         1.10
|   7| 1053044345|          1|     .00000|00:00:16.611(     16611.722 ms.)|         1.01
|   8| 3644780286|          1|     .00000|00:00:00.436(       436.205 ms.)|          .03

TOP10 SQL par le temps d'exécution maximum

Requête

SELECT 
  id AS snapshotid , 
  queryid , 
  snapshot_timestamp ,  
  max_time 
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 4 DESC 
LIMIT 10

-----------------------------------------------------------------------------------------
| TOP10 SQL PAR TEMPS D'EXÉCUTION MAXIMAL
|   #|          instantané| identifiant de l'instantané|    identifiant de requête|                           temps_max (ms)
+----+------------------+-----------+-----------+----------------------------------------
|   1|  05.04.2019 01:03|       4169|  655729273|        00:02:01.869(    121869.981 ms.)
|   2|  04.04.2019 17:00|       4153|  821760255|        00:01:41.570(    101570.841 ms.)
|   3|  04.04.2019 16:00|       4146|  821760255|        00:01:41.570(    101570.841 ms.)
|   4|  04.04.2019 16:00|       4144| 4152624390|        00:01:36.964(     96964.607 ms.)
|   5|  04.04.2019 17:00|       4151| 4152624390|        00:01:36.964(     96964.607 ms.)
|   6|  05.04.2019 10:00|       4188| 1484454471|        00:01:33.452(     93452.150 ms.)
|   7|  04.04.2019 17:00|       4150| 2460318461|        00:01:33.113(     93113.835 ms.)
|   8|  04.04.2019 15:00|       4140| 1484454471|        00:00:11.892(     11892.302 ms.)
|   9|  04.04.2019 16:00|       4145| 1484454471|        00:00:11.892(     11892.302 ms.)
|  10|  04.04.2019 17:00|       4152| 1484454471|        00:00:11.892(     11892.302 ms.)

TOP10 SQL par la lecture/écriture de tampon PARTAGÉ

Requête

SELECT 
  id AS snapshotid , 
  queryid ,
  snapshot_timestamp , 
  shared_blks_read , 
  shared_blks_written 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( shared_blks_read > 0 OR shared_blks_written > 0 )
ORDER BY 4 DESC  , 5 DESC 
LIMIT 10

--------------------------------------------------------------------------------------------
| TOP10 SQL PAR LECTURE/ÉCRITURE DU BUFFER PARTAGÉ
|   #|          instantané| snapshotID|    queryid|   blocs partagés lus|  blocs partagés écrits
+----+------------------+-----------+-----------+---------------------+---------------------
|   1|  04.04.2019 17:00|       4153|  821760255|               797308|                    0
|   2|  04.04.2019 16:00|       4146|  821760255|               797308|                    0
|   3|  05.04.2019 01:03|       4169|  655729273|               797158|                    0
|   4|  04.04.2019 16:00|       4144| 4152624390|               756514|                    0
|   5|  04.04.2019 17:00|       4151| 4152624390|               756514|                    0
|   6|  04.04.2019 17:00|       4150| 2460318461|               734117|                    0
|   7|  04.04.2019 17:00|       4155| 3644780286|                52973|                    0
|   8|  05.04.2019 01:03|       4168| 1053044345|                52818|                    0
|   9|  04.04.2019 15:00|       4141| 2194493487|                52813|                    0
|  10|  04.04.2019 16:00|       4147| 2194493487|                52813|                    0
--------------------------------------------------------------------------------------------

Histogramme de la répartition des requêtes par temps d'exécution maximal

Requêtes

SELECT  
  MIN(max_time) AS hist_min  , 
  MAX(max_time) AS hist_max , 
  (( MAX(max_time) - MIN(min_time) ) / hist_columns ) as hist_width
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT ;

SELECT 
  SUM(calls) AS calls
FROM 
  pg_stat_history 
WHERE 
  queryid IS NOT NULL AND
  database_id =DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND 
  ( max_time >= hist_current_min AND  max_time < hist_current_max ) ;

|-----------------------------------------------------------------------------------------------
| HISTOGRAMME DU TEMPS MAXIMAL
| APPELS TOTAUX : 33851920
| TEMPS MIN  : 00:00:01.063
| TEMPS MAX  : 00:02:01.869
---------------------------------------------------------------------------------
|                      durée min|                      durée max|     appels
+----------------------------------+----------------------------------+----------
| 00:00:01.063(      1063.830 ms.) | 00:00:13.144(     13144.445 ms.) | 9
| 00:00:13.144(     13144.445 ms.) | 00:00:25.225(     25225.060 ms.) | 0
| 00:00:25.225(     25225.060 ms.) | 00:00:37.305(     37305.675 ms.) | 0
| 00:00:37.305(     37305.675 ms.) | 00:00:49.386(     49386.290 ms.) | 0
| 00:00:49.386(     49386.290 ms.) | 00:01:01.466(     61466.906 ms.) | 0
| 00:01:01.466(     61466.906 ms.) | 00:01:13.547(     73547.521 ms.) | 0
| 00:01:13.547(     73547.521 ms.) | 00:01:25.628(     85628.136 ms.) | 0
| 00:01:25.628(     85628.136 ms.) | 00:01:37.708(     97708.751 ms.) | 4
| 00:01:37.708(     97708.751 ms.) | 00:01:49.789(    109789.366 ms.) | 2
| 00:01:49.789(    109789.366 ms.) | 00:02:01.869(    121869.981 ms.) | 0

TOP10 Instantanés par Requête par Seconde

Requêtes

--pg_qps.sql
--Calculer le nombre de requêtes par seconde 
CREATE OR REPLACE FUNCTION pg_qps( pg_stat_history_id integer ) RETURNS double precision AS $$
DECLARE
 pg_stat_history_rec record ;
 prev_pg_stat_history_id integer ;
 prev_pg_stat_history_rec record;
 total_seconds double precision ;
 result double precision;
BEGIN 
  result = 0 ;
  
  SELECT *
  INTO pg_stat_history_rec
  FROM 
    pg_stat_history
  WHERE id = pg_stat_history_id ;

  IF pg_stat_history_rec.snapshot_timestamp IS NULL 
  THEN
    RAISE EXCEPTION 'ERREUR - pg_stat_history non trouvé pour id = %',pg_stat_history_id;
  END IF ;  
  
 --RAISE NOTICE 'pg_stat_history_id = % , snapshot_timestamp = %', pg_stat_history_id , 
 pg_stat_history_rec.snapshot_timestamp ;
  
  SELECT 
    MAX(id)   
  INTO
    prev_pg_stat_history_id
  FROM
    pg_stat_history
  WHERE 
    database_id = pg_stat_history_rec.database_id AND
	queryid IS NULL AND
	id  0 
  THEN
    result = pg_stat_history_rec.calls / total_seconds ;
  ELSE
   result = 0 ; 
  END IF;
   
 RETURN result ;
END
$$ LANGUAGE plpgsql;


SELECT 
  id , 
  snapshot_timestamp ,
  calls , 	
  total_time , 
  ( select pg_qps( id )) AS QPS ,
  blk_read_time ,
  blk_write_time
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT AND
  ( select pg_qps( id )) IS NOT NULL 
ORDER BY 5 DESC 
LIMIT 10

|-----------------------------------------------------------------------------------------------
| TOP10 Snapshots classés par le nombre de QueryPerSeconds
-----------------------------------------------------------------------------------------------------------------------------------------------
|    #|          snapshot| snapshotID|      appels|                      temps total db|        QPS|                          temps I/O| Pourcentage temps I/O
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|    2|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    3|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    4|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|    5|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|    6|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    7|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    8|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    9|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   10|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666

Historique d'exécution horaire avec QueryPerSeconds et Temps I/O

Requête

SELECT 
  id , 
  snapshot_timestamp ,
  appels , 	
  temps_total , 
  ( select pg_qps( id )) AS QPS ,
  temps_blk_lecture ,
  temps_blk_ecriture
FROM 
  pg_stat_history
WHERE 
  queryid IS NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
ORDER BY 2

|-----------------------------------------------------------------------------------------------
| HISTORIQUE D'EXÉCUTION HORAIRE AVEC QueryPerSeconds et Temps I/O
-----------------------------------------------------------------------------------------------------------------------------------------------
| HISTORIQUE DES REQUÊTES PAR SECONDE
|    #|          instantané| snapshotID|      appels|                      temps total db|        QPS|                          temps I/O| % temps I/O
+-----+------------------+-----------+-----------+----------------------------------+-----------+----------------------------------+-----------
|    1|  04.04.2019 11:00|       4131|       3747|  00:00:00.835(       835.374 ms.)|      1.041|  00:00:00.000(          .000 ms.)|       .000
|    2|  04.04.2019 12:00|       4133|    1002722|  00:01:52.419(    112419.376 ms.)|    278.534|  00:00:00.149(       149.105 ms.)|       .133
|    3|  04.04.2019 13:00|       4135|    2373043|  00:04:26.791(    266791.988 ms.)|    659.179|  00:00:00.064(        64.261 ms.)|       .024
|    4|  04.04.2019 14:00|       4137|    2397326|  00:04:43.033(    283033.854 ms.)|    665.924|  00:00:00.024(        24.505 ms.)|       .009
|    5|  04.04.2019 15:00|       4139|    2394416|  00:04:51.435(    291435.010 ms.)|    665.116|  00:00:12.025(     12025.895 ms.)|      4.126
|    6|  04.04.2019 16:00|       4143|    3525360|  00:10:13.492(    613492.351 ms.)|    979.267|  00:08:41.396(    521396.555 ms.)|     84.988
|    7|  04.04.2019 17:00|       4149|    3529197|  00:11:48.830(    708830.618 ms.)|    980.332|  00:12:47.834(    767834.052 ms.)|    108.324
|    8|  04.04.2019 18:01|       4157|    1145596|  00:01:19.217(     79217.372 ms.)|    313.004|  00:00:01.319(      1319.676 ms.)|      1.666
|    9|  04.04.2019 19:03|       4159|    2890362|  00:03:16.784(    196784.755 ms.)|    776.979|  00:00:01.441(      1441.386 ms.)|       .732
|   10|  04.04.2019 20:04|       4161|    5758631|  00:06:30.513(    390513.926 ms.)|   1573.396|  00:00:01.470(      1470.110 ms.)|       .376
|   11|  04.04.2019 21:03|       4163|    2781536|  00:03:06.470(    186470.979 ms.)|    785.745|  00:00:00.249(       249.865 ms.)|       .134
|   12|  04.04.2019 23:03|       4165|    1443155|  00:01:34.467(     94467.539 ms.)|    200.438|  00:00:00.015(        15.287 ms.)|       .016
|   13|  05.04.2019 01:03|       4167|    4387191|  00:06:51.380(    411380.293 ms.)|    609.332|  00:05:18.847(    318847.407 ms.)|     77.507
|   14|  05.04.2019 02:03|       4171|     189852|  00:00:10.989(     10989.899 ms.)|     52.737|  00:00:00.539(       539.110 ms.)|      4.906
|   15|  05.04.2019 03:01|       4173|       3627|  00:00:00.103(       103.000 ms.)|      1.042|  00:00:00.004(         4.131 ms.)|      4.010
|   16|  05.04.2019 04:00|       4175|       3627|  00:00:00.085(        85.235 ms.)|      1.025|  00:00:00.003(         3.811 ms.)|      4.471
|   17|  05.04.2019 05:00|       4177|       3747|  00:00:00.849(       849.454 ms.)|      1.041|  00:00:00.006(         6.124 ms.)|       .721
|   18|  05.04.2019 06:00|       4179|       3747|  00:00:00.849(       849.561 ms.)|      1.041|  00:00:00.000(          .051 ms.)|       .006
|   19|  05.04.2019 07:00|       4181|       3747|  00:00:00.839(       839.416 ms.)|      1.041|  00:00:00.000(          .062 ms.)|       .007
|   20|  05.04.2019 08:00|       4183|       3747|  00:00:00.846(       846.382 ms.)|      1.041|  00:00:00.000(          .007 ms.)|       .001
|   21|  05.04.2019 09:00|       4185|       3747|  00:00:00.855(       855.426 ms.)|      1.041|  00:00:00.000(          .065 ms.)|       .008
|   22|  05.04.2019 10:00|       4187|       3797|  00:01:40.150(    100150.165 ms.)|      1.055|  00:00:21.845(     21845.217 ms.)|     21.812

Texte de tous les SQL-selects

Requête

SELECT 
  queryid , 
  query 
FROM 
  pg_stat_history
WHERE 
  queryid IS NOT NULL AND 
  database_id = DATABASE_ID  AND
  snapshot_timestamp BETWEEN BEGIN_TIMEPOINT AND END_TIMEPOINT
GROUP BY queryid , query

Conclusion

Comme on peut le voir, avec des moyens assez simples, il est possible d'obtenir beaucoup d'informations utiles sur la charge et l'état de la base de données.

Remarque :Si nous enregistrons le queryid dans les requêtes, nous obtiendrons un historique pour chaque requête (dans un souci d'économie d'espace, les rapports pour chaque requête sont omis).

Ainsi, les données statistiques sur les performances des requêtes sont disponibles et collectées.
La première étape, « collecte de données statistiques », est terminée.

Nous pouvons passer à la deuxième étape : « configuration des métriques de performance ».

Mais c'est déjà une toute autre histoire.

À suivre...

Source : habr.com

Surveillance des performances des requêtes PostgreSQL. Partie 1 — rapport

Préface

Pourquoi tout cela m'intéresse-t-il ?

Énoncé du problème.

Il y a :

Exigences :

Introduction ou analyse des options de solution

Option 1 - «Travailler à la demande»

Option 2 - «On danse avec des tambourins, on vend et on chausse.»

Option 3 - Il n'est pas nécessaire de réinventer la roue, il faut l'acheter et rouler.

Que faire - Tchernychevski ? Ta question est tout à fait pertinente. (c)

Quels sont les avantages et les inconvénients de cette option :

L'idée générale — schématiquement

Tables de service

Étape 1 — nous collectons des informations statistiques sur la performance et obtenons des rapports

L'utilisation de cette table est très simple

En fait, le reporting

Données agrégées sur une période donnée

Temps DB

Temps I/O

TOP10 SQL par total_time

TOP10 SQL par temps I/O total

TOP10 SQL par le temps d'exécution maximum

TOP10 SQL par la lecture/écriture de tampon PARTAGÉ

Histogramme de la répartition des requêtes par temps d'exécution maximal

TOP10 Instantanés par Requête par Seconde

Historique d'exécution horaire avec QueryPerSeconds et Temps I/O

Texte de tous les SQL-selects

Conclusion