Calculateur de réglage mémoire

Où passe la mémoire d'une base, et le paramètre qui se multiplie sans bruit

Les bases managées arrivent avec des paramètres mémoire déduits de la taille de l'instance, et la plupart sont des valeurs par défaut raisonnables qu'il vaut mieux laisser tranquilles. Une ne l'est pas, car elle ne se comporte pas comme les autres : work_mem est accordée par nœud de tri ou de hachage, son coût réel est donc multiplié par le nombre de connexions et par le nombre de tels nœuds dans chaque requête. Cet outil déduit les paramètres standards de la mémoire de l'instance, puis affiche ce produit explicitement.

Comment ça marche

• Déduit shared_buffers, effective_cache_size et maintenance_work_mem de la mémoire de l'instance, avec les ratios qu'utilise RDS lui-même.
• Affiche les équivalents MySQL — innodb_buffer_pool_size et les tampons par connexion — quand vous changez de moteur.
• Multiplie work_mem par votre nombre de connexions et par les tris par requête, c'est-à-dire le chiffre qui doit réellement tenir.
• Avertit quand l'allocation de base plus ce pire cas dépasse la mémoire de l'instance.

PostgreSQL, tel que RDS fixe les valeurs par defaut
  shared_buffers        = {DBInstanceClassMemory/32768}  -> 25% de la RAM
  effective_cache_size  ~ 75% de la RAM (indication au planificateur, pas une allocation)
  maintenance_work_mem  ~ 5%, plafonne vers 2 Gio

MySQL
  innodb_buffer_pool_size = {DBInstanceClassMemory*3/4} -> 75% de la RAM

celui qui se multiplie
  pire cas = work_mem x connexions x tris_par_requete

Exemple chiffré

Une instance PostgreSQL de 32 Gio aux valeurs par défaut, et ce qui arrive quand un réglage est relevé.

1. shared_buffers = 25% de 32 Gio = 8 Gio
2. effective_cache_size = 75% = 24 Gio, mais cela ne réserve rien — cela indique seulement au planificateur ce que le système met probablement en cache
3. maintenance_work_mem = 5% = 1,6 Gio, sous le plafond de 2 Gio
4. work_mem = 16 Mio, avec 200 connexions et 2 nœuds de tri par requête
5. pire cas = 16 Mio x 200 x 2 = 6,25 Gio
6. 8 Gio + 6,25 Gio = 14,25 Gio sur 32 — confortable
7. relevez maintenant work_mem à 64 Mio pour accélérer un rapport lent, et passez à 1 000 connexions avec 4 tris
8. pire cas = 64 Mio x 1 000 x 4 = 250 Gio sur une instance de 32 Gio

Les deux dernières étapes sont la voie habituelle vers une mise à mort par l'OOM killer. Rien ne paraissait imprudent : quelqu'un a quadruplé work_mem pour accélérer une requête, et le trafic a grandi. La multiplication a fait le reste — et cela survient sous charge, pas au moment du changement, d'où la rareté des détections en revue.

Lire le résultat

• effective_cache_size n'alloue rien. C'est l'estimation que vous donnez au planificateur de la mémoire que le système consacre probablement au cache de vos données, et elle change les plans qui paraissent bon marché. La régler haut ne consomme pas de mémoire ; la régler de travers produit de mauvais plans.
• shared_buffers à 25% est le point de départ conventionnel et la valeur par défaut de RDS. La relever n'est pas gratuit : PostgreSQL s'appuie sur le cache de pages du système comme second niveau, la mémoire versée dans shared_buffers est donc retirée à ce cache. De très grandes valeurs peuvent ralentir l'ensemble.
• MySQL a la forme inverse. innodb_buffer_pool_size vaut 75% par défaut car InnoDB ne s'appuie pas sur le cache du système comme le fait PostgreSQL : il veut les données dans son propre pool. N'y transposez pas l'intuition PostgreSQL.
• Relevez work_mem pour la session plutôt que pour le serveur quand un seul rapport en a besoin. SET work_mem dans cette connexion donne le plan souhaité sans accorder la même dotation à toutes les autres connexions de l'instance.
• Une requête comportant plusieurs nœuds de tri ou de hachage peut prendre work_mem plusieurs fois, au sein d'une seule connexion. Les workers parallèles le multiplient encore. Le curseur de tris par requête existe parce que supposer un seul est la façon la plus courante de sous-estimer ce calcul.

Questions fréquentes

Le pire cas est-il réaliste, ou juste arithmétique ?

C'est un plafond, pas une prévision — il faudrait que chaque connexion exécute simultanément une requête gourmande. Mais c'est le plafond qui compte, car les bases n'y échouent pas en douceur : elles se font tuer par l'OOM killer, et les pics de trafic sont précisément le moment où toutes les connexions travaillent en même temps.

Ma base managée refuse de modifier shared_buffers. Pourquoi ?

Sur RDS et Azure, cela se règle via un groupe de paramètres sous forme de formule sur DBInstanceClassMemory, pas comme un nombre fixe, et la modification exige un redémarrage car le segment de mémoire partagée est alloué au démarrage. Cloud SQL bloque certains indicateurs entièrement. La valeur par défaut convient généralement ; le paramètre qui mérite votre attention est work_mem.

Comment savoir la work_mem dont une requête a réellement besoin ?

Lancez EXPLAIN (ANALYZE, BUFFERS) et cherchez un tri par fusion externe ou un hachage sur disque. Si un tri a débordé sur le disque, le plan l'indique avec la taille. Ce chiffre — un peu au-dessus du débordement — est ce que veut la requête, et il vous dit si relever le réglage globalement en vaut la peine ou s'il faut plutôt corriger la requête.

Et les tampons par connexion de MySQL ?

Ils se multiplient de la même façon. sort_buffer_size, join_buffer_size et read_rnd_buffer_size sont alloués par connexion au besoin, en plus du buffer pool. La même discipline s'applique : gardez-les modestes et relevez-les pour la session qui en a vraiment besoin.

Sources

• PostgreSQL — paramètres de consommation de ressources
• Amazon RDS — bonnes pratiques
• MySQL — configuration du buffer pool InnoDB