Optimiser MySQL et MariaDB : améliorer les performances sur les serveurs Linux

Les performances d’une base de données influencent directement la rapidité et la stabilité des applications Web. MySQL et MariaDB offrent de nombreuses options d’optimisation, allant de la configuration aux index, en passant par le cache et la réplication. Des ajustements ciblés permettent d’exploiter plus efficacement les ressources et de réduire nettement les temps de réponse des requêtes.

Pourquoi optimiser MariaDB et MySQL ?

L’optimisation de MariaDB et de MySQL est essentielle, car des bases de données non optimisées atteignent vite leurs limites. Une performance insuffisante peut entraîner des temps de chargement élevés, des délais d’expiration ou même des interruptions du système.

Lorsque le volume de données augmente ou que plusieurs utilisateurs accèdent simultanément à la base, chaque requête inefficace sollicite inutilement le processeur et la mémoire vive. Le stockage de données redondantes ou l’absence d’index ralentissent également le traitement. Une base de données bien optimisée réduit la charge du serveur, améliore l’évolutivité et garantit la stabilité des applications, même en cas de forte activité.

Comment optimiser MySQL/MariaDB ?

Il existe de nombreuses façons d’optimiser une base de données MariaDB ou MySQL sur un serveur Linux. Cela comprend les ajustements de configuration, l’optimisation des index, l’amélioration des requêtes, le réglage d’InnoDB ainsi que l’utilisation du cache et de la réplication. Les principales mesures sont présentées ci-dessous.

Option 1 : identifier les requêtes lentes

Une étape importante pour optimiser MySQL/MariaDB consiste à repérer les requêtes lentes ou inefficaces. Une requête mal structurée peut ralentir l’ensemble de la base de données.

MySQL et MariaDB peuvent être configurés pour consigner toutes les requêtes dont l’exécution dépasse un certain seuil défini. Cela vous permet d’analyser les requêtes lentes et de les corriger au besoin.

Pour activer la journalisation des requêtes lentes, connectez-vous à MySQL ou MariaDB :

mysql -u root -p

Entrez la commande suivante pour activer la journalisation :

SET GLOBAL slow_query_log = 'ON';

La valeur seuil par défaut est fixée à 10 secondes. Utilisez la commande suivante pour activer la journalisation de toutes les requêtes dont l’exécution dépasse une seconde :

SET GLOBAL long_query_time = 1;

Ces requêtes seront enregistrées dans le fichier /var/lib/mysql/hostname-slow.log.

Des outils de surveillance comme mysqltuner ou performance_schema peuvent également être utilisés pour identifier les requêtes à optimiser et fournir des informations précieuses sur les performances.

Option 2 : adapter la configuration InnoDB

La configuration de MariaDB/MySQL est l’un des moyens les plus efficaces pour améliorer durablement les performances d’une base de données. De nombreuses installations par défaut utilisent des valeurs génériques adaptées à de petits environnements de test, mais inappropriées pour des systèmes en production avec un grand nombre de requêtes. En ajustant finement les paramètres InnoDB, la base de données exploite les ressources de manière bien plus efficace.

Les paramètres les plus importants incluent notamment :

• innodb_flush_log_at_trx_commit : ce paramètre équilibre performance et fiabilité. La valeur par défaut 1 écrit chaque transaction immédiatement sur le disque, garantissant une sécurité maximale, mais ralentissant les performances en cas de forte charge transactionnelle. La valeur 2 réduit considérablement les opérations d’E/S, avec un risque minime de perte de données en cas de crash.
• innodb_log_file_size : la taille des fichiers journaux InnoDB influence directement la vitesse d’écriture. Des fichiers plus grands permettent de stocker davantage de transactions en mémoire avant leur écriture sur le disque.
• innodb_file_per_table : un fichier de tablespace distinct est créé pour chaque table InnoDB. Cela simplifie la gestion des grandes tables, réduit la fragmentation du tablespace commun et améliore la performance lors des sauvegardes.
• innodb_buffer_pool_size : cette valeur doit généralement représenter entre 50 et 80 % de la mémoire vive disponible, afin de conserver un maximum de données et d’index en mémoire.
• innodb_flush_method : ce paramètre définit la méthode d’écriture des données et journaux sur le disque. Réglez cette option sur O_DIRECT pour éviter la double mise en tampon.

Pour modifier les paramètres InnoDB, ouvrez le fichier my.cnf et ajustez la configuration. Voici un exemple de configuration typique :

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_log_file_size = 512M
innodb_file_per_table = 1
innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT

Redémarrez MariaDB/MySQL pour que les modifications prennent effet.

Option 3 : adapter les index

Les index sont essentiels pour accélérer les requêtes MySQL ou MariaDB. Plutôt que de parcourir chaque ligne d’une table, la base de données peut accéder directement aux enregistrements pertinents via l’index.

Cependant, un trop grand nombre d’index ou des index mal choisis peuvent nuire aux performances. Chaque index consomme de l’espace et alourdit les opérations d’écriture. Il est donc recommandé d’indexer uniquement les colonnes fréquemment utilisées dans les clauses WHERE, JOIN ou ORDER BY.

Exemple : si la table users est souvent interrogée par la colonne email, créez un index pour accélérer ces requêtes :

CREATE INDEX idx_user_email ON users(email);

Avec cet index, des requêtes telles que

SELECT * FROM users WHERE email='xyz@example.com';

…s’exécutent nettement plus rapidement, car la base de données n’a pas besoin de parcourir chaque ligne de la table, mais accède directement aux entrées correspondantes.

Les index combinés peuvent également être utiles lorsque plusieurs colonnes sont fréquemment utilisées ensemble dans des requêtes.

Les index devenus inutiles ou rarement sollicités doivent être supprimés régulièrement afin d’économiser de l’espace et d’améliorer la performance des opérations d’écriture. Par exemple, pour supprimer l’index idx_old_column :

DROP INDEX idx_old_column ON users;

Option 4 : optimiser les requêtes

Les requêtes SQL complexes ou mal conçues peuvent surcharger la base de données et ralentir considérablement les performances, surtout lorsqu’elles concernent de grandes tables.

Pour optimiser les requêtes, commencez par analyser leur mode d’exécution à l’aide de la commande EXPLAIN, qui affiche le plan d’exécution détaillé et aide à repérer les points à améliorer.

EXPLAIN SELECT id, email FROM users WHERE status='active';

Avec EXPLAIN, MySQL et MariaDB indiquent quels index sont utilisés, combien de lignes sont lues et dans quel ordre les tables sont traitées. Vous pouvez ainsi évaluer l’efficacité d’une requête et déterminer si des optimisations sont nécessaires, par exemple en ajoutant des index ou en ajustant les jointures.

Évitez les requêtes du type SELECT *, car elles chargent toutes les colonnes, y compris celles dont vous n’avez pas besoin. Sélectionnez uniquement les colonnes nécessaires afin de réduire le volume de données transférées et d’accélérer l’exécution. Pour les jointures complexes, formulez les conditions dans la clause WHERE de manière aussi précise que possible afin d’éviter les scans complets de tables.

Option 5 : réplication et mise en cache

La réplication, c’est-à-dire la répartition de la charge sur plusieurs serveurs, et la mise en cache, qui réduit le nombre d’accès directs à la base de données, contribuent également à optimiser MariaDB et MySQL.

La réplication repose généralement sur le principe primaire/réplique : le serveur maître gère toutes les opérations d’écriture, tandis qu’un ou plusieurs serveurs répliquent les données et prennent en charge les requêtes de lecture. Ce modèle permet de répartir la charge et d’éviter la saturation du serveur principal. Bien que la mise en place de la réplication demande un certain travail initial, elle améliore nettement la performance, notamment pour les applications à fort trafic.

La mise en cache peut, elle aussi, accélérer sensiblement les temps de réponse. MySQL et MariaDB disposent du Query Cache, qui conserve en mémoire les résultats des requêtes fréquemment exécutées. Ainsi, les mêmes requêtes n’ont pas besoin d’être recalculées.

Vous pouvez définir la taille du cache et activer le Query Cache à l’aide des paramètres suivants :

SET GLOBAL query_cache_size = 64*1024*1024;
SET GLOBAL query_cache_type = 1;

Pour les applications modernes, il est également judicieux d’utiliser des solutions de mise en cache externes comme Redis, qui permettent d’accéder encore plus rapidement aux données fréquemment demandées.

Option 6 : partitionnement des tables

Pour les tables très volumineuses, le traitement des requêtes peut devenir plus lent, car la base de données doit parcourir un grand nombre de lignes. Le partitionnement permet de diviser une table en segments plus petits et logiquement séparés, par exemple selon la date, l’intervalle d’ID ou d’autres critères.

Chaque partition est gérée en interne comme une table distincte, ce qui accélère les requêtes ciblant uniquement certaines partitions.

Voici un exemple de partitionnement par année pour une table de commandes :

CREATE TABLE orders (
id INT,
order_date DATE,
amount DECIMAL(10,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025)
);

Dans cet exemple, toutes les commandes de 2023 sont stockées dans la partition p2023, et celles de 2024 dans la partition p2024.

Option 7 : utiliser le pool de connexions

Chaque nouvelle connexion à MySQL ou MariaDB consomme du temps et des ressources. Si votre application ouvre et ferme une connexion à chaque requête, cela génère une charge inutile sur le serveur.

Le pool de connexions, utilisé pour optimiser MariaDB et MySQL, permet de maintenir un certain nombre de connexions ouvertes. Les applications réutilisent ensuite ces connexions existantes au lieu d’en créer de nouvelles à chaque requête.

Voici un exemple en PHP avec mysqli :

$mysqli = new mysqli('localhost', 'user', 'password', 'db');
$mysqli->options(MYSQLI_OPT_CONNECT_TIMEOUT, 5);

Au lieu d’ouvrir une nouvelle connexion pour chaque requête, le pool réutilise les connexions déjà établies. Cela réduit la charge sur le serveur de base de données et améliore la rapidité des temps de réponse.