Paramètres serveur dans Azure Database pour PostgreSQL - Serveur flexible

S’APPLIQUE À : Azure Database pour PostgreSQL – Serveur flexible

Azure Database pour PostgreSQL fournit un sous-ensemble de paramètres configurables pour chaque serveur. Pour plus d’informations sur les paramètres Postgres, consultez la Documentation PostgreSQL.

Types de paramètres

Azure Database pour PostgreSQL : serveur flexible est pré-configuré avec des paramètres par défaut optimaux pour chaque paramètre. Les paramètres sont classés dans l’un des types suivants :

• Statique : ces paramètres nécessitent un redémarrage du serveur pour implémenter toutes les modifications.
• Dynamique : ces paramètres peuvent être modifiés sans avoir à redémarrer l’instance du serveur. Toutefois, les modifications s’appliquent uniquement aux nouvelles connexions établies après la modification.
• Lecture seule : ces paramètres ne sont pas configurables par l’utilisateur en raison de leur rôle essentiel pour maintenir la fiabilité, la sécurité ou d’autres aspects opérationnels du service.

Pour déterminer le type de paramètre, accédez au Portail Azure et ouvrez le volet Paramètres de serveur. Les paramètres sont regroupés en onglets pour faciliter l’identification.

Personnalisation des paramètres

Différentes méthodes et niveaux sont disponibles pour personnaliser vos paramètres en fonction de vos besoins spécifiques.

Niveau global

Pour modifier les paramètres globalement au niveau de l’instance ou du serveur, accédez au volet Paramètres de serveur dans le Portail Azure. Vous pouvez également utiliser d’autres outils disponibles tels que l’interface de ligne de commande Azure, l’API REST, les modèles Azure Resource Manager ou les outils partenaires.

Remarque

Étant donné qu’Azure Database pour PostgreSQL est un service de base de données managé, les utilisateurs n’ont pas accès à l’hôte ni au système d’exploitation pour afficher ou modifier des fichiers de configuration tels que postgresql.conf. Le contenu des fichiers est automatiquement mis à jour en fonction des modifications apportées aux paramètres que vous apportez.

Niveaux granulaires

Vous pouvez ajuster les paramètres à des niveaux plus granulaires. Ces ajustements remplacent les valeurs définies globalement. Leur étendue et leur durée dépendent du niveau auquel vous les faites :

• Niveau de la base de données : utilisez la commande ALTER DATABASE pour les configurations spécifiques à la base de données.

• Rôle ou niveau utilisateur : utilisez la commande ALTER USER pour les paramètres centrés sur l’utilisateur.

• Fonction, niveau de procédure : lorsque vous définissez une fonction ou une procédure, vous pouvez spécifier ou modifier les paramètres de configuration qui seront définis lors de l’appel de la fonction.

• Niveau de la table : par exemple, vous pouvez modifier les paramètres liés à l’autovacuum à ce niveau.

• Niveau de session : pendant la durée d’une session de base de données individuelle, vous pouvez ajuster des paramètres spécifiques. PostgreSQL facilite cet ajustement avec les commandes SQL suivantes :

  • Utilisez la commande SET pour effectuer des ajustements spécifiques à la session. Ces modifications constituent les paramètres par défaut pour la session en cours. L’accès à ces modifications peut nécessiter des privilèges SET spécifiques, et les limitations pour les paramètres modifiables et en lecture seule décrits précédemment ne s’appliquent pas. La fonction SQL correspondante est set_config(setting_name, new_value, is_local).
  • Utilisez la commande SHOW pour examiner les paramètres existants. Son équivalent de fonction SQL est current_setting(setting_name text).

Paramètres importants

Les sections suivantes décrivent certains des paramètres.

shared_buffers

Attribut	Valeur
Valeur par défaut	25 % de la RAM totale
Valeur autorisée	10 à 75 % de la RAM totale
Type	statique
Level	Global
Notes spécifiques à Azure	Le paramètre shared_buffers est mis à l’échelle de façon linéaire (approximativement) à mesure que les vCores augmentent dans un niveau.

Description

Le paramètre de configuration shared_buffers détermine la quantité de mémoire système allouée à la base de données PostgreSQL pour la mise en mémoire tampon des données. Il sert de réserve de mémoire centralisée accessible à tous les processus de la base de données.

Lorsque des données sont nécessaires, le processus de base de données vérifie d'abord la mémoire tampon partagée. Si les données requises sont présentes, elles sont rapidement récupérées et contournent une lecture de disque plus longue. En servant d’intermédiaire entre les processus de base de données et le disque, shared_buffers réduit efficacement le nombre d’opérations d’E/S requises.

huge_pages

Attribut	Valeur
Valeur par défaut	TRY
Valeur autorisée	TRY, ON, OFF
Type	statique
Level	Global
Notes spécifiques à Azure	Pour les serveurs avec quatre vCores ou plus, d'énormes pages sont automatiquement allouées à partir du système d'exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs ayant moins de quatre vCores. Le nombre de pages volumineuses est automatiquement ajusté si des paramètres de mémoire partagée sont modifiés, y compris les modifications apportées à shared_buffers.

Description

Les pages volumineuses sont une fonctionnalité qui permet de gérer la mémoire dans des blocs plus volumineux. Vous pouvez généralement gérer des blocs allant jusqu’à 2 Mo, par opposition aux pages standard de 4 Ko.

L’utilisation de pages volumineuses peut offrir des avantages en matière de performances qui déchargent efficacement le processeur :

• Elles réduisent la surcharge associée aux tâches de gestion de la mémoire, comme moins de mémoire tampon de recherche de traduction (TLB) manquent.
• Elles réduisent le temps nécessaire à la gestion de la mémoire.

Plus précisément, dans PostgreSQL, vous pouvez utiliser des pages volumineuses uniquement pour la zone de mémoire partagée. Une partie importante de la zone de mémoire partagée est allouée pour les mémoires tampons partagées.

Un autre avantage est que les pages de grande taille empêchent le transfert de la zone de mémoire partagée sur le disque, ce qui stabilise davantage les performances.

Recommandations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation des pages volumineuses peut compromettre les performances.
• Si vous commencez par un serveur plus petit qui ne prend pas en charge les pages volumineuses, mais que vous prévoyez un scale-up vers un serveur qui le fait, conservez le paramètre huge_pages sur TRY pour une transition transparente et des performances optimales.

work_mem

Attribut	Valeur
Valeur par défaut	4MB
Valeur autorisée	4MB-2GB
Type	Dynamique
Level	Global et granulaire

Description

Le paramètre work_mem dans PostgreSQL contrôle la quantité de mémoire allouée pour certaines opérations internes dans la zone de mémoire privée de chaque session de base de données. Par exemple, ces opérations sont le tri et le hachage.

Contrairement aux mémoires tampons partagées, qui se trouvent dans la zone de mémoire partagée, work_mem est alloué dans un espace mémoire privé par session ou par requête. En définissant une taille work_mem adéquate, vous pouvez améliorer considérablement l’efficacité de ces opérations et réduire la nécessité d’écrire des données temporaires sur disque.

Points clés

• Mémoire de connexion privée : work_mem fait partie de la mémoire privée utilisée par chaque session de base de données. Cette mémoire est distincte de la zone de mémoire partagée que shared_buffers utilise.
• Utilisation spécifique aux requêtes : toutes les sessions ou requêtes n’utilisent pas work_mem. Les requêtes simples comme SELECT 1 ne sont pas susceptibles de nécessiter work_mem. Toutefois, des requêtes complexes impliquant des opérations telles que le tri ou le hachage peuvent consommer un ou plusieurs blocs de work_mem.
• Opérations parallèles : pour les requêtes qui s’étendent sur plusieurs back-ends parallèles, chaque back-end peut potentiellement utiliser un ou plusieurs blocs de work_mem.

Supervision et ajustement de work_mem

Il est essentiel de superviser en permanence les performances de votre système et d’ajuster work_mem si nécessaire, principalement si les délais d’exécution des requêtes liées au tri ou aux opérations de hachage sont lents. Voici des façons de superviser les performances à l’aide d’outils disponibles dans le Portail Azure :

• Analyse des performances des requêtes : vérifiez l’onglet Principales requêtes par fichiers temporaires pour identifier les requêtes qui génèrent des fichiers temporaires. Cette situation suggère une nécessité potentielle d’augmenter work_mem.
• Guides de résolution des problèmes : utilisez l’onglet Fichiers temporaires élevés dans les guides de résolution des problèmes pour identifier les requêtes problématiques.

Ajustement granulaire

Lors de la gestion du paramètre work_mem, il est souvent plus efficace d’adopter une approche d’ajustement granulaire plutôt que de définir une valeur globale. Cette approche garantit que vous allouez de manière judicieuse la mémoire en fonction des besoins spécifiques des processus et des utilisateurs. Elle réduit également le risque de rencontrer des problèmes de mémoire insuffisante. Voici comment procéder :

• Niveau utilisateur : si un utilisateur spécifique est principalement impliqué dans des tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la valeur work_mem de cet utilisateur. Utilisez la commande ALTER ROLE pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction ou de procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires importants, l’augmentation de la valeur work_mem au niveau d’une fonction ou d’une procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer plus de mémoire à ces opérations.

• Niveau de la base de données : modifiez work_mem au niveau de la base de données si seules des bases de données spécifiques génèrent un nombre élevé de fichiers temporaires.

• Niveau global : si une analyse de votre système révèle que la plupart des requêtes génèrent de petits fichiers temporaires, tandis que seules quelques-unes créent des fichiers volumineux, il peut être prudent d’augmenter globalement la valeur work_mem. Cette action facilite la plupart des requêtes à traiter en mémoire, ce qui vous permet d’éviter les opérations sur disque et d’améliorer l’efficacité. Toutefois, soyez toujours prudent et supervisez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’elle peut gérer l’augmentation de la valeur work_mem.

Détermination de la valeur work_mem minimale pour les opérations de tri

Pour rechercher la valeur minimale de work_mem pour une requête spécifique, en particulier celle qui génère des fichiers de disque temporaires pendant le processus de tri, commencez par prendre en compte la taille de fichier temporaire générée pendant l’exécution de la requête. Par exemple, si une requête génère un fichier temporaire de 20 Mo :

1. Connectez-vous à votre base de données en utilisant psql ou votre client PostgreSQL préféré.
2. Définissez une valeur work_mem initiale légèrement supérieure à 20 Mo pour tenir compte des en-têtes supplémentaires lors du traitement en mémoire. Utilisez une commande telle que : SET work_mem TO '25MB'.
3. Exécutez EXPLAIN ANALYZE sur la requête problématique sur la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". Si elle indique "external merge Disk: xkB", augmentez la valeur work_mem de manière incrémentielle et retestez jusqu’à ce que "quicksort Memory" apparaisse. L’apparence de "quicksort Memory" signale que la requête fonctionne désormais en mémoire.
5. Après avoir déterminé la valeur par le biais de cette méthode, vous pouvez l’appliquer globalement ou à des niveaux plus granulaires (comme décrits précédemment) pour répondre à vos besoins opérationnels.

maintenance_work_mem

Attribut	Valeur
Valeur par défaut	Dépendant de la mémoire du serveur
Valeur autorisée	1MB-2GB
Type	Dynamique
Level	Global et granulaire

Description

maintenance_work_mem est un paramètre de configuration dans PostgreSQL. Il régit la quantité de mémoire allouée pour les opérations de maintenance, telles que VACUUM, CREATE INDEX et ALTER TABLE. Contrairement à work_mem, qui affecte l’allocation de mémoire pour les opérations de requête, maintenance_work_mem est réservé aux tâches qui gèrent et optimisent la structure de la base de données.

Points clés

• Limite de mémoire vide : si vous souhaitez accélérer le nettoyage des tuples morts en augmentant maintenance_work_mem, sachez que VACUUM a une limitation intégrée pour collecter des identificateurs de tuples morts. Il ne peut utiliser que 1 Go de mémoire pour ce processus.
• Séparation de la mémoire pour le nettoyage automatique : le paramètre autovacuum_work_mem vous permet de contrôler la mémoire utilisée par les opérations de nettoyage automatique indépendamment. Ce paramètre agit comme un sous-ensemble de maintenance_work_mem. Vous pouvez décider de la quantité de mémoire utilisée par le nettoyage automatique sans affecter l’allocation de mémoire pour d’autres tâches de maintenance et opérations de définition de données.

Étapes suivantes

Pour plus d’informations sur les extensions PostgreSQL prises en charge, consultez Extensions PostgreSQL dans Azure Database pour PostgreSQL – Serveur flexible.