1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Le spécialiste français de PostgreSQL crée en 2005
• Coopérative proposant des services :
  • de conseil
  • de support
  • de formation
• métier principal : DBA polyvalent
• Site : http://dalibo.com

• Thibaut MADELAINE
• DBA PostgreSQL à Dalibo depuis 4 ans
• Mainteneur de pitrery et sqlserver2pgsql

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

| sy.pɛʁ.vi.ze |

« Se placer au-dessus pour voir, remarquer, prendre des mesures »

• Améliorer / mesurer les performances
• Améliorer l’applicatif
• Anticiper / prévenir les incidents
• Réagir vite en cas de crash

• Administrateur de bases de données
  • surveillance, performance
  • mise à jour
• Administrateur système
  • surveillance, qualité de service
• Développeur
  • correction et optimisation de requêtes

• Charge CPU
• Entrées/sorties disque
• Espace disque
• Sur-activité et inactivité du serveur
• Temps de réponse

• Nombre de connexions
• Volumétries
• Requêtes lentes et/ou fréquentes
• Nombre de transactions par seconde
• Ratio d’utilisation du cache
• Retard de réplication

• Script de monitoring PostgreSQL pour Nagios
  • nombreuses sondes spécifiques à PostgreSQL
  • nombreuses métriques remontées
• Permet de faire la liste des points d’observation possibles
• https://github.com/OPMDG/check_pgactivity

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Objectifs :
  • Approfondir la compréhension de notre instance
  • Repérer les points de contentions
  • Trouver les requêtes posant problème
• Quand ?
  • En test et validation
  • Lors de dysfonctionnements

• Les graphiques de métrologie
• Des outils dédiés :
  • pgCluu
  • pgBadger
  • temBoard

• Outils de collectes de métriques de performances
  • Dépôt github
  • génère un rapport HTML complet
• Différents aspects mesurés :
  • informations sur le système
  • consommation des ressources CPU, RAM, I/O
  • utilisation de la base de données

• Script Perl
• site officiel : https://pgbadger.darold.net/
• Traite les journaux applicatifs de PostgreSQL
• Génére un rapport HTML très détaillé

• Utilisation des traces applicatives
• Où tracer ?
• Quel niveau de traces ?
• Tracer les requêtes
• Tracer certains comportements

• traces en anglais
  • lc_messages='C'
• ajouter le plus d’information possible
  • log_line_prefix = '%t [%p]: user=%u,db=%d,app=%a,client=%h '

• log_connections, log_disconnections
• log_autovacuum_min_duration
• log_checkpoints
• log_lock_waits
• log_temp_files

• log_min_duration_statement
• en production, trace les requêtes longues
  • 10000 pour les requêtes de plus de 10 secondes
• pour un audit, à 0 : trace toutes les requêtes

$ pgbadger postgresql-13-main.log

• Rapport dans le fichier out.html
• Très nombreuses options :
  • fichier de sortie : --outfile
  • filtrage par date : --begin, --end
  • autres filtrages : --dbname, --dbuser, --appname, …

• Multi-instances
• Surveillance OS / PostgreSQL
• Suivi de l’activité
• Configuration de chaque instance
• Historisation des requêtes pg_stat_statements

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Des nouvelles versions logicielles régulières
• Apportent :
  • Des innovations
  • Des améliorations
  • Des corrections de dysfonctionnement
  • Des corrections de failles de sécurités

• … qui se soigne
  • Avant la version 10
    • X.Y : version majeure (8.4, 9.6)
    • X.Y.Z : version mineure (9.6.19)
  • Après la version 10
    • X : version majeure (10, 11, 12, 13)
    • X.Y : version mineure (12.4)

• Apporte des nouvelles fonctionnalités
• Nécessite une migration des données
• Une sortie tous les 12 à 15 mois
• Maintenue environ 5 ans par la communauté
• Les versions majeures supportées : 9.6, 10, 11, 12 et 13

• Bien lire les Release Notes
• Bien tester l’application avec la nouvelle version
  • rechercher les régressions en terme de fonctionnalités et de performances
  • penser aux extensions et aux outils
• Pour mettre à jour
  • mise à jour des binaires
  • et mise à jour/traitement des fichiers de données

• Les versions 9.5 et inférieures : à migrer au plus vite !
• Les versions 9.6 et 10 : projet de migration à lancer

• Corrections de bugs ou de failles de sécurité
• Sortie au moins trimestrielle dans toutes les versions majeures supportées
• Dernières releases (11 février 2021) :
  • 13.2, 12.6, 11.11, 10.16 et 9.6.21

• Méthode :
  • arrêter PostgreSQL
  • mettre à jour les binaires
  • redémarrer PostgreSQL
• Pas besoin de s’occuper des données, sauf cas exceptionnel
  • bien lire les Release Notes pour s’en assurer

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Symptôme : baisse régulière des performances
• Raison : fragmentation des tables et des index
• Cause : implémentation du MVCC

• Le « noyau » de PostgreSQL
• Garantit ACID
• Permet les écritures concurrentes sur la même table

• Colonnes supplémentaires masquées par défaut :
  • ctid
  • xmin et xmax

• Codée sur 6 octets
  • 4 octets pour la page
  • 2 octets pour la ligne
• Fournit une adresse physique dans une table

Table initiale :

• Comment est effectuée la suppression d’un enregistrement ?
• Comment est effectuée l’annulation de la transaction 150 ?

• Avantages :
  • avantages classiques de MVCC (concurrence d’accès)
  • implémentation simple et performante
  • peu de sources de contention
  • verrouillage simple d’enregistrement
  • rollback instantané
  • données conservées aussi longtemps que nécessaire

• Inconvénients :
  • Tables plus volumineuses
  • Pas de visibilité dans les index
  • Nettoyage des enregistrements (VACUUM)

• Supervision de la fragmentation
• Réglage de l’autovacuum
• Reconstruction régulière des index

• Déclenchement d’un vacuum si :

nb_enregistrements_morts (n_dead_tup) >=
    autovacuum_vacuum_threshold + nb_enregs × autovacuum_vacuum_scale_factor

• Paramètres :
  • autovacuum_vacuum_threshold : 50
  • autovacuum_vacuum_scale_factor : 20 %

• Déconseillé de modifier le paramétrage de façon globale
• Adaptation conseillée :
  • table par table
  • suivant le nombre d’enregistrements

ALTER TABLE table_name SET (autovacuum_vacuum_scale_factor = 0.05);

• Lancer REINDEX régulièrement permet :
  • de gagner de l’espace disque
  • d’améliorer les performances
  • de réparer un index corrompu
• VACUUM ne provoque pas de réindexation
• VACUUM FULL réindexe
• Option CONCURRENTLY à partir de la version 12

• Script plannifié régulièrement
• Recherche des N index les plus fragmentés
• Version 12+ : REINDEX INDEX index CONCURRENTLY
• Sinon (hors contrainte unique) :

CREATE INDEX CONCURRENTLY index_bis (...);
BEGIN;
DROP INDEX index CONCURRENTLY;
ALTER INDEX index_bis RENAME TO index;
COMMIT;

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Opération essentielle pour la sécurisation des données
• PostgreSQL propose différentes solutions
  • de sauvegarde à froid ou à chaud, mais cohérentes
  • des méthodes de restauration partielle ou complète

• Pourquoi établir une politique ?
• Que sauvegarder ?
• À quelle fréquence sauvegarder les données ?
• Quels supports ?
• Quels outils ?
• Vérifier la restauration des sauvegardes

• Sécuriser les données
• Mettre à jour le moteur de données
• Dupliquer une base de données de production
• Archiver les données

• RPO (Recovery Point Objective) : Perte de Données Maximale Admissible
• RTO (Recovery Time Objective) : Durée Maximale d’Interruption Admissible
• => Permettent de définir la politique de sauvegarde/restauration

• Sauvegarde à chaud avec Time Navigator
• Permet des restaurations à n’importe quel point dans le temps
• Solution puissante… mais complexe

• Point In Time Recovery
• À chaud
• En continu
• Cohérente

• Chaque donnée est écrite 2 fois sur le disque !
• Sécurité quasiment infaillible
• Comparable à la journalisation des systèmes de fichiers

• Un seul sync sur le fichier de transactions
• Le fichier de transactions est écrit de manière séquentielle
• Les fichiers de données sont écrits de façon asynchrone
• Point In Time Recovery
• Réplication (WAL shipping)

• Les journaux de transactions (WAL) contiennent toutes les modifications
• Il faut les archiver
• … et avoir une image des fichiers des données à un instant t
• La restauration se fait en restaurant cette image
• … et en rejouant les journaux dans l’ordre

• Sauvegarde de l’instance complète
• Nécessite un grand espace de stockage (données + journaux)
• Risque d’accumulation des journaux en cas d’échec d’archivage
  • …avec arrêt de l’instance si pg_wal plein !
• Restauration de l’instance complète

• Superviser l’espace disques :
  • du FS de l’archivage
  • du FS des données de PostgreSQL
• Savoir où se trouve la procédure de restauration…
  • … et la tester régulièrement !

1. La supervision
2. L’audit
3. La mise à jour applicative
4. Le contrôle de la fragmentation
5. La sauvegarde
6. L’optimisation SQL

• Le vrai problème de l’informatique :
  • ne serait-ce pas les utilisateurs ???
• Un aperçu :
  • Schéma de données mal conçu
  • Pas d’indexation
  • Vues imbriquées
  • Requêtes baroques
  • etc.

• La solution : la formation PERF1 de Dalibo
  • https://www.dalibo.com/formation-postgresql-performance-1
• Au programme :
  • Configuration du système et de l’instance
  • Techniques d’indexation
  • Comprendre EXPLAIN
  • Analyses et diagnostics

• EXPLAIN : récupère le plan d’exécution optimal
• EXPLAIN ANALYSE : plan et réalisé de la requête
• Difficile à lire, utiliser les outils :
  • https://explain.dalibo.com/
  • https://explain.depesz.com/

• Support Dalibo :
  • sur la plate-forme : https://support.dalibo.com
  • par mail à l’adresse : support@dalibo.com
  • par téléphone au : +33 (0) 970 444 750