# Extensions PostgreSQL pour les DBA > Extensions d’observation et de diagnostic pour les DBA : pgstattuple, pageinspect, pgrowlocks, cache et pgAudit. --- Index LLMS : [llms.txt](/fr/llms.txt) --- ## 3/ Extensions PostgreSQL pour les DBA ![Illustration du manuel](/images/dba42/manual/extension-elephant.jpg) ## 3.1 Préambule - Nombreuses extensions pour observer le comportement de PostgreSQL - Contribs ou projets externes De nombreux permettent de manipuler une facette de PostgreSQL à laquelle on n’a normalement pas accès. Leur utilisation est parfois très spécialisée et pointue. ## 3.2 pgstattuple pgstattuple fournit une mesure (par parcours complet de l’objet) sur: - Pour une table - remplissage des blocs - enregistrements morts - espace libre - Pour un index - profondeur de l’index - remplissage des feuilles - fragmentation (feuilles non consécutives) Cette extension est fournie avec le serveur PostgreSQL (« contrib »). Par exemple : ```sql CREATE EXTENSION SELECT * FROM pgstattuple('dspam_token_data'); ``` ```text -[ RECORD 1]----------------------- table_len | 601743360 tuple_count | 8587417 tuple_len | 412196016 tuple_percent | 68.5 dead_tuple_count | 401098 dead_tuple_len | 19252704 dead_tuple_percent | 3.2 free_space | 93370000 free_percent | 15.52 ``` ```sql SELECT * FROM pgstatindex('dspam_token_data_uid_key'); ``` ```text -[ RECORD 1]----------------------- version | 2 tree_level | 2 index_size | 429047808 root_block_no | 243 internal_pages | 244 leaf_pages | 52129 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 51.78 leaf_fragmentation | 43.87 ``` Comme chaque interrogation nécessite une lecture complète de l’objet, ces fonctions ne sont pas à appeler en supervision. Elles servent de façon ponctuelle pour s’assurer qu’un objet nécessite une réorganisation. Ici, l’index `dspam_token_data_uid_key` pourrait certainement être reconstruit… il deviendrait 40 % plus petit environ (remplissage à 51 % au lieu de 90 %). `leaf_fragmentation` indique le pourcentage de pages feuilles qui ne sont pas physiquement contiguës sur le disque. Cela peut être important dans le cas d’un index utilisé pour des `Range Scans` (requête avec des inégalités), mais n’a aucune importance ici puisqu’il s’agit d’une clé primaire technique, donc d’un index qui n’est interrogé que pour récupérer des enregistrements de façon unitaire. ## 3.3 pg_freespacemap La *freespacemap* : - est renseignée par VACUUM, par objet (table/index) - est consommée par les sessions modifiant des données (INSERT/UPDATE) - est interrogée la freespacemap pour connaître l’espace libre - est rarement utilisée (doute sur l’efficacité de VACUUM) Cette extension est fournie avec le serveur PostgreSQL (« contrib »). Voici deux exemples d’utilisation de `pg_freespacemap` : ```sql SELECT * FROM pg_freespace('dspam_token_data') LIMIT 20; ``` ```text blkno | avail -------+------- 0 | 32 1 | 0 2 | 0 3 | 32 4 | 0 5 | 0 6 | 0 7 | 0 8 | 32 9 | 32 10 | 32 11 | 0 12 | 0 13 | 0 14 | 0 15 | 0 16 | 0 17 | 0 18 | 32 19 | 32 ``` ```sql SELECT * FROM pg_freespace('dspam_token_data') ORDER BY avail DESC LIMIT 20; ``` ```text blkno | avail -------+------- 67508 | 7520 67513 | 7520 67460 | 7520 67507 | 7520 67451 | 7520 67512 | 7520 67452 | 7520 67454 | 7520 67505 | 7520 67447 | 7520 67324 | 7520 67443 | 7520 67303 | 7520 67509 | 7520 67444 | 7520 67448 | 7520 67445 | 7520 66888 | 7520 67516 | 7520 67514 | 7520 ``` L’interprétation de « avail » est un peu complexe, et différente suivant qu’on inspecte une table ou un index. Il est préférable de se référer à la documentation. ## 3.4 pg_visibility La *Visibility Map* : - Est renseignée par `VACUUM`, par table - Permet de savoir que l’ensemble des enregistrements de ce bloc est visible - Indispensable pour les parcours d’index seul - Interroger la *visibility map* permet de voir si un bloc est : - visible - gelé - Rarement utilisé Cette extension est fournie avec le serveur PostgreSQL (« contrib »). On crée une table de test avec 451 lignes : ```sql CREATE TABLE test_visibility AS SELECT generate_series(0,450) x; SELECT 451 ``` On regarde dans quel état est la *visibility map* : ```sql SELECT oid FROM pg_class WHERE relname='test_visibility' ; ``` ```text oid ------- 18370 ``` ```sql SELECT * FROM pg_visibility(18370); ``` ```text blkno | all_visible | all_frozen | pd_all_visible -------+-------------+------------+---------------- 0 | f | f | f 1 | f | f | f ``` Les deux blocs que composent la table `test_visibility` sont à `false`, ce qui est normal puisque l’opération de vacuum n’a jamais été exécutée sur cette table. On lance donc une opération de vacuum : ```sql VACUUM VERBOSE test_visibility ; ``` ```text INFO: exécution du VACUUM sur « public.test_visibility » INFO: « test_visibility » : 0 versions de ligne supprimables, 451 non supprimables parmi 2 pages sur 2 DÉTAIL : 0 versions de lignes mortes ne peuvent pas encore être supprimées. Il y avait 0 pointeurs d éléments inutilisés. Ignore 0 page à cause des verrous de blocs. 0 page est entièrement vide. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM ``` Vacuum voit bien nos 451 lignes, et met donc la *visibility map* à jour. Lorsqu’on la consulte, on voit bien que toutes les lignes sont visibles de toutes les transactions : ```sql SELECT * FROM pg_visibility(33259); ``` ```text blkno | all_visible | all_frozen | pd_all_visible -------+-------------+------------+---------------- 0 | t | f | t 1 | t | f | t ``` La colonne `all_frozen` passera à `t` après un `VACUUM FREEZE`. ## 3.5 pageinspect - Vision du contenu d’un bloc - Sans le dictionnaire, donc sans décodage des données - Affichage brut - Utilisé surtout en debug, ou dans les cas de corruption - Fonctions de décodage pour les tables, les index (B-tree, hash, GIN, GiST), FSM - Nécessite de connaître le code de PostgreSQL Cette extension est fournie avec le serveur PostgreSQL (« contrib »). Voici quelques exemples : - Contenu d’une page d’une table : ```sql SELECT * FROM heap_page_items(get_raw_page('dspam_token_data',0)) LIMIT 5; ``` ```text lp | lp_off | lp_flags | lp_len | t_xmin | t_xmax | t_field3 | t_ctid ----+--------+----------+--------+------------+--------+----------+-------- 1 | 201 | 2 | 0 | | | | 2 | 1424 | 1 | 48 | 1439252980 | 0 | 0 | (0,2) 3 | 116 | 2 | 0 | | | | 4 | 7376 | 1 | 48 | 2 | 0 | 140 | (0,4) 5 | 3536 | 1 | 48 | 1392499801 | 0 | 0 | (0,5) lp | t_infomask2 | t_infomask | t_hoff | t_bits | t_oid ----+-------------+------------+--------+--------+------- 1 | | | | | 2 | 5 | 2304 | 24 | | 3 | | | | | 4 | 5 | 10496 | 24 | | 5 | 5 | 2304 | 24 | | ``` - Et son entête : ```sql SELECT * FROM page_header(get_raw_page('dspam_token_data',0)); ``` ```text -[ RECORD 1 ]-------------------------------------------- lsn | F1A/5A6EAC40 checksum | 0 flags | 1 lower | 852 upper | 896 special | 8192 pagesize | 8192 version | 4 prune_xid | 1450780148 ``` - Méta-données d’un index (contenu dans la première page) : ```sql SELECT * FROM bt_metap('dspam_token_data_uid_key'); ``` ```text magic | version | root | level | fastroot | fastlevel --------+---------+------+-------+----------+----------- 340322 | 2 | 243 | 2 | 243 | 2 ``` La page racine est la 243. Allons la voir : ```sql SELECT * FROM bt_page_items('dspam_token_data_uid_key',243) LIMIT 10; ``` ```text offset | ctid | len | nulls | vars | data --------+-----------+-----+-------+------+------------------------------------- 1 | (3,1) | 8 | f | f | 2 | (44565,1) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00 3 | (242,1) | 20 | f | f | 77 c6 0d 6f a6 92 db 81 28 00 00 00 4 | (43569,1) | 20 | f | f | 47 a6 aa be 29 e3 13 83 18 00 00 00 5 | (481,1) | 20 | f | f | 30 17 dd 8e d9 72 7d 84 0a 00 00 00 6 | (43077,1) | 20 | f | f | 5c 3c 7b c5 5b 7a 4e 85 0a 00 00 00 7 | (719,1) | 20 | f | f | 0d 91 d5 78 a9 72 88 86 26 00 00 00 8 | (41209,1) | 20 | f | f | a7 8a da 17 95 17 cd 87 0a 00 00 00 9 | (957,1) | 20 | f | f | 78 e9 64 e9 64 a9 52 89 26 00 00 00 10 | (40849,1) | 20 | f | f | 53 11 e9 64 e9 1b c3 8a 26 00 00 00 ``` La première entrée de la page 243, correspondant à la donnée `f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00` est stockée dans la page 3 de notre index : ```sql SELECT * FROM bt_page_stats('dspam_token_data_uid_key',3); ``` ```text -[ RECORD 1]------------------ blkno | 3 type | i live_items | 202 dead_items | 0 avg_item_size | 19 page_size | 8192 free_size | 3312 btpo_prev | 0 btpo_next | 44565 btpo | 1 btpo_flags | 0 ``` ```sql SELECT * FROM bt_page_items('dspam_token_data_uid_key',3) LIMIT 10; ``` ```text offset | ctid | len | nulls | vars | data --------+-----------+-----+-------+------+------------------------------------- 1 | (38065,1) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00 2 | (1,1) | 8 | f | f | 3 | (37361,1) | 20 | f | f | 30 fd 30 b8 70 c9 01 80 26 00 00 00 4 | (2,1) | 20 | f | f | 18 2c 37 36 27 03 03 80 27 00 00 00 5 | (4,1) | 20 | f | f | 36 61 f3 b6 c5 1b 03 80 0f 00 00 00 6 | (43997,1) | 20 | f | f | 30 4a 32 58 c8 44 03 80 27 00 00 00 7 | (5,1) | 20 | f | f | 88 fe 97 6f 7e 5a 03 80 27 00 00 00 8 | (51136,1) | 20 | f | f | 74 a8 5a 9b 15 5d 03 80 28 00 00 00 9 | (6,1) | 20 | f | f | 44 41 3c ee c8 fe 03 80 0a 00 00 00 10 | (45317,1) | 20 | f | f | d4 b0 7c fd 5d 8d 05 80 26 00 00 00 ``` Le type de la page est `i`, c’est-à-dire « internal », donc une page interne de l’arbre. Continuons notre descente, allons voir la page 38065 : ```sql SELECT * FROM bt_page_stats('dspam_token_data_uid_key',38065); ``` ```text -[ RECORD 1]----------------------- blkno | 38065 type | l live_items | 169 dead_items | 21 avg_item_size | 20 page_size | 8192 free_size | 3588 btpo_prev | 118 btpo_next | 119 btpo | 0 btpo_flags | 65 ``` ```sql SELECT * FROM bt_page_items('dspam_token_data_uid_key',38065) LIMIT 10; ``` ```text offset | ctid | len | nulls | vars | data --------+-------------+-----+-------+------+------------------------------------ 1 | (11128,118) | 20 | f | f | 33 37 89 95 b9 23 cc 80 0a 00 00 00 2 | (45713,181) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 0f 00 00 00 3 | (45424,97) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 26 00 00 00 4 | (45255,28) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 27 00 00 00 5 | (15672,172) | 20 | f | f | f3 4b 2e 8c 39 a3 cb 80 28 00 00 00 6 | (5456,118) | 20 | f | f | f3 bf 29 a2 39 a3 cb 80 0f 00 00 00 7 | (8356,206) | 20 | f | f | f3 bf 29 a2 39 a3 cb 80 28 00 00 00 8 | (33895,272) | 20 | f | f | f3 4b 8e 37 99 a3 cb 80 0a 00 00 00 9 | (5176,108) | 20 | f | f | f3 4b 8e 37 99 a3 cb 80 0f 00 00 00 10 | (5466,41) | 20 | f | f | f3 4b 8e 37 99 a3 cb 80 26 00 00 00 ``` Nous avons trouvé une feuille (type `l`). Les ctid pointés sont maintenant les adresses dans la table : ```sql SELECT * FROM dspam_token_data WHERE ctid = '(11128,118)'; ``` ```text uid | token | spam_hits | innocent_hits | last_hit -----+----------------------+-----------+---------------+------------ 40 | -6317261189288392210 | 0 | 3 | 2014-11-10 ``` ## 3.6 pgrowlocks Les verrous mémoire de PostgreSQL ne verrouillent pas les enregistrements : - Il est parfois compliqué de comprendre qui verrouille qui, à cause de quel enregistrement - pgrowlocks inspecte une table pour détecter les enregistrements verrouillés, leur niveau de verrouillage, et qui les verrouille - scan complet de la table ! Cette extension est fournie avec le serveur PostgreSQL (« contrib »). Par exemple : ```sql SELECT * FROM pgrowlocks('dspam_token_data'); ``` ```text locked_row | locker | multi | xids | modes | pids ------------+------------+-------+--------------+-------------------+------- (0,2) | 1452109863 | f | {1452109863} | {"No Key Update"} | {928} ``` Nous savons donc que l’enregistrement (0,2) est verrouillé par le pid 928. Nous avons le mode de verrouillage, le (ou les) numéro de transaction associés. Un enregistrement peut être verrouillé par plus d’une transaction dans le cas d’un `SELECT FOR SHARE`. Dans ce cas, PostgreSQL crée un « multixact » qui est stocké dans `locker`, `multi` vaut `true`, et `xids` contient plus d’un enregistrement. C’est un cas très rare d’utilisation. ## 3.7 Gestion du cache - pg_buffercache : voir ce qu’il y a dans mes *shared buffers* - pg_prewarm : forcer le chargement du cache Ces extensions sont fournies avec le serveur PostgreSQL (« contrib »). pg_buffercache et pg_prewarm sont des extensions déjà connues de beaucoup de DBA. Rappelons que pg_buffercache permet de lister chaque bloc dans le cache de PostgreSQL, et de savoir notamment s’il est *dirty*. pg_prewarm est lui très utile pour forcer le chargement d’un objet dans le cache de PostgreSQL ou de l’OS, y compris automatiquement au démarrage. ## 3.8 pgAudit - Extension disponible via le PGDG - Pour tracer les accès aux objets - Pour les audits & certifications - Format CSV dans `postgresql.log` pgAudit trace les requêtes, les accès aux tables et divers événements et peut également servir dans le cadre d’audits ou de certifications. Cette extension, écrite en C, utilise des *event triggers* et du code supplémentaire pour les DDL. Les événements sont dirigés vers les traces de PostgreSQL (postgresql.log ou équivalent), sous un format CSV. Cette extension est à installer depuis les paquets du PGDG ou à compiler depuis le [dépôt Github](https://github.com/pgaudit/pgaudit/). La sortie se fait au format CSV dans `postgresql.log`. Exemple : ```text LOG: AUDIT: SESSION,792,1,MISC,BEGIN,,,BEGIN, LOG: AUDIT: SESSION,793,1,WRITE,UPDATE,,,UPDATE pgbench_accounts SET abalance = abalance + 3043 WHERE aid = 394881, LOG: AUDIT: SESSION,794,1,READ,SELECT,,,SELECT abalance FROM pgbench_accounts WHERE aid = 394881, LOG: AUDIT: SESSION,795,1,WRITE,UPDATE,,,UPDATE pgbench_tellers SET tbalance = tbalance + 3043 WHERE tid = 49, LOG: AUDIT: SESSION,796,1,WRITE,UPDATE,,,UPDATE pgbench_branches SET bbalance = bbalance + 3043 WHERE bid = 3, LOG: AUDIT: SESSION,797,1,WRITE,INSERT,,,"INSERT INTO pgbench_history (tid, bid, aid, delta, mtime) VALUES (49, 3, 394881, 3043, CURRENT_TIMESTAMP)", LOG: AUDIT: SESSION,798,1,MISC,COMMIT,,,END, … LOG: AUDIT: SESSION,4,1,DDL,CREATE TABLE,TABLE,public.pgbench_accounts,"create table pgbench_accounts(aid int not null,bid int,abalance int,filler char(84)) with (fillfactor=100)", … LOG: AUDIT: SESSION,18,1,DDL,ALTER TABLE,TABLE,public.pgbench_accounts,alter table pgbench_accounts add primary key (aid), … LOG: AUDIT: SESSION,20,1,DDL,ALTER TABLE,,,alter table pgbench_accounts add constraint pgbench_accounts_bid_fkey foreign key (bid) references pgbench_branches, LOG: AUDIT: SESSION,20,2,READ,SELECT,,,"SELECT fk.""bid"" FROM ONLY ""public"".""pgbench_accounts"" fk LEFT OUTER JOIN ONLY ""public"".""pgbench_branches"" pk ON ( pk.""bid"" OPERATOR(pg_catalog.=) fk.""bid"") WHERE pk.""bid"" IS NULL AND (fk.""bid"" IS NOT NULL)", LOG: AUDIT: SESSION,20,1,DDL,ALTER TABLE,TABLE,public.pgbench_accounts,alter table pgbench_accounts add constraint pgbench_accounts_bid_fkey foreign key (bid) references pgbench_branches, … LOG: AUDIT: SESSION,1,1,DDL,DROP TABLE,TABLE,public.pgbench_tellers,"drop table if exists pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers", … LOG: AUDIT: SESSION,12,1,MISC,VACUUM,,,vacuum analyze pgbench_branches, ``` Attention, le volumé généré peut devenir colossal ! Pour les détails et la configuration, voir la base de connaissance Dalibo sur .