Optimisation de configuration MariaDB / MySQL

Voici une configuration qui fonctionne sur un site à fort trafic.

!!! note À adapter à la taille de la base et au trafic utilisateurs.

On désactive le cache de requêtes.
L’invalidation de ce cache est coûteuse et vérrouille tout le cache pendant sa mise à jours.

set global query_cache_type=0;
set global optimizer_switch="subquery_cache=off";

On défini par exemple :

# si la db fait 20Go
innodb_buffer_pool_size = 20Go
innodb_buffer_pool_instances = 20

# threads innodb simultanés : 2 x nb de CPU + nb de disques
innodb_thread_concurrency = 86

# vérifier la fréquence de fork de Mariadb/MySQL et les pics de nb de threads
thread_cache = 128
# on vérifie la fréquence d'accès aux tables
table_cache = 2048

# on active les log des requêtes lentes : 
slow_query_log = 1
# on remonte les requêtes lentes au delà de la seconde
long_query_time = 1
slow_launch_time = 1

Explications

Dimensionner correctement innodb_buffer_pool permet de garder les données en mémoire après leur lecture.
La limitation de innodb_thread_concurrency évite les fork bombs lorsque des requêtes sont bloquées.
Le thread_cache permet d’éviter la création et la suppression de threads lors d’un trafic normal.

Explications#

Explications