Aller au contenu

AWS Mis à jour le 2026-05-08

Tuning du parameter group pour Postgres / MySQL en production sur RDS — le diff qui compte vraiment

max_connections, shared_buffers, work_mem, rétention des journaux. Le piège statique-vs-dynamique de redémarrage qui attrape les équipes qui éditent le mauvais parameter group au mauvais moment. Un vrai diff pour une charge SaaS Postgres 4 cœurs / 32 Go.

Un nouveau Postgres RDS démarre avec le parameter group par défaut. max_connections est à 100, shared_buffers est fixé en fonction de la classe d’instance mais sous-optimal pour la charge, work_mem est au défaut Postgres upstream de 4MB, et la rétention des journaux est configurée pour éliminer tout ce qui a plus d’un jour. Le propriétaire de l’application demande pourquoi ses requêtes tableau de bord font des pointes, et la réponse est « la base tourne sur les réglages d’usine ». Le parameter group par défaut est conçu pour être sûr, pas rapide. Le tuner est une des opérations à plus haut levier sur une base RDS, et le seul mécanisme qui surprend les équipes est la distinction redémarrage statique-versus-dynamique.

Ce qui se passe

Les parameter groups sont la façon dont AWS expose la configuration de l’engine. Les défauts sont conservateurs — ils supposent la plus petite instance raisonnable, une charge générique, et un opérateur qui n’a pas pensé au tuning. La plupart des charges de production ont besoin d’un parameter group custom dès le premier jour.

Deux catégories de paramètres importent opérationnellement :

Paramètres dynamiques. Prennent effet sans redémarrage. Appliqués dès que le parameter group est modifié, sur la prochaine connexion ou requête. Exemples sur Postgres : work_mem, log_min_duration_statement, statement_timeout. Le changement est visible en quelques secondes.

Paramètres statiques. Exigent un redémarrage de base pour prendre effet. Exemples sur Postgres : max_connections, shared_buffers, wal_level. Les changements sont sauvegardés, le parameter group affiche pending-reboot, et rien ne change tant que l’instance n’est pas redémarrée. Une équipe qui change max_connections de 300 à 600 et attend que le changement s’applique attendra pour toujours — il faut redémarrer.

Le piège : un parameter group peut avoir simultanément des changements statiques en pending-reboot et des changements dynamiques appliqués. L’équipe applique un changement dynamique, le voit prendre effet, suppose que le parameter group est entièrement actif, et est surprise que le changement statique n’ait toujours pas pris. La vue pg_settings (ou la colonne de statut de paramètre dans la console RDS) est la source de vérité.

Pour Postgres sur RDS, les paramètres qui comptent le plus pour une charge SaaS OLTP typique :

  • max_connections — le plafond absolu de backends concurrents. Statique. Réglez assez haut pour la taille du pool de l’application au pic, plus la marge pour des sessions ad-hoc et des replication slots.
  • shared_buffers — le cache de pages de Postgres. Défaut sur RDS : {DBInstanceClassMemory/32768} pages (8KB chacune), environ 25% de la mémoire d’instance. Statique. Le bon réglage est 25–40% de la mémoire d’instance pour OLTP, plus bas pour les charges analytiques qui s’appuient sur le cache de pages OS.
  • work_mem — la mémoire de tri/hash par opération. Défaut : 4MB upstream, RDS choisit une petite valeur. Dynamique. Trop bas et les requêtes débordent sur disque ; trop haut et une seule requête complexe peut utiliser work_mem * nombre_de_hash_joins * backends_concurrents de mémoire. À fixer par session pour les requêtes lourdes connues.
  • maintenance_work_mem — mémoire pour VACUUM, CREATE INDEX, etc. Dynamique. Le défaut est trop bas pour toute base de taille production. Réglez à 256MB–2GB.
  • effective_cache_size — indice à Postgres sur le cache total disponible (Postgres + OS). Dynamique. Réglez à ~75% de la mémoire d’instance.
  • random_page_cost — paramètre du modèle de coût, les défauts supposent des disques mécaniques. Dynamique. Réglez à 1.1 sur EBS gp3/io1 ou Aurora (I/O classe SSD).
  • log_min_duration_statement — journaliser les requêtes plus lentes que N ms. Dynamique. Réglez à une valeur qui fait remonter les requêtes problématiques sans noyer les journaux (typiquement 1000ms pour OLTP, 5000ms pour analytique).
  • log_statement — journaliser DDL, mod, all ou none. Dynamique. Réglez à ddl pour la production (chaque CREATE/ALTER/DROP est journalisé) ; réglez à mod si la conformité l’exige.
  • statement_timeout — tuer les requêtes qui dépassent N ms. Dynamique. À fixer par rôle, pas globalement ; le rôle analytique obtient 30 minutes, le rôle applicatif 30 secondes.
  • idle_in_transaction_session_timeout — tuer les sessions inactives dans une transaction pendant N ms. Dynamique. Réglez à 60 secondes — assez long pour des DBA humains en psql, assez court pour empêcher les transactions fuitées de tenir des verrous.

Pour MySQL sur RDS, les paramètres analogues :

  • max_connections — même rôle. Quasi-statique dans certaines versions.
  • innodb_buffer_pool_size — le cache de pages de MySQL. Le paramètre de tuning MySQL le plus important. Dynamique dans MySQL 5.7+, statique dans les versions antérieures. Réglez à 50–75% de la mémoire d’instance pour OLTP.
  • innodb_log_file_size — taille du fichier redo log. Statique. Le défaut est trop petit pour les charges write-heavy.
  • tmp_table_size / max_heap_table_size — seuils de tables temporaires. Dynamique. Le défaut est trop bas pour les requêtes avec résultats intermédiaires.
  • slow_query_log / long_query_time — journalisation des requêtes lentes. Dynamique.
  • innodb_flush_log_at_trx_commit — durabilité vs débit. Reste à 1 pour la production ; ne réglez pas à 2 pour gagner de la vitesse à moins de comprendre le compromis de durabilité.

Notes Aurora-spécifiques : la plupart des paramètres sont identiques à upstream, mais un sous-ensemble est géré par Aurora et non tunable par l’utilisateur (par exemple synchronous_commit est géré ; vous ne pouvez pas le désactiver). La console affiche une colonne « modifiable » — si elle dit non, c’est verrouillé.

Comment vérifier

Vérifiez le parameter group actuel :

aws rds describe-db-instances \
  --db-instance-identifier prod-db-1 \
  --query 'DBInstances[].[DBInstanceIdentifier,DBParameterGroups]' \
  --output json

Si DBParameterGroupName dit default.postgres15 ou similaire, la base est sur le défaut AWS. Vous ne pouvez pas modifier le groupe par défaut — créez-en un custom, attachez-le, redémarrez, et alors seulement faites le tuning.

Dumpez les paramètres qui diffèrent des défauts :

aws rds describe-db-parameters \
  --db-parameter-group-name prod-db-1-pg \
  --source user \
  --query 'Parameters[].[ParameterName,ParameterValue,ApplyType,IsModifiable]' \
  --output table

ApplyType est static ou dynamic. Les statiques vous disent ce qui a besoin d’un redémarrage.

À l’intérieur de Postgres, la vue runtime de la même chose :

SELECT name, setting, unit, context, pending_restart
FROM pg_settings
WHERE source = 'configuration file'
ORDER BY name;

pending_restart = true est la preuve flagrante de « on a changé ça mais on n’a pas redémarré ».

Pour un instantané actuel de la pression mémoire, vérifiez ce que fait work_mem :

-- Top 20 requêtes par temps total
SELECT query, calls, mean_exec_time, total_exec_time, temp_blks_written
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

temp_blks_written > 0 veut dire que la requête a débordé sur disque — work_mem était trop bas pour cette requête.

La correction

Un diff concret pour un Postgres 4 cœurs 32GB sur RDS, charge SaaS OLTP, charge analytique modeste. Engine : Postgres 15. Instance : db.r6g.xlarge.

Créez le parameter group :

aws rds create-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --db-parameter-group-family postgres15 \
  --description "Production Postgres 15 - 4c/32G OLTP"

Appliquez le tuning :

aws rds modify-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --parameters \
    "ParameterName=max_connections,ParameterValue=400,ApplyMethod=pending-reboot" \
    "ParameterName=shared_buffers,ParameterValue={DBInstanceClassMemory*25/100/8192},ApplyMethod=pending-reboot" \
    "ParameterName=work_mem,ParameterValue=32768,ApplyMethod=immediate" \
    "ParameterName=maintenance_work_mem,ParameterValue=1048576,ApplyMethod=immediate" \
    "ParameterName=effective_cache_size,ParameterValue={DBInstanceClassMemory*75/100/8192},ApplyMethod=immediate" \
    "ParameterName=random_page_cost,ParameterValue=1.1,ApplyMethod=immediate" \
    "ParameterName=log_min_duration_statement,ParameterValue=1000,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=ddl,ApplyMethod=immediate" \
    "ParameterName=log_lock_waits,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=log_temp_files,ParameterValue=0,ApplyMethod=immediate" \
    "ParameterName=idle_in_transaction_session_timeout,ParameterValue=60000,ApplyMethod=immediate" \
    "ParameterName=track_io_timing,ParameterValue=1,ApplyMethod=immediate"

Attachez à l’instance :

aws rds modify-db-instance \
  --db-instance-identifier prod-db-1 \
  --db-parameter-group-name prod-db-1-pg \
  --apply-immediately

Redémarrez pour appliquer les paramètres statiques :

aws rds reboot-db-instance --db-instance-identifier prod-db-1

Vérifiez dans Postgres :

SHOW max_connections;       -- 400
SHOW shared_buffers;        -- 8GB (32G * 25%)
SHOW work_mem;              -- 32MB
SHOW effective_cache_size;  -- 24GB
SELECT name, pending_restart FROM pg_settings WHERE pending_restart;
-- devrait être vide

Le raisonnement derrière chaque valeur :

  • max_connections=400 — à 32GB d’instance, 400 connexions est la limite supérieure avant que la mémoire de processus Postgres ne devienne significative (chaque backend utilise ~10MB de mémoire de base plus work_mem). Au-dessus de 400, passez à RDS Proxy ou pgbouncer.
  • shared_buffers=25% — Postgres sur Linux bénéficie de ce que le cache de pages OS cache aussi les pages ; régler shared_buffers au-dessus de 40% donne à Postgres un plus gros cache mais affame le cache OS, souvent une perte nette.
  • work_mem=32MB — généreux pour OLTP. Des requêtes concurrentes (400 max) faisant chacune un hash join peuvent théoriquement consommer 400 * 32MB = 12GB. Réglez plus haut par session pour des requêtes de reporting connues.
  • maintenance_work_mem=1GB — VACUUM et CREATE INDEX tournent plus vite avec plus de mémoire. Utilisé seulement quand ces opérations tournent, pas du tout autrement.
  • effective_cache_size=75% — informe le planificateur sur la taille du cache ; n’alloue pas de mémoire.
  • random_page_cost=1.1 — dit au planificateur que l’I/O aléatoire est presque aussi pas chère que séquentielle, vrai sur stockage classe SSD.
  • log_min_duration_statement=1000 — fait remonter les requêtes lentes sans journaliser chaque rapide.
  • log_lock_waits=1 — journalise quand une requête attend un verrou ; fait remonter la contention.
  • log_temp_files=0 — journalise chaque création de fichier temp ; fait remonter un work_mem sous-dimensionné.
  • idle_in_transaction_session_timeout=60000 — 60 secondes. Empêche les transactions zombies de tenir des row-locks et de gonfler les tables.
  • track_io_timing=1 — fait remonter le temps I/O dans pg_stat_statements ; petit coût CPU.

Pour MySQL sur une instance similaire, le diff analogue :

aws rds modify-db-parameter-group \
  --db-parameter-group-name prod-db-1-pg \
  --parameters \
    "ParameterName=max_connections,ParameterValue=400,ApplyMethod=immediate" \
    "ParameterName=innodb_buffer_pool_size,ParameterValue={DBInstanceClassMemory*65/100},ApplyMethod=immediate" \
    "ParameterName=innodb_log_file_size,ParameterValue=536870912,ApplyMethod=pending-reboot" \
    "ParameterName=innodb_flush_log_at_trx_commit,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=slow_query_log,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=long_query_time,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=tmp_table_size,ParameterValue=67108864,ApplyMethod=immediate" \
    "ParameterName=max_heap_table_size,ParameterValue=67108864,ApplyMethod=immediate"

Trois règles que nous tenons sur le travail de parameter group :

Ne modifiez jamais le parameter group de production durant un incident. Un changement qui exige un redémarrage déclenche un redémarrage de base, et le redémarrage peut durer plus longtemps que le problème initial. Faites les changements de tuning durant des fenêtres planifiées ; le tuning d’urgence est habituellement un mauvais tuning.

Pistez les changements de parameter group en gestion de versions. La définition infrastructure-as-code du parameter group devrait être la source de vérité. Les changements console dérivent à moins que quelqu’un ne les possède.

Tunez par mesure, pas par modèle. Une charge OLTP 4c/32G est un point de départ raisonnable. La charge réelle peut vouloir des valeurs différentes — mesurez pg_stat_statements, vérifiez les fichiers temp, surveillez le hit ratio du buffer cache, ajustez. Le diff ci-dessus est une ligne de départ, pas une ligne d’arrivée.

Le tuning est itératif. La première passe de tuning capture 80% du gain disponible. La passe suivante, trois mois plus tard, ajuste sur la base des patterns de production réels. La troisième passe, six mois plus tard, est essentiellement du fine-tuning. Après cela, la base reste tunée jusqu’à ce que la charge ou la version d’engine change.

Stack Harbor conçoit et opère les topologies RDS / Aurora en production dans le cadre de Gestion AWS et de la couche Environnements infogérés. Si votre posture base de données se résume à « case Multi-AZ cochée + espoir », commencez par une consultation d’infrastructure.