Geekly articles weekly

Essayer de créer un analogue ASH pour PostgreSQL

ÉnoncĂ© du problĂšme


Pour optimiser les requĂȘtes PostgreSQL, cela nĂ©cessite vraiment la capacitĂ© d'analyser l'historique de l'activitĂ©, en particulier - les attentes, les verrous, les statistiques de table.

Options disponibles


Outil d'analyse de charge 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 de derniers enregistrements stockés.

Le champ queryid est ajoutĂ© - le mĂȘme queryid de l'extension pg_stat_statements (une installation prĂ©liminaire est requise). "

Cela aiderait certainement beaucoup, mais la nuisance mĂȘme est le premier paragraphe, " Toutes les informations accumulĂ©es sont stockĂ©es uniquement dans la RAM ", c'est-Ă -dire nous avons un impact sur la base cible. De plus, il n'y a pas d'historique de verrouillage ni de statistiques de table. C'est-Ă -dire D'une maniĂšre gĂ©nĂ©rale, la solution est incomplĂšte: « Il n'y a pas encore de package prĂȘt Ă  installer. Il est proposĂ© de tĂ©lĂ©charger les sources et de construire la bibliothĂšque vous-mĂȘme. Tout d'abord, vous devez installer le package «devel» pour votre serveur et Ă©crire le chemin d'accĂšs Ă  pg_config dans la variable PATH. "

En gĂ©nĂ©ral - beaucoup de bruit, et dans le cas de bases de donnĂ©es de production sĂ©rieuses, il n'y aura peut-ĂȘtre aucun moyen de faire quelque chose avec le serveur. Encore une fois, vous devez trouver quelque chose qui vous appartient.

Avertissement
En raison de la nouveauté du sujet et de l'inachÚvement de la période de test, l'article est principalement à titre indicatif, plutÎt comme un ensemble de résumés et de résultats intermédiaires.
Un matériel plus détaillé sera préparé ultérieurement, en plusieurs parties.


DĂ©crire les exigences de la solution


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

Pg_stat_activity afficher l'historique
Historique des verrous de session Ă  l'aide de la vue pg_locks

La décision requise est de minimiser l'impact sur la base de données cible.

L'idée générale est que 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, certaines pertes de données sont possibles, mais ce n'est pas critique pour les rapports, mais il n'y a aucun impact sur la base de données cible à partir de la mémoire et de l'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. Tables de service


Pour le stockage des tables, un schéma distinct est utilisé 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


Utilisez le tableau 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 - aucun index ni restriction.

Pour stocker directement l'historique, 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 de vitesse d'insertion, certains index ont été créés pour accélérer la génération de rapports.

Historique de verrouillage de session


Pour stocker les instantanés de verrouillage de session en cours, utilisez le tableau:

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

Aussi, pour accélérer l'insertion - pas d'index ni de restrictions.

Pour stocker directement l'historique, une table partitionnée est utilisée:

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

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

2. Remplir l'historique actuel


Pour capturer directement des instantanés d'une vue, un script bash est utilisé qui exécute 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

La fonction plpgsql 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 la vue, utilisez le service systemd et deux scripts:

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

Nous commençons le service:
# systemctl daemon-reload
# systemctl start pg_current_activity.service

Ainsi, l'histoire des performances est collectée sous forme de prises de vue toutes les secondes. Bien sûr, si tout est laissé tel quel, les tables augmenteront trÚs rapidement de taille et un travail plus ou moins productif deviendra impossible.

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

3.Archive histoire


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

De nouvelles sections sont créées toutes les heures, tandis que les anciennes données des tables history * sont supprimées, de sorte que la taille des tables history * 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 plpgsql activity_hist.archive_current_activity. L'algorithme de travail est trÚs simple (en utilisant l'exemple de section pour la table archive_pg_stat_activity).

Créez et remplissez 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éer des 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 )' );

Supprimer les anciennes données de la table history_pg_stat_activity
 DELETE FROM activity_hist.history_pg_stat_activity WHERE timepoint < partition_max_range;

Bien sûr, périodiquement, les anciennes sections sont supprimées car inutiles.

Rapports de base


En fait, pourquoi tout cela se fait-il. Pour recevoir des rapports Ă  trĂšs distance, rappelant Ă  peu prĂšs Oracle AWR.
Il est important d'ajouter que pour recevoir des rapports, il est nécessaire de créer une relation entre les vues pg_stat_activity et pg_stat_statements. Les tables sont liées en ajoutant la colonne 'queryid' aux tables 'history_pg_stat_activity', 'archive_pg_stat_activity'. La façon d'ajouter 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 TOTAL DU CPU POUR LES DEMANDES


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 intervalle ' 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 * intervalle' 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 backend' ET datname! = 'Postgres' ET (aa.wait_event_type IS NULL) = ANDaa.state 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

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


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

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

EN ATTENTE DE DEMANDES


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

Un 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

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

Un exemple:
  -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -----------------------
 HISTORIQUE DES PROCESSUS DE BLOCAGE
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  # |  pid |  nom d'utilisateur |  nom_application |  datname |  commencé |  durée |  état |  interroger
 + ---- + ---------- + ---------- + -------------------- + - --------- + -------------------- + ------------------- - + ------------------------------ + ----------------- -----------------------
 |  1 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 54 |  00: 00: 04 |  inactif |
 |  2 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 31: 58 |  00: 00: 06 |  inactif dans la transaction |  commencer;
 |  3 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 32: 16 |  00: 01: 45 |  inactif dans la transaction |  table de verrouillage wafer_data;
 |  4 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 35: 54 |  00: 01: 23 |  inactif |  commettre;
 |  5 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 46 |  00: 00: 02 |  inactif dans la transaction |  commencer;
 |  6 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 38: 54 |  00: 00: 08 |  inactif dans la transaction |  table de verrouillage wafer_data;
 |  7 |  26211 |  tuser |  psql |  tdb1 |  2019-09-02 19: 39: 08 |  00: 42: 42 |  inactif |  commettre;
 |  8 |  26211 |  tuser |  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 reçus simplifient dĂ©jĂ  considĂ©rablement la vie lors de l'analyse des incidents de performances.
Sur la base de requĂȘtes de base, vous pouvez obtenir Ă  distance un rapport qui rappelle approximativement Oracle AWR.
Exemple de rapport de synthĂšse
  + ------------------------------------------------- -----------------------------------
 |  RAPPORT CONSOLIDÉ D'ACTIVITÉ ET D'ATTENTE.  DATETIME: 09/03/2019 14:08
 | ------------------------------------------------- -----------------------------------
 |  HÔTE: XXXX
 |  BEGIN_SNAPSHOT: 09/02/2019 14:08 END_SNAPSHOT: 09/03/2019 14:00
 | ------------------------------------------------- -----------------------------------
 |  TAILLE DES BASES DE DONNÉES ACTUELLES:
 |  BASE DE DONNÉES: moniteur
 |  TAILLE (MB): 1370.00
 | ------------------------------------------------- -----------------------------------
 |  TEMPS DU CPU CLUSTER: 19:44:22
 |  TEMPS D'ATTENTE DU CLUSTER: 78:49:16
 |
 |  SQL DBTIME: 65:53:09
 |  TEMPS DU CPU SQL: 19:05:21
 |  TEMPS D'ATTENTE SQL: 21:50:46
 |  SQL IOTIME: 20:53:00
 |  TEMPS DE LECTURE SQL: 20:52:55
 |  TEMPS D'ÉCRITURE SQL: 00:00:05
 |
 |  APPELS SQL: 311293
 -------------------------------------------------- -----------
 |  LECTURE DE BLOCS PARTAGÉS SQL: 13351563334
 |  HITS DE BLOCS PARTAGÉS SQL: 2775427045
 |  BLOCS PARTAGÉS SQL HITS / READS%: 20,79
 |  BLOCS PARTAGÉS SQL DIRTÉS: 21105
 |  BLOCS PARTAGÉS SQL ÉCRITS: 3656
 |
 |  LECTURES TEMPORAIRES DE BLOCS SQL: 7464932
 |  BLOCS TEMPORAIRES SQL ÉCRITS: 10176024
 -------------------------------------------------- -----------
 |
 |  ATTENTE STATIQUE
 |
 + ------------------------------------------------- -----------------------------------
 |  TOP 10 DES ATTENTES PAR TEMPS D'ATTENTE TOTAL POUR LES PROCESSUS SYSTÈME
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  wait_event |  la durée
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Activité |  LogicalLauncherMain |  11:21:01
 |  2 |  Activité |  CheckpointerMain |  11:20:35
 |  3 |  Activité |  AutoVacuumMain |  11:20:31
 |  4 |  Activité |  WalWriterMain |  11:19:35
 |  5 |  Activité |  BgWriterMain |  10:14:19
 |  6 |  Activité |  BgWriterHibernate |  01:06:04
 |  7 |  Activité |  WalSenderMain |  00:04:05
 |  8 |  Client |  ClientWrite |  00:04:00
 |  9 |  IO |  BufFileWrite |  00:02:45
 |  10 |  LWLock |  buffer_mapping |  00:02:14
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  TOP 10 DES ATTENTES PAR TEMPS D'ATTENTE TOTAL POUR LES PROCESSUS DES CLIENTS
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  # |  wait_event_type |  wait_event |  durée |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  1 |  Lock |  transactionid |  11: 55: 37 |  18.1
 |  2 |  IO |  DataFileRead |  07: 19: 43 |  12/12
 |  3 |  Client |  ClientRead |  00: 46: 54 |  1.19
 |  4 |  Lock |  relation |  00: 40: 37 |  1.03
 |  5 |  LWLock |  buffer_mapping |  00: 31: 08 |  0,79
 |  6 |  LWLock |  buffer_io |  00: 22: 12 |  0,56
 |  7 |  DĂ©lai d'expiration |  PgSleep |  00: 10: 58 |  0,28
 |  8 |  Lock |  tuple |  00: 01: 30 |  0,04
 |  9 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 |  10 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 + ----- + ------------------------------ + ------------ -------- + -------------------- + ----------
 |  TYPES D'ATTENTE PAR TEMPS D'ATTENTE TOTAL, POUR LES PROCESSUS SYSTÈME
 + ----- + ------------------------------ + ------------ --------
 |  # |  wait_event_type |  la durée
 + ----- + ------------------------------ + ------------ --------
 |  1 |  Activité |  56:46:10
 |  2 |  IO |  00:05:13
 |  3 |  Client |  00:04:00
 |  4 |  LWLock |  00:03:07
 + ----- + ------------------------------ + ------------ --------
 |  TYPES D'ATTENTE PAR TEMPS D'ATTENTE TOTAL, POUR LES PROCESSUS DES CLIENTS
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  # |  wait_event_type |  durée |  % dbtime
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  1 |  Lock |  12: 37: 44 |  19.17
 |  2 |  IO |  07: 21: 40 |  11.17
 |  3 |  LWLock |  00: 53: 26 |  1,35
 |  4 |  Client |  00: 46: 54 |  1.19
 |  5 |  DĂ©lai d'expiration |  00: 10: 58 |  0,28
 |  6 |  IPC |  00: 00: 04 |  0
 + ----- + ------------------------------ + ------------ -------- + --------------------
 |  ATTENTE DES PROCESSUS SYSTÈME
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  # |  backend_type |  dbname |  wait_event_type |  wait_event |  la durée
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  1 |  lanceur de réplication logique |  |  Activité |  LogicalLauncherMain |  11:21:01
 |  2 |  pointeur de contrÎle |  |  Activité |  CheckpointerMain |  11:20:35
 |  3 |  lanceur autovacuum |  |  Activité |  AutoVacuumMain |  11:20:31
 |  4 |  walwriter |  |  Activité |  WalWriterMain |  11:19:35
 |  5 |  écrivain de fond |  |  Activité |  BgWriterMain |  10:14:19
 |  6 |  écrivain de fond |  |  Activité |  BgWriterHibernate |  01:06:04
 |  7 |  walsender |  |  Activité |  WalSenderMain |  00:04:05
 |  8 |  walsender |  |  Client |  ClientWrite |  00:04:00
 |  9 |  travailleur parallĂšle |  tdb1 |  IO |  BufFileWrite |  00:02:45
 |  10 |  travailleur parallĂšle |  tdb1 |  LWLock |  buffer_mapping |  00:02:05
 |  11 |  travailleur parallĂšle |  tdb1 |  IO |  DataFileRead |  00:01:10
 |  12 |  travailleur parallĂšle |  tdb1 |  IO |  BufFileRead |  00:01:05
 |  13 |  travailleur parallĂšle |  tdb1 |  LWLock |  buffer_io |  00:00:45
 |  14 |  travailleur autovacuum |  tdb1 |  LWLock |  buffer_mapping |  00:00:09
 |  15 |  walwriter |  |  IO |  WALWrite |  00:00:08
 |  16 |  walwriter |  |  LWLock |  WALWriteLock |  00:00:04
 |  17 |  Ă©crivain de fond |  |  LWLock |  WALWriteLock |  00:00:03
 |  18 |  Ă©crivain de fond |  |  IO |  WALWrite |  00:00:02
 |  19 |  Ă©crivain de fond |  |  IO |  DataFileWrite |  00:00:02
 |  20 |  pointeur de contrĂŽle |  |  IO |  ControlFileSyncUpdate |  00:00:01
 |  21 |  travailleur autovacuum |  tdb1 |  LWLock |  buffer_io |  00:00:01
 + ----- + ----------------------------- + ---------- + - ------------------ + ---------------------- + -------- ------------
 |  ATTENTE POUR SQL
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  # |  queryid |  dbname |  wait_event_type |  wait_event |  durée |  % dbtime
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |  1 |  389015618226997618 |  tdb1 |  Lock |  transactionid |  09: 47: 43 |  14,87
 |  2 |  389015618226997618 |  tdb1 |  IO |  DataFileRead |  05: 47: 07 |  8.78
 |  3 |  |  tdb1 |  Lock |  transactionid |  02: 07: 54 |  3.24
 |  4 |  |  tdb1 |  IO |  DataFileRead |  01: 30: 24 |  2.29
 |  5 |  8085340880788646241 |  tdb1 |  Client |  ClientRead |  00: 40: 20 |  1.02
 |  6 |  389015618226997618 |  tdb1 |  LWLock |  buffer_mapping |  00: 20: 41 |  0,52
 |  7 |  389015618226997618 |  tdb1 |  LWLock |  buffer_io |  00: 17: 30 |  0,44
 |  8 |  2649515222348904837 |  tdb1 |  DĂ©lai d'expiration |  PgSleep |  00: 10: 58 |  0,28
 |  9 |  4710212362688288619 |  tdb1 |  Lock |  relation |  00: 10: 44 |  0,27
 |  10 |  9150846928388977274 |  tdb1 |  Lock |  relation |  00: 10: 24 |  0,26
 |  11 |  28942442626229688 |  tdb1 |  Lock |  relation |  00: 07: 48 |  0,2
 |  12 |  1237430309438971376 |  tdb1 |  Lock |  relation |  00: 07: 32 |  0,19
 |  13 |  |  tdb1 |  LWLock |  buffer_mapping |  00: 04: 32 |  0,11
 |  14 |  |  tdb1 |  LWLock |  buffer_io |  00: 04: 13 |  0,11
 |  15 |  |  tdb1 |  Client |  ClientRead |  00: 03: 57 |  0,1
 |  16 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_mapping |  00: 02: 26 |  0,06
 |  17 |  3167065002719415275 |  tdb1 |  Lock |  relation |  00: 02: 20 |  0,06
 |  18 |  5731212217001535134 |  tdb1 |  Client |  ClientRead |  00: 01: 53 |  0,05
 |  19 |  1237430309438971376 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 42 |  0,04
 |  20 |  389015618226997618 |  tdb1 |  Lock |  tuple |  00: 01: 30 |  0,04
 |  21 |  8304755792398128062 |  tdb1 |  Lock |  relation |  00: 01: 29 |  0,04
 |  22 |  28942442626229688 |  tdb1 |  IO |  BufFileWrite |  00: 01: 16 |  0,03
 |  23 |  9150846928388977274 |  tdb1 |  IO |  DataFileRead |  00: 01: 07 |  0,03
 |  24 |  28942442626229688 |  tdb1 |  LWLock |  buffer_mapping |  00: 01: 03 |  0,03
 |  25 |  9150846928388977274 |  tdb1 |  LWLock |  buffer_mapping |  00: 00: 44 |  0,02
 |  26 |  28942442626229688 |  tdb1 |  IO |  BufFileRead |  00: 00: 37 |  0,02
 |  27 |  28942442626229688 |  tdb1 |  LWLock |  buffer_io |  00: 00: 25 |  0,01
 |  28 |  1237430309438971376 |  tdb1 |  IO |  DataFileRead |  00: 00: 24 |  0,01
 |  29 |  28942442626229688 |  tdb1 |  IO |  DataFileRead |  00: 00: 22 |  0,01
 |  30 |  5731212217001535134 |  tdb1 |  Lock |  relation |  00: 00: 20 |  0,01
 |  31 |  4710212362688288619 |  tdb1 |  IO |  DataFileRead |  00: 00: 19 |  0,01
 |  32 |  9150846928388977274 |  tdb1 |  Client |  ClientRead |  00: 00: 09 |  0
 |  33 |  3422818749220588372 |  tdb1 |  Client |  ClientRead |  00: 00: 08 |  0
 |  34 |  1237430309438971376 |  tdb1 |  Client |  ClientRead |  00: 00: 06 |  0
 |  35 |  389015618226997618 |  tdb1 |  LWLock |  buffer_content |  00: 00: 05 |  0
 |  36 |  4710212362688288619 |  tdb1 |  Client |  ClientRead |  00: 00: 05 |  0
 |  37 |  4710212362688288619 |  tdb1 |  LWLock |  buffer_io |  00: 00: 04 |  0
 |  38 |  28942442626229688 |  tdb1 |  Client |  ClientRead |  00: 00: 04 |  0
 |  39 |  28942442626229688 |  tdb1 |  IPC |  ParallelFinish |  00: 00: 03 |  0
 |  40 |  389015618226997618 |  tdb1 |  IO |  DataFileWrite |  00: 00: 02 |  0
 |  41 |  -5730801771815999400 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  42 |  2404820632950544954 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  43 |  -6572922443698419129 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  44 |  8304755792398128062 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  45 |  -1473395109729441239 |  tdb1 |  Client |  ClientRead |  00: 00: 02 |  0
 |  46 |  |  tdb1 |  LWLock |  buffer_content |  00: 00: 01 |  0
 |  47 |  180077086776069052 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |  0
 |  48 |  |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0
 |  49 |  28942442626229688 |  tdb1 |  IPC |  MessageQueueReceive |  00: 00: 01 |  0
 |  50 |  2369289265278398647 |  tdb1 |  Client |  ClientRead |  00: 00: 01 |  0
 |  51 |  9150846928388977274 |  tdb1 |  IO |  DataFileWrite |  00: 00: 01 |  0
 + ----- + ------------------------- + ---------- + ------ -------------- + -------------------- + -------------- ------ + ----------
 |
 |  STATIQUE SQL CLIENT
 |
 + ------------------------------------------------- -----------------------------------
 |  CLIENT SQL ordonné par Elapsed Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  temps écoulé |  appels |  % dbtime |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  06: 43: 19 |  36 |  10.2 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  02: 06: 53 |  715 |  3.21 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  01: 52: 07 |  720 |  2,84 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 39: 03 |  357 |  0,99 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 22: 00 |  8 |  0,56 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + --------------------
 |  CLIENT SQL ordonné par CPU Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps processeur |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  16: 14: 38 |  36 |  10.2 |  06: 43: 19 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  00: 13: 38 |  720 |  2,84 |  01: 52: 07 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 11: 39 |  357 |  0,99 |  00: 39: 03 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 10: 58 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 |  00: 09: 44 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordonné par temps d'attente des E / S utilisateur
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps io_wait |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  05: 47: 09 |  36 |  10.2 |  06: 43: 19 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  00: 02: 15 |  357 |  0,99 |  00: 39: 03 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 00: 24 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  00: 00: 19 |  720 |  2,84 |  01: 52: 07 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordonné par des lectures de tampons partagés
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  tampons lit |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  2562353244 |  36 |  10.2 |  06: 43: 19 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  11041689 |  357 |  0,99 |  00: 39: 03 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  3303551 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  3242892 |  720 |  2,84 |  01: 52: 07 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  0 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordonné par Disk Reads Time
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  temps de lecture |  appels |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  03: 37: 46 |  36 |  10.2 |  06: 43: 19 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  00: 04: 07 |  357 |  0,99 |  00: 39: 03 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  00: 00: 59 |  720 |  2,84 |  01: 52: 07 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  00: 00: 42 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  00: 00: 00 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  CLIENT SQL ordonné par Executions
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  appels |  lignes |  % dbtime | total_time |  % CPU |  % IO |  dbname |  queryid
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  720 |  720 |  2,84 |  01: 52: 07 |  1.19 |  0,08 |  tdb1 |  4710212362688288619
 |  715 |  715 |  3.21 |  02: 06: 53 |  0,85 |  0,06 |  tdb1 |  1237430309438971376
 |  357 |  0 |  0,99 |  00: 39: 03 |  1.02 |  0,33 |  tdb1 |  28942442626229688
 |  36 |  36 |  10.2 |  06: 43: 19 |  85 septembre |  17,38 |  tdb1 |  389015618226997618
 |  8 |  8 |  0,56 |  00: 22: 00 |  0,96 |  0 |  tdb1 |  2649515222348904837
 + -------------------- + ---------- + ---------- + ------ ---- + ---------- + ---------- + ---------- + ------------ --------
 |  Liste complĂšte du texte SQL
 ----------------------------------------------
 ...

À suivre. L'Ă©tape suivante est la formation de l'historique des verrous (pg_stat_locks), une description plus dĂ©taillĂ©e du processus de remplissage des tables.

Source: https://habr.com/ru/post/fr467181/

More articles:


All Articles