PostgreSQL vs Percona Server : qui a besoin de plus de réglages pour la vitesse

La thèse franche : qui a besoin de plus de réglages

Si vous définissez la « bonne vitesse » comme des performances respectables sans tuning héroïque, PostgreSQL y parvient généralement
avec moins de paramètres « obligatoires »—pour un primaire unique, un schéma raisonnable et des patterns de requêtes non chaotiques.
Il a des réglages, bien sûr, mais beaucoup portent sur la prévisibilité et les gardes-fous plutôt que sur « tournez ces 17 interrupteurs ou souffrez ».

Percona Server (une distribution MySQL renforcée, principalement basée sur InnoDB) peut être extrêmement rapide, mais il vous demande plus souvent
de faire des choix explicites plus tôt : dimensionnement du buffer pool, taille des redo logs, compromis de durabilité, comportement de vidage, réglages de réplication,
instrumentation. Certains de ces paramètres par défaut sont sûrs ; d’autres sont conservateurs ; d’autres tiennent jusqu’au changement de charge puis deviennent silencieusement coûteux.

Donc, qui a besoin de plus de réglages ? En pratique :

• Percona Server a tendance à nécessiter plus de tuning explicite pour atteindre la « bonne vitesse » sur de l’OLTP intensif en écriture, forte concurrence,
  et topologies avec réplication lourde—surtout quand la latence compte et que l’ensemble de données ne tient plus en mémoire.
• PostgreSQL a tendance à nécessiter moins de réglages pour la « bonne vitesse » sur des charges mixtes et des requêtes complexes—mais il exige une hygiène
  continue (autovacuum, contrôle du bloat, statistiques) sinon il vieillira comme du lait dans une salle serveur chaude.

Le point caché : la question n’est pas « lequel a plus de réglages », mais « quels réglages sont inévitables et combien coûte une erreur ? »
Des mauvais réglages d’InnoDB flush peuvent transformer un système rapide en un système saccadé. Des mauvais comportements d’autovacuum peuvent faire paraître PostgreSQL
« lent au hasard » alors qu’il est en fait noyé sous les tuples morts.

Première blague (courte et méritée) : régler des bases de données, c’est comme accorder des guitares — la plupart des gens tournent les mécaniques jusqu’à ce que ça sonne différent, pas mieux.

Quelques faits intéressants (et pourquoi ils comptent)

Ce ne sont pas des anecdotes pour un quiz au bar. Ils expliquent pourquoi la surface de tuning paraît différente.

1. Le MVCC de PostgreSQL stocke les anciennes versions de lignes dans le heap jusqu’à ce que vacuum les récupère. C’est pourquoi
   le réglage d’autovacuum est du tuning de performance, pas du ménage.
2. Le buffer pool d’InnoDB est le centre de gravité des performances pour MySQL/Percona. S’il est sous-dimensionné, tout devient lié à l’I/O ;
   s’il est sur-dimensionné sans marge, le cache de pages de l’OS et le système de fichiers commencent à se concurrencer.
3. Percona Server existe parce que le « MySQL de base » ne suffisait pas à beaucoup d’opérateurs—historiquement axé sur l’observabilité,
   des diagnostics supplémentaires, et des améliorations de performances tout en restant compatible. Cette culture se voit dans le nombre de paramètres et métriques exposés.
4. PostgreSQL a obtenu le parallélisme de requête en étapes majeures depuis 9.6+, et c’est encore un domaine où quelques réglages
   (comme max_parallel_workers_per_gather) peuvent transformer une requête de rapport de « pause café » à « clignement d’œil ».
5. La lignée de réplication de MySQL a poussé beaucoup de tuning opérationnel : row vs statement, format de binlog, réglages de synchronisation, variantes de group replication.
   La topologie fait partie des performances.
6. Le WAL et les checkpoints de PostgreSQL se comportent différemment que le redo/undo d’InnoDB. Le réglage des checkpoints Postgres vise souvent à lisser les rafales d’écriture ;
   le réglage du redo d’InnoDB vise souvent à soutenir le débit d’écriture sans thrashing.
7. Percona a introduit/avancé des fonctionnalités comme des améliorations du slow query log et des variables d’état supplémentaires qui rendent le « mesurer d’abord » plus pratique.
   Le revers : vous pouvez vous mesurer jusqu’à générer de l’overhead si vous activez tout.
8. Les deux écosystèmes ont appris à la dure que « des caches plus gros » ne sont pas toujours plus rapides. Pression mémoire, effets NUMA, comportement des allocateurs
   peuvent transformer « plus de RAM pour la BDD » en « plus de latence pour tout le monde ».

Philosophie de tuning de base : comportement par défaut vs réalité de production

Ce que « bonne vitesse » signifie réellement en exploitation

En production, la vitesse n’est pas un seul chiffre. C’est :

• latence p95 et p99 sous concurrence réelle
• stabilité du débit pendant la compaction/vacuum/checkpoints
• comportement de queue prévisible pendant les sauvegardes, la rattrapage de réplication et le basculement
• temps pour diagnostiquer quand ça ralentit (observabilité comme performance)

Paramètres par défaut de PostgreSQL : polis, conservateurs et parfois naïfs

Les valeurs par défaut de PostgreSQL sont conçues pour « tourner sur un portable » et « ne pas bloquer la machine ». Elles ne vont pas automatiquement allouer la plupart de votre RAM.
C’est de la bonne tenue. C’est aussi pourquoi les nouvelles installations peuvent décevoir jusqu’à ce que vous définissiez quelques paramètres clés.

Le côté positif : une fois que vous avez réglé la mémoire et le comportement des checkpoints de façon adéquate, PostgreSQL se comporte souvent de façon prévisible.
Vous affinerez toujours pour des patterns de requêtes et des charges de maintenance spécifiques, mais le système est moins susceptible d’exiger une longue liste de bascules « sinon ».

Paramètres par défaut de Percona Server : capables, mais en attente d’un choix

Percona Server hérite de beaucoup de valeurs MySQL, et beaucoup sont sûres mais pas rapides. Mais l’audience de Percona gère souvent de l’OLTP sérieux,
donc l’hypothèse opérationnelle est différente : vous configurerez le buffer pool, les redo logs, le comportement de flush, et la réplication. Si vous ne le faites pas,
la base de données fonctionnera quand même—mais vous laisserez de la vitesse sur la table, et parfois la table prend feu sous charge.

PostgreSQL : les réglages qui font vraiment la différence

1) Mémoire : shared_buffers, work_mem, maintenance_work_mem

PostgreSQL a plusieurs domaines de mémoire. Cela perturbe ceux qui ont grandi avec « réglez le buffer pool et oubliez-le ».
Votre travail est d’éviter la mort par mille allocations work_mem tout en gardant assez de cache et de marge pour la maintenance.

• shared_buffers : cache partagé principal utilisé par PostgreSQL. Point de départ commun : 20–30% de la RAM sur Linux.
  Plus n’est pas toujours mieux ; le cache de l’OS reste important.
• work_mem : par nœud de tri/hash, par requête, par worker. Trop élevé et la concurrence devient roulette d’échange. Trop bas et vous déversez sur disque. Utilisez-le intentionnellement.
• maintenance_work_mem : vacuum et construction d’index. Trop faible rend la maintenance lente ; trop élevé peut affamer les autres travaux.

2) WAL et checkpoints : éviter la falaise d’écriture

Les plaintes de performance PostgreSQL ressemblent souvent à : « c’est rapide, puis l’est plus, puis redevient lent. »
C’est généralement les checkpoints, le background writer, ou la saturation du stockage.

• checkpoint_timeout et max_wal_size : augmentez-les pour réduire la fréquence des checkpoints.
• checkpoint_completion_target : visez à étaler l’I/O des checkpoints dans le temps.
• wal_compression : peut aider quand le volume de WAL est élevé et que le CPU est disponible.

3) Autovacuum : soit vous le réglez, soit il vous régle

Autovacuum est un système de maintenance en arrière-plan qui empêche le bloat et le wraparound d’ID de transaction. S’il prend du retard, vous obtenez :
tables gonflées, requêtes lentes, inefficacité des index, et I/O « mystérieux ».

Leviers clés :

• autovacuum_max_workers : plus de workers aide sur beaucoup de tables ; trop peut provoquer des pics d’I/O.
• autovacuum_vacuum_scale_factor et autovacuum_analyze_scale_factor : abaissez-les pour les tables chaudes.
• autovacuum_vacuum_cost_limit et …_delay : contrôlent l’agressivité.

4) Réalisme du planner : statistiques et « effective_cache_size »

Le planner de PostgreSQL est souvent excellent, mais il n’est pas voyant. Si les stats sont obsolètes ou trop grossières, il choisit le mauvais plan.
Si il croit que le cache est plus petit que la réalité, il peut privilégier des plans qui font trop d’I/O.

• effective_cache_size : indique au planner combien de cache il peut supposer (shared buffers + cache OS).
• default_statistics_target et cibles de statistiques par colonne : augmentez-les pour des distributions biaisées.

5) Gestion des connexions : le classique « trop de backends »

PostgreSQL utilise un modèle process-par-connexion (avec des variations et améliorations au fil du temps). Un grand nombre de connexions augmente l’usage mémoire
et les changements de contexte. Vous ne réglez pas ça par vœu pieux ; vous le corrigez par du pooling.

• max_connections : ne le mettez pas à 5000 parce que vous le pouvez.
• Utilisez un pooler (comme pgBouncer) lorsque votre application ouvre beaucoup de connexions courtes.

Percona Server : les réglages qu’il ne faut pas ignorer

1) InnoDB buffer pool : le gros

Si vous ne réglez qu’une chose dans Percona Server/MySQL, c’est celle-ci. Le buffer pool met en cache les données et les index ; c’est là où vos IOPS de lecture s’arrêtent.
Guidage typique : 60–75% de la RAM sur un hôte dédié, en laissant de la place pour l’OS, les connexions, la réplication, et le cache de système de fichiers (surtout si vous utilisez O_DIRECT les choix diffèrent).

• innodb_buffer_pool_size : réglez-le intentionnellement.
• innodb_buffer_pool_instances : aide la concurrence sur de grands pools ; trop d’instances ajoute de l’overhead.

2) Redo logs et flushing : durabilité vs débit n’est pas un débat moral

La performance d’écriture en InnoDB est fortement façonnée par la taille des redo logs et le comportement de flushing.
Vous pouvez la rendre rapide en prenant des risques de durabilité. Parfois c’est acceptable ; souvent non.

• innodb_redo_log_capacity (ou anciennes tailles de fichiers de log) : trop petit provoque des checkpoints fréquents et du churn.
• innodb_flush_log_at_trx_commit : 1 est le plus sûr ; 2 est courant pour la performance avec un certain risque ; 0 est pimenté.
• sync_binlog : la durabilité du binlog compte aussi si vous dépendez de la réplication ou de la récupération PITR.

3) Capacité d’I/O et pages dirty : apprenez le stockage au moteur

InnoDB tente de s’adapter, mais a encore besoin d’indices. Le NVMe moderne se comporte différemment d’un SSD en réseau ou d’un bloc cloud avec crédits d’explosion.

• innodb_io_capacity et innodb_io_capacity_max : réglez-les sur des capacités IOPS réalistes.
• innodb_max_dirty_pages_pct et réglages associés : contrôlent la quantité de données dirty pouvant s’accumuler avant le début de storms de flushing.

4) Threading et concurrence : moins de « magie », plus de choix

MySQL a une longue histoire de tuning de concurrence. Beaucoup de charges supportent les valeurs par défaut, jusqu’à ce qu’elles ne le fassent plus.
Alors vous découvrez que vous êtes CPU-bound sur des mutex ou que vous souffrez d’overhead d’ordonnancement de threads.

• thread_cache_size : réduit l’overhead de création de threads.
• max_connections : trop élevé crée une pression mémoire et une contention des verrous.

5) Réplication et binlog : la performance est consciente de la topologie

Le tuning de réplication existe aussi pour PostgreSQL, mais l’opérationnel MySQL/Percona tourne souvent autour de la réplication.
Le format du binlog, l’ordre des commits, et le parallélisme d’application des réplicas affectent à la fois la vitesse et les attentes de cohérence.

• binlog_format : ROW est courant pour la cohérence ; STATEMENT peut être plus léger mais risqué ; MIXED est un compromis.
• replica_parallel_workers (ou anciens slave_*) : aide les réplicas à rattraper leur retard, mais attention à la contention.

Schémas de charge : où chaque moteur « fonctionne tout seul » (et où il ne le fait pas)

OLTP haute concurrence avec requêtes simples

Percona Server (InnoDB) est dans son habitat naturel ici. Avec un buffer pool bien dimensionné et des réglages de flush sensés, il peut soutenir des débits d’écriture élevés.
PostgreSQL peut aussi bien faire de l’OLTP, mais vous prêterez attention à autovacuum et au design des index plus tôt car des tuples morts s’accumulent sous des patterns d’updates intensifs.

Si votre charge est « updates partout, tout le temps », PostgreSQL nécessite la maintenance comme préoccupation de première classe. Si vous le traitez comme un système « régler et oublier »,
votre histoire de performance se dégradera sur des mois.

Requêtes complexes, analytics, jointures et « pourquoi ce rapport est lent ? »

PostgreSQL a généralement l’avantage en sophistication du planner, extensibilité, et facilité d’expression du SQL complexe.
Ce n’est pas que MySQL ne peut pas faire des jointures ; c’est que vous rencontrez plus rapidement des arêtes vives dans la planification et le choix d’index.

C’est là que le tuning PostgreSQL est moins une question de « plus de réglages » et davantage de qualité des stats, index appropriés, et
éviter les régressions de plan après des upgrades ou changements de schéma.

Surtensions d’écriture et tâches par lots

Le comportement checkpoint/WAL de PostgreSQL peut créer des rafales périodiques d’I/O si mal réglé.
Percona peut aussi souffrir de storms de flushing quand la gestion des pages dirty est mal réglée, ou quand le stockage ment sur ses performances.

Sous des charges par lots, l’objectif est le même : lisser le pattern d’écriture, garder la latence prévisible, et empêcher que le périphérique de stockage
ne devienne le point unique de vérité qui souffre.

Simplicité opérationnelle vs contrôle opérationnel

PostgreSQL peut paraître plus simple car moins de réglages sont « obligatoires ». Mais sa correction opérationnelle dépend du vacuuming et d’une gestion sensée des connexions.
Percona semble plus « riche en réglages » car il offre de nombreux points de contrôle explicites, surtout autour de la durabilité et de l’I/O.

Choisissez en fonction de qui le fera tourner à 02:13. Si vous avez une équipe qui aime le contrôle explicite et une forte observabilité, l’écosystème Percona est confortable.
Si vous valorisez des valeurs par défaut sensées et un SQL puissant avec une surface de tuning plus petite pour atteindre des performances acceptables, PostgreSQL est souvent plus amical—en supposant que vous vous engagiez à l’hygiène du vacuum.

Stockage et OS : la partie que vous vouliez ignorer

Vous ne pouvez pas compenser un mauvais stockage par du tuning. Vous ne pouvez que le faire échouer de façons plus intéressantes.
PostgreSQL et Percona sont implacablement honnêtes : si la latence de stockage est incohérente, votre p99 ressemblera à un film d’horreur.

La latence bat le débit pour la plupart des OLTP

La plupart des charges OLTP se soucient davantage d’une latence cohérente sub-millisecondes à quelques millisecondes que du MB/s brut.
Un périphérique qui fait 3 GB/s séquentiel mais qui se bloque occasionnellement 200 ms est un farceur, pas un disque de base de données.

Le système de fichiers et les choix de montage comptent (mais moins que vous ne le craignez)

Les systèmes de fichiers Linux modernes conviennent. Ce qui compte, c’est d’aligner le pattern d’I/O de votre BDD avec la pile de stockage :

• Faites attention au double caching (cache BDD + cache OS) quand la mémoire est serrée.
• Méfiez-vous des paramètres writeback agressifs qui créent des blocages périodiques.
• Comprenez si vous êtes sur NVMe local, stockage en bloc réseau, ou « SSD cloud » avec comportement en rafales.

Une citation opérationnelle pour rester honnête

L’espoir n’est pas une stratégie. — General Gordon R. Sullivan

C’est une citation managériale, mais les SRE l’ont adoptée parce que ça fait mal de manière utile. Ne « supposez » pas que vos valeurs par défaut sont suffisantes. Mesurez et décidez.

Tâches pratiques : commandes, sorties et décisions (12+)

Ce sont les tâches que vous lancez quand un système est lent et que vous devez décider quoi changer sans démarrer un club de folklore de configuration.
Chaque tâche inclut : une commande, une sortie typique, ce que ça signifie, et la décision à prendre.

Task 1: Confirm whether you’re CPU-bound or I/O-bound (Linux)

cr0x@server:~$ iostat -xz 1 3
Linux 6.5.0 (db1)  12/30/2025  _x86_64_  (16 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          32.10    0.00    7.90   18.50    0.00   41.50

Device            r/s     rkB/s   rrqm/s  %rrqm r_await rareq-sz     w/s     wkB/s   wrqm/s  %wrqm w_await wareq-sz  aqu-sz  %util
nvme0n1         420.0  28500.0     10.0   2.3    3.10    67.9    780.0  112000.0    50.0   6.0   18.20   143.6   15.2   98.0

Signification : Un %iowait élevé et une utilisation du périphérique proche de 100% avec un w_await élevé indiquent un problème de latence d’écriture.
Ce n’est pas un moment pour « plus d’index ».

Décision : Priorisez le tuning du chemin d’écriture (checkpoints/WAL PostgreSQL, flush/redo InnoDB) et l’investigation du stockage avant d’optimiser les requêtes.

Task 2: Check memory pressure and swapping (Linux)

cr0x@server:~$ vmstat 1 5
procs -----------memory---------- ---swap-- -----io---- -system-- ------cpu-----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0      0 120432  98200 8123456  0    0   120  2400  920 2100 35  8 40 17  0
 6  1   8192  20480  11000 7012000  0  256  1024  8900 1200 4800 42 10 25 23  0

Signification : Activité de swap-out (so) et chute de la mémoire libre sous charge suggèrent un surengagement de mémoire.
Pour PostgreSQL cela signifie souvent work_mem multiplié par la concurrence. Pour MySQL, un buffer pool surdimensionné plus des connexions élevées peut provoquer cela.

Décision : Réduisez le risque mémoire par connexion (pooling, diminuer work_mem, diminuer max_connections) avant d’« ajouter de la RAM » par réflexe.

Task 3: PostgreSQL — see top waits (what are backends stuck on?)

cr0x@server:~$ psql -X -c "SELECT wait_event_type, wait_event, count(*) FROM pg_stat_activity WHERE state='active' GROUP BY 1,2 ORDER BY 3 DESC LIMIT 10;"
 wait_event_type |     wait_event      | count
-----------------+---------------------+-------
 IO              | DataFileRead         |    18
 Lock            | transactionid        |     7
 LWLock          | WALWrite             |     5
                 |                     |     0

Signification : Beaucoup de DataFileRead signifie des manques de cache / lectures liées à l’I/O. WALWrite indique une pression d’écriture WAL.
transactionid lock waits peuvent signaler contention ou transactions longues.

Décision : Si l’I/O domine, inspectez les taux de hit du cache et la latence du stockage. Si WALWrite domine, réglez WAL/checkpoints et confirmez la latence fsync.

Task 4: PostgreSQL — check buffer cache hit ratio (directional, not a religion)

cr0x@server:~$ psql -X -c "SELECT datname, blks_hit, blks_read, round(100.0*blks_hit/nullif(blks_hit+blks_read,0),2) AS hit_pct FROM pg_stat_database ORDER BY hit_pct ASC;"
  datname  | blks_hit | blks_read | hit_pct
-----------+----------+-----------+---------
 appdb     | 93210012 |  8200455  | 91.93
 postgres  |  1023012 |    12045  | 98.84

Signification : 92% peut être bien ou médiocre selon la charge. L’important est la tendance et la corrélation avec la latence.
Une chute soudaine suggère une croissance du working set, des patterns de requêtes mauvais, ou une mémoire insuffisante.

Décision : Si le taux de hit chute et que les lectures augmentent, envisagez des améliorations d’index ou plus de mémoire (shared_buffers + cache OS), mais validez d’abord avec les plans de requêtes.

Task 5: PostgreSQL — identify autovacuum lag and bloat risk

cr0x@server:~$ psql -X -c "SELECT relname, n_dead_tup, n_live_tup, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"
    relname     | n_dead_tup | n_live_tup |     last_autovacuum     |     last_autoanalyze
----------------+------------+------------+-------------------------+-------------------------
 orders         |    8923012 |   40211234 |                         | 2025-12-30 00:41:13+00
 order_items    |    6112001 |   88012210 | 2025-12-29 22:10:02+00  | 2025-12-30 00:40:59+00
 sessions       |    4100122 |    1802210 |                         | 2025-12-30 00:40:15+00

Signification : Un grand nombre de tuples morts et l’absence de last_autovacuum sur des tables chaudes est un signal d’alerte.
Les requêtes ralentissent car les index et les scans de heap grossissent, et les vérifications de visibilité deviennent plus coûteuses.

Décision : Réglez autovacuum par table (facteurs d’échelle, limites de coût) et corrigez les transactions longues qui empêchent le nettoyage.

Task 6: PostgreSQL — find long transactions blocking vacuum

cr0x@server:~$ psql -X -c "SELECT pid, usename, now()-xact_start AS xact_age, state, left(query,80) AS q FROM pg_stat_activity WHERE xact_start IS NOT NULL ORDER BY xact_age DESC LIMIT 5;"
 pid  | usename |  xact_age   | state  | q
------+---------+-------------+--------+--------------------------------------------------------------------------------
 8421 | app     | 03:12:44    | idle   | BEGIN; SELECT * FROM customers WHERE id=...
 9122 | app     | 00:18:02    | active | UPDATE orders SET status='paid' WHERE id=...

Signification : Une session « idle in transaction » qui maintient un snapshot peut empêcher vacuum de supprimer les tuples morts,
conduisant au bloat et à la dégradation des performances.

Décision : Corrigez la portée des transactions dans l’application. Tuez des sessions seulement en cas d’urgence, puis corrigez le code.

Task 7: PostgreSQL — check checkpoint behavior

cr0x@server:~$ psql -X -c "SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint FROM pg_stat_bgwriter;"
 checkpoints_timed | checkpoints_req | checkpoint_write_time | checkpoint_sync_time | buffers_checkpoint
-------------------+-----------------+-----------------------+----------------------+--------------------
               120 |              95 |              18234012 |              3100221 |           90211234

Signification : Un checkpoints_req élevé signifie que vous atteignez les limites de taille WAL et forcez des checkpoints.
De longs temps d’écriture/sync des checkpoints corrèlent avec des pics de latence.

Décision : Augmentez max_wal_size, réglez checkpoint_timeout, et lissez avec checkpoint_completion_target.
Si le stockage ne suit pas, corrigez le stockage ou réduisez l’amplification d’écriture (batches, moins d’index, changements de schéma).

Task 8: MySQL/Percona — see what threads are waiting on (quick view)

cr0x@server:~$ mysql -e "SHOW PROCESSLIST\G" | head -n 40
*************************** 1. row ***************************
     Id: 12091
   User: app
   Host: 10.0.1.25:52144
     db: appdb
Command: Query
   Time: 12
  State: Waiting for table metadata lock
   Info: ALTER TABLE orders ADD COLUMN note TEXT
*************************** 2. row ***************************
     Id: 12102
   User: app
   Host: 10.0.1.18:51902
     db: appdb
Command: Query
   Time: 10
  State: update
   Info: UPDATE orders SET status='paid' WHERE id=...

Signification : Les waits de metadata lock peuvent figer le trafic pendant des changements de schéma. Cela peut ressembler à « la base est lente », mais c’est en fait « une DDL bloque ».

Décision : Utilisez des approches de changement de schéma en ligne quand nécessaire ; planifiez les DDL ; réduisez les transactions longues qui tiennent des verrous.

Task 9: MySQL/Percona — check InnoDB buffer pool health

cr0x@server:~$ mysql -e "SHOW ENGINE INNODB STATUS\G" | sed -n '1,120p'
*************************** 1. row ***************************
  Type: InnoDB
  Name:
Status:
=====================================
2025-12-30 01:12:09 INNODB MONITOR OUTPUT
=====================================
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 274877906944
Buffer pool size   16777216
Free buffers       1024
Database pages     16776190
Old database pages 6192000
Modified db pages  210400
Pages read 98200123, created 2210021, written 81230111
...

Signification : Peu de buffers libres et des taux de lecture élevés peuvent être normaux sous charge, mais si les lectures et la latence sont élevées,
le pool peut être trop petit pour le working set ou les patterns de requêtes forcent des scans.

Décision : Si la mémoire le permet, augmentez innodb_buffer_pool_size. Sinon, corrigez d’abord les requêtes et les index ; ne laissez pas l’OS s’asphyxier.

Task 10: MySQL/Percona — confirm redo log pressure and flushing

cr0x@server:~$ mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_os_log_fsyncs'; SHOW GLOBAL STATUS LIKE 'Innodb_log_waits'; SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';"
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| Innodb_os_log_fsyncs| 18220031 |
+---------------------+----------+
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_log_waits | 12044 |
+------------------+-------+
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+

Signification : Innodb_log_waits indique des sessions en attente d’espace redo log / comportement de flush.
Avec innodb_flush_log_at_trx_commit=1, la latence fsync est directement dans le chemin de la transaction.

Décision : Si les waits sont significatifs, augmentez la capacité des redo et validez la latence fsync du stockage. Envisagez des compromis de durabilité seulement avec un accord RPO/RTO clair.

Task 11: MySQL/Percona — find top statements quickly (Performance Schema summary)

cr0x@server:~$ mysql -e "SELECT DIGEST_TEXT, COUNT_STAR, ROUND(SUM_TIMER_WAIT/1e12,2) AS total_s FROM performance_schema.events_statements_summary_by_digest ORDER BY SUM_TIMER_WAIT DESC LIMIT 3\G"
*************************** 1. row ***************************
DIGEST_TEXT: SELECT * FROM orders WHERE customer_id = ? ORDER BY created_at DESC LIMIT ?
 COUNT_STAR: 1822011
   total_s: 9120.55
*************************** 2. row ***************************
DIGEST_TEXT: UPDATE order_items SET status = ? WHERE order_id = ?
 COUNT_STAR: 801122
   total_s: 6122.17

Signification : Un temps total élevé peut signifier « appelé souvent » ou « chaque appel est lent ». Les résumés digest vous aident à choisir vos batailles.

Décision : Prenez les pires digests et lancez EXPLAIN, vérifiez les index, et mesurez les lignes examinées vs retournées.

Task 12: PostgreSQL — find top total-time queries (pg_stat_statements)

cr0x@server:~$ psql -X -c "SELECT query, calls, round(total_exec_time::numeric,1) AS total_ms, round(mean_exec_time::numeric,2) AS mean_ms FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
                           query                           | calls  | total_ms | mean_ms
-----------------------------------------------------------+--------+----------+---------
 SELECT * FROM orders WHERE customer_id=$1 ORDER BY ...     | 1822011| 812034.2 |   0.45
 UPDATE order_items SET status=$1 WHERE order_id=$2         |  801122| 610221.7 |   0.76

Signification : Même histoire que les digests MySQL : trouvez les vrais goulets. PostgreSQL vous donne le temps moyen et le temps total ; les deux comptent.

Décision : Pour les requêtes à appels fréquents, cherchez un support d’index et évitez les fetchs inutiles. Pour un mean time lent, inspectez les plans et l’I/O.

Task 13: PostgreSQL — inspect a query plan for “lies” and misestimates

cr0x@server:~$ psql -X -c "EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM orders WHERE customer_id=123 ORDER BY created_at DESC LIMIT 50;"
 Limit  (cost=0.43..12.86 rows=50 width=128) (actual time=0.210..4.882 rows=50 loops=1)
   Buffers: shared hit=120 read=320
   ->  Index Scan Backward using idx_orders_customer_created on orders  (cost=0.43..9012.12 rows=35210 width=128) (actual time=0.207..4.865 rows=50 loops=1)
         Index Cond: (customer_id = 123)
         Buffers: shared hit=120 read=320
 Planning Time: 0.220 ms
 Execution Time: 4.910 ms

Signification : Les read=320 buffers indiquent des lectures physiques durant l’exécution. Si cette requête est chaude et que ce nombre augmente, vous êtes limité par l’I/O.
Les mauvaises estimations apparaissent lorsque « rows= » est très différent de « actual rows= ».

Décision : Si les erreurs d’estimation sont sévères, augmentez la cible de stats ou ajoutez des statistiques étendues. Si les lectures dominent, augmentez le cache ou réduisez le working set avec de meilleurs index.

Task 14: MySQL/Percona — check replication lag and apply pressure

cr0x@server:~$ mysql -e "SHOW REPLICA STATUS\G" | egrep 'Seconds_Behind_Source|Replica_SQL_Running|Replica_IO_Running|Last_SQL_Error'
Replica_IO_Running: Yes
Replica_SQL_Running: Yes
Seconds_Behind_Source: 87
Last_SQL_Error:

Signification : Un lag sans erreur signifie que le réplica n’arrive pas à appliquer assez vite. Cela peut être I/O, CPU, ou des contraintes d’application mono-thread.

Décision : Augmentez le parallélisme d’application des réplicas là où c’est sûr, réduisez l’amplification d’écriture, et assurez-vous que le stockage du réplica n’est pas plus lent que le primaire.

Task 15: Check filesystem latency symptoms (Linux dmesg)

cr0x@server:~$ dmesg -T | tail -n 8
[Mon Dec 30 01:11:44 2025] nvme nvme0: I/O 123 QID 6 timeout, completion polled
[Mon Dec 30 01:11:44 2025] blk_update_request: I/O error, dev nvme0n1, sector 182291234 op 0x1:(WRITE) flags 0x800 phys_seg 32 prio class 0

Signification : Si le noyau log des timeouts ou des erreurs, arrêtez de blâmer la base de données.

Décision : Traitez comme un incident d’infrastructure : remplacez le périphérique, corrigez le contrôleur/firmware, validez la santé du volume cloud, puis revenez aux réglages DB.

Mode d’emploi pour un diagnostic rapide

Quand les choses sont lentes, vous ne commencez pas par éditer des configs. Vous commencez par prouver où le temps est passé.
Ce playbook est conçu pour « la production est en feu, mais on reste adultes ».

Première étape : est-ce la base, ou la base attend-elle autre chose ?

1. Vérifiez la latence et la saturation du stockage (iostat -xz, métriques volume cloud). Si %util est saturé et que les awaits sont élevés,
   votre « requête lente » peut être une histoire de « disque lent ».
2. Vérifiez la pression mémoire (vmstat, swap). Le swapping transforme les deux moteurs en art de performance tragique.
3. Vérifiez la saturation CPU (moyenne de charge vs nombre de CPU, top, pidstat).
   Si le CPU est saturé, demandez : est-ce l’exécution des requêtes, la maintenance en arrière-plan, ou l’overhead compression/chiffrement ?

Deuxième étape : les sessions attendent-elles des verrous ou de l’I/O ?

• PostgreSQL : waits de pg_stat_activity (IO vs Lock vs LWLock). Beaucoup de Lock waits signifie contention ; beaucoup d’IO waits signifie cache/stockage.
• Percona : SHOW PROCESSLIST state + InnoDB status. Metadata locks, verrous de ligne, et log waits racontent des histoires différentes.

Troisième étape : identifiez les requêtes principales par temps total, pas par impressions

• PostgreSQL : pg_stat_statements, puis EXPLAIN (ANALYZE, BUFFERS) sur les pires éléments.
• Percona : résumé digest du Performance Schema, puis EXPLAIN et revue des index.

Quatrième étape : vérifiez les « tueurs silencieux » spécifiques au moteur

• PostgreSQL : retard d’autovacuum, tuples morts, transactions longues, fréquence des checkpoints.
• Percona : waits de redo log, misses du buffer pool, storms de flushing, lag de réplication qui charge le primaire.

Deuxième blague (et c’est fini) : si votre base est lente et que vous n’avez pas vérifié la latence disque, vous dépannez essentiellement par danse interprétative.

Trois mini-récits du monde de l’entreprise (anonymisés, techniquement réels)

Mini-récit 1 : L’incident causé par une fausse hypothèse

Une entreprise SaaS de taille moyenne utilisait PostgreSQL pour le trafic transactionnel et avait récemment ajouté une fonctionnalité « timeline d’activité client ».
C’était une table classique orientée append avec des mises à jour périodiques pour des changements d’état. Tout allait bien en staging.

En production, la latence p99 a commencé à grimper sur plusieurs semaines. L’équipe a supposé que c’était « juste la croissance » et a prévu un scale vertical.
Ils ont augmenté CPU et RAM, se sont tapés dans le dos et ont attendu que les graphiques se calment.

Ils ne l’ont pas fait. La RAM supplémentaire a aidé un temps, puis le système a repris sa lente dérive. Lors de l’incident suivant, quelqu’un a enfin lancé
pg_stat_user_tables et a vu une montagne de tuples morts sur une poignée de tables chaudes. Autovacuum n’arrivait pas à suivre.
Pire : il y avait des transactions de longue durée lancées par un worker en arrière-plan qui maintenaient des snapshots ouverts pendant des heures pendant qu’il traitait une file.

La mauvaise hypothèse était simple : « Autovacuum est automatique, donc c’est géré. » Il est automatique comme une machine à laver est automatique si vous en avez une.
Vous devez quand même y mettre les habits, et vous ne devriez vraiment pas laisser des serviettes mouillées une semaine.

La correction fut ennuyeuse et chirurgicale : raccourcir la portée des transactions dans le worker, baisser les scale factors du vacuum sur les tables chaudes, augmenter le nombre d’autovacuum workers,
et programmer un VACUUM (FULL) contrôlé uniquement là où c’était absolument nécessaire. Les performances se sont stabilisées. Aucune mise à niveau matérielle héroïque requise.

Mini-récit 2 : L’optimisation qui a mal tourné

Une plateforme e‑commerce utilisait Percona Server avec un trafic d’écriture lourd : commandes, paiements, réservations d’inventaire. Ils cherchaient un meilleur débit,
et quelqu’un a proposé un changement « sûr » : augmenter le buffer pool de « grand » à « presque toute la RAM ». L’idée était de réduire les lectures disque.

Le changement est déployé pendant une fenêtre de faible trafic. Rien n’a explosé. Le lendemain, sous peak, le système a commencé à se bloquer.
Pas progressivement. Des blocages qui faisaient ressembler les timeouts applicatifs à des problèmes réseau. Le CPU n’était pas saturé. Le débit disque semblait correct.
Tout le monde a regardé les dashboards et n’a rien appris.

Le coupable : pression mémoire. En donnant à InnoDB presque toute la RAM, ils ont affamé l’OS et laissé peu de marge pour la mémoire par connexion,
les buffers de réplication, et le comportement du système de fichiers. L’hôte a commencé à swapper de façon intermittente, ce qui a transformé fsync et flush en pics de latence imprévisibles.
La base n’était pas « lente ». Elle était occasionnellement gelée.

Le rollback de la taille du buffer pool a corrigé les symptômes immédiats. La solution à long terme fut plus nuancée :
dimensionner le buffer pool en laissant une vraie marge, limiter max_connections, et introduire du connection pooling au niveau applicatif.
Ils ont aussi arrêté de faire des DDL à midi, ce qui, bien que sans rapport avec le buffer pool, a rendu tout le monde plus heureux.

La leçon : « plus de cache » n’est pas universellement bon. C’est un compromis. L’OS fait partie du système, pas un détail ennuyant que vous pouvez évincer.

Mini-récit 3 : La pratique ennuyeuse mais correcte qui a sauvé la mise

Une équipe de services financiers exploitait les deux moteurs : PostgreSQL pour le reporting et MySQL/Percona pour un système OLTP legacy.
Ils n’étaient pas l’équipe la plus flashy. Ils étaient, cependant, discrètement efficaces.

Leur arme secrète était une routine stricte et terne : revue hebdomadaire du top SQL par temps total, vérification mensuelle du bloat et de la santé du vacuum sur Postgres,
et validation trimestrielle du temps de récupération sur les deux systèmes. Ils gardaient les configs sous contrôle de version et exigeaient une note de changement expliquant
l’effet attendu et le plan de rollback pour chaque modification de tuning.

Un jour, un problème de firmware de stockage a augmenté la latence d’écriture. Les deux bases ont ralenti, mais l’équipe l’a identifié rapidement parce qu’ils avaient des mesures de base
et savaient à quoi ressemblait un comportement fsync et checkpoint « normal ». Ils n’ont pas perdu des heures à bricoler les réglages de la base pour compenser un périphérique en train de mourir.

La victoire opérationnelle n’était pas héroïque. C’était de la discipline : une baseline connue et saine, l’habitude de mesurer avant de changer, et une récupération répétée.
Le rapport d’incident fut court. Le week-end fut en grande partie sauvé. Voilà à quoi ressemble le succès en production—calme.

Erreurs courantes : symptôme → cause racine → correction

1) Pics p99 PostgreSQL toutes les quelques minutes

Symptôme : Les pics de latence corrèlent avec des rafales d’I/O d’écriture.

Cause racine : Checkpoints trop fréquents ou trop « en rafales » à cause d’un max_wal_size bas ou d’un lissage de checkpoint insuffisant.

Correction : Augmentez max_wal_size, augmentez checkpoint_timeout dans des limites raisonnables, réglez checkpoint_completion_target (souvent 0.7–0.9), et vérifiez la latence fsync du stockage.

2) PostgreSQL se dégrade lentement sur des semaines

Symptôme : Des requêtes autrefois rapides deviennent systématiquement plus lentes ; l’espace disque augmente de façon inattendue.

Cause racine : Autovacuum n’arrive pas à suivre ; les tuples morts s’accumulent ; des transactions longues empêchent le nettoyage ; les stats sont obsolètes.

Correction : Réglez autovacuum pour les tables chaudes (abaissez les scale factors), corrigez les transactions longues, augmentez prudemment le nombre d’autovacuum workers, et reanalyser les tables critiques.

3) Percona/MySQL se bloque sous charge d’écriture avec des « log waits »

Symptôme : Le débit s’effondre pendant les pics d’écriture ; le statut montre une augmentation des log waits.

Cause racine : Redo log trop petit ou fsync stockage lent ; les réglages de flush placent fsync dans le chemin de commit.

Correction : Augmentez la capacité des redo, confirmez la latence NVMe/volume cloud, et revisitez les knobs de durabilité seulement avec une décision business explicite.

4) Pause du trafic MySQL pendant un changement de schéma

Symptôme : Beaucoup de threads affichent « Waiting for table metadata lock ».

Cause racine : DDL bloque ou est bloqué par des transactions longues ; les metadata locks sérialisent l’accès.

Correction : Utilisez des méthodes de changement de schéma en ligne, tuez/évitez les transactions longues, planifiez les DDL en période creuse, et concevez les migrations en tenant compte des verrous.

5) Les deux moteurs : « nous avons augmenté max_connections et c’était pire »

Symptôme : Plus de timeouts, latence plus élevée, montée du context switching, swap possible.

Cause racine : Surcharge de concurrence : trop de sessions actives saturent le CPU, la mémoire, ou le gestionnaire de verrous ; les files passent de l’app vers la base.

Correction : Ajoutez du pooling, définissez des limites de connexions sensées, implémentez du backpressure, et scalez les lectures plutôt que d’augmenter les connexions jusqu’au chaos.

6) Des requêtes deviennent plus lentes après une « optimisation d’index »

Symptôme : La latence d’écriture augmente ; le lag de réplication augmente ; CPU et I/O montent.

Cause racine : Trop d’index augmentent l’amplification d’écriture ; l’« optimisation » de lecture ne vaut pas le coût global.

Correction : Supprimez les index inutilisés, consolidez les index multi-colonnes, et validez avec des stats de charge réelles (pas des suppositions).

Listes de contrôle / plan pas à pas

Pas à pas : amener PostgreSQL à la « bonne vitesse » avec un minimum de réglages

1. Définissez shared_buffers à une fraction raisonnable (départ autour de 20–30% RAM sur un hôte dédié).
2. Définissez effective_cache_size pour refléter shared buffers + cache OS (souvent 50–75% RAM).
3. Réglez work_mem de façon conservatrice, puis augmentez sélectivement par rôle/session pour les requêtes lourdes ; ne le mettez pas globalement haut.
4. Activez et utilisez pg_stat_statements pour classer les requêtes par temps total.
5. Réglez les checkpoints (max_wal_size, checkpoint_timeout, checkpoint_completion_target) pour éviter les falaises d’I/O.
6. Auditez la santé d’autovacuum chaque semaine ; appliquez des réglages par table pour les tables chaudes.
7. Utilisez du pooling de connexions si vous avez beaucoup de connexions courtes ou des services à fort fan-out.
8. Basez une mesure de la latence de stockage et surveillez-la comme faisant partie de votre application (parce que c’en est).

Pas à pas : amener Percona Server à la « bonne vitesse » sans pensée magique

1. Dimensionnez innodb_buffer_pool_size intentionnellement ; laissez de la marge pour l’OS et la concurrence.
2. Définissez la capacité des redo pour ne pas checkpointer constamment ; validez les log waits.
3. Décidez explicitement de la durabilité : innodb_flush_log_at_trx_commit et sync_binlog alignés avec le RPO business.
4. Réglez innodb_io_capacity pour correspondre à la réalité du stockage, pas aux specs marketing.
5. Activez les synthèses basées sur digest et utilisez-les régulièrement.
6. Gardez max_connections raisonnable ; corrigez le comportement de connexion de l’app au lieu d’exploser la limite.
7. Planifiez le tuning de réplication (parallel apply, binlog format) comme partie intégrante des performances, pas un après-coup.
8. Exercez les changements de schéma en tenant compte des verrous ; les metadata locks ne sont pas une surprise.

Checklist décisionnelle : « Ai‑je besoin de plus de réglages, ou de moins de problèmes ? »

• Si vous ne pouvez pas mesurer les points chauds des requêtes, le tuning est du jeu de hasard. Activez d’abord les bonnes stats.
• Si vous ne pouvez pas décrire vos besoins de durabilité, ne touchez pas aux knobs de flush.
• Si vous ne pouvez pas borner la concurrence, votre base le fera pour vous—avec de la latence.
• Si la latence de stockage est incohérente, vos résultats de tuning le seront aussi.

FAQ

1) Lequel est plus rapide dès l’installation ?

Ça dépend de la charge, mais PostgreSQL donne souvent l’impression d’être « correct » plus rapidement parce que moins de réglages sont nécessaires pour éviter des pièges évidents.
Percona/MySQL demande souvent le dimensionnement du buffer pool et des choix redo/flush pour atteindre son meilleur comportement sous OLTP intensif.

2) PostgreSQL est‑il « auto‑tuning » grâce à autovacuum ?

Non. Autovacuum est automatique, pas omniscient. Les tables chaudes nécessitent fréquemment un réglage par table, et les transactions longues peuvent le saboter.
Traitez la santé du vacuum comme partie de la gestion de performance.

3) Percona Server, c’est juste MySQL avec des réglages en plus ?

C’est compatible MySQL et historiquement axé sur des améliorations de performance et d’observabilité. Les « réglages supplémentaires » existent souvent parce que les opérateurs ont demandé du contrôle.
Plus de contrôle est bon—jusqu’à ce que ça devienne une complexité ingouvernée.

4) Quel est le levier de tuning PostgreSQL le plus important ?

Si on vous force à choisir un seul : garder la maintenance saine—efficacité d’autovacuum plus prévention des pics de checkpoint. La mémoire compte, mais le bloat et les falaises d’écriture ruinent tout.

5) Quel est le levier de tuning Percona/MySQL le plus important ?

innodb_buffer_pool_size. S’il est erroné, vous allez soit thrash disque soit affamer l’OS et provoquer des gels. En deuxième position : la configuration redo/flush alignée avec le stockage.

6) Dois‑je mettre shared_buffers de PostgreSQL à 80% de la RAM comme un buffer pool ?

Généralement non. PostgreSQL bénéficie du cache OS et a besoin de marge pour work_mem, maintenance, et le reste du système.
Commencez plus petit et mesurez ; « toute la RAM dans shared_buffers » est une blessure auto‑infligée courante.

7) Puis‑je fixer les requêtes lentes en ajoutant des index jusqu’à ce que ça aille ?

Vous pouvez, brièvement, jusqu’à ce que les écritures et la réplication deviennent plus lentes et que le cache se transforme en tiroir à bazar.
Les index ont un coût. Utilisez les stats de requêtes pour justifier chaque index, et retirez le poids mort.

8) Pourquoi mes requêtes PostgreSQL deviennent‑elles plus lentes alors que le CPU est inactif ?

Souvent des waits I/O, des verrous, ou du bloat. Vérifiez les waits dans pg_stat_activity, regardez les tuples morts, et confirmez la latence du stockage.
Un CPU inactif n’est pas un signe de santé ; c’est parfois un signe d’attente.

9) Pourquoi mes requêtes MySQL restent « bloquées » pendant des migrations ?

Metadata locks. Le DDL nécessite des verrous, et des transactions longues peuvent les retenir. Utilisez des méthodes de migration sensibles aux verrous et gardez les transactions courtes.

10) Lequel a besoin de plus de réglages pour de bons résultats sur du block storage cloud ?

Percona/MySQL a tendance à demander plus de réglages explicites d’I/O et de durabilité parce que le comportement fsync est central pour la latence des commits.
PostgreSQL se préoccupe aussi fortement de la latence fsync (WAL), mais il s’agit souvent de moins de réglages pour atteindre un comportement stable—à condition d’avoir fait le tuning des checkpoints.

Prochaines étapes sans humiliation

Si vous choisissez entre PostgreSQL et Percona Server strictement sur « qui a besoin de plus de réglages », vous posez la bonne question de la mauvaise manière.
Demandez plutôt : quels réglages sont obligatoires pour ma charge, et à quelle vitesse mon équipe peut diagnostiquer les problèmes quand la charge change.

Prochaines étapes pratiques :

1. Choisissez une charge représentative (OLTP lecture-lourd, OLTP écriture-lourd, mixte, reporting) et benchmarkez avec une concurrence similaire à la production.
2. Activez les statistiques de requêtes (pg_stat_statements ou Performance Schema digests) et créez un rituel hebdomadaire « top SQL ».
3. Établissez des baselines pour la latence du stockage, le comportement checkpoint/flush, et le lag de réplication. Écrivez‑les.
4. Limitez la concurrence délibérément avec du pooling et des plafonds de connexions sensés. Ne laissez pas l’application DDoS la base poliment.
5. Changez une chose à la fois, mesurez, et gardez un plan de rollback. Mettez la config sous contrôle de version comme si ça comptait—parce que ça compte.

PostgreSQL récompense la maintenance régulière et une bonne hygiène SQL. Percona Server récompense les choix explicites et un contrôle opérationnel serré.
Aucun des deux n’est « intrinsèquement plus rapide ». Le plus rapide est celui que vous pouvez exploiter sans deviner.