• L'optimisation doit porter sur les différents composants
  • le serveur qui héberge le SGBDR : le matériel, la distribution, le noyau, les systèmes de fichiers
  • le moteur de la base de données : postgresql.conf
  • la base de données : l'organisation des fichiers de PostgreSQL
  • l'application en elle-même : le schéma et les requêtes

• Quelques considérations générales sur l'optimisation
• Choix et configuration du matériel
• Choix et configuration du système d'exploitation
• Configuration du serveur de bases de données

• Deux points déterminants :
  • Vision globale du système d'information
  • Compréhension de l'utilisation de la base

• L'optimisation n'est pas un processus unique
  • il s'agit au contraire d'un processus itératif
• La base doit être surveillée régulièrement !
  • nécessité d'installer des outils de supervision

• Performances très liées aux possibilités du matériel
• Quatre composants essentiels
  • les processeurs
  • la mémoire
  • les disques
  • le système disque (RAID, SAN)

• Trois critères importants
  • nombre de cœurs
  • fréquence
  • cache
• Privilégier
  • le nombre de cœurs si le nombre de sessions parallèles est important
  • ou la fréquence si les requêtes sont complexes
• 64 bits

• Essentiel pour un serveur de bases de données
• Plus il y en a, mieux c'est
  • moins d'accès disque
• Pour le système comme pour PostgreSQL

• Trois grandes technologies : SATA, SAS et SSD

• Différents niveaux de RAID
• Les plus intéressants pour un SGBD
  • RAID 1 (système, journaux de transactions)
  • RAID 10 (fichiers de données)
• Attention au cache
  • toujours activer le cache en lecture
  • activer le cache en écriture que si batterie présente (et supervisée)

• Pouvoir sélectionner les disques dans un groupe RAID
• Attention au cache
  • toujours activer le cache en lecture
  • activer le cache en écriture que si batterie présente
• Attention à la latence réseau
• Attention au système de fichiers
  • pas de NFS

• Masque les ressources physiques au système
  • Plus difficile d'optimiser les performances
• Propose généralement des fonctionnalités d'overcommit
  • grandes difficultés à trouver la cause du problème du point de vue de la VM
  • dédier un minimum de ressources aux VM PostgreSQL
• En pause tant que l'hyperviseur ne dispose pas de l'ensemble des vCPU alloués à la machine virtuelle
• Mutualise les disques = problèmes de performances
  • Configurer les disques de PostgreSQL en « Thick Provisionning »

• Éviter l'effet dit de time drift en utilisant la même source NTP sur les OS invités (VM) et l'hôte ESXi;
• Utiliser les adaptateurs réseau paravirtualisés de type VMXNET3.
• Utiliser l'adaptateur paravirtualisé PVSCSI pour les disques dédiés aux partitions PostgreSQL;
• En cas de présence d'une architecture matérielle de type NUMA :
  • dimensionner la mémoire de chaque VM pour qu'elle ne dépasse pas le volume de mémoire physique au sein d'un groupe NUMA;

• Quel système choisir ?
• Quelle configuration réaliser ?

• PostgreSQL fonctionne sur différents systèmes
  • Linux, BSD, Windows, Solaris, HPUX, etc.
• Principalement développé et testé sous Linux
• Windows intéressant pour les postes des développeurs
  • mais moins performant que Linux
  • moins d'outillage

• Choisir la version la plus récente du noyau car
  • plus stable
  • plus compatible avec le matériel
  • plus de fonctionnalités
  • plus de performances
• Utiliser la version de la distribution Linux
  • ne pas le compiler soi-même

• En plus du choix du noyau, certains paramètres nécessitent une configuration personnalisée
  • gestion du cache disque système
  • gestion de la sur-allocation de mémoire
  • taille et comportement du swap
  • affinité entre les cœurs et les espaces mémoire
  • scheduler processeur
  • huge pages

• Gestion de l'écriture des « dirty pages »
• Paramètres
  • vm.dirty_ratio
  • vm.dirty_background_ratio
  • vm.dirty_bytes
  • vm.dirty_background_bytes
• Plus nécessaire depuis la version 9.6 (*_flush_after)

• Supervision de la sur-allocation par le noyau
• Si cas critique, l'OOM fait un kill -9 du processus
• À désactiver pour un serveur dédié
  • vm.overcommit_memory
  • vm.overcommit_ratio

• Taille de la swap
  • pas plus de 2 Go
• Contrôler son utilisation
  • vm.swappiness

• Pour architecture NUMA (multi-sockets)
• Chaque socket travaille plus efficacement avec une zone mémoire allouée
• Peut pénaliser le cache disque système
  • vm.zone_reclaim_mode

• Réduire la propension du kernel à migrer les processus
  • kernel.sched_migration_cost_ns = 5000000 (sched_migration_cost pour les noyaux <3.6)
• Désactiver le regroupement par session TTY
  • kernel.sched_autogroup_enabled = 0

• Utiliser des pages mémoires de 2 Mo au lieu de 4 ko
• Réduction de la consommation mémoire des processus
• Garantie Shared Buffers non swappé
• vm.nr_overcommit_hugepages=x
• huge_pages=on|off|try

• Outil
  • sysctl
• Fichier de configuration
  • /etc/sysctl.conf

• Windows :
  • NTFS
• Linux :
  • ext4, reiserfs, jfs, xfs, btrfs
• Solaris :
  • ZFS
• Utiliser celui préconisé par votre système d'exploitation/distribution
  • … et oublier NFS !

• Quelques options à connaître :
  • noatime, nodiratime
  • dir_index
  • data=writeback
  • nobarrier
• Permet de gagner un peu en performance

• Version
• Configuration
• Emplacement des fichiers

• Chaque nouvelle version majeure a des améliorations de performance
  • mettre à jour est un bon moyen pour gagner en performances
• Ne pas compiler
  • sauf pour les Intel Itanium

• shared_buffers
• wal_buffers
• work_mem
• maintenance_work_mem

• effective_cache_size
• random_page_cost

• max_worker_processes
• max_parallel_workers
• max_parallel_workers_per_gather
• min_parallel_table_scan_size
• min_parallel_index_scan_size

• fsync
• checkpoint_segments
  • remplacé par min_wal_size et max_wal_size à partir de la version 9.5
• checkpoint_timeout
• checkpoint_completion_target

• track_activities
• track_counts
• track_functions et track_io_timing

• autovacuum

• Outil écrit en Python, par Greg Smith
  • Repris en Ruby par Alexey Vasiliev
• Propose quelques meilleures valeurs pour certains paramètres
• Quelques options pour indiquer des informations système
• Version web
• Il existe également pgconfig

• Outil pour réaliser rapidement des tests de performance
• Fourni dans les modules de "contrib" de PostgreSQL
• Travail sur une base de test créée par l'outil…
  • … ou sur une vraie base de données

• On peut faire varier différents paramètres, tel que :
  • le nombre de clients
  • le nombre de transactions par client
  • faire un test de performance en SELECT only, UPDATE only ou TPC-B
  • faire un test de performance dans son contexte applicatif
  • exécuter le plus de requêtes possible sur une période de temps donné
  • etc.

• pgbench est capable de créer son propre environnement de test
• Environnement adapté pour des tests de type TPC-B
• Permet de rapidement tester une configuration PostgreSQL
  • en termes de performance
  • en termes de charge
• Ou pour expérimenter/tester

• pgbench est capable de travailler avec une base existante
• Lecture des requêtes depuis un ou plusieurs fichiers
• Utilisation possible de variables et commandes

• Séparer les objets suivant leur utilisation
• Tablespaces
• Quelques stratégies
  • séparer tables et index
  • séparer archives et données vivantes
• Configuration possible des tablespaces
  • seq_page_cost, random_page_cost, effective_io_concurrency

• Placer les journaux sur un autre disque
• Option -X de l'outil initdb
• Lien symbolique

• Placer les fichiers statistiques sur un autre disque
  • et de préférence sur un montage en RAM
• Option stats_temp_directory

• Outil écrit en Perl par Julien Francoz
• Propose quelques meilleures valeurs pour certains paramètres
  • système
  • PostgreSQL
• Site du projet : https://github.com/jfcoz/postgresqltuner

PostgreSQL propose de nombreuses voies d'optimisation.

Cela passe en priorité par un bon choix des composants matériels et par une configuration pointilleuse. Mais ceci ne peut se faire qu'en connaissance de l'ensemble du système, et notamment des applications utilisant les bases de l'instance.

N'hésitez pas, c'est le moment !

• Le matériel, le système et la configuration sont importants pour les performances
• Mais il est aussi essentiel de se préoccuper des requêtes et de leurs performances

• Exécution globale d'une requête
• Planificateur : utilité, statistiques et configuration
• EXPLAIN
• Nœuds d'un plan
• Outils

• L'exécution peut se voir sur deux niveaux
  • Niveau système
  • Niveau SGBD
• De toute façon, composée de plusieurs étapes

• Le client envoie une requête au serveur de bases de données
• Le serveur l'exécute
• Puis il renvoie le résultat au client

• Requêtes DDL
• Instructions TRUNCATE et COPY
• Pas de réécriture, pas de plans d'exécution... une exécution directe

• Prédicat
  • filtre de la clause WHERE
• Sélectivité
  • pourcentage de lignes retournées après application d'un prédicat
• Cardinalité
  • nombre de lignes d'une table
  • nombre de lignes retournées après filtrage

Cette requête d'exemple :

SELECT matricule, nom, prenom, nom_service, fonction, localisation
  FROM employes emp
  JOIN services ser ON (emp.num_service = ser.num_service)
 WHERE ser.localisation = 'Nantes';

L'objet de ce module est de comprendre son plan d'exécution :

 Hash Join  (cost=1.06..2.29 rows=4 width=48)
   Hash Cond: (emp.num_service = ser.num_service)
   ->  Seq Scan on employes emp  (cost=0.00..1.14 rows=14 width=35)
   ->  Hash  (cost=1.05..1.05 rows=1 width=21)
         ->  Seq Scan on services ser  (cost=0.00..1.05 rows=1 width=21)
               Filter: ((localisation)::text = 'Nantes'::text)

• Chargé de sélectionner le meilleur plan d'exécution
• Énumère tous les plans d'exécution
  • Tous ou presque...
• Calcule leur coût suivant des statistiques, un peu de configuration et beaucoup de règles
• Sélectionne le meilleur (le moins coûteux)

• SQL est un langage déclaratif
• Une requête décrit le résultat à obtenir
  • Mais pas la façon de l'obtenir
• C'est au planificateur de déduire le moyen de parvenir au résultat demandé

• 1ère règle : Récupérer le bon résultat
• 2è règle : Le plus rapidement possible
  • En minimisant les opérations disques
  • En préférant les lectures séquentielles
  • En minimisant la charge CPU
  • En minimisant l'utilisation de la mémoire

• L'optimiseur s'appuie sur :
  • un mécanisme de calcul de coûts
  • des statistiques sur les données
  • le schéma de la base de données

• À partir du modèle de données
  • suppression de jointures externes inutiles
• Transformation des sous-requêtes
  • certaines sous-requêtes transformées en jointures
• Appliquer les prédicats le plus tôt possible
  • réduit le jeu de données manipulé
• Intègre le code des fonctions SQL simples (inline)
  • évite un appel de fonction coûteux

• Stratégie d'accès aux lignes
  • Par parcours d'une table, d'un index, de TID, etc
• Stratégie d'utilisation des jointures
  • Ordre des jointures
  • Type de jointure (Nested Loop, Merge/Sort Join, Hash Join)
  • Ordre des tables jointes dans une même jointure
• Stratégie d'agrégation
  • Brut, trié, haché

• Modèle basé sur les coûts
  • quantifier la charge pour répondre à une requête
• Chaque opération a un coût :
  • lire un bloc selon sa position sur le disque
  • manipuler une ligne issue d'une lecture de table ou d'index
  • appliquer un opérateur

• L'optimiseur a besoin de connaître :
  • le coût relatif d'un accès séquentiel au disque.
  • le coût relatif d'un accès aléatoire au disque.
  • le coût relatif de la manipulation d'une ligne en mémoire.
  • le coût de traitement d'une donnée issue d'un index.
  • le coût d'application d'un opérateur.
  • le coût de la manipulation d'une ligne en mémoire pour un parcours parallèle parallélisé.
  • le coût de mise en place d'un parcours parallélisé.

• Toutes les décisions du planificateur se basent sur les statistiques
  • Le choix du parcours
  • Comme le choix des jointures
• Statistiques mises à jour avec ANALYZE
• Sans bonnes statistiques, pas de bons plans

• L'optimiseur utilise les statistiques pour déterminer :
  • la cardinalité d'un filtre -> quelle stratégie d'accès
  • la cardinalité d'une jointure -> quel algorithme de jointure
  • la cardinalité d'un regroupement -> quel algorithme de regroupement

• Taille
• Cardinalité
• Stocké dans pg_class
  • relpages et reltuples

• Nombre de valeurs distinctes
• Nombre d'éléments qui n'ont pas de valeur (NULL)
• Largeur d'une colonne
• Distribution des données
  • tableau des valeurs les plus fréquentes
  • histogramme de répartition des valeurs

• Les informations statistiques vont dans la table pg_statistic
  • mais elle est difficile à comprendre
  • mieux vaut utiliser la vue pg_stats
  • une table vide n'a pas de statistiques
• Taille et cardinalité dans pg_class
  • colonnes relpages et reltuples

• Une ligne par colonne de chaque table
• 3 colonnes d'identification
  • schemaname, tablename, attname
• 8 colonnes d'informations statistiques
  • inherited, null_frac, avg_width, n_distinct
  • most_common_vals, most_common_freqs, histogram_bounds
  • most_common_elems, most_common_elem_freqs, elem_count_histogram
  • correlation

• Pas par défaut
• CREATE STATISTICS
• Deux types de statistique
  • nombre de valeurs distinctes
  • dépendances fonctionnelles
• À partir de la version 10

• Une ligne par objet statistique
• 4 colonnes d'identification
  • stxrelid, stxname, stxnamespace, stxkeys
• 1 colonne pour connaître le type de statistiques géré
  • stxkind
• 2 colonnes d'informations statistiques
  • stxndistinct
  • stxdependencies

• Ordre SQL de calcul de statistiques
  • ANALYZE [ VERBOSE ] [ table [ ( colonne [, ...] ) ] ]
• Sans argument : base entière
• Avec argument : la table complète ou certaines colonnes seulement
• Prend un échantillon de chaque table
• Et calcule des statistiques sur cet échantillon
• Si table vide, conservation des anciennes statistiques

• Dépend principalement de la fréquence des requêtes DML
• Cron
  • Avec psql
  • Avec vacuumdb (option --analyze-only en 9.0)
• Autovacuum fait du ANALYZE
  • Pas sur les tables temporaires
  • Pas assez rapidement dans certains cas

• Se configure dans postgresql.conf
  • default_statistics_target = 100

• Configurable par colonne

  ALTER TABLE nom ALTER [ COLUMN ] colonne SET STATISTICS valeur;

• Par défaut, récupère 30000 lignes au hasard
  • 300 * default_statistics_target
• Va conserver les 100 valeurs les plus fréquentes avec leur fréquence

• Plan d'exécution
  • représente les différentes opérations pour répondre à la requête
  • sous forme arborescente
  • composé des nœuds d'exécution
  • plusieurs opérations simples mises bout à bout

• Nœud
  • opération simple : lectures, jointures, tris, etc.
  • unité de traitement
  • produit et consomme des données
• Enchaînement des opérations
  • chaque nœud produit les données consommées par le nœud parent
  • nœud final retourne les données à l'utilisateur

• Des options supplémentaires
  • ANALYZE
  • BUFFERS
  • COSTS
  • TIMING
  • VERBOSE
  • SUMMARY
  • FORMAT
• Donnant des informations supplémentaires très utiles

• Différence importante entre l'estimation du nombre de lignes et la réalité
• Boucles
  • appels très nombreux dans une boucle (nested loop)
  • opération lente sur lesquels PostgreSQL boucle
• Temps d'exécution conséquent sur une opération
• Opérations utilisant beaucoup de blocs (option BUFFERS)

• Détermine à partir des statistiques
  • cardinalité des prédicats
  • cardinalité des jointures
• Coût d'accès déterminé selon
  • des cardinalités
  • volumétrie des tables

• Un plan est composé de nœuds
  • certains produisent des données
  • d'autres consomment des données et les retournent
  • le nœud final retourne les données à l'utilisateur
  • chaque nœud consomme au fur et à mesure les données produites par les nœuds parents

• Seq Scan
• Parallel Seq Scan
• Function Scan
• et des parcours d'index

• Index Scan
• Index Only Scan
• Bitmap Index Scan
• Et leurs versions parallélisées

• PostgreSQL implémente les 3 algorithmes de jointures habituels :
  • Nested Loop (boucle imbriquée)
  • Hash Join (hachage de la table interne)
  • Merge Join (tri-fusion)
• Parallélisation possible
  • version 9.6 pour Nested Loop et Hash Join
  • version 10 pour Merge Join
• Et pour EXISTS, IN et certaines jointures externes :
  • Semi Join et Anti Join

• Un seul noeud de tri :
  • Sort
• Regroupement/Agrégation :
  • Aggregate
  • HashAggregate
  • GroupAggregate
  • Partial Aggregate/Finalize Aggregate

• Limit
• Unique
• Append (UNION ALL), Except, Intersect
• Gather
• InitPlan, Subplan, etc.

• L'optimiseur se trompe parfois
  • mauvaises statistiques
  • écriture particulière de la requête
  • problèmes connus de l'optimiseur

SELECT * FROM t1 WHERE c1=1 AND c2=1

• c1=1 est vrai pour 20% des lignes
• c2=1 est vrai pour 10% des lignes
• Le planificateur va penser que le résultat complet ne récupérera que 20% * 10% (soit 2%) des lignes
  • En réalité, ça peut aller de 0 à 10% des lignes
• Problème corrigé en version 10
  • CREATE STATISTICS pour des statistiques multi-colonnes

SELECT *
FROM commandes
WHERE extract('year' from date_commande) = 2014;

• L'optimiseur n'a pas de statistiques sur le résultat de la fonction extract
  • il estime la sélectivité du prédicat à 0.5%.

SELECT * FROM t1 WHERE c2 LIKE 'x%';

• PostgreSQL peut utiliser un index dans ce cas
• Si l'encodage n'est pas C, il faut déclarer l'index avec une classe d'opérateur
  • varchar_pattern_ops, text_pattern_ops, etc
• En 9.1, il faut aussi faire attention au collationnement
• Ne pas oublier pg_trgm (surtout en 9.1) et FTS

• Exemple
  • EXPLAIN avec LIMIT 199
  • EXPLAIN avec LIMIT 200
• Corrigé en 9.2

• DELETE lent
• Généralement un problème de clé étrangère

Delete  (actual time=111.251..111.251 rows=0 loops=1)
  ->  Hash Join  (actual time=1.094..21.402 rows=9347 loops=1)
        ->  Seq Scan on lot_a30_descr_lot
            (actual time=0.007..11.248 rows=34934 loops=1)
        ->  Hash  (actual time=0.501..0.501 rows=561 loops=1)
              ->  Bitmap Heap Scan on lot_a10_pdl
                  (actual time=0.121..0.326 rows=561 loops=1)
                    Recheck Cond: (id_fantoir_commune = 320013)
                    ->  Bitmap Index Scan on...
                        (actual time=0.101..0.101 rows=561 loops=1)
                          Index Cond: (id_fantoir_commune = 320013)
Trigger for constraint fk_lotlocal_lota30descrlot:
  time=1010.358 calls=9347
Trigger for constraint fk_nonbatia21descrsuf_lota30descrlot:
  time=2311695.025 calls=9347
Total runtime: 2312835.032 ms

SELECT DISTINCT t1.* FROM t1 JOIN t2 ON (t1.id=t2.t1_id);

• DISTINCT est souvent utilisé pour dédoublonner les lignes de t1
  • mais génère un tri qui pénalise les performances
• GROUP BY est plus rapide
• Une clé primaire permet de dédoublonner efficacement des lignes
  • à utiliser avec GROUP BY

• Trop de lignes retournées

• Prédicat incluant une transformation :

  WHERE col1 + 2 > 5

• Statistiques pas à jour ou peu précises

• Opérateur non-supporté par l'index :

  WHERE col1 <> 'valeur';

• Paramétrage de PostgreSQL : effective_cache_size

• NOT IN avec une sous-requête
  • à remplacer par NOT EXISTS
• Utilisation systématique de UNION au lieu de UNION ALL
  • entraîne un tri systématique
• Sous-requête dans le SELECT
  • utiliser LATERAL

• pgAdmin3
• explain.depesz.com
• pev
• auto_explain
• plantuner

• Vision graphique d'un EXPLAIN
• Une icône par nœud
• La taille des flèches dépend de la quantité de données
• Le détail de chaque nœud est affiché en survolant les nœuds

• Site web proposant un affichage particulier du EXPLAIN ANALYZE
• Il ne travaille que sur les informations réelles
• Les lignes sont colorées pour indiquer les problèmes
  • Blanc, tout va bien
  • Jaune, inquiétant
  • Marron, plus inquiétant
  • Rouge, très inquiétant
• Installable en local

• Site web proposant un affichage particulier du EXPLAIN ANALYZE
  • mais différent de celui de Depesz
• Fournir un plan d'exécution en JSON
• Installable en local

• Extension pour PostgreSQL >= 8.4
• Connaître les requêtes lentes est bien
• Mais difficile de connaître leur plan d'exécution au moment où elles ont été lentes
• D'où le module auto_explain

• Extension, pour PostgreSQL >= 8.4
• Suivant la configuration
  • Interdit l'utilisation de certains index
  • Force à zéro les statistiques d'une table vide

• Planificateur très avancé
• Mais faillible
• Cependant
  • ne pensez pas être plus intelligent que le planificateur

N'hésitez pas, c'est le moment !

• Quatre types de nœuds
  • Parcours (de table, d'index, de TID, etc.)
  • Jointures (Nested Loop, Sort/Merge Join, Hash Join)
  • Opérateurs sur des ensembles (Append, Except, Intersect, etc.)
  • Et quelques autres (Sort, Aggregate, Unique, Limit, Materialize)

• Ne prend rien en entrée
• Mais renvoie un ensemble de données
  • Trié ou non, filtré ou non
• Exemples typiques
  • Parcours séquentiel d'une table, avec ou sans filtrage des enregistrements produits
  • Parcours par un index, avec ou sans filtrage supplémentaire

• Parcours séquentiel de la table (Sequential Scan, ou SeqScan)
• Aussi appelé FULL TABLE SCAN par d'autres SGBD
• La table est lue entièrement
  • Même si seulement quelques lignes satisfont la requête
  • Sauf dans le cas de la clause LIMIT sans ORDER BY
• Elle est lue séquentiellement par bloc de 8 Ko
• Optimisation synchronize_seqscans

• Parcours aléatoire de l'index
• Pour chaque enregistrement correspondant à la recherche
  • Parcours non séquentiel de la table (pour vérifier la visibilité de la ligne)
• Sur d'autres SGBD, cela revient à un
  • INDEX RANGE SCAN, suivi d'un TABLE ACCESS BY INDEX ROWID
• Gros gain en performance quand le filtre est très sélectif
• L'ensemble de lignes renvoyé est trié

• En VO, Bitmap Index Scan / Bitmap Heap Scan
• Disponible à partir de la 8.1
• Diminuer les déplacements de la tête de lecture en découplant le parcours de l'index du parcours de la table
  • Lecture en un bloc de l'index
  • Lecture en un bloc de la partie intéressante de la table
• Autre intérêt : pouvoir combiner plusieurs index en mémoire
  • Nœud BitmapAnd
  • Nœud BitmapOr
• Coût de démarrage généralement important
  • Parcours moins intéressant avec une clause LIMIT

• Avant la 9.2, pour une requête de ce type
  • SELECT c1 FROM t1 WHERE c1<10
• PostgreSQL devait lire l'index et la table
  • car les informations de visibilité ne se trouvent que dans la table
• En 9.2, le planificateur peut utiliser la « Visibility Map »
  • nouveau nœud « Index Only Scan »
  • Index B-Tree (9.2+)
  • Index SP-GiST (9.2+)
  • Index GiST (9.5+) => Types : point, box, inet, range

• TID Scan
• Function Scan
• Values
• Result

• Prend deux ensembles de données en entrée
  • L'une est appelée inner (interne)
  • L'autre est appelée outer (externe)
• Et renvoie un seul ensemble de données
• Exemples typiques
  • Nested Loop, Merge Join, Hash Join

• Pour chaque ligne de la relation externe
  • Pour chaque ligne de la relation interne
  • Si la condition de jointure est avérée
    • Émettre la ligne en résultat
• L'ensemble externe n'est parcouru qu'une fois
• L'ensemble interne est parcouru pour chaque ligne de l'ensemble externe
  • Avoir un index utilisable sur l'ensemble interne augmente fortement les performances

• Trier l'ensemble interne
• Trier l'ensemble externe
• Tant qu'il reste des lignes dans un des ensembles
  • Lire les deux ensembles en parallèle
  • Lorsque la condition de jointure est avérée
  • Émettre la ligne en résultat
• Parcourir les deux ensembles triés (d'où Sort-Merge Join)
• Ne gère que les conditions avec égalité
• Produit un ensemble résultat trié
• Le plus rapide sur de gros ensembles de données

• Calculer le hachage de chaque ligne de l'ensemble interne
• Tant qu'il reste des lignes dans l'ensemble externe
  • Hacher la ligne lue
  • Comparer ce hachage aux lignes hachées de l'ensemble interne
  • Si une correspondance est trouvée
  • Émettre la ligne trouvée en résultat
• Ne gère que les conditions avec égalité
• Idéal pour joindre une grande table à une petite table
• Coût de démarrage important à cause du hachage de la table

SELECT pg_class.relname, pg_class.reltuples
FROM pg_class
LEFT JOIN pg_namespace
       ON pg_class.relnamespace=pg_namespace.oid;

• Un index unique existe sur la colonne oid de pg_namespace
• Jointure inutile
  • sa présence ne change pas le résultat
• PostgreSQL peut supprimer la jointure à partir de la 9.0

• Trouver le bon ordre de jointure est un point clé dans la recherche de performances
• Nombre de possibilités en augmentation factorielle avec le nombre de tables
• Si petit nombre, recherche exhaustive
• Sinon, utilisation d'heuristiques et de GEQO
  • Limite le temps de planification et l'utilisation de mémoire
  • GEQO remplacé par Simulated Annealing ? (recuit simulé en VF)

• Prend un ou plusieurs ensembles de données en entrée
• Et renvoie un ensemble de données
• Concernent principalement les requêtes sur des tables partitionnées ou héritées
• Exemples typiques
  • Append
  • Intersect
  • Except

• Prend plusieurs ensembles de données
• Fournit un ensemble de données en sortie
  • Non trié
• Utilisé par les requêtes
  • Sur des tables héritées (partitionnement inclus)
  • Ayant des UNION ALL et des UNION
  • Attention que le UNION sans ALL élimine les duplicats, ce qui nécessite une opération supplémentaire de tri

• Append avec optimisation
• Fournit un ensemble de données en sortie trié
• Utilisé par les requêtes
  • UNION ALL ou partitionnement/héritage
  • Utilisant des parcours triés
  • Idéal avec Limit

• Nœud HashSetOp Except
  • instructions EXCEPT et EXCEPT ALL
• Nœud HashSetOp Intersect
  • instructions INTERSECT et INTERSECT ALL

• Prend un ensemble de données en entrée
• Et renvoie un ensemble de données
• Exemples typiques
  • Sort
  • Aggregate
  • Unique
  • Limit
  • InitPlan, SubPlan

• Utilisé pour le ORDER BY
  • Mais aussi DISTINCT, GROUP BY, UNION
  • Les jointures de type Merge Join
• Gros délai de démarrage
• Trois types de tri
  • En mémoire, tri quicksort
  • En mémoire, tri top-N heapsort (si clause LIMIT)
  • Sur disque

• Agrégat complet
• Pour un seul résultat

• Hachage de chaque n-uplet de regroupement (group by)
• accès direct à chaque n-uplet pour appliquer fonction d’agrégat
• Intéressant si l'ensemble des valeurs distinctes tient en mémoire, dangereux sinon

• Reçoit des données déjà triées
• Parcours des données
  • Regroupement du groupe précédent arrivé à une donnée différente

• Reçoit des données déjà triées
• Parcours des données
  • Renvoi de la donnée précédente une fois arrivé à une donnée différente
• Résultat trié

• Permet de limiter le nombre de résultats renvoyés
• Utilisé par
  • clauses LIMIT et OFFSET d'une requête SELECT
  • fonctions min() et max() quand il n'y a pas de clause WHERE et qu'il y a un index
• Le nœud précédent sera de préférence un nœud dont le coût de démarrage est peu élevé (SeqScan, NestedLoop)

• Deux types de supervision
  • occasionnelle
  • automatique
• Superviser le matériel et le système
• Superviser PostgreSQL et ses statistiques
• Utiliser les bons outils

• Supervision occasionnelle système
  • Linux
  • Windows
• Supervision occasionnelle PostgreSQL
• Outils

• Nombreux outils
• Les tester pour les sélectionner

• ps est l’outil de base pour les processus
• Exemples
  • ps aux
  • ps -ef | grep postgres

• Principal intérêt : %CPU et %MEM
• Intérêts secondaires
  • charge CPU
  • consommation mémoire
• Autres outils
  • atop, htop, topas

• Principal intérêt : %IO
• À partir du noyau 2.6.20

• Outil le plus fréquemment utilisé
• Principal intérêt
  • lecture et écriture disque
  • iowait
• Intérêts secondaires
  • nombre de processus en attente

• Une ligne par partition
• Intéressant pour connaître la partition la plus concernée par
  • les lectures
  • ou les écritures

• Outil le plus ancien
• Récupère des statistiques de façon périodique
• Permet de lire les statistiques datant de plusieurs heures, jours, etc.

• Principal intérêt : connaître la répartition de la mémoire

• Gestion des sémaphores
• ipcs pour avoir la liste
• ipcrm pour en supprimer

• Là aussi, nombreux outils
• Les tester pour les sélectionner

• ps et grep en une commande

• Surveillance des processus
• Filtres
• Récupération de la ligne de commande, identificateur de session et utilisateur
• Site officiel

• Semblable à top
• Site officiel

• Semblable à sysstat
• Mais avec plus d’informations
• Et des graphes immédiats

• Plusieurs aspects à surveiller :
  • activité de la base
  • activité sur les tables
  • requêtes SQL
  • écritures

• qui est connecté ?
• qui fait quoi ?
• qui est bloqué ?
• qui bloque les autres ?
• comment arrêter une requête ?

• Liste des processus
  • sessions (backends)
  • processus en tâche de fond (10+)
• Change de définition en 9.2, puis en 9.6
• Requête en cours/dernière exécutée (9.2+)
• IDLE IN TRANSACTION
• Sessions en attente de verrou

• Annuler une requête
  • pg_cancel_backend (pid int)
• Fermer une connexion
  • pg_terminate_backend(pid int)

• Visualisation des verrous en place
• Tous types de verrous sur objets
• Complexe à interpréter
  • verrous sur enregistrements pas directement visibles
  • voir l'article détaillé sur la base de connaissance Dalibo.

• Message dans les traces
  • uniquement pour les attentes de plus d’une seconde
  • paramètre log_lock_waits à on
  • rapport pgBadger disponible

• Des informations globales à chaque base
• Nombre de sessions
• Nombre de transactions validées/annulées
• Nombre d’accès blocs
• Nombre d’accès enregistrements
• Taille et nombre de fichiers temporaires
• Temps d’entrées/sorties

• Message dans les traces
  • à chaque connexion/déconnexion
  • paramètre log_connections et log_disconnections
  • rapport pgBadger disponible

• Quelle taille font mes objets ?
• Quel est leur taux de fragmentation ?
• Comment sont-ils accédés ?

• Table
  • pg_relation_size(relation name)
  • pg_total_relation_size(relation name)
  • pg_table_size(table name)
  • pg_indexes_size(table name)

• Pour un affichage plus lisible
  • pg_size_pretty(size bigint)

• Fragmentation induite par MVCC
  • touche tables et index
• Requêtes pour estimer la fragmentation :
  • Dépôt github
  • supervision avec check_pgactivity
• Mesure précise de la fragmentation :
  • extension pgstattuple

• Statistiques niveau «ligne»
• Nombre de lignes insérées/mises à jour/supprimées
• Type et nombre d’accès
• Opérations de maintenance
• Détection des tables mal indexées ou très accédées

• Vue par index
• Nombre d’accès et efficacité

• Opérations au niveau bloc
• Demandés au système ou trouvés dans le cache de PostgreSQL
• Calculer des hit ratios

• Quelles sont les requêtes lentes ?
• Quelles sont les requêtes les plus fréquentes ?
• Quelles requêtes génèrent des fichiers temporaires ?
• Quelles sont les requêtes bloquées ?
  • et par qui ?
• Progression d’une requête

• log_min_duration_statements =
  • 0 permet de tracer toutes les requêtes
  • trace des paramètres
  • traces exploitables par des outils tiers
  • pas d’informations sur les accès, ni des plans d’exécution
• D’autres paramètres existent mais sont peu intéressants

• log_temp_files = <taille minimale>
  • 0 trace tous les fichiers temporaires
  • associe les requêtes SQL qui les génèrent
  • traces exploitable par des outils tiers

• Extension disponible depuis la 8.4
  • réellement intéressante à partir de la 9.2
• Ajoute une nouvelle vue statistique, appelée pg_stat_statements
• Les requêtes sont normalisées
• Indique les requêtes exécutées, avec durée d’exécution, utilisation du cache, etc.

Métriques intéressantes :

• Durée d’exécution :
  • total_time
  • min_time/max_time (9.5+)
  • stddev_time (9.5+)
  • mean_time (9.5+)
• Nombre de lignes retournées : rows

• Accès à la mémoire partagée
  • shared_blks_hit/read/dirtied/written
• Accès à la mémoire locale (objets dédiés à la session comme les tables temporaires)
  • local_blks_hit/read/dirtied/written
• Lecture/écriture de fichier temporaire
  • temp_blks_read/written
• Temps d’accès en entrée/sortie
  • blk_read_time/blk_write_time

• Vue pg_stat_activity
  • colonnes wait_event et wait_event_type
• Vue pg_locks
  • colonne granted
• Fonction pg_blocking_pids

• API de progression de requêtes
• Utilisé par la commande VACUUM
• À partir de la 9.6

• Quelle quantité de données sont écrites ?
• Quel canal d’écriture est utilisé ?

• log_checkpoints = on
• Affiche des informations à chaque checkpoint :
  • mode de déclenchement
  • volume de données écrits
  • durée du checkpoint
• Trace exploitable par des outils tiers

• Activité des écritures dans les fichiers de données
• Visualisation du volume d’allocations et d’écritures

• Différents outils existent autour de PostgreSQL
• Outils d’analyse occasionnel :
  • pg_activity
• Outils d’analyse des traces :
  • pgbadger
• Outils d’analyse des statistiques :
  • pgCluu, OPM
  • pg_stat_statements, PoWA

• Script Python
• top pour PostgreSQL
• Affiche :
  • les requêtes en cours
  • les sessions bloquées
  • les sessions bloquantes
• Dépôt github

• Script Perl
• Traite les journaux applicatifs
• Recherche des informations sur les requêtes
• Génération d’un rapport HTML très détaillé
• Dépôt github

• 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

• Plate-forme de supervision dédiée à PostgreSQL
  • extensible
  • sondes adaptées à PostgreSQL
  • graphes
  • alertes
• Site officiel
• Site de démonstration
  • opm/demo

• Objectif : identifier les requêtes coûteuses
  • sans devoir accéder aux logs
  • quasi en temps-réel
• Background worker
  • dépendant de pg_stat_statements
  • nécessite PostgreSQL 9.4
• Site officiel

• Un système est pérenne s’il est bien supervisé
• Les système de supervision automatique ont souvent besoin d’être complété
• PostgreSQL fourni énormément d’indicateurs utiles à la supervision
• Les outils de supervision ponctuels sont utiles pour rapidement diagnostiquer l’état d’un serveur