Macro-tuning power!

MySQL est une base très utilisée dans le monde Internet.
Simple à prendre en main et hautement paramétrable, elle couvre les besoins des plus grands sites Web à forte audience. Encore faut-il savoir l’optimiser à ses besoins…

Cet article vous montre comment contrôler simplement les effets d’une optimisation d’une base MySQL.

L’optimisation ou l’art de trouver le bon équilibre

Un serveur a des ressources limitées. Les optimisations n’y changeront rien.
Le fine-tuning n’a que pour objectif d’utiliser 100% des ressources pour répondre à nos besoins.

Chaque service a ses propres besoins.
Par exemple, une base d’archivage devra être rapide en écriture, une base d’un portail Web devra être rapide en lecture et une base BI devra permettre l’exécution rapide de requêtes complexes.
Il n’existe donc pas une configuration optimale universelle, ni une façon universelle de structurer ses données, seulement des bonnes pratiques qui permettent d’arriver plus ou moins vite à la configuration idéale dans un contexte donné.

Ainsi l’optimisation d’une requête peut dégrader les autres. Tout est affaire d’équilibre. La meilleure optimisation est trouvée lorsque l’entropie du système est la plus faible.

Principe du contrôle rapide des optimisations

Le principe repose sur la mesure du temps d’exécution des requêtes lentes avant et après optimisation.

Vous allez utiliser la fonction MySQL benchmark().
SELECT BENCHMARK(count,expr) exécute count fois l’expression expr et renvoie le résultat et le temps nécessaire pour obtenir le résultat.
Ce temps d’exécution nous servira de référence, de valeur entropique. Au mieux, les optimisations doivent le faire diminuer. Au pire, il reste constant mais les ressources du serveur doivent être moins sollicitées.

BENCHMARK ne supporte que les expressions qui renvoie une ligne et une colonne de résultat. Vous devrez donc transformer les requêtes mesurées pour parvenir à ce résultat. Pour les SELECT, un COUNT(*) fera l’affaire.

Identifier les requêtes à optimiser

Je vous propose d’optimiser les requêtes les plus utilisées, les plus lentes et celles qui renvoient les plus gros volumes de données.
Celle-ci nous servirons à calculer l’entropie du système.

Activez la journalisation des requêtes SQL. Éditez et vérifiez la présence dans le fichier /etc/mysql/my.cnf des lignes suivantes :

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

Si besoin, redémarrez MySQL :

# service mysql restart

Et utilisez votre application quelques heures.

Les requêtes plus lentes qu’1 seconde ou qui n’utilisent pas d’index vont être stockées dans le fichier mysql-slow.log.

Récupérez les 3 requêtes les courantes :

# mysqldumpslow -t 3 -s c /var/log/mysql/mysql-slow.log 

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 74195  Time=0.00s (18s)  Lock=0.00s (1s)  Rows=1.0 (74001), @x
  SELECT lid, type, language, priority FROM xmlsitemap WHERE loc = 'S'

Count: 33299  Time=0.00s (162s)  Lock=0.00s (2s)  Rows=1.0 (33299), @x
  SELECT SUM(occurence) AS `sum` FROM `keyword` WHERE `hasresults` = 'S'

Count: 17644  Time=0.00s (18s)  Lock=0.00s (0s)  Rows=400.5 (7066526), @x
  SELECT * FROM variable

Récupérez les 3 requêtes les plus lentes :

# mysqldumpslow -t 3 -s at /var/log/mysql/mysql-slow.log 

Reading mysql slow query log from /var/log/mysql/mysql-slow.log

Count: 1  Time=16.13s (16s)  Lock=0.00s (0s)  Rows=0.0 (0), @x
  INSERT INTO cache_block (cid, data, created, expire, headers, serialized) VALUES ('S', 'S', N, -N, 'S', N)

Count: 12  Time=2.55s (30s)  Lock=0.00s (0s)  Rows=10.0 (120), @x
  SELECT * FROM `link` WHERE `state` = 'S' ORDER BY RAND()  LIMIT N

Count: 1  Time=2.39s (2s)  Lock=0.00s (0s)  Rows=10.0 (10), @x
  SELECT `id`,`name`,`url`,`description`,`pr`,`category`,`image`,`hits`,`vote` FROM 
`link` WHERE ((`id` = 'S' OR MATCH (name,url,description,keywords) AGAINST 
('S')) AND `state` = 'S') ORDER BY MATCH (name,url,description,keywords) 
AGAINST ('S') DESC LIMIT N,N

Récupérez les 3 requêtes qui donnent les plus gros résultats :

# mysqldumpslow -t 3 -s as /var/log/mysql/mysql-slow.log 

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 3  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=24.0 (72), @x
  SELECT /*!N SQL_NO_CACHE */ * FROM `g2_TkOperatnParameterMap`

Count: 18  Time=0.00s (0s)  Lock=0.01s (0s)  Rows=2.0 (36), @x
  SELECT locale, name, formula, enabled FROM locales_meta ORDER BY isdefault DESC, enabled DESC, name ASC

Count: 4  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=55.8 (223), @x
  select * from keyword where date < "S"

Valoriser le couts de ces requêtes

Prenons cette requête en exemple :

SELECT * FROM `link` WHERE `state` = 'S' ORDER BY RAND()  LIMIT N

Mesurez le temps d’exécution :

mysql> select benchmark(100000000,"SELECT id FROM `link` WHERE `state` = '4' ORDER BY RAND() LIMIT 1") \G;
*************************** 1. row ***************************
benchmark(100000,"SELECT id FROM `link` WHERE `state` = '4' ORDER BY RAND() LIMIT 1"): 0
1 row in set (70.42 sec)

Réitérez pour toutes les requêtes lentes.
Pondérez ces valeurs suivant votre perception et sommez l’ensemble pour obtenir votre entropie.

Dans mon cas, l’entropie est 70.42.

Contrôler les optimisations

L’optimisation de MySQL est un vaste chantier que j’aborderai dans 3 autres articles. Dans cet article, je ne parle que d’un moyen rapide pour contrôler la qualité de vos optimisations.

Procédez à vos optimisations. Redémarrez votre serveur et refaite les mesures :

mysql> select benchmark(100000000,"SELECT id FROM `link` WHERE `state` = '4' ORDER BY RAND() LIMIT 1") \G;
*************************** 1. row ***************************
benchmark(100000,"SELECT id FROM `link` WHERE `state` = '4' ORDER BY RAND() LIMIT 1"): 0
1 row in set (0.08 sec)

L’entropie est maintenant à 0.08.
Près de 1000 fois plus basse. L’optimisation apportée est donc valable.