PostgreSQL

Postgresql tips and recipes¶

This section contains advice on postgresql database server tuning, performing backups and working with read replicas.

Optimisation des performances de PostgreSQL¶

Le réglage de PostgreSQL est nécessaire pour obtenir un système performant, mais facultatif pour le fonctionnement de DHIS2. Les différents paramètres peuvent être spécifiés dans le fichier de configuration postgresql.conf ou, de préférence, dans un fichier spécifique du répertoire conf.d'. Les paramètres sont basés sur l'allocation de 8 Go de RAM à PostgreSQL et doivent être ajustés en fonction de l'environnement.

sudo nano /etc/postgresql/12/main/postgresql.conf

Définissez les propriétés suivantes.

jit = off

This is important to set for postgresql versions 12 and greater. The jit compiler functionality causes a significant slowdown on many DHIS2 specific queries, eg Program Indicator queries. For versions 11 and below, the setting is off by default.

max_connections = 200

Détermine le nombre maximum de connexions autorisées par PostgreSQL.

shared_buffers = 3GB

Détermine la quantité de mémoire à allouer exclusivement à la mise en cache de PostgreSQL. Ce paramètre contrôle la taille de la mémoire partagée du noyau qui doit être réservée à PostgreSQL. Il doit être fixé à environ 40% de la mémoire totale dédiée à PostgreSQL.

work_mem = 24MB

Détermine la quantité de mémoire utilisée pour les opérations internes de tri et de hachage. Ce paramètre s'applique à chaque connexion et à chaque requête, de sorte qu'une grande quantité de mémoire peut être consommée si cette valeur est trop élevée. Il est essentiel de définir correctement cette valeur pour optimiser les performances d'agrégation de DHIS2.

maintenance_work_mem = 1GB

Détermine la quantité de mémoire que PostgreSQL peut utiliser pour les opérations de maintenance telles que la création d'index, l'exécution du vacuum et l'ajout de clés étrangères. Augmenter cette valeur peut améliorer les performances de création d'index pendant les processus de génération d'analyses.

temp_buffers = 16MB

Définit le nombre maximum d'objets temporaires utilisés par chaque session de la base de données. Il s'agit d'objets locaux utilisés uniquement pour l'accès aux tableaux temporaires.

effective_cache_size = 8GB

Estimation de la quantité de mémoire disponible pour la mise en cache du disque par le système d'exploitation (il ne s'agit pas d'une allocation) ; elle est utilisée par PostgreSQL pour déterminer si un plan de requête tiendra ou non dans la mémoire. Définir une valeur plus élevée que la mémoire disponible entraînera de mauvaises performances. Cette valeur doit être incluse dans le paramètre shared_buffers. PostgreSQL a deux couches de cache : La première couche utilise la mémoire partagée du noyau et est contrôlée par le paramètre shared_buffers. PostgreSQL délègue la seconde couche au cache disque du système d'exploitation et la taille de la mémoire disponible peut être fournie avec le paramètre effective_cache_size.

checkpoint_completion_target = 0.8

Définit la mémoire utilisée pour la mise en mémoire tampon pendant le processus d'écriture WAL. Augmenter cette valeur peut améliorer le débit dans les systèmes à forte densité d'écriture.

synchronous_commit = off

Spécifie si les transactions doivent attendre que les enregistrements WAL soient écrits sur le disque avant d'être renvoyées au client ou non. Si cette option est désactivée, les performances seront considérablement améliorées. Cela implique également qu'il y aura un léger décalage entre le moment où la transaction au client est déclarée réussie et le moment où elle est réellement sûre, mais l'état de la base de données ne peut pas être corrompu et c'est une bonne alternative pour les systèmes exigeants en termes de performances et d'écriture comme DHIS2.

wal_writer_delay = 10s

Spécifie le décalage entre les opérations d'écriture WAL. Y définir une valeur élevée permettra d'améliorer les performances des systèmes à forte densité d'écriture, car de nombreuses opérations d'écriture peuvent être exécutées en un seul vidage sur le disque.

random_page_cost = 1.1

SSD uniquement: Définit l'estimation par le planificateur de requêtes du coût d'une page de disque non extraite de manière séquentielle. Une valeur faible incitera le système à préférer les scans d'index aux scans séquentiels. Une valeur faible convient aux bases de données qui fonctionnent sur des disques SSD ou qui sont massivement mises en cache dans la mémoire. La valeur par défaut est 4.0, ce qui est raisonnable pour les disques traditionnels.

max_locks_per_transaction = 96

Spécifie le nombre moyen de verrous d'objets alloués pour chaque transaction. Cette valeur est principalement définie pour permettre la réalisation des mises à niveau de routine qui touchent un grand nombre de tableaux.

track_activity_query_size = 8192

Spécifie le nombre d'octets réservés au suivi de la commande en cours d'exécution pour chaque session active. Elle permet d'afficher la chaîne de requête complète pour la surveillance des requêtes en cours d'exécution.

Redémarrez PostgreSQL en appelant la commande suivante :

sudo systemctl restart postgresql

Configuration de la base de données réplica en lecture¶

DHIS 2 permet d'utiliser des réplicas en lecture seule de la base de données principale (la base de données principale de DHIS 2). L'objectif des réplicas en lecture est d'améliorer les performances des requêtes en lecture de la base de données et d'augmenter les capacités au-delà des contraintes d'une seule base de données. Les opérations à forte intensité de lecture en bénéficieront, notamment les requêtes d'analyse et les requêtes d'événements.

La configuration exige que vous ayez créé une ou plusieurs instances répliquées de la base de données principale de DHIS 2. PostgreSQL permet de le faire grâce à un concept appelé réplication en flux. La configuration des réplicas en lecture pour PostgreSQL n'est pas abordée dans ce guide.

Les réplicas en lecture peuvent être définis dans le fichier de configuration dhis.conf. Vous pouvez en spécifier jusqu'à 5 par instance DHIS 2. Chaque réplica est désignée par un nombre compris entre 1 et 5. L'URL de connexion JDBC doit être définie pour chaque réplica. Le nom d'utilisateur et le mot de passe peuvent être spécifiés. Dans le cas contraire, le nom d'utilisateur et le mot de passe de la base de données principale seront utilisés en lieu et place.

La configuration des réplicas en lecture dans dhis.conf ressemble à celle ci-dessous. Chaque réplica est spécifié avec le préfixe de la clé de configuration readN, où N fait référence au numéro du réplica.

# Configuration du réplica en lecture 1

# URL de connexion à la base de données, nom d'utilisateur et mot de passe
read1.connection.url = jdbc:postgresql://127.0.0.11/dbread1
read1.connection.username = dhis
read1.connection.password = xxxx

# Configuration du réplica en lecture 2

# URL de connexion à la base de données, nom d'utilisateur et mot de passe
read2.connection.url = jdbc:postgresql://127.0.0.12/dbread2
read2.connection.username = dhis
read2.connection.password = xxxx

# Configuration du réplica en lecture 3

# URL de connexion à la base de données, retour à la base de données principale pour obtenir  le nom d'utilisateur et le mot de passe
read3.connection.url = jdbc:postgresql://127.0.0.13/dbread3

Vous devez redémarrer votre conteneur de servlets pour que les modifications s'appliquent. DHIS 2 répartira automatiquement la charge entre les réplicas en lecture. L'ordre des réplicas n'a pas d'importance.

Travailler avec la base de données PostgreSQL¶

Les opérations courantes lors de la gestion d'une instance DHIS2 sont le vidage et la restauration des bases de données. Notez que lorsque vous effectuez des sauvegardes de la base de données DHIS 2, il est préférable d'exclure les tableaux générés par le système, telles que les tableaux de ressources et d'analyses. Pour effectuer un vidage (copie) de votre base de données dans un fichier, vous pouvez appeler la commande suivante :

pg_dump {database} -U {user} -T "_*" -T "analytics*"  -f {filename}

pg_dump dhis2 -U dhis -T "analytics*" -T "_*" -f dhis2.sql

La compression du fichier de sortie est recommandée. Avec gzip, cette compression peut être fait comme ceci :

pg_dump dhis2 -U dhis -T "analytics*" -T "_*" | gzip > dhis2.sql.gz

Pour restaurer la copie de la base de données sur un autre système, vous devez d'abord créer une base de données vide tel que décrit dans la section sur l'installation. Vous devez également décompresser la copie à l'aide de la commande gunzip si vous aviez créé une version compressée. Pour restaurer la copie, vous pouvez appeler la commande suivante :

psql -d dhis2 -U dhis -f dhis2.sql