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