Une installation de base de Mysql n’est pas optimisé pour votre ou vos sites web.
Aussi après un certains temps de fonctionnement, une heure par exemple, vous devez commencer à régler les paramètres de Mysql pour que Mysql aille plus vite.
Nous allons nous aider d’un programme en Perl Mysqltuner, que vous allez télécharger depuis votre shell via la commande suivante :
$ wget http://mysqltuner.pl/ -O mysqltuner.pl
$ perl mysqltuner.pl
Au bout de quelques instants Mysqltuner va vous sortir un rapport avec des recommandation. Ce sont de réglages à faire dans votre fichier my.cnf qui est le fichier de configuration de Mysql.
Voici un exemple de rapport généré :
[!!] User ‘@localhost’ is an anonymous account.
[!!] User ‘@ns35.ovh.net’ is an anonymous account.
[!!] User ‘@localhost’ has no password set.
[!!] User ‘@ns35.ovh.net’ has no password set.
[!!] User ‘root@127.0.0.1’ has no password set.
[!!] User ‘root@ns35.ovh.net’ has no password set.
[!!] User ‘@localhost’ has user name as password.
[!!] User ‘@ns35.ovh.net’ has user name as password.
[!!] There is no basic password file list!
——– CVE Security Recommendations ————————————————————–
[–] Skipped due to –cvefile option undefined
——– Performance Metrics ———————————————————————–
[–] Up for: 4d 12h 12m 30s (89M q [228.694 qps], 20M conn, TX: 582G, RX: 4G)
[–] Reads / Writes: 78% / 22%
[–] Binary logging is disabled
[–] Physical Memory : 1.9G
[–] Max MySQL memory : 449.2M
[–] Other process memory: 518.0M
[–] Total buffers: 34.0M global + 2.7M per thread (151 max threads)
[–] P_S Max memory usage: 0B
[–] Galera GCache Max memory usage: 0B
[OK] Maximum reached memory usage: 141.2M (7.12% of installed RAM)
[OK] Maximum possible memory usage: 449.2M (22.64% of installed RAM)
[OK] Overall possible memory usage with other process is compatible with memory available
[OK] Slow queries: 0% (22/89M)
[OK] Highest usage of available connections: 25% (39/151)
[OK] Aborted connections: 0.00% (65/20403660)
[!!] name resolution is active : a reverse name resolution is made for each new connection and can reduce performance
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 296569
[OK] Temporary tables created on disk: 9% (90K on disk / 958K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 299K opened)
[OK] Open file limit used: 12% (127/1K)
[OK] Table locks acquired immediately: 99% (32M immediate / 32M locks)
Remove Anonymous User accounts – there are 2 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Set up a Secure Password for user@host ( SET PASSWORD FOR ‘user’@’SpecificDNSorIp’ = PASSWORD(‘secure_password’); )
Enable the slow query log to troubleshoot bad queries
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_open_cache gradually to avoid file descriptor limits
Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Beware that open_files_limit (1024) variable
should be greater than table_open_cache (64)
Variables to adjust:
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_open_cache (> 64)
innodb_file_per_table=ON
innodb_log_file_size should be equals to 1/4 of buffer pool size (=2M) if possible.
Regardez les point d’exclamations, ce sont les point à travailler ! Mais tous les point n’ont pa s la même importance selon ce que vous recherchez. Moi je cherche la performance, voici ce que je regarde de ce rapport :
Ce qui saute aux yeux c’est query cache qui n’est pas activé !! Le cache stocke les requêtes qui sont les plus utilisée, afin de ne pas taper dans le moteur de base de données Mysql, mais sur un résultat déjà calculé donc on gagne du temps.
Voyons comment on peut soigner ce point :
Vous aurez remarqué qu’une des recommandations est de logger les requêtes lents via le slow query log. Pour ce faire, on va mettre en action le slow query log .
Ensuite nous allons donner une taille au cache de 8 Mo comme suggéré.
D’abord il faut se connecter à Mysql en ligne de commande.
Ensuite pour connaitre l’existence d’un query cache :
mysql > SHOW VARIABLES LIKE ‘have_query_cache’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+
Selon la documentation officielle ce n’est pas parce que c’est à YES qu’il existe !
mysql> SHOW VARIABLES LIKE ‘query_cache_size’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| query_cache_size | 0 |
+——————+——-+
Enfin il existe mais est à zéro. Donnons lui une taille :
mysql> SET GLOBAL query_cache_size = 8000000;
+——————+———+
| Variable_name | Value |
+——————+———+
| query_cache_size | 7999488 |
+——————+———+
voilà c’est en place ! Attendre un petit peu suivant le traffic de votre site web.
Attention ! ceci est à faire dans le fichier my.cnf, sinon quand vous allez redémarrer mysql, ça va disparaitre. Voilci quelques valeur recommandées :
query_cache_type = 1 // pour activer le cache
query_cache_size = 256M
query_cache_limit = 2M
query_cache_strip_comments =1