Administration de PostgreSQL

Auteur : Philippe Le Van - @plv@framapiaf.org

Date : 06 juin 2024

Introduction

Cette page contient des notes sur l'administration de PostgreSQL.

WARNING: Ces notes ne sont pas fiables et consolidées. Elles sont vraiment juste des notes de travail. Ne prenez pas ces notes pour une référence technique.

Configurations de la base

Consulter les paramètres de configuration

Pour consulter les paramètres de configuration de la base, on peut utiliser la commande SHOW :

-- résultat plus compact (3 colonnes)
SHOW all;

-- résultat plus détaillé (17 colonnes)
select * from pg_settings;

-- voir toutes les configs qui concernent le vacuum
select * from pg_settings where name like '%vacuum%'

Des paramètres de configuration utiles

Paramètres de timeouts

statement_timeout (integer)

Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

idle_in_transaction_session_timeout (integer)

Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

The default value of 0 disables this feature.

Le premier paramètre est plutôt à éviter, il affecte toutes les sessions.

Le 2ème est plus intéressant, il permet de libérer des ressources en cas de session qui reste ouverte.

Vacuum

Utilisation manuelle

La commande `VACUUM` elle même

L’opération VACUUM est une opération de maintenance qui permet de libérer de l’espace disque et d’optimiser les performances de PostgreSQL.

Voici quelques commandes vacuum utiles testées sur pgsql 10:

-- lancer un vacuum sur une table
VACUUM my_table;

-- lancer un vacuum sur une base avec des informations
VACUUM (VERBOSE) my_table;

-- lancer un vacuum sur une base avec des informations et en analysant les index
-- Le analyze est utile pour mettre à jour les statistiques de la table et a
-- un impact sur les performances des requêtes
VACUUM (VERBOSE, ANALYZE) my_table;

Sur une table de 100 millions de lignes, qui est mise à jour presque intégralement chaque nuit, le vacuum prend environ 1h.

En cas de timeout [57014] ERROR: canceling statement due to statement timeout, on peut relancer le vacuum, il reprendra là où le 1er s'était arrêté.

Warning

L’espace disque libéré par le VACUUM n’est pas rendu au système de fichiers. Pour cela, il faut lancer un VACUUM FULL, mais attention, cette opération bloque la table concernée, potentiellement pendant un long moment.

Utilisation de l’autovacuum

L’autovacuum est activé par défaut sur PostgreSQL.

Dans le cas standard, il n’est pas nécessaire de tunner les configs.

Nous avons besoin d’un tunning plus complet dans le cas d’une table de 118 millions de lignes qui est entièrement remplacée chaque nuit.

On a appliqué les paramètres suivants :

-- l’autovacuum traite 50% des données à chaque passage (par défaut c’est 0.2)
alter table simple_fec set (autovacuum_vacuum_scale_factor = 0.5);

-- l’autovacuum se déclenche à partir de 10% de lignes modifiées (par défaut c’est 50%)
alter table simple_fec set (autovacuum_vacuum_threshold = 10);

-- l’analyse statistique (qui optimise les indexes) traite 50% des données à chaque passage (par défaut c’est 10%)
alter table simple_fec set (autovacuum_analyze_scale_factor = 0.5);