Retour : Page Principale > sommaire aide > sommaire aide-mémos

Optimisation de Mysql


Comment débuter

Pour commencer, il est possible d'utiliser l'outil en ligne de Percona permettant de créer un fichier my.cnf de base : https://tools.percona.com/wizard

Utiliser Mysql Tuner

Installer Mysql Tuner : apt-get install mysqltuner
Une fois installé, si aucun fichier /root/.my.cnf stockant le mot de passe existe pour l'utilisateur courant, indiqué ses infos via les options de la commande mysqltuner.
Pour voir les options : mysqltunner --help
Avec plusieurs instances de mysql utilisez la commande : PATH=$PATH:/usr/local/mysql/5.6/bin mysqltuner
Le rapport avec des indications s'affiche.

Comparer le nombre de tables écrites en mémoire par rapport au disque dur

Utiliser la requête suivante pour vérifier le rapport : SHOW GLOBAL STATUS LIKE 'Created_tmp%';
Created_tmp_disk_tables : indique le nombre de tables temporaires sur disque créées automatiquement par le serveur lors de l'exécution d'énoncés. Si la valeur du paramètre Created_tmp_disk_tables est trop grande, augmentez la valeur de tmp-table-size afin que les tables temporaires soient maintenues en mémoire au lieu d'être sur disque.

Vérifier que les requêtes sur InnoDB utilisent le buffer

Pour modifier la taille du buffer InnoDB, il faut modifier le paramètre innodb-buffer-pool-size dans le fichier my.cnf.

Solution 1
Vérifier que le buffer est bien utilisé avec la requête : SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_rea%';
Réaliser ce calcul : innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100 = 0.001
Ou lancer 
SELECT round ((P2.variable_value / P1.variable_value),4), 
P2.variable_value, P1.variable_value
FROM information_schema.GLOBAL_STATUS P1,
information_schema.GLOBAL_STATUS P2
WHERE P1. variable_name = 'innodb_buffer_pool_read_requests'
AND P2. variable_name = 'innodb_buffer_pool_reads';

Si le résultat est supérieur à 0.001, il est nécessaire d'augmenter la taille du buffer innoDB en augment la valeur de innodb-buffer-pool-size dans le fichier my.cnf.

Solution 2
Vérifier que les requêtes sur InnoDB ont un bon hit ratio avec la requête : SHOW ENGINE INNODB STATUS ;
  • si le nombre de "Free buffers" est très important, il est possible qu'il faille réduire la taille du buffer.
  • la valeur "Innodb hit ratio: 1000/1000" indique une utilisation correcte du buffer, un ratio légèrement plus faible est acceptable mais au dessous de 950/1000 il est nécessaire d'augmenter la taille du buffer.

Optimiser les tables défragmentées

Utiliser la requête suivante (source : http://stackoverflow.com/questions/1031885/mysql-optimize-table-for-all-fragmented-tables) :
SELECT concat("OPTIMIZE TABLE ", table_schema,".",table_name,";")
FROM information_schema.tables
WHERE DATA_FREE > 0
INTO OUTFILE '/tmp/optimize.sql';
SOURCE /tmp/optimize.sql;


Surveiller l'évolution de l'utilisation de Mysql avec Phpmyadmin

Pour surveiller les réglages nécessaire pour Mysql, il est possible d'utiliser Phpmyadmin.
Se rendre depuis l'accueil, dans l'onglet "Etat", puis menu "Conseiller".
Un certain nombre d'indications peuvent être récupérées pour modifier le fichier my.cnf.

Utiliser un disque en Ram pour le dossier temp de Mysql

Si de trop nombreuses requêtes sont écrites sur le disque par rapport à la mémoire commencer par :
  • optimiser les requêtes SQL (utiliser EXPLAIN)
  • modifier les paramètres de my.cnf pour forcer l'utilisaton de la mémoire vive
  • optimiser l'écriture sur disque de Mysql en créant un disque dur virtuel en RAM.

Ressources