MySQL vs PostgreSQL sur un VPS 4GB : quoi régler en premier pour des sites web

Premier choix : MySQL ou PostgreSQL pour des sites sur 4GB

Sur un VPS 4GB, « la meilleure base » est celle que vous pouvez garder prévisible sous pression mémoire et trafic en rafales. Votre ennemi n’est pas le débit théorique. Ce sont les tempêtes de swap, les flux de connexions et les pics de latence stockage qui transforment « assez rapide » en « pourquoi le paiement expire ? »

Choisissez MySQL (InnoDB) quand :

• Votre stack est déjà natif MySQL (WordPress, Magento, beaucoup d’apps PHP) et vous ne voulez pas être la personne qui réécrit tout « pour le plaisir ».
• Vous voulez une histoire de cache assez simple : le buffer pool InnoDB est le gros réglage, et il se comporte comme tel.
• Vous avez besoin d’une réplication facile à exploiter avec des outils courants, et vous acceptez les compromis d’éventuelle consistance selon certains modes.

Choisissez PostgreSQL quand :

• Vous tenez à la justesse des requêtes et aux fonctionnalités SQL riches (vraies fonctions fenêtre, CTE, meilleures contraintes et types) et que vous allez réellement les utiliser.
• Vous voulez des plans de requête prévisibles, une bonne observabilité et des valeurs par défaut sensées pour de nombreux patterns d’app modernes.
• Vous pouvez vous engager à utiliser du pooling de connexions (pgBouncer) parce que le modèle process-per-connection de PostgreSQL punit le « j’ouvre juste plus de connexions » sur de petites machines.

Si c’est majoritairement du trafic CMS avec des plugins que vous ne contrôlez pas, je suis généralement conservateur : restez sur MySQL à moins que l’app ne soit déjà orientée Postgres. Si vous construisez quelque chose de nouveau avec une équipe qui écrit du SQL intentionnellement, PostgreSQL est souvent un meilleur pari long terme. Mais sur 4GB, le gain à court terme est la simplicité opérationnelle, pas la pureté philosophique.

Règle générale : si vous ne pouvez pas décrire vos 5 principales requêtes et leurs index, vous ne « choisissez pas une base de données », vous choisissez quels modes de défaillance vous voulez expérimenter en premier.

Faits intéressants & contexte historique (qui changent réellement les décisions)

1. La domination précoce de MySQL sur le web vient de l’omniprésence du LAMP et d’une vitesse « suffisante » pour les sites à lecture majoritaire. C’est pourquoi tant d’apps web supposent encore des bizarreries du dialecte MySQL.
2. InnoDB est devenu le moteur par défaut dans MySQL 5.5 (ère 2010). Si vous pensez encore en termes MyISAM (verrouillage de table, pas de récupération après crash), vous traînez un fossile dans la poche.
3. Le modèle MVCC de PostgreSQL est une des raisons pour lesquelles il reste cohérent sous concurrence, mais il crée un besoin constant de vacuum. Ignorer le vacuum ne fera pas crier la base ; elle empirera lentement.
4. PostgreSQL a évolué vers un modèle d’exécution plus adapté au parallélisme (requêtes parallèles, meilleur planner). Sur un petit VPS cela compte moins que sur du gros fer, mais c’est une des raisons pour lesquelles Postgres « paraît moderne » pour les requêtes analytiques.
5. Le cache de requêtes de MySQL a été supprimé dans MySQL 8.0 parce qu’il ne montait pas bien sous concurrence. Si quelqu’un vous dit d’« activer query_cache_size », vous avez trouvé un voyageur temporel.
6. Postgres est crédité pour les standards et l’exactitude parce qu’historiquement il a privilégié les fonctionnalités et l’intégrité plutôt que la vitesse brute initiale. Aujourd’hui il est aussi rapide, mais l’ADN culturel se voit dans les valeurs par défaut et les outils.
7. Les deux moteurs sont conservateurs sur la durabilité par défaut (fsync, WAL/redo). Désactiver des paramètres de durabilité rend les benchmarks héroïques et les postmortems dignes d’une scène de crime.
8. MariaDB a divergé de MySQL de façon significative. Les conseils de « tuning MySQL » s’appliquent parfois mal aux versions MariaDB et à ses moteurs de stockage. Vérifiez ce que vous exécutez réellement.
9. RDS et les services managés ont influencé le folklore de tuning : les gens copient des valeurs cloud sur un VPS, puis se demandent pourquoi une machine 4GB se comporte comme si elle était sous l’eau.

Architecture de référence pour un VPS 4GB (et pourquoi ça compte)

Sur un VPS 4GB, vous n’avez pas de « mémoire supplémentaire ». Vous avez un budget. Dépensez-le sur des caches qui réduisent les E/S, et sur une marge qui empêche le swap. Le cache de page de l’OS compte aussi parce que MySQL et PostgreSQL finissent par lire depuis le système de fichiers, et le noyau n’est pas votre ennemi ; c’est votre dernière ligne de défense.

Budget mémoire basé sur la réalité

• OS + SSH + daemons de base : 300–600MB
• Serveur web + PHP-FPM : très variable. Quelques centaines de MB à plusieurs GB selon le nombre de processus et le comportement de l’app.
• Base de données : ce qui reste, mais pas tout. Si vous donnez tout à la BDD, la couche web fera OOM ou swappe lors des pics de trafic.

Pour « sites web sur une seule VPS », la base n’est pas isolée. C’est un des rares cas où « set and forget » n’est pas de la paresse ; c’est de la survie.

Opinion : Si vous hébergez à la fois web et BD sur le même VPS 4GB, prévoyez d’allouer environ 1.5–2.5GB à la couche cache de la base max, à moins que vous n’ayez mesuré l’usage mémoire de PHP sous charge et qu’il soit vraiment faible. Votre objectif est une latence stable, pas un buffer pool héroïque.

Blague #1 : Un VPS 4GB est comme un studio — techniquement vous pouvez y mettre un tapis de course, mais vous détesterez votre vie et vos voisins aussi.

Feuille de diagnostic rapide : trouver le goulot en 10 minutes

Voici l’ordre dans lequel je vérifie les choses quand « le site est lent » et que la base est le suspect principal. Chaque étape indique si vous devez regarder le CPU, la mémoire, les connexions, les locks ou le stockage.

Première étape : la machine manque-t-elle de ressources (CPU, RAM, swap) ?

• Vérifiez la charge vs le nombre de CPU.
• Vérifiez l’activité de swap et les défauts de page majeurs.
• Vérifiez l’historique de l’OOM killer.

Deuxième : est-ce la latence du stockage (IOPS/fsync/WAL/redo) ?

• Temps d’attente I/O élevé, fsync lent, commits longs, ou checkpoints bloqués.
• Vérifiez la profondeur de file d’attente et les temps d’attente moyens.

Troisième : est-ce la pression de connexions ?

• Trop de connexions ou de threads DB.
• Storms de connexions depuis des workers PHP.
• Comptes de threads/processus atteignant la RAM.

Quatrième : est-ce des locks ou des transactions longues ?

• MySQL : locks de métadonnées, locks de lignes InnoDB, transactions de longue durée.
• Postgres : requêtes bloquées, sessions idle-in-transaction, vacuum bloqué par de vieux snapshots.

Cinquième : est-ce des « mauvaises requêtes + index manquants » ?

• Slow query logs / pg_stat_statements montrent les pires éléments.
• Recherchez des scans complets de table et des « filesort »/fichiers temporaires ou des scans séquentiels sur de très grands jeux de lignes.

C’est tout. Ne commencez pas par toucher des réglages au hasard. Ne copiez pas un « my.cnf haute performance » pour un serveur 64GB. Mesurez, puis choisissez un changement que vous pouvez expliquer.

Citation (idée paraphrasée) : L’idée de fiabilité de John Allspaw : la production est l’endroit où les hypothèses vont mourir, donc concevez et opérez pour apprendre, pas pour la certitude.

Tâches pratiques : commandes, sorties et ce que vous faites ensuite

Ce sont des tâches réelles que vous pouvez exécuter sur un VPS Linux. Chacune inclut : la commande, ce que signifie une sortie typique, et la décision que vous prenez. Exécutez-les dans l’ordre quand vous êtes en triage ou en train de poser des bases.

Tâche 1 : Confirmer la pression système de base (CPU, RAM, swap)

cr0x@server:~$ uptime
 14:22:19 up 36 days,  3:18,  1 user,  load average: 5.84, 5.12, 3.90

Ce que cela signifie : Sur un VPS 2 vCPU, des load averages au-dessus de ~2–3 sur des périodes soutenues indiquent souvent des files d’exécution en attente (CPU) ou des attentes I/O non interruptibles.

Décision : Si la charge est élevée, vérifiez immédiatement iowait et mémoire/swap avant de toucher la config de la base.

cr0x@server:~$ free -h
               total        used        free      shared  buff/cache   available
Mem:           3.8Gi       3.3Gi       120Mi        90Mi       420Mi       220Mi
Swap:          1.0Gi       860Mi       164Mi

Ce que cela signifie : Le swap est utilisé et « available » est faible : un signal d’alerte pour des workloads sensibles à la latence.

Décision : Si le swap est utilisé en période de pointe, réduisez l’empreinte mémoire de la BD et/ou le nombre de workers PHP-FPM. Envisagez d’activer une petite quantité de swap comme coussin, pas comme couche de performance.

Tâche 2 : Vérifier le swapping actif et l’iowait

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
 2  1 880000 120000  28000 380000   12   45   210   480  520  900 18  8 40 34  0
 3  0 880000 118000  28000 381000    0   60   160   510  600 1100 20  9 35 36  0
 1  2 880000 115000  28000 379000   20    0   400   200  480  850 15  7 38 40  0
 4  0 880000 119000  28000 382000    0    8    90   520  610 1200 22 10 32 36  0
 2  1 880000 117000  28000 381500    0   35   200   600  590 1050 19  9 34 38  0

Ce que cela signifie : Des valeurs non nulles pour si/so indiquent du swapping. Un wa élevé indique de l’attente I/O.

Décision : Si le swapping est actif, arrêtez de tuner les requêtes et corrigez la mémoire/les compteurs de connexions. Si l’iowait est élevé, concentrez-vous sur la latence du stockage, le comportement des checkpoints/flush et la réduction de l’amplification d’écriture.

Tâche 3 : Identifier rapidement la latence disque et le queueing

cr0x@server:~$ iostat -xz 1 3
avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          18.20    0.00    8.10   34.50    0.00   39.20

Device            r/s     w/s   rkB/s   wkB/s  rrqm/s  wrqm/s  %util  await  aqu-sz
vda              12.0    48.0   900.0  6200.0    0.2     3.1   92.0   21.5    1.8

Ce que cela signifie : %util proche de 100% et await > 10–20ms sur un disque VPS signifie généralement que votre stockage est le goulot.

Décision : Réduisez les écritures (batching, réglage autovacuum, tuning InnoDB flush), migrez la BD vers un stockage meilleur, ou séparez DB et web. Sur certains plans VPS, le seul « tuning » qui compte est de payer pour plus d’IOPS.

Tâche 4 : Confirmer quelle BD et quelle version vous exécutez

cr0x@server:~$ mysql --version
mysql  Ver 8.0.36-0ubuntu0.22.04.1 for Linux on x86_64 ((Ubuntu))

Ce que cela signifie : Les versions majeures changent les valeurs par défaut et suppriment des réglages (comme le query cache). La version vous dit quel conseil est applicable.

Décision : N’appliquez pas de recettes de tuning si elles ne correspondent pas à votre version majeure et à votre saveur (MySQL vs MariaDB).

cr0x@server:~$ psql --version
psql (PostgreSQL) 16.1 (Ubuntu 16.1-1.pgdg22.04+1)

Ce que cela signifie : Les versions Postgres plus récentes améliorent le vacuum, le WAL et le planner. Cela change « ce qui fait mal » sur les petites machines.

Décision : Sur un vieux Postgres, vous devrez peut-être le surveiller plus manuellement. Sur un Postgres récent, concentrez-vous davantage sur le pooling de connexions et les seuils autovacuum.

Tâche 5 : Compter les connexions BD (MySQL)

cr0x@server:~$ mysql -e "SHOW STATUS LIKE 'Threads_connected';"
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 185   |
+-------------------+-------+

Ce que cela signifie : 185 connexions sur un VPS 4GB avec PHP est souvent un problème, même avant que les requêtes deviennent lentes.

Décision : Limitez la concurrence applicative, activez les connexions persistantes avec précaution, ou adoptez un pattern qui limite la concurrence DB (mise en file côté app, cache, ou séparation du trafic en lecture). Si vous ne pouvez pas contrôler l’app, réduisez max_connections et acceptez des échecs contrôlés plutôt qu’un effondrement total.

Tâche 6 : Compter les connexions BD (PostgreSQL)

cr0x@server:~$ sudo -u postgres psql -c "SELECT count(*) AS connections FROM pg_stat_activity;"
 connections
-------------
         142
(1 row)

Ce que cela signifie : 142 sessions Postgres = 142 processus backend. Sur un VPS 4GB, c’est un coût mémoire et de commutation de contexte.

Décision : Installez pgBouncer et réduisez max_connections. Sur petites machines, Postgres sans pooling est une plaisanterie de performance que vous vous faites.

Tâche 7 : Trouver les requêtes longues et les bloqueurs (PostgreSQL)

cr0x@server:~$ sudo -u postgres psql -c "SELECT pid, now()-query_start AS age, state, wait_event_type, wait_event, left(query,80) AS q FROM pg_stat_activity WHERE state <> 'idle' ORDER BY age DESC LIMIT 5;"
 pid  |   age    | state  | wait_event_type | wait_event |                                       q
------+----------+--------+-----------------+------------+--------------------------------------------------------------------------------
 9123 | 00:02:18 | active | Lock            | relation   | UPDATE orders SET status='paid' WHERE id=$1
 9051 | 00:01:44 | active | IO              | DataFileRead | SELECT * FROM products WHERE slug=$1
(2 rows)

Ce que cela signifie : Les attentes de lock indiquent de la contention ; les attentes IO indiquent un stockage lent ou des caches manquants.

Décision : Si les attentes de lock dominent, corrigez la portée des transactions et l’indexation. Si les attentes IO dominent, augmentez le caching effectif (avec raison) et réduisez les lectures aléatoires via des index et la mise en forme des requêtes.

Tâche 8 : Trouver les waits de lock (MySQL)

cr0x@server:~$ mysql -e "SHOW FULL PROCESSLIST;"
Id	User	Host	db	Command	Time	State	Info
210	app	10.0.0.12:50344	shop	Query	75	Waiting for table metadata lock	ALTER TABLE orders ADD COLUMN foo INT
238	app	10.0.0.15:38822	shop	Query	12	Sending data	SELECT * FROM orders WHERE created_at > NOW() - INTERVAL 1 DAY

Ce que cela signifie : Les locks de métadonnées peuvent geler les écritures et les lectures derrière des modifications de schéma, selon l’opération et la version.

Décision : Arrêtez de faire des changements de schéma en ligne de façon désinvolte sur un petit VPS unique. Planifiez une maintenance ou utilisez des outils de migration de schéma en ligne conçus pour réduire le verrouillage.

Tâche 9 : Vérifier le hit rate du buffer pool InnoDB et la pression de lecture

cr0x@server:~$ mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_read%';"
+---------------------------------------+---------+
| Variable_name                         | Value   |
+---------------------------------------+---------+
| Innodb_buffer_pool_read_requests      | 9823412 |
| Innodb_buffer_pool_reads              | 412390  |
+---------------------------------------+---------+

Ce que cela signifie : reads sont des lectures physiques ; read_requests sont logiques. Si les lectures physiques sont élevées par rapport aux requêtes, vous manquez de cache.

Décision : Si le working set tient en RAM, augmentez innodb_buffer_pool_size prudemment. Sinon, priorisez les index et réduisez le working set (moins de colonnes, moins de scans).

Tâche 10 : Vérifier le cache Postgres et les débordements de fichiers temporaires

cr0x@server:~$ sudo -u postgres psql -c "SELECT datname, blks_hit, blks_read, temp_files, temp_bytes FROM pg_stat_database ORDER BY temp_bytes DESC LIMIT 5;"
  datname  | blks_hit | blks_read | temp_files |  temp_bytes
-----------+----------+-----------+------------+--------------
 appdb     |  9201123 |   612332  |      1832  | 2147483648
(1 row)

Ce que cela signifie : Beaucoup de temp_bytes suggèrent des tris/hashes déversés sur disque parce que work_mem est trop petit pour ces opérations — ou que les requêtes font trop de travail.

Décision : Ne montez pas work_mem globalement sur un petit VPS. Corrigez d’abord les requêtes et les index ; ensuite augmentez work_mem par rôle ou par session pour des charges spécifiques.

Tâche 11 : Voir les requêtes principales (Postgres, si pg_stat_statements est activé)

cr0x@server:~$ sudo -u postgres psql -c "SELECT calls, mean_exec_time, rows, left(query,80) AS q FROM pg_stat_statements ORDER BY total_exec_time DESC LIMIT 5;"
 calls | mean_exec_time | rows |                                       q
-------+----------------+------+--------------------------------------------------------------------------------
 82021 |          12.45 |    1 | SELECT id FROM sessions WHERE token=$1
  1220 |         210.12 |  300 | SELECT * FROM orders WHERE user_id=$1 ORDER BY created_at DESC LIMIT 50
(2 rows)

Ce que cela signifie : Les requêtes à temps total élevé sont celles qui consument votre budget. Les requêtes à fort nombre d’appels sont vos « mort par mille coupures ».

Décision : Indexez les chemins chauds et réduisez les requêtes bavardes. Si une requête s’exécute 80k fois et prend 12ms, c’est un regret sur un cœur entier.

Tâche 12 : Activer et lire rapidement le slow query log MySQL

cr0x@server:~$ mysql -e "SET GLOBAL slow_query_log=ON; SET GLOBAL long_query_time=0.5; SET GLOBAL log_queries_not_using_indexes=ON;"
...output omitted...

Ce que cela signifie : Vous activez la collecte d’éléments de preuve. Gardez des seuils raisonnables pour ne pas vous noyer de logs.

Décision : Collectez pendant 15–60 minutes en période de pointe, puis utilisez les données pour corriger les pires éléments. Désactivez log_queries_not_using_indexes si c’est trop bruyant pour votre app.

cr0x@server:~$ sudo tail -n 5 /var/log/mysql/mysql-slow.log
# Query_time: 1.204  Lock_time: 0.000 Rows_sent: 50  Rows_examined: 84512
SELECT * FROM orders WHERE user_id=123 ORDER BY created_at DESC LIMIT 50;

Ce que cela signifie : Rows examined est énorme par rapport à rows sent : index manquant classique ou ordre d’index erroné.

Décision : Ajoutez/ajustez des index composites pour correspondre au filtre + ordre (par ex., (user_id, created_at)), puis vérifiez avec EXPLAIN.

Tâche 13 : Vérifier l’espace filesystem et la pression d’inodes

cr0x@server:~$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/vda1        80G   74G  2.1G  98% /

Ce que cela signifie : Les disques presque pleins détruisent la performance et peuvent casser les écritures BD. Postgres peut refuser de checkpointer ; MySQL peut planter ou passer en lecture seule selon les circonstances.

Décision : Libérez de l’espace maintenant. Puis configurez la rotation des logs et une alerte de surveillance à 80–85%.

Tâche 14 : Vérifier la mémoire du noyau et des processus BD

cr0x@server:~$ ps -eo pid,comm,rss,pmem --sort=-rss | head
 2311 mysqld     1854320 46.2
 1822 php-fpm8.1  412000 10.2
 1825 php-fpm8.1  405000 10.1
  911 postgres    220000  5.4

Ce que cela signifie : RSS montre la mémoire résidente réelle. Quelques gros workers PHP plus un cache BD volumineux peuvent faire basculer la machine en swap.

Décision : Si BD + PHP consomment déjà la majeure partie de la RAM, arrêtez d’augmenter les buffers BD. Réduisez la concurrence et plafonnez les consommateurs mémoire.

Si vous choisissez MySQL : quoi régler en premier sur un VPS 4GB

MySQL sur du petit hardware VPS se comporte généralement bien si vous ne le traitez pas comme un puits sans fond pour les connexions et la mémoire. InnoDB est votre moteur par défaut ; tunez pour InnoDB, pas pour la nostalgie.

1) Réglez innodb_buffer_pool_size comme un adulte

Objectif : Cacher les données/index chauds, réduire les lectures aléatoires, éviter d’étouffer tout le reste.

• Si la BD est sur la même machine que le web : commencez autour de 1.0–1.5GB.
• Si la BD est principalement seule : jusqu’à 2.0–2.5GB peut convenir.

Mode d’échec : Surdimensionner le buffer pool ne « consomme pas la mémoire libre ». Il entre en concurrence avec le cache de page OS et la couche web. Ensuite vous swappez. Puis chaque requête devient un benchmark de stockage.

2) Réglez max_connections plus bas que vous ne le pensez

Les threads MySQL consomment de la mémoire. Les apps PHP adorent ouvrir des connexions comme si c’était gratuit. Ce n’est pas gratuit.

• Commencez autour de 100–200 selon l’app et la latence des requêtes.
• Si vous voyez 300–800 connexions, vous n’avez pas un « problème de performance de la base ». Vous avez un problème de contrôle de concurrence.

3) Gardez le redo log et le comportement de flush sensés

Sur un petit VPS avec une latence de stockage incertaine, des flushs trop agressifs peuvent causer des pics. Mais transformer la durabilité en suggestion est la façon la plus rapide d’actualiser votre CV.

• innodb_flush_log_at_trx_commit=1 pour une vraie durabilité (défaut).
• Si vous devez absolument réduire la pression fsync et pouvez accepter de perdre jusqu’à 1 seconde de transactions en cas de crash : envisagez =2. Documentez-le. Mettez-le dans les runbooks. Ne prétendez pas que c’est gratuit.

4) Désactivez ce dont vous n’avez pas besoin, mais ne vous aveuglez pas

Performance Schema est utile ; il impose aussi un coût. Sur un petit VPS, vous pouvez réduire l’instrumentation au lieu de la supprimer complètement.

• Si vous êtes constamment CPU-bound avec une faible latence des requêtes, pensez à réduire les consommateurs de Performance Schema.
• Mais gardez suffisamment de visibilité pour attraper les régressions. Déboguer sans métriques, c’est juste de l’écriture créative.

5) Réglez prudemment les limites de tables temporaires

Les apps web adorent ORDER BY et GROUP BY, souvent avec des jeux de résultats trop larges.

• tmp_table_size et max_heap_table_size peuvent réduire les tables temporaires sur disque, mais si vous les montez trop haut vous exploserez la mémoire sous concurrence.

Esquisse de configuration de départ MySQL (pas une religion à copier-coller)

Ceci illustre l’esprit pour un VPS mix web+DB 4GB. Ajustez selon les mesures ci-dessus.

cr0x@server:~$ sudo cat /etc/mysql/mysql.conf.d/99-vps-tuning.cnf
[mysqld]
innodb_buffer_pool_size = 1G
innodb_buffer_pool_instances = 1
max_connections = 150
innodb_flush_log_at_trx_commit = 1
innodb_flush_method = O_DIRECT
slow_query_log = ON
long_query_time = 0.5

Ce que cela signifie : Buffer pool plus petit pour préserver de la marge, connexions plafonnées, I/O direct pour réduire le double-caching (dépend de votre FS et workload), et slow query logging pour obtenir des preuves.

Décision : Appliquez et redémarrez pendant une fenêtre calme, puis re-vérifiez swap/iowait et slow logs. Si la latence s’améliore et que le swap disparaît, vous êtes sur la bonne voie.

Si vous choisissez PostgreSQL : quoi régler en premier sur un VPS 4GB

Postgres est excellent pour les sites web, mais il vous fait payer l’attention à trois choses tôt : le nombre de connexions, le vacuum, et le WAL/checkpoints. Ignorez l’une d’elles et vous aurez des ralentissements « aléatoires » qui ne sont pas du tout aléatoires.

1) Installez le pooling de connexions (pgBouncer) avant d’en « avoir besoin »

Sur 4GB, les backends Postgres ne sont pas jetables. Un pic de trafic qui ouvre des centaines de connexions peut se transformer en pression mémoire et overhead de commutation de contexte.

Faites : exécutez pgBouncer en mode transaction pooling pour les workloads web typiques.

Ne faites pas : monter max_connections à 500 et appeler ça du scaling.

2) Réglez shared_buffers de façon conservatrice

Le folklore dit « 25% de la RAM ». Sur un VPS mix web+BD, je commencerais autour de :

• 512MB à 1GB pour shared_buffers.

Postgres bénéficie aussi du cache de page du système. Donner tout à shared_buffers peut priver le noyau et les autres processus.

3) Gardez work_mem bas globalement ; augmentez-le chirurgicalement

work_mem est par opération de tri/hash, par requête, par backend. Vous n’avez pas assez de RAM pour l’exubérance ici.

• Commencez à 4–16MB globalement selon la concurrence.
• Augmentez pour un rôle ou une session spécifique si vous avez une requête de rapport connue et lourde.

4) Gardez autovacuum en bonne santé

Autovacuum n’est pas un ménage optionnel. C’est la façon dont Postgres évite le bloat des tables et permet les index-only scans.

• Surveillez les tuples morts et le retard de vacuum.
• Ajustez les seuils autovacuum par table chaude si nécessaire.

5) Rendez les checkpoints moins en pics

Sur un stockage VPS lent, les pics de checkpoint apparaissent comme des falaises de latence aléatoires. Des checkpoints plus lisses réduisent la douleur.

• Augmentez checkpoint_timeout (avec raison).
• Fixez checkpoint_completion_target haut pour étaler les écritures.

Esquisse de configuration de départ Postgres

cr0x@server:~$ sudo cat /etc/postgresql/16/main/conf.d/99-vps-tuning.conf
shared_buffers = 768MB
effective_cache_size = 2304MB
work_mem = 8MB
maintenance_work_mem = 128MB
checkpoint_completion_target = 0.9
checkpoint_timeout = 10min
wal_compression = on
log_min_duration_statement = 500ms

Ce que cela signifie : shared_buffers conservateur, hint réaliste pour le cache effectif, work_mem modeste, checkpoints plus lisses, et journalisation des requêtes lentes.

Décision : Appliquez et reload/restart, puis surveillez la croissance des fichiers temporaires et le timing des checkpoints. Si votre disque est lent, lissage des checkpoints se traduira par moins de falaises de latence.

Connexions : le tueur silencieux sur petites machines

Si vous hébergez des sites, la façon la plus simple de ruiner une base est de laisser l’application décider de la concurrence. PHP-FPM workers + « ouvrir une connexion BD par requête » devient une horde tonitruante. Sur 4GB, vous ne survivez pas en étant plus rapide. Vous survivez en étant plus calme.

À quoi ressemble « trop de connexions »

• CPU BD élevé mais ne faisant pas de travail utile (commutation de contexte, contention de mutex).
• L’usage mémoire grimpe avec le trafic jusqu’au swap.
• La latence augmente même pour les requêtes simples.

Ce que vous faites à la place

• Limitez la concurrence applicative : moins d’enfants PHP-FPM, ou configurez le gestionnaire de processus pour éviter les explosions.
• Utilisez du pooling : pgBouncer pour Postgres ; pour MySQL, envisagez du pooling côté application ou assurez-vous que les connexions persistantes sont configurées sagement.
• Échouez vite : parfois réduire max_connections est la bonne décision parce que cela protège la machine d’un thrash complet.

Blague #2 : Connexions illimitées c’est comme un buffet de crevettes illimité — ça a l’air super jusqu’à ce que vous soyez celui qui ferme le restaurant.

Stockage et réalités du système de fichiers : IOPS, fsync, et pourquoi « SSD rapide » ment

Sur les plateformes VPS, « stockage SSD » peut signifier n’importe quoi, du NVMe respectable à un device bloc réseau partagé ayant une mauvaise journée. Les bases de données se soucient plus de la latence que du débit. Quelques millisecondes de fsync en plus par commit deviennent visibles sur le site.

Comment les écritures vous blessent différemment en MySQL vs PostgreSQL

• MySQL/InnoDB : redo logging + doublewrite buffer (selon config/version) + flush des pages sales. Des flushs en rafales peuvent amplifier la latence.
• PostgreSQL : écritures WAL + checkpoints + background writer. Le vacuum crée aussi des I/O, et le bloat augmente les I/O futures.

Bonne pratique pour petit VPS : réduire d’abord l’amplification d’écriture

• Corrigez les apps bavardes (trop de petites transactions).
• Batch les écritures quand la cohérence le permet.
• Évitez de mettre à jour constamment des colonnes « last_seen » sur chaque requête si ce n’est pas nécessaire.
• Gardez les index légers ; chaque index est une taxe d’écriture.

Pièges du système de fichiers

• Ne mettez pas les bases sur des filesystems réseau instables à moins que la plateforme garantisse les sémantiques de durabilité.
• Méfiez-vous des conditions de disque plein : Postgres et MySQL se comportent mal de manière différente, mais aucune de ces manières n’est « agréable ».

Trois mini-histoires d’entreprise issues du terrain

1) L’incident causé par une fausse hypothèse : « Le cache va couvrir »

Une petite équipe gérait une collection de sites marketing et un service de checkout sur un VPS 4GB. Il y avait MySQL, Nginx et PHP-FPM. Le trafic était « majoritairement statique », ce qui était vrai jusqu’à ce qu’une campagne lance et que le service checkout reçoive des rafales de requêtes authentifiées.

L’hypothèse était que le cache de pages et le cache applicatif couvriraient les lectures, donc ils ont poussé innodb_buffer_pool_size près de 3GB pour « rendre la base rapide ». Ça marchait bien en heure calme. Puis la campagne a frappé.

PHP-FPM a spawné pour gérer le trafic. Chaque worker utilisait plus de mémoire que ce que quelqu’un se souvenait. L’OS a commencé à swapper. Le buffer pool énorme de la base laissait moins de place au kernel pour tout le reste. La latence n’a pas augmenté graduellement ; elle s’est écrasée. Le checkout a commencé à expirer, les retries ont augmenté le trafic, et la tempête de retries a transformé un problème de ressource en un DoS auto-hébergé.

La correction n’était pas exotique. Ils ont réduit le buffer pool pour laisser de la marge, plafonné les enfants PHP-FPM, abaissé max_connections de MySQL pour que le système échoue vite au lieu de thrash, et mis une file explicite devant le checkout. Ils ont aussi appris la différence opérationnelle entre « mémoire libre » et « mémoire disponible sous rafale ».

2) L’optimisation qui a mal tourné : « Augmente juste work_mem, c’est bon »

Une appli interne tournait sur PostgreSQL. Les utilisateurs se plaignaient de rapports lents, alors quelqu’un a augmenté significativement work_mem parce qu’un article disait que ça réduirait l’I/O des fichiers temporaires. Ça l’a fait. Pour un utilisateur. Dans une session.

Puis un lundi est arrivé. Plusieurs utilisateurs ont exécuté des rapports en concurrence. Ces rapports faisaient chacun plusieurs tris et hash joins. Postgres a alloué correctement work_mem par opération. L’utilisation mémoire a explosé. Le VPS n’a pas planté immédiatement ; il est devenu de plus en plus lent quand le swap s’est déclenché. La base semblait « vivante » mais chaque requête attendait derrière la tempête d’I/O causée par le swapping.

L’équipe a rétabli work_mem à une valeur conservatrice et a plutôt corrigé la requête de rapport. Ils ont ajouté un index manquant, réduit les colonnes sélectionnées, et introduit une table de synthèse rafraîchie périodiquement. Pour la requête lourde, ils ont utilisé un rôle avec un work_mem plus élevé et l’ont forcée via un parcours de reporting contrôlé. La leçon n’était pas « ne jamais tuner ». C’était « ne touchez pas globalement un paramètre pour un problème local sur une petite machine ».

3) La pratique ennuyeuse mais correcte qui a sauvé la mise : « Capper les connexions et logger les requêtes lentes »

Une autre organisation hébergeait plusieurs petits sites clients sur un VPS 4GB partagé. Rien de fancy. Ils ne cherchaient pas les microsecondes. Ils ont fait trois choses ennuyeuses dès le jour 1 : plafonner les connexions BD, activer le slow query logging avec un seuil sensé, et surveiller l’usage disque avec une alerte bien avant 90%.

Un après-midi, une mise à jour de plugin a introduit une régression de requête. Le site n’est pas tombé immédiatement parce que les caps de connexion ont empêché la charge illimitée de s’empiler dans la BD. À la place, quelques requêtes ont échoué rapidement, ce qui a rendu le problème visible sans fondre la machine entière.

Le slow query log contenait le coupable : une requête qui a commencé à scanner une grande table sans index utile. Ils ont ajouté l’index, ont corrigé la régression, et l’incident a été contenu en une courte fenêtre. Pas de mystère. Pas de « c’est parti tout seul ». Pas d’archéologie du week-end.

C’est à quoi ressemble la fiabilité ennuyeuse : échec contrôlé, collecte de preuves, et assez de marge pour qu’un mauvais déploiement ne devienne pas une catastrophe système.

Erreurs courantes : symptôme → cause racine → correctif

1) Symptom : arrêts soudains de 10–60s sur tout le site

Cause racine : pics de latence stockage pendant checkpoints/flushs ou tempêtes de swap.

Correctif : confirmez avec iostat et vmstat ; réduisez la pression mémoire (caches BD plus petits, moins de workers app), lissez les checkpoints (Postgres), et réduisez l’amplification d’écriture (les deux).

2) Symptom : CPU BD élevé, les requêtes individuellement « pas si lentes »

Cause racine : trop de connexions simultanées ; l’overhead de contention domine.

Correctif : plafonnez les connexions ; ajoutez du pooling (pgBouncer) ; réduisez la concurrence PHP-FPM ; mettez du cache côté app ou reverse proxy ; échouez vite plutôt que de thrash.

3) Symptom : Postgres grossit et la performance se dégrade lentement

Cause racine : retard de vacuum et bloat table/index dû à un autovacuum insuffisant ou des transactions longues.

Correctif : identifiez les sessions idle-in-transaction, réglez autovacuum par table chaude, et arrêtez de garder les transactions ouvertes à travers des requêtes web.

4) Symptom : MySQL « Waiting for table metadata lock » dans le processlist

Cause racine : modification de schéma ou DDL bloquée par des transactions longues ; les requêtes se mettent en file derrière les locks de métadonnées.

Correctif : planifiez le DDL en fenêtres de maintenance ; gardez les transactions courtes ; utilisez des approches de changement de schéma en ligne si nécessaire.

5) Symptom : beaucoup de fichiers temporaires ou « Using temporary; Using filesort » en MySQL

Cause racine : index manquants pour les patterns ORDER BY/GROUP BY ; requêtes triant de grands ensembles de données.

Correctif : ajoutez des index composites correspondant à filter+sort ; réduisez les colonnes sélectionnées ; paginez correctement ; évitez OFFSET pour des pages profondes.

6) Symptom : erreurs fréquentes « too many connections »

Cause racine : fuites de connexion applicatives, pas de pooling, ou pics du nombre de workers web.

Correctif : pooler les connexions ; définissez des timeouts sensés ; limitez la concurrence app ; réglez max_connections à un nombre que vous pouvez financer.

7) Symptom : après le « tuning », la performance a empiré

Cause racine : un réglage global (comme work_mem ou un buffer pool trop grand) a augmenté la mémoire par connexion et déclenché le swap sous concurrence.

Correctif : revenez en arrière ; appliquez le tuning par utilisateur/par requête ; mesurez explicitement la mémoire et la concurrence.

Listes de vérification / plan étape par étape

Étape 0 : Décidez ce que « bon » signifie

• Choisissez un objectif de type SLO : ex., homepage p95 < 500ms, checkout p95 < 800ms.
• Choisissez une fenêtre de mesure et capturez la baseline (CPU, RAM, swap, iowait, connexions BD, requêtes lentes).

Étape 1 : Stabilisez l’hôte

• Assurez-vous que le disque a au moins 15–20% d’espace libre.
• Assurez-vous que vous ne swappez pas pendant les pics normaux.
• Définissez des limites de service conservatrices (systemd limits si nécessaire) pour éviter des processus incontrôlés.

Étape 2 : Capper la concurrence délibérément

• Réglez PHP-FPM max children à un nombre que vous pouvez financer en RAM.
• Réglez DB max_connections pour protéger la machine.
• Sur Postgres : déployez pgBouncer et réduisez les connexions backend.

Étape 3 : Réglez les premiers knobs mémoire

• MySQL : réglez innodb_buffer_pool_size pour que le working set rentre sans priver l’OS.
• Postgres : réglez shared_buffers de façon conservatrice ; gardez work_mem bas globalement.

Étape 4 : Activez la collecte d’éléments de preuve

• MySQL : slow query log à 0.5–1s pendant la pointe, puis analyser et corriger.
• Postgres : log_min_duration_statement et idéalement pg_stat_statements.

Étape 5 : Corrigez les 3 principaux patterns de requêtes

• Ajoutez les index manquants qui réduisent les scans de lignes.
• Éliminez les N+1 dans l’app.
• Arrêtez de faire des requêtes coûteuses par requête ; pré-calculer ou mettre en cache.

Étape 6 : Retester et mettre des garde-fous

• Relancez vos tâches de triage en période de pointe.
• Ajoutez des alertes sur l’activité swap, l’utilisation disque, les compteurs de connexions, et le taux de requêtes lentes.
• Documentez vos réglages « sûrs » et la raison pour que votre futur vous ne les annule pas.

FAQ

1) Sur un VPS 4GB, dois-je prioriser le cache BD ou le cache de page OS ?

Priorisez la stabilité. Pour une machine unique web+BD, ne privez pas l’OS. Un cache BD modéré plus de la marge l’emporte sur un cache géant qui déclenche le swap lors des pics.

2) PostgreSQL est-il « plus lent » que MySQL pour les sites web ?

Pas en règle générale. Pour beaucoup de workloads web, l’un ou l’autre est assez rapide quand les index sont corrects. Le vrai différenciateur sur 4GB est la gestion des connexions et les patterns d’écriture, pas la vitesse brute du moteur.

3) Quel est le premier réglage MySQL que je devrais changer ?

innodb_buffer_pool_size, dimensionné selon votre réalité. Ensuite plafonnez max_connections. Puis activez le slow query logging et corrigez ce qu’il montre.

4) Quel est le premier réglage PostgreSQL que je devrais changer ?

La stratégie de pooling des connexions (pgBouncer) et max_connections. Ensuite shared_buffers conservateur et la journalisation/pg_stat_statements pour identifier les principales requêtes.

5) Puis-je juste augmenter le swap pour résoudre les problèmes mémoire ?

Vous pouvez augmenter le swap pour éviter des crashs OOM brutaux, mais le swap n’est pas de la RAM performante. Si votre base ou les workers PHP atteignent régulièrement le swap, la latence deviendra imprévisible.

6) Dois-je désactiver fsync pour gagner en vitesse ?

Non pour des sites en production où l’intégrité des données compte. Si vous désactivez la durabilité et que l’hôte plante, vous pouvez perdre des données. Les benchmarks adorent ; les clients non.

7) Comment savoir si je suis lié par l’I/O ?

Un iowait élevé dans vmstat, un await et %util élevés dans iostat, et des sessions DB en attente d’événements IO (Postgres) sont des signaux forts.

8) Quand devrais-je séparer web et BD sur des serveurs distincts ?

Quand vos réglages deviennent des compromis entre la mémoire web et la mémoire BD, ou quand la latence stockage rend les écritures BD imprévisibles. La séparation vous apporte isolation et planification de capacité plus claire.

9) Les valeurs par défaut sont-elles suffisantes aujourd’hui ?

Les valeurs par défaut sont meilleures qu’avant, mais elles ne sont pas adaptées à votre situation « tout sur une seule machine 4GB ». Les caps de connexions et le budget mémoire restent de votre ressort.

10) Quel est le « gain de performance » le plus sûr que je peux faire sans expertise DB profonde ?

Activez le slow query logging (ou pg_stat_statements), identifiez les 3 gros consommateurs de temps, et ajoutez les index appropriés. Plafonnez aussi les connexions pour que le serveur reste stable sous charge.

Prochaines étapes qui ne vous mettront pas dans l’embarras plus tard

Sur un VPS 4GB, vous n’optimisez pas une base de données. Vous gérez la contention entre web, base et stockage tout en essayant de garder la latence ennuyeuse.

1. Exécutez la feuille de diagnostic rapide pendant la pointe et notez ce qui se passe réellement : swap, iowait, connexions, locks, requêtes principales.
2. Plafonnez la concurrence en premier : workers PHP-FPM et connexions BD. Ajoutez pgBouncer si vous êtes sur Postgres.
3. Réglez le premier knob mémoire (InnoDB buffer pool ou shared_buffers Postgres) à une valeur conservative qui laisse de la marge.
4. Activez les preuves (slow query logs / pg_stat_statements) et corrigez les principaux coupables avec des index et des changements de requêtes.
5. Re-vérifiez le disque et le comportement d’écriture ; lissez les checkpoints, réduisez les déversements temp, et arrêtez les écritures bruyantes inutiles.
6. Décidez si la vraie correction est architecturale : déplacer la BD sur un VPS séparé, upgrader le tier de stockage, ou utiliser une DB managée. Parfois le paramètre de tuning le plus efficace est votre facture.

Si vous ne faites qu’une chose aujourd’hui : plafonnez les connexions et arrêtez le swap. Tout le reste n’est que garniture.