Astuces de Webmaster

2 avril 2013

Ralentissement MySQL : identifier les requêtes à problème dans la processlist

Architecture — Tags : , Rom's @ 10:15

Pour travailler avec la processlist, on fait habituellement « show processlist; » pour avoir un aperçu rapide de ce qui se passe.

Mais si on a un souci, on a besoin de voir exactement d’où peut venir le problème.

Pour cela, on travaillera avec la table INFORMATION_SCHEMA.PROCESSLIST qui est physiquement là où ça tape.

Commande SSH pour se connecter à MySQL pour rappel :

 shell |  Copier le code |? 
1
mysql -uNomUtilisateur -p NomBase

le prompt vous demandera le mot de passe de l’utilisateur en question

Voir le nombre de requêtes par état :

 SQL |  Copier le code |? 
1
SELECT COUNT(*),STATE FROM INFORMATION_SCHEMA.PROCESSLIST GROUP BY STATE;

Voir les requêtes par serveur appelant (lorsqu’on a une base distante et plusieurs serveurs qui tapent dessus)

 SQL |  Copier le code |? 
1
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE HOST LIKE 'front1.%';

Voir les requêtes, avec la plus ancienne en bas

 SQL |  Copier le code |? 
1
SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC;

Il vous suffit ensuite de killer la requête qui arrive en dernière dans les résultats de cette précédente requête

 SQL |  Copier le code |? 
1
KILL <ID>;

Structure de la table pour info

+-------+---------+----------------------------------------------+---------+---------+------+-----------+----------------------------------------------------------------+
| ID    | USER    | HOST                                         | DB      | COMMAND | TIME | STATE     | INFO                                                           |
+-------+---------+----------------------------------------------+---------+---------+------+-----------+----------------------------------------------------------------+
| 10267 | menkool | menivesti-inf3-front10.cust.wacool.net:57104 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10256 | menkool | menivesti-inf3-front10.cust.wacool.net:57101 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10252 | menkool | menivesti-inf3-front10.cust.wacool.net:57100 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10246 | menkool | menivesti-inf3-front10.cust.wacool.net:57099 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10241 | menkool | menivesti-inf3-front10.cust.wacool.net:57096 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10236 | menkool | menivesti-inf2-front12.cust.wacool.net:35375 | menkool | Sleep   |    0 |           | NULL                                                           |
| 10201 | menkool | menivesti-inf2-front12.cust.wacool.net:35369 | menkool | Sleep   |    0 |           | NULL                                                           |
|  6716 | menkool | localhost                                    | menkool | Query   |    0 | executing | SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST ORDER BY TIME ASC |
+-------+---------+----------------------------------------------+---------+---------+------+-----------+----------------------------------------------------------------+
8 rows in set (0.00 sec)

30 octobre 2012

Comment créer une Sauvegarde Quotidienne MySQL sur 3 jours glissants [Debian et autres]

Architecture — Tags : , , , Rom's @ 10:36

Il est utile de conserver des sauvegardes sur plusieurs jours en cas de plantage, de mauvaise manipulation, et du fait qu’on ne s’en aperçoit pas toujours tout de suite.

Le top est de faire une sauvegarde à fréquence définie, et pourquoi pas une sauvegarde quotidienne et sur plusieurs jours

Voici un exemple très rapide de script pour ce faire :

 shell |  Copier le code |? 
1
cd /home/monsite/ && mkdir sauvegardes
2
cd sauvegardes && mkdir save_j1 && mkdir save_j2 && mkdir save_j3
3
touch sauvegarde_bdd.sh
4
nano sauvegarde_bdd.sh

inscrire ce contenu et quitter (ctrl+X et validation avec Y + entrée avec l’éditeur nano)

 shell |  Copier le code |? 
1
cd /home/monsite/sauvegardes/
2
rm -rf save_j3
3
mv save_j2 save_j3
4
mv save_j1 save_j2
5
mkdir save_j1
6
mv sauvegardeQuotidienne.sql.gz save_j1/
7
mysqldump -u[NOMUTILISATEUR] -p[MOTDEPASSE] [NOMBDD] > sauvegardeQuotidienne.sql
8
gzip sauvegardeQuotidienne.sql

il suffit ensuite de créer le cron pour automatiser cela chaque jour (à 2h30 dans cet exemple)

 shell |  Copier le code |? 
1
sudo crontab -e

Ajouter la ligne suivante :

30 2 * * * /home/monsite/sauvegardes/sauvegarde_bdd.sh

ctrl+X et validation avec Y + entrée ensuite.

C’est fait, plus qu’à vérifier le lendemain. Pour dégzipper un fichier sur un ordinateur avec Windows, utiliser 7-zip.

Merci de vos retours !

17 juin 2011

Optimiser MySQL et Apache (orienté Debian)

Optimiser mySQL :

  • 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)

[mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 64K # pour utiliser moins de mémoire, si on n'utilise pas Berkeley DB on ajoute cette ligne skip-bdb # idem si on n'utilise pas InnoDB skip-innodb # de la même façon, si on n'utilise pas InnoDB, on peut commenter les lignes suivantes #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # Vous pouvez définir .._buffer_pool_size entre 50 et 80 % # de la RAM mais attention à ce que l'utilisation globale de RAM par MySQL ne soit pas trop important (à suivre) #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Définissez .._log_file_size à 25% du buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Décommentez la ligne suivante si vous n'êtes pas familier avec SQL #safe-updates [isamchk] key_buffer = 8M sort_buffer_size = 8M [myisamchk] key_buffer = 8M sort_buffer_size = 8M [mysqlhotcopy] interactive-timeout

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

Première chose, faites table rase de tous les modules non nécessaires. Regardez vos fichiers de configuration et regardez ce que vos modules utilisent vraiment. Si vous n’utilisez pas certains modules, ne les chargez pas, cela libère de l’espace sur la mémoire vive (RAM).

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 !

Donc si vous avez des besoins spécifiques, déterminez-les dans la configuration de votre Virtual Host (VHOST – souvent dans /etc/apache2/apache2.conf ou dans /etc/apache2/sites-available/MONVHOST.conf)

(tout ceci provient en grande partie d’une traduction de http://blog.ecvps.com/?p=242)

Autres lectures :

Articles plus anciens »

Romain BOYER ©2014 - Contact : boyer Arobase romain Point biz - Profil sur LinkedIn - Profil sur Google+