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 suppresion de threads lors d'un trafic normal.