Optimiser mySQL :
Quelques paramètres qui nécessitent attention
query_cache_size
MySQL fournit une fonctionnalité qui peut s’avérer très pratique : un cache de requêtes. Lorsqu’une même requête est exécutée plusieurs fois, et retourne les mêmes résultats à chaque fois, MySQL peut mettre en cache le résultat, ce qui décharge assez le serveur.
key_buffer
La valeur de key_buffer_size est la taille du buffer utilisé pour les index. Plus il y a de mémoire tampon, et plus rapidement la commande SQL se termine et un résultat sera retourné. La règle de base est de régler le key_buffer_size à au moins un quart, mais pas plus de la moitié de la quantité totale de mémoire sur le serveur. Idéalement, il sera assez grand pour contenir tous les index (la taille totale de tous les fichiers .MYI sur le serveur).
table_cache
La valeur par défaut est 64. Chaque fois que MySQL accède à une table, il la place dans le cache. Si le système accède à de nombreuses tables, il est plus efficace d’avoir ces tables dans le cache. MySQL, étant multi-thread, peut exécuter de nombreuses requêtes sur une table au même moment, et chacune de ces requête va ouvrir une connexion avec la table. Examinez la valeur de open_tables aux heures de pointe. Si vous trouvez qu’il reste à la même valeur que la valeur de votre table_cache, puis que le nombre de opened_tables commence à augmenter rapidement, il faut augmenter la taille du table_cache si vous avez suffisamment de mémoire disponible.
sort_buffer
Le sort_buffer est très utile pour accélérer les opérations myisamchk (c’est pourquoi il est défini beaucoup plus haut dans les fichiers de configuration par défaut), mais il peut aussi être utile lors de l’exécution quotidienne un grand nombre de classements (fonctions comme sort(),…).
thread_cache
Si vous avez un serveur surchargé à force de recevoir un grand nombre de connexions rapides, définissez votre thread_cache suffisamment haut pour que la valeur threads_created de SHOW STATUS (commande SQL) cesse d’augmenter. Cela devrait décharger un peu le CPU.
Autres optimisations MySQL
Bien souvent, c’est sur les jointures que se jouent les lenteurs. Utilisez les jointures à bon escient, vérifiez que pour chaque clé utilisée dans les jointures, il existe bien un index sur ce champ clé, sinon créez-le tout de suite et ça fera une grande différence.
Les chiffres pèsent moins lourd dans les index que les lettres, par conséquent, préférez les clés numériques pour vos jointures de grosses tables pour éviter de trop surcharger vos index.
Apache
Le principal problème avec Apache est la quantité de RAM qu’il utilise. Nous allons voir comment baisser la quantité de RAM utilisée par Apache.
Faire moins de demandes simultanées
Dans le fichier apache2.conf (anciennement httpd.conf) :
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
Essayez de définir certaines valeurs aussi bas que ci-dessus
Réduire vos timeout
Vous souhaitez limiter le nombre de processus simultanés, donc vous ne voulez pas de résidus de processus qui restent trop longtemps actifs, donc définissez votre timeout au plus bas.
On a tous eu des problèmes avec le timeout sur des processus coûteux en mémoire et en temps d’exécution… essayez de les faire fonctionner soit sur d’autres plateformes (hors Apache, JAVA étant situé sur une couche plus basse que PHP par exemple, il exécutera un même code beaucoup plus rapidement), soit en d’autres temps (la nuit, sur un VHOST à part avec une configuration particulière).
Éviter l’utilisation des .htaccess
Vous avez probablement vu la commande AlowOverride None qui dit grosso modo “ne cherchez pas les fichiers .htaccess”. Utiliser ces fichiers demandera à Apache de :
(tout ceci provient en grande partie d’une traduction de http://blog.ecvps.com/?p=242)
- Identifiez-vous en root sur le Shell du serveur considéré
- Pour optimiser MySQL, on va s’intéresser principalement au fichier /etc/mysql/my.cnf (généralement ainsi situé, notamment sur les distributions Debian, mais n’hésitez pas à faire un # locate my.cnf pour identifier la localisation de ce fichier et vérifier s’il n’y a pas de doublon)
- nano /etc/mysql/my.cnf (éditons la configuration)
Quelques paramètres qui nécessitent attention
query_cache_size
MySQL fournit une fonctionnalité qui peut s’avérer très pratique : un cache de requêtes. Lorsqu’une même requête est exécutée plusieurs fois, et retourne les mêmes résultats à chaque fois, MySQL peut mettre en cache le résultat, ce qui décharge assez le serveur.
key_buffer
La valeur de key_buffer_size est la taille du buffer utilisé pour les index. Plus il y a de mémoire tampon, et plus rapidement la commande SQL se termine et un résultat sera retourné. La règle de base est de régler le key_buffer_size à au moins un quart, mais pas plus de la moitié de la quantité totale de mémoire sur le serveur. Idéalement, il sera assez grand pour contenir tous les index (la taille totale de tous les fichiers .MYI sur le serveur).
table_cache
La valeur par défaut est 64. Chaque fois que MySQL accède à une table, il la place dans le cache. Si le système accède à de nombreuses tables, il est plus efficace d’avoir ces tables dans le cache. MySQL, étant multi-thread, peut exécuter de nombreuses requêtes sur une table au même moment, et chacune de ces requête va ouvrir une connexion avec la table. Examinez la valeur de open_tables aux heures de pointe. Si vous trouvez qu’il reste à la même valeur que la valeur de votre table_cache, puis que le nombre de opened_tables commence à augmenter rapidement, il faut augmenter la taille du table_cache si vous avez suffisamment de mémoire disponible.
sort_buffer
Le sort_buffer est très utile pour accélérer les opérations myisamchk (c’est pourquoi il est défini beaucoup plus haut dans les fichiers de configuration par défaut), mais il peut aussi être utile lors de l’exécution quotidienne un grand nombre de classements (fonctions comme sort(),…).
thread_cache
Si vous avez un serveur surchargé à force de recevoir un grand nombre de connexions rapides, définissez votre thread_cache suffisamment haut pour que la valeur threads_created de SHOW STATUS (commande SQL) cesse d’augmenter. Cela devrait décharger un peu le CPU.
Autres optimisations MySQL
Bien souvent, c’est sur les jointures que se jouent les lenteurs. Utilisez les jointures à bon escient, vérifiez que pour chaque clé utilisée dans les jointures, il existe bien un index sur ce champ clé, sinon créez-le tout de suite et ça fera une grande différence.
Les chiffres pèsent moins lourd dans les index que les lettres, par conséquent, préférez les clés numériques pour vos jointures de grosses tables pour éviter de trop surcharger vos index.
Apache
Le principal problème avec Apache est la quantité de RAM qu’il utilise. Nous allons voir comment baisser la quantité de RAM utilisée par Apache.
- Charger moins de modules
- Faire moins de demandes simultanées
- Recycler les processus Apache
- Utiliser les KeepAlive, mais pas pour trop longtemps
- Réduire vos timeout
- Créer moins de logs
- ne pas résoudre pas les Hostname
- N’utilisez pas les .htaccess
Faire moins de demandes simultanées
Dans le fichier apache2.conf (anciennement httpd.conf) :
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 20
MaxRequestsPerChild 0
Essayez de définir certaines valeurs aussi bas que ci-dessus
Réduire vos timeout
Vous souhaitez limiter le nombre de processus simultanés, donc vous ne voulez pas de résidus de processus qui restent trop longtemps actifs, donc définissez votre timeout au plus bas.
On a tous eu des problèmes avec le timeout sur des processus coûteux en mémoire et en temps d’exécution… essayez de les faire fonctionner soit sur d’autres plateformes (hors Apache, JAVA étant situé sur une couche plus basse que PHP par exemple, il exécutera un même code beaucoup plus rapidement), soit en d’autres temps (la nuit, sur un VHOST à part avec une configuration particulière).
Éviter l’utilisation des .htaccess
Vous avez probablement vu la commande AlowOverride None qui dit grosso modo “ne cherchez pas les fichiers .htaccess”. Utiliser ces fichiers demandera à Apache de :
- rechercher ces fichiers fréquemment
- parser le fichier .htaccess à chaque requête !
(tout ceci provient en grande partie d’une traduction de http://blog.ecvps.com/?p=242)