Utilisation des ressources / Mémoire

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

commit_timestamp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache d’horodatage de validation. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	commit_timestamp_buffers

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiples de « work_mem » à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	2
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Utilisation de pages volumineuses sur Linux ou Windows.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

huge_page_size

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Taille d’une page volumineuse qui doit être demandée.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	huge_page_size

io_combine_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Limitez la taille des lectures et des écritures de données.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	1-128
Type de paramètre	dynamic
Documentation	io_combine_limit

io_max_combine_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Limite à l’échelle du serveur qui contraint io_combine_limit.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	1-128
Type de paramètre	dynamic
Documentation	io_max_combine_limit

io_max_concurrency

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Nombre maximal d’E/S qu’un processus peut exécuter simultanément.
Type de données	entier
Valeur par défaut	64
Valeurs autorisées	-1-1024
Type de paramètre	statique
Documentation	io_max_concurrency

io_method

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne la méthode pour l’exécution d’E/S asynchrones.
Type de données	enumeration
Valeur par défaut	worker
Valeurs autorisées	worker,sync
Type de paramètre	statique
Documentation	io_method

io_workers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Nombre de processus de travail d’I/O, pour io_method=worker.
Type de données	entier
Valeur par défaut	3
Valeurs autorisées	1-32
Type de paramètre	dynamic
Documentation	io_workers

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique. Cette quantité de mémoire peut être utilisée par chaque mémoire tampon de réorganisation interne avant de se déverser sur le disque.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance. Cela inclut des opérations telles que VACUUM et CREATE INDEX.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

min_dynamic_shared_memory

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Quantité de mémoire partagée dynamique réservée au démarrage.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	mémoire_partagée_dynamique_minimale

multixact_member_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de membres MultiXact.
Type de données	entier
Valeur par défaut	32
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	multixact_member_buffers

multixact_offset_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de décalage MultiXact.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	multixact_offset_buffers

notify_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de messages LISTEN/NOTIFY.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	notify_buffers

serializable_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de transactions sérialisable.
Type de données	entier
Valeur par défaut	32
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	serializable_buffers

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

subtransaction_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de sous-transaction. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	subtransaction_buffers

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisées par chaque session.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

transaction_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache d’état des transactions. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	transaction_buffers

vacuum_buffer_usage_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons pour VACUUM, ANALYZE et autovacuum.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	0-16777216
Type de paramètre	dynamic
Documentation	vacuum_buffer_usage_limit (limite d'utilisation du tampon de vide)

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les espaces de travail de requête. Cette quantité de mémoire peut être utilisée par chaque opération de tri interne et table de hachage avant de basculer vers des fichiers de disque temporaires.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

commit_timestamp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache d’horodatage de validation. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	commit_timestamp_buffers

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiples de « work_mem » à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	2
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Utilisation de pages volumineuses sur Linux ou Windows.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

huge_page_size

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Taille d’une page volumineuse qui doit être demandée.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	huge_page_size

io_combine_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Limitez la taille des lectures et des écritures de données.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	16
Type de paramètre	lecture seule
Documentation	io_combine_limit

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique. Cette quantité de mémoire peut être utilisée par chaque mémoire tampon de réorganisation interne avant de se déverser sur le disque.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance. Cela inclut des opérations telles que VACUUM et CREATE INDEX.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

min_dynamic_shared_memory

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Quantité de mémoire partagée dynamique réservée au démarrage.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	mémoire_partagée_dynamique_minimale

multixact_member_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de membres MultiXact.
Type de données	entier
Valeur par défaut	32
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	multixact_member_buffers

multixact_offset_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de décalage MultiXact.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	multixact_offset_buffers

notify_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de messages LISTEN/NOTIFY.
Type de données	entier
Valeur par défaut	16
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	notify_buffers

serializable_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de transactions sérialisable.
Type de données	entier
Valeur par défaut	32
Valeurs autorisées	16-131072
Type de paramètre	statique
Documentation	serializable_buffers

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

subtransaction_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache de sous-transaction. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	subtransaction_buffers

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisées par chaque session.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

transaction_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons dédié utilisé pour le cache d’état des transactions. Spécifiez 0 pour que cette valeur soit déterminée sous la forme d’une fraction de shared_buffers.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	0-131072
Type de paramètre	statique
Documentation	transaction_buffers

vacuum_buffer_usage_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons pour VACUUM, ANALYZE et autovacuum.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	0-16777216
Type de paramètre	dynamic
Documentation	vacuum_buffer_usage_limit (limite d'utilisation du tampon de vide)

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les espaces de travail de requête. Cette quantité de mémoire peut être utilisée par chaque opération de tri interne et table de hachage avant de basculer vers des fichiers de disque temporaires.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiple de work_mem à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	2
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

huge_page_size

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Taille d’une page volumineuse qui doit être demandée.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	huge_page_size

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

min_dynamic_shared_memory

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Quantité de mémoire partagée dynamique réservée au démarrage.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	mémoire_partagée_dynamique_minimale

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

vacuum_buffer_usage_limit

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la taille du pool de mémoires tampons pour VACUUM, ANALYZE et autovacuum.
Type de données	entier
Valeur par défaut	256
Valeurs autorisées	0-16777216
Type de paramètre	dynamic
Documentation	vacuum_buffer_usage_limit (limite d'utilisation du tampon de vide)

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiple de work_mem à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	2
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

huge_page_size

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Taille d’une page volumineuse qui doit être demandée.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	huge_page_size

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

min_dynamic_shared_memory

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Quantité de mémoire partagée dynamique réservée au démarrage.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	mémoire_partagée_dynamique_minimale

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiple de work_mem à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	1
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

huge_page_size

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Taille d’une page volumineuse qui doit être demandée.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	huge_page_size

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

min_dynamic_shared_memory

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Quantité de mémoire partagée dynamique réservée au démarrage.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0
Type de paramètre	lecture seule
Documentation	mémoire_partagée_dynamique_minimale

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiple de work_mem à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	1
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

logical_decoding_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour le décodage logique.
Type de données	entier
Valeur par défaut	65536
Valeurs autorisées	64-2147483647
Type de paramètre	dynamic
Documentation	logical_decoding_work_mem

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

hash_mem_multiplier

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Multiple de work_mem à utiliser pour les tables de hachage.
Type de données	numérique
Valeur par défaut	1
Valeurs autorisées	1-1000
Type de paramètre	dynamic
Documentation	hash_mem_multiplier

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

type_de_mémoire_partagée

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée utilisée pour la région de mémoire partagée principale.
Type de données	enumeration
Valeur par défaut	mmap
Valeurs autorisées	mmap
Type de paramètre	lecture seule
Documentation	type_mémoire_partagée

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.

autovacuum_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser par chaque processus Worker de nettoyage automatique.
Type de données	entier
Valeur par défaut	-1
Valeurs autorisées	-1-2097151
Type de paramètre	dynamic
Documentation	autovacuum_work_mem

dynamic_shared_memory_type

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Sélectionne l’implémentation de mémoire partagée dynamique utilisée.
Type de données	enumeration
Valeur par défaut	posix
Valeurs autorisées	posix
Type de paramètre	lecture seule
Documentation	dynamic_shared_memory_type

pages géantes

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Active/désactive l’utilisation des pages de mémoire volumineuses. Ce paramètre n’est pas applicable aux serveurs ayant moins de 4 cœurs virtuels.
Type de données	enumeration
Valeur par défaut	try
Valeurs autorisées	on,off,try
Type de paramètre	statique
Documentation	huge_pages

Descriptif

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.
• Ils 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 volumineuses empêchent l’échange de la zone de mémoire partagée vers le disque, ce qui stabilise davantage les performances.

Recommendations

• Pour les serveurs qui ont des ressources de mémoire significatives, évitez de désactiver les pages volumineuses. La désactivation d’énormes pages peut compromettre les performances.
• Si vous commencez avec un serveur plus petit qui ne prend pas en charge les grandes pages, mais que vous prévoyez une mise à l'échelle vers un serveur qui les prend en charge, maintenez le paramètre huge_pages pour assurer une transition transparente et des performances optimales.

Notes spécifiques à Azure

Pour les serveurs avec quatre vCores ou plus, les pages volumineuses sont automatiquement allouées à partir du système d’exploitation sous-jacent. La fonctionnalité n’est pas disponible pour les serveurs avec 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.

maintenance_work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la mémoire maximale à utiliser pour les opérations de maintenance comme VACUUM ou Create Index.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	1024-2097151
Type de paramètre	dynamic
Documentation	maintenance_work_mem

Descriptif

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 INDEXet 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ée aux tâches qui maintiennent et optimisent la structure de la base de données.

![REMARQUE] Des valeurs maintenance_work_mem trop agressives peuvent périodiquement provoquer des erreurs de mémoire insuffisante dans le système. Il est extrêmement important de comprendre la quantité de mémoire disponible sur le serveur et le nombre d’opérations simultanées susceptibles d’allouer de la mémoire pour les tâches décrites précédemment, avant d’apporter des modifications à ce paramètre.

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 : vous pouvez utiliser le autovacuum_work_mem paramètre pour contrôler la mémoire que les opérations de nettoyage automatique utilisent 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 l'autovacuum sans affecter l'allocation de mémoire pour d'autres tâches de maintenance et la définition des données.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur maintenance_work_mem est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Toute modification ultérieure de la sélection de produit au calcul qui prend en charge le serveur flexible n’aura aucun effet sur la valeur par défaut pour le paramètre de serveur maintenance_work_mem de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du maintenance_work_mem paramètre en fonction des valeurs de la formule suivante.

La formule utilisée pour calculer la valeur de maintenance_work_mem est (long)(82.5 * ln(memoryGiB) + 40) * 1024.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	maintenance_work_mem
2 Gio	99 328 Kib
4 Gio	157 696 Kib
8 Gio	216 064 Kib
16 Gio	274 432 Kib
32 Gio	332 800 KiB
48 Gio	367 616 Kib
64 Gio	392 192 KiB
80 Gio	410 624 Kib
128 Gio	450 560 Kib
160 Gio	468 992 KiB
192 Gio	484 352 Kio
256 Gio	508 928 Kib
384 Gio	542 720 KiB
432 Gio	552 960 KiB
672 Gio	590 848 KiB

max_prepared_transactions

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de transactions préparées simultanément. Lors de l’exécution d’un serveur réplica, vous devez définir ce paramètre sur la même valeur que celle du serveur primaire.
Type de données	entier
Valeur par défaut	0
Valeurs autorisées	0-262143
Type de paramètre	statique
Documentation	max_prepared_transactions

max_stack_depth

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la profondeur maximale de la pile, en kilo-octets.
Type de données	entier
Valeur par défaut	2048
Valeurs autorisées	2048
Type de paramètre	lecture seule
Documentation	max_stack_depth

shared_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre de mémoires tampons de mémoire partagée utilisées par le serveur. L’unité est de 8 Ko. Les valeurs autorisées se trouvent dans une plage de 10 % à 75 % de la mémoire disponible.
Type de données	entier
Valeur par défaut	Dépend des ressources (cœurs virtuels, RAM ou espace disque) allouées au serveur.
Valeurs autorisées	16-1073741823
Type de paramètre	statique
Documentation	shared_buffers

Descriptif

Le shared_buffers paramètre de configuration 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 pool de mémoire centralisé accessible à tous les processus de 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. Les mémoires tampons partagées servent d’intermédiaire entre les processus de base de données et le disque et réduisent efficacement le nombre d’opérations d’E/S requises.

Notes spécifiques à Azure

La valeur par défaut du paramètre de serveur shared_buffers est calculée lorsque vous approvisionnez l’instance du serveur flexible Azure Database pour PostgreSQL, en fonction du nom du produit que vous sélectionnez pour son calcul. Les modifications ultérieures de la sélection de produits pour le calcul qui prend en charge le serveur flexible n'ont aucun effet sur la valeur par défaut du paramètre de serveur de cette instance.

Chaque fois que vous modifiez le produit affecté à une instance, vous devez également ajuster la valeur du shared_buffers paramètre en fonction des valeurs des formules suivantes.

Pour les machines virtuelles avec jusqu’à 2 Gio de mémoire, la formule utilisée pour calculer la valeur est shared_buffersmemoryGib * 16384.

Pour les machines virtuelles avec plus de 2 Gio, la formule utilisée pour calculer la valeur de shared_buffers est memoryGib * 32768.

Compte tenu de la formule précédente, le tableau suivant liste les valeurs affectées à ce paramètre de serveur en fonction de la quantité de mémoire approvisionnée :

Taille de la mémoire	shared_buffers
2 Gio	32 768
4 Gio	131 072
8 Gio	262144
16 Gio	524288
32 Gio	1048576
48 Gio	1572864
64 Gio	2097152
80 Gio	2621440
128 Gio	4 194 304
160 Gio	5242880
192 Gio	6291456
256 Gio	8388608
384 Gio	12582912
432 Gio	14155776
672 Gio	22020096

temp_buffers

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit le nombre maximal de mémoires tampons temporaires utilisés par chaque session de base de données.
Type de données	entier
Valeur par défaut	1024
Valeurs autorisées	100-1073741823
Type de paramètre	dynamic
Documentation	temp_buffers

work_mem

Caractéristique	Valeur
Catégorie	Utilisation des ressources / Mémoire
Descriptif	Définit la quantité de mémoire à utiliser par les opérations de tri internes et les tables de hachage avant d'écrire dans des fichiers de disque temporaire.
Type de données	entier
Valeur par défaut	4096
Valeurs autorisées	4096-2097151
Type de paramètre	dynamic
Documentation	work_mem

Descriptif

Le work_mem paramètre 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 sont allouées dans un espace mémoire privé par session ou par requête. En définissant une taille adéquate work_mem , 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 qui 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 celles-ci SELECT 1 sont peu susceptibles de nécessiter work_mem. Toutefois, les requêtes complexes qui impliquent 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.

Surveillance et ajustement des work_mem

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

• Insights sur les performances des requêtes : vérifiez les requêtes principales par onglet 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

Bien que vous gériez le work_mem paramètre, 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. Il 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 les tâches d’agrégation ou de création de rapports, qui sont gourmandes en mémoire, envisagez de personnaliser la work_mem valeur de cet utilisateur. Utilisez la ALTER ROLE commande pour améliorer les performances des opérations de l’utilisateur.

• Niveau de fonction/procédure : si des fonctions ou procédures spécifiques génèrent des fichiers temporaires substantiels, l’augmentation de la work_mem valeur au niveau de la fonction ou de la procédure spécifique peut être bénéfique. Utilisez la commande ALTER FUNCTION ou ALTER PROCEDURE pour allouer spécifiquement 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 seuls quelques-uns créent des fichiers volumineux, il peut être prudent d’augmenter globalement la work_mem valeur. 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 surveillez l’utilisation de la mémoire sur votre serveur pour vous assurer qu’il peut gérer la valeur accrue work_mem .

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

Pour trouver la valeur minimale work_mem d’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 à l’aide de psql ou de votre client PostgreSQL préféré.
2. Définissez une valeur initiale work_mem 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 dans la même session.
4. Passez en revue la sortie pour "Sort Method: quicksort Memory: xkB". S’il indique "external merge Disk: xkB", augmentez la work_mem valeur de manière incrémentielle et retestez jusqu’à ce qu’elle "quicksort Memory" apparaisse. L'apparition 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écrit précédemment) pour répondre à vos besoins opérationnels.