Bonnes pratiques et optimisation

PostgreSQL est une base de données capable de fonctionner dans des environnements ayant des ressources très limitées et partageant ces ressources avec un grand nombre d'autres applications.

Afin d'assurer que PostgreSQL tournera convenablement dans ces environnements, la configuration par défaut est très peu consommatrice de ressources mais inadaptée pour des bases de données spatiales.

Nous allons voir comment configurer le serveur PostgreSQL pour optimiser son fonctionnement pour l'exploitation des données spatiales en modifiant quelques paramètres importants dans le fichier postgresql.conf.

Le fichier postgresql.conf peut être édité et modifié par l'administrateur du serveur dans un éditeur de texte.

Il est également possible, si on dispose des droits de SUPERUSER, à partir de PostgreSQL 9.4 d'utiliser les commandes ALTER SYSTEM

Important :

Les valeurs recommandées ci-dessous pour chaque paramètre de configuration constituent un bon point de départ pour améliorer sensiblement le fonctionnement du serveur pour l'exploitation des données spatiales, mais elles doivent être testées et ajustées pour chaque configuration matérielle du serveur informatique, notamment par rapport à la mémoire vive disponible.

shared_buffers (integer)

Initialise la quantité de mémoire que le serveur de bases de données utilise comme mémoire partagée.

La valeur par défaut, en général 128kB, est insuffisante pour une base de données spatiales en production.

Si vous disposez d'un serveur dédié à la base de données, avec 1 Go de mémoire ou plus, une valeur de départ raisonnable pour ce paramètre est de 25% de la mémoire du système.

PostgreSQL profite aussi du cache du système d'exploitation, il est donc peu probable qu'une allocation de plus de 40% de la mémoire fonctionnera mieux qu'une valeur plus restreinte.

Valeur recommandée : 512MB (en fait 1/4 de la mémoire totale de la machine)

work_mem (integer)

Indique la quantité de mémoire que les opérations de tri interne et les tables de hachage (relations données/empreintes ou signatures des données) peuvent utiliser avant de basculer sur des fichiers disque temporaires.

La valeur par défaut est de 1MB.

Pour une requête complexe, il peut y avoir plusieurs opérations de tri ou de hachage exécutées en parallèle ; chacune peut utiliser de la mémoire à hauteur de cette valeur avant de commencer à placer les données dans des fichiers temporaires sur le disque.

De plus, de nombreuses sessions peuvent exécuter de telles opérations simultanément. La mémoire totale utilisée peut, de ce fait, atteindre plusieurs fois la valeur de work_mem.

Il faut considérer combien de connexions et quelle complexité est attendue dans les requêtes avant d'augmenter cette valeur.

Le bénéfice acquis par l'augmentation de cette valeur est que la plupart des opérations de classification, dont les clauses ORDER BY et DISTINCT, les jointures, les agrégations basées sur les hachages et l'exécution de requête imbriquées, pourront être réalisées sans avoir à passer par un stockage sur disque.

Valeur recommandée : 16MB (on peut mettre plus, par exemple 128MB pour une mémoire totale de 24 Go RAM)

maintenance_work_mem (integer)

Indique la quantité maximale de mémoire que peuvent utiliser les opérations de maintenance telles que VACUUM, CREATE INDEX et ALTER TABLE ADD FOREIGN KEY.

La valeur par défaut est de 16MB.

Puisque seule une de ces opérations peut être exécutée à la fois dans une session et que, dans le cadre d'un fonctionnement normal, peu d'opérations de ce genre sont exécutées concurrentiellement sur une même installation, il est possible d'initialiser cette variable à une valeur bien plus importante que work_mem.

Une grande valeur peut améliorer les performances des opérations VACUUM et de la restauration des sauvegardes.

Valeur recommandée : 128MB (256MB à 1GB pour une mémoire totale de 24 Go RAM)

Write-Ahead Logging (WAL) est une méthode conventionnelle pour s'assurer de l'intégrité des données.

Le concept central du WAL est d'effectuer les changements des fichiers de données (donc les tables et les index) uniquement après que ces changements ont été écrits de façon sûre dans un journal, appelé journal des transactions.

Il n'est pas nécessaire d'écrire les pages de données vers le disque à chaque validation de transaction car, dans l'éventualité d'une défaillance, on pourra récupérer la base de données en utilisant le journal des transactions.

wal_buffers (integer)

Définit la quantité de mémoire partagée utilisée pour les données des journaux de transactions qui n'ont pas encore été écrites sur disque.

Elle indique que les informations, pour annuler les effets d'une opération sur un objet, doivent être écrites dans le journal en mémoire stable avant que l'objet modifié ne migre sur le disque.

Cette règle permet d'assurer l'intégrité des données lors d'une reprise après défaillance. En effet, il suffira de lire le journal pour retrouver l'état de la base lors de son arrêt brutal.

La taille de ce tampon nécessite simplement d'être suffisamment grand pour stocker les données WAL pour une seule transaction.

Alors que la valeur par défaut est généralement suffisante, les données spatiales tendent à être plus volumineuses.

Il est donc recommandé d'augmenter la quantité de mémoire spécifiée dans ce paramètre.

Valeur recommandée : 1MB

checkpoint_segments (integer)

Nombre maximum de journaux de transaction entre deux points de vérification automatique des WAL (chaque segment fait normalement 16 Mo).

Cette valeur définit le nombre maximum de segments des journaux (typiquement 16MB) qui doit être rempli entre chaque point de reprise WAL.

Un point de reprise WAL est une partie d'une séquence de transactions pour lequel on garantit que les fichiers de données ont été mis à jour avec toutes les requêtes précédant ce point.

À ce moment-là toutes les pages sont repérées sur le disque et les points de reprise sont écrits dans le fichier de journal.

Cela permet au processus de reprise après défaillance de trouver les derniers points de reprise et de récupérer l'état des données avant la défaillance.

Étant donné que les points de reprise nécessitent un repérage de toutes le pages ayant été modifiées sur le disque, cela va créer une charge d'entrées/sorties significative.

Le même argument que précédemment s'applique ici pour les données spatiales.

Augmenter cette valeur limitera le nombre de points de reprise, mais impliquera un redémarrage plus lent en cas de défaillance.

La valeur par défaut est de 3 segments.

Dans les phases d'import de référentiel volumineux ou les opérations de remontée de dump conséquent, ce qui constitue la grosse part du travail a l'initialisation de la base le paramètre peut être porté à beaucoup plus (exemple 128*16MB soir 2 GB). Cela signifie que postgres n'exécute pas l'écriture physique tant qu'il n'a pas atteint 2GB de donnés à écrire. Ceci afin de ne pas faire travailler les disques pour des petites opérations en usage courant mais bien par block de tâche à exécuter. Par ailleurs, même si 2GB ne sont pas écrits, si le paramètre checkpoint_timeout est par défaut à 5min, postgresql écrira soit tous les 2GB soit toutes les 5 minutes, le premier de l'un des deux termes atteint.

Valeur recommandée : 10 (et jusqu'à 128 pour 24 Go de RAM totale, en particulier en cas d’imports volumineux).

random_page_cost (floating point)

Initialise l'estimation faite par le planificateur du coût de récupération non-séquentielle d'une page disque.

Mesurée comme un multiple du coût de récupération d'une page séquentielle, sa valeur par défaut est 4.0

Cette valeur sans unité représente le coût d'accès aléatoire à une page du disque.

Cette valeur est relative aux autres paramètres de coût notamment l'accès séquentiel aux pages, et le coût des opérations processeur.

Bien qu'il n'y ait pas de valeur magique ici, la valeur par défaut peut généralement être optimisée.

Réduire cette valeur par rapport au paramètre seq_page_cost incite le système à privilégier les parcours d'index.

L'augmenter donne l'impression de parcours d'index plus coûteux.

Valeur recommandée : 2.0

effective_cache_size (integer)

Initialise l'estimation faite par le planificateur de la taille réelle du cache disque disponible pour une requête.

Ce paramètre est lié à l'estimation du coût d'utilisation d'un index : une valeur importante favorise les parcours d'index, une valeur faible les parcours séquentiels.

Ce paramètre n'a pas d'influence sur la taille de la mémoire partagée allouée par PostgreSQL et ne réserve pas non plus le cache disque du noyau, il n'a qu'un rôle estimatif. Le système ne suppose pas non plus que les données reste dans le cache du disque entre des requêtes.

La valeur par défaut est de 128MB.

Valeur recommandée : 512MB (peut être porté à 2/3 de la RAM totale pour un serveur dédié).

Il est recommandé d'activer le journal des opérations.

Attention cependant car le fichier de log doit être analysé et nettoyé régulièrement.

logging_collector = on

On se reportera à la documentation de PostgreSQL pour la gestion détaillée.

il est possible d'activer un mécanisme de rotation des fichiers de log avec log_truncate_on_rotation.

De nombreux paramètres permettent de définir les événements à tracer. La mise au point est à affiner avec l'administrateur système.