Hébergement

Lorsqu'un site web à fort trafic subit une explosion soudaine de son audience, la base de données relationnelle est presque systématiquement le premier composant d'infrastructure à montrer des signes de faiblesse. Dans l'écosystème Drupal, dont l'architecture logicielle repose sur de multiples requêtes complexes de jointure pour construire chaque entité, chaque bloc de vue et chaque métadonnée de cache, la montée en charge met rapidement à genoux le serveur de données.

La réponse classique consiste à augmenter les ressources matérielles : passer d'un serveur dédié doté de 64 Go de RAM à un mastodonte de 256 Go ou louer une instance cloud de dernière génération. Pourtant, cette escalade financière ne résout pas le problème structurel sous-jacent. Cet article démontre comment concevoir une architecture distribuée résiliente sous Debian 12 en déployant une réplication MariaDB pilotée par ProxySQL, offrant un routage intelligent des requêtes sans aucune altération du code applicatif Drupal.

Les limites de la montée en charge verticale des bases de données relationnelles

L'augmentation continue de la mémoire vive et de la puissance processeur d'un serveur unique présente des rendements décroissants rapides. Dès lors que le volume de requêtes concurrentes s'envole, le moteur de stockage InnoDB subit une double contrainte physique et logique.

Verrous transactionnels et saturation I/O sous fort trafic

Contrairement aux lectures pures, chaque opération de mise à jour, d'insertion ou de suppression requiert des verrous exclusifs sur les lignes modifiées et des écritures synchrones dans le journal de transactions (redo log). Drupal effectue des écritures régulières même lors d'une simple navigation anonyme si la télémétrie, les compteurs de statistiques ou les sessions dynamiques ne sont pas externalisés :

• La mise à jour des entrées de cache en base génère des verrous temporaires sur la table de cache applicative.
• L'écriture dans la table watchdog pour le journal d'erreurs monopolise des descripteurs de fichiers disque.
• Les requêtes de lecture massives se retrouvent bloquées derrière les verrous d'écriture, ce qui entraîne une explosion en cascade de la latence de rendu PHP-FPM.

La concurrence écriture-lecture au cœur du moteur InnoDB

Dans un scénario de publication de contenu ou de gestion de paniers d'achat, le moteur InnoDB maintient la cohérence grâce au mécanisme MVCC (Multi-Version Concurrency Control). Bien que ce procédé permette aux lectures de ne pas bloquer les écritures simples, il exige une allocation importante de l'espace d'annulation (undo logs).

Lorsque plusieurs centaines d'utilisateurs parcourent le catalogue pendant que des administrateurs publient des contenus volumineux ou que des flux d'import s'exécutent en arrière-plan :

• Le buffer pool se remplit de versions historiques de pages de données au détriment des pages de données fréquemment consultées.
• La charge de balayage disque augmente pour purger l'espace d'annulation.
• Le processeur gaspille des cycles précieux dans la gestion de l'ordonnancement des threads et la résolution des verrous de mutex.

La seule réponse pérenne consiste à séparer physiquement les responsabilités : un nœud unique dédié aux écritures strictes et une grappe de nœuds réplicas chargés d'absorber la totalité du flux de lecture.

Architecture de réplication sous Debian 12 : mise en place avec GTID

Pour illustrer cette architecture de production, nous retenons un environnement composé de trois machines virtuelles ou serveurs dédiés sous Debian 12 (Bookworm) :

• db-primary (192.168.10.10) : nœud primaire recevant les écritures et générant les journaux de transactions binaires.
• db-replica-01 (192.168.10.11) : premier serveur de lecture.
• db-replica-02 (192.168.10.12) : second serveur de lecture.
• web-app (192.168.10.20) : serveur hébergeant Drupal 10 ou 11 et l'instance ProxySQL locale.

Nous utilisons MariaDB 10.11 LTS, version par défaut et stable sous Debian 12. La réplication s'appuie impérativement sur les identifiants de transaction globaux (GTID), qui suppriment la gestion fragile des coordonnées physiques de fichiers et de positions binaires.

Configuration du nœud primaire

Sur la machine 192.168.10.10, créez un fichier de configuration dédié à la réplication :

# /etc/mysql/mariadb.conf.d/60-replication-primary.cnf
[mysqld]
server-id               = 101
bind-address            = 0.0.0.0
log_bin                 = /var/log/mysql/mariadb-bin
log_bin_index           = /var/log/mysql/mariadb-bin.index
binlog_format           = ROW
expire_logs_days        = 7
max_binlog_size         = 500M

# Activation du GTID strict
gtid_strict_mode        = 1

# Optimisation de la cohérence disque
innodb_flush_log_at_trx_commit = 1
sync_binlog             = 1

Redémarrez le service MariaDB et créez le compte dédié à la réplication sur le nœud primaire :

-- Connexion administrative sur db-primary
CREATE USER 'repl_user'@'192.168.10.%' IDENTIFIED BY 'CleSecreteReplication2026!';
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'repl_user'@'192.168.10.%';
FLUSH PRIVILEGES;

Effectuez ensuite une sauvegarde cohérente avec mariadb-dump pour amorcer les réplicas sans bloquer les écritures :

mariadb-dump --all-databases --single-transaction --master-data=2 \
  --routines --triggers --events -u root -p > /tmp/mariadb_initial_dump.sql

Configuration des nœuds réplicas

Sur chaque réplica (db-replica-01 et db-replica-02), déployez une configuration miroir en modifiant uniquement l'identifiant unique :

# /etc/mysql/mariadb.conf.d/60-replication-replica.cnf
[mysqld]
server-id               = 102 # Indiquer 103 pour db-replica-02
bind-address            = 0.0.0.0
relay_log               = /var/log/mysql/mariadb-relay-bin
relay_log_index         = /var/log/mysql/mariadb-relay-bin.index
read_only               = 1
log_replica_updates     = 1
gtid_strict_mode        = 1

Après avoir restauré la sauvegarde initiale sur les réplicas, raccordez-les au serveur primaire en vous appuyant sur le protocole GTID :

-- Connexion administrative sur le réplica
STOP SLAVE;
CHANGE MASTER TO
  MASTER_HOST='192.168.10.10',
  MASTER_PORT=3306,
  MASTER_USER='repl_user',
  MASTER_PASSWORD='CleSecreteReplication2026!',
  MASTER_USE_GTID=current_pos;
START SLAVE;
SHOW SLAVE STATUS\G

Assurez-vous que les variables Slave_IO_Running et Slave_SQL_Running affichent toutes deux la valeur Yes.

Mise en œuvre de la réplication semi-synchrone

Par défaut, la réplication asynchrone n'offre aucune garantie qu'une transaction enregistrée sur le primaire soit parvenue à au moins un réplica avant que le client n'obtienne confirmation de validation. La réplication semi-synchrone résout cette incertitude sans induire la lourdeur d'un commit bi-phasé complet.

Installez les greffons nécessaires :

-- Sur le primaire
INSTALL SONAME 'semisync_master';
SET GLOBAL rpl_semi_sync_master_enabled = 1;
SET GLOBAL rpl_semi_sync_master_timeout = 1000; -- repli en asynchrone après 1 seconde d'absence de réponse

-- Sur chaque réplica
INSTALL SONAME 'semisync_slave';
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;

Cette configuration garantit qu'au moins un réplica a copié l'événement binaire dans son journal relais (relay log) avant que le thread primaire ne signale la complétion du commit à Drupal.

Routage applicatif transparent avec ProxySQL : séparer lectures et écritures

Modifier le code d'un projet Drupal ou de ses modules communautaires pour spécifier manuellement une base de données de lecture via l'API de base de données est une démarche risquée et non maintenable. ProxySQL s'intercale comme un routeur SQL de haute performance, ultra-léger et conscient du protocole MySQL.

Installation et topologie des groupes de serveurs

ProxySQL est installé directement sur le serveur hébergeant Drupal (192.168.10.20) afin de communiquer avec l'application via le socket local ou le port d'écoute standard 6033, éliminant ainsi toute couche de latence réseau supplémentaire.

L'accès à l'interface administrative de ProxySQL s'effectue via le port 6032 :

-- Connexion à l'interface d'administration ProxySQL
mysql -u admin -padmin -h 127.0.0.1 -P 6032

-- Déclaration des deux groupes de serveurs (Hostgroups)
-- Hostgroup 10 : Écritures (db-primary)
-- Hostgroup 20 : Lectures (db-replica-01, db-replica-02)

INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_replication_lag, weight) VALUES
(10, '192.168.10.10', 3306, 0, 1000),
(20, '192.168.10.11', 3306, 5, 100),
(20, '192.168.10.12', 3306, 5, 100);

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

Déclaration des utilisateurs applicatifs

ProxySQL doit connaître les identifiants utilisés par Drupal pour authentifier et transmettre les connexions :

INSERT INTO mysql_users (username, password, default_hostgroup) 
VALUES ('drupal_user', 'MotDePasseDrupalUltraSecurise!', 10);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Tout utilisateur démarre par défaut sur le groupe d'écriture (10), garantissant qu'en cas d'absence de règle spécifique, l'intégrité transactionnelle est préservée.

Définition des règles de routage SQL

L'aiguillage des requêtes SELECT vers les réplicas s'effectue au moyen d'expressions régulières compilées par ProxySQL :

-- Nettoyage préalable des règles
DELETE FROM mysql_query_rules;

-- Règle 1 : les requêtes de verrouillage FOR UPDATE doivent aller sur le primaire
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (1, 1, '^SELECT.*FOR UPDATE', 10, 1);

-- Règle 2 : les requêtes sur les tables de sessions et de sémaphores restent sur le primaire
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (2, 1, '^SELECT.*FROM (sessions|semaphore|queue)', 10, 1);

-- Règle 3 : toutes les autres requêtes SELECT sont dirigées vers les réplicas
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply) 
VALUES (3, 1, '^SELECT', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Configuration transparente côté Drupal

Dans le fichier settings.php de Drupal, la configuration de connexion ne référence pas les serveurs de base de données distants mais cible uniquement l'instance ProxySQL locale.

<?php
// Extrait de docroot/sites/default/settings.php

$databases['default']['default'] = [
  'database'  => 'drupal_production',
  'username'  => 'drupal_user',
  'password'  => 'MotDePasseDrupalUltraSecurise!',
  'prefix'    => '',
  'host'      => '127.0.0.1',
  'port'      => '6033',
  'namespace' => 'Drupal\\mysql\\Driver\\Database\\mysql',
  'driver'    => 'mysql',
  'pdo'       => [
    PDO::ATTR_PERSISTENT => FALSE,
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => TRUE,
  ],
];

Le code de Drupal ne subit aucune modification : chaque composant, vue, requête d'entité ou module sur mesure transmet ses requêtes au pilote standard, qui délègue le multiplexage et le routage d'infrastructure à ProxySQL.

Gestion du délai de réplication : garantir la cohérence des données

L'un des risques inhérents à toute architecture de réplication est le délai d'application des transactions sur les réplicas (replication lag). Si ce décalage n'est pas rigoureusement maîtrisé, un utilisateur soumettant un commentaire ou mettant à jour son profil sera immédiatement redirigé vers une vue lue sur un réplica en retard, donnant l'illusion trompeuse que son action a échoué.

Détection automatique du décalage dans ProxySQL

ProxySQL interroge en permanence l'état de chaque réplica. Dans notre définition des serveurs, le champ max_replication_lag = 5 indique à ProxySQL de mesurer la valeur de Seconds_Behind_Master.

Dès qu'un réplica dépasse 5 secondes de latence :

• ProxySQL place immédiatement le nœud concerné hors ligne pour les lectures.
• La charge de lecture est réassignée instantanément aux réplicas restants sains.
• Si tous les réplicas dépassent le seuil de tolérance, le trafic de lecture bascule automatiquement sur le primaire, évitant ainsi le service de données incohérentes.

Pour activer cette supervision, ProxySQL s'appuie sur un compte de monitoring dédié sur MariaDB :

-- Création du compte de supervision sur le nœud primaire
CREATE USER 'monitor'@'192.168.10.%' IDENTIFIED BY 'MonitPassword2026!';
GRANT REPLICATION CLIENT, PROCESS ON *.* TO 'monitor'@'192.168.10.%';
FLUSH PRIVILEGES;

Configurez ensuite ce compte dans l'interface d'administration de ProxySQL :

SET mysql-monitor_username = 'monitor';
SET mysql-monitor_password = 'MonitPassword2026!';
SET mysql-monitor_replication_lag_interval = 1000; -- Vérification toutes les secondes
LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

Stratégies Drupal pour la cohérence de lecture après écriture

Pour les flux critiques où même un retard d'une milliseconde est inacceptable, deux garde-fous supplémentaires doivent être adoptés :

• Utilisation explicite du nœud primaire dans les formulaires personnalisés : Drupal propose nativement la méthode \Drupal::database()->select() qui respecte les cibles si plusieurs connexions sont déclarées. Avec ProxySQL, il suffit d'englober les requêtes sensibles dans une transaction explicite $transaction = \Drupal::database()->startTransaction(); : ProxySQL détecte l'ouverture de transaction et canalise l'intégralité des requêtes subséquentes vers le groupe d'écriture jusqu'au commit final.
• Isolation des sessions des utilisateurs authentifiés : pour les administrateurs et rédacteurs de contenu connectés sur le back-office, vous pouvez configurer une règle ProxySQL spécifique basée sur le préfixe de session Drupal ou l'utilisateur MySQL afin de router la totalité de leur trafic vers le nœud primaire.

Comparaison des topologies de montée en charge pour bases relationnelles

Pour synthétiser les choix d'architecture à la disposition d'une équipe technique, voici l'évaluation des approches courantes :

• Montée en charge verticale simple
  • Avantages : complexité nulle, aucun problème de cohérence de lecture, configuration minimale sous Debian.
  • Inconvénients : coûts exponentiels des serveurs, limites physiques du matériel, arrêt complet lors des opérations de maintenance.
  • Adéquation : sites vitrines et projets à trafic modéré et prévisible.
• Cluster Galera synchrone multi-maîtres
  • Avantages : tolérance aux pannes élevée, bascule automatique sans perte, lectures locales performantes.
  • Inconvénients : pénalité de latence sur chaque écriture liée au quorum de certification, sensibilité extrême aux verrous sur les tables de sessions et de files d'attente Drupal.
  • Adéquation : architectures d'entreprise nécessitant un RPO nul avec un ratio de lecture supérieur à 95 pour cent.
• Réplication MariaDB GTID associée à ProxySQL
  • Avantages : absorption massive des pics de lecture, séparation transparente du trafic sans retoucher au code Drupal, gestion fine du replication lag.
  • Inconvénients : gestion asynchrone nécessitant une surveillance continue du décalage temporel, mise en place initiale plus technique.
  • Adéquation : plateformes médias, sites e-commerce et applications à fort volume de consultation concurrente.

FAQ : questions fréquentes sur ProxySQL, MariaDB et Drupal

ProxySQL introduit-il une latence réseau perceptible pour les requêtes Drupal ?

Lorsqu'il est déployé localement sur le même serveur que le moteur PHP-FPM, ProxySQL communique via un socket UNIX ou l'interface de bouclage local (127.0.0.1). La surconsommation de temps d'exécution est inférieure à 0,3 milliseconde par requête, ce qui est largement compensé par la suppression des files d'attente de requêtes bloquées sur la base de données.

Que se passe-t-il si le serveur primaire MariaDB tombe en panne ?

ProxySQL surveille la disponibilité des serveurs mais ne gère pas de manière autonome la promotion d'un réplica au rang de primaire. Pour mettre en place une bascule automatique (failover) sans coupure de service, il est recommandé de combiner ProxySQL avec un orchestrateur de cluster comme MariaDB MaxScale ou GitHub Orchestrator, qui promouvra un réplica via GTID et notifiera ProxySQL pour mettre à jour ses hostgroups.

Pourquoi ne pas utiliser le module Drupal Auto Entityqueue ou la gestion native des esclaves de Drupal ?

Drupal dispose historiquement d'une directive d'esclave (replica) dans le tableau $databases de son fichier settings.php. Cependant, cette implémentation logicielle requiert que chaque développeur utilise explicitement la cible replica dans ses requêtes d'entités ou de vues. Dès lors qu'un module tiers n'implémente pas ce paradigme, ses lectures sollicitent le primaire. ProxySQL agit de manière agnostique au niveau protocolaire et garantit un taux d'utilisation maximal des réplicas.

Comment ProxySQL gère-t-il les transactions ouvertes par Drupal ?

Dès que ProxySQL intercepte une commande BEGIN ou START TRANSACTION, il bascule automatiquement le multiplexage de connexion en mode exclusif et route l'ensemble des requêtes suivantes vers le hostgroup primaire (écriture), même s'il s'agit de requêtes SELECT. Le routage vers les réplicas ne reprend qu'après la validation (COMMIT) ou l'annulation (ROLLBACK) de la transaction.