Bonsoir,
Je vous sollicite ce soir pour un gros soucis de performance avec MySQL suite au remplacement du serveur physique l'hébergeant.
Contexte :
- machine physique dédiée à MySQL
- bases de données de tests uniquement
- processeur : Intel(R) Xeon(R) CPU E5-2650 v2 @ 2.60GHz
- 128 Go de RAM
- RAID 1 Logiciel avec mdadm (3.3.1-r2) ; 2 disques
- système d'exploitation : Gentoo
Le problème :
- avant la migration, la restauration d'une sauvegarde de production prenait 8H
- après la migration, la même restauration prends 14H
La nouvelle machine est plus rapide au niveau processeur, RAM et disques durs. La seule différence avec l'ancienne se situe au niveau du RAID : l'ancienne avait un RAID matériel (MegaRAID SAS 2108) ; la nouvelle est en raid logiciel avec mdadm.
Ce qui a été fait / vérifié :
- l'installation : elle est clean puisque d'autres machines fonctionnement parfaitement et elles sont toutes installées de la même manière
- les partitions sont alignées (vérifié avec 'parted')
- c'est une Gentoo donc les paquets sont à jour
- kernel récent : 3.17.7-r1 hardened de Gentoo
- configuration kernel : la même que l'ancienne machine car nous harmonisons nos configurations au maximum
- hdparm -tT /dev/sd[a-b] donne des bons résultats (160 Mo/s pour chacun des disques)
- hdparm -tT /dev/md4 (partition contenant le "/" donc système + MySQL) donne des bons résultats (160 Mo/s aussi)
- un transfert brut d'un fichier de 10 Go du disque vers la RAM est plus rapide que sur l'ancienne machine
- un transfert brut d'un fichier de 10 Go de la ram vers le disque est plus rapide que sur l'ancienne machine
- un transfert réseau entre la machine qui pousse le dump et cette machine est plus rapide qu'entre la machine qui pousse le dump et l'ancienne machine
- j'ai essayé de modifier /proc/sys/dev/raid/speed_limit_min et /proc/sys/dev/raid/speed_limit_max (même si normalement ça influence que la synchronisation du raid)
J'ai essayé de modifier des paramètres de la configuration MySQL :
- avec la même configuration que l'ancienne machine : c'est lent
- en modifiant des paramètres de MySQL : le résultat reste le même
Niveau montage c'est du basique :
/dev/md4 on / type ext4 (rw,noatime,data=ordered)
Les disques sont ok :
md4 : active raid1 sda4[0] sdb4[1]
2921742272 blocks [2/2] [UU]
bitmap: 0/22 pages [0KB], 65536KB chunk
Pendant la restauration de la sauvegarde, iotop me donne quelques indications :
- l'écriture ne dépasse pas les 30 Mo/s sur les deux machines
- sur la nouvelle machine (celle qui est lente), le premier consommateur des disques est 'jbd2[…]' puis vient MySQL alors que sur l'ancienne machine c'est l'inverse (d'abord MySQL puis 'jdb2[…]')
- sur les deux machines, un top me dit que je ne dépasse pas les 3 I/O Wait (WA)
Je pense avoir fait le tour de toutes les choses testées et j'ai toujours des soucis.
Est-ce que quelqu'un aurait une idée d'où vienne ces lenteurs ? Une idée de réglage(s) à effectuer ?
Je vous remercie d'avance.
# Comment as-tu choisi tes réglages ?
Posté par Framasky (site web personnel) . Évalué à 5.
Tu dis avoir modifié la conf de mysql… comment as-tu choisi les paramètres à modifier ? Est-ce que tu as regardé ce que retourne mysqltuner ?
Je ne suis pas expert mysql, du coups je me repose pas mal sur mysqltuner, et il est toujours de bon conseil.
Being a sysadmin is easy. As easy as riding a bicycle. Except the bicycle is on fire, you’re on fire and you’re in Hell.
# Commentaire supprimé
Posté par Anonyme . Évalué à 1.
Ce commentaire a été supprimé par l’équipe de modération.
# Réponses aux questions
Posté par kortex . Évalué à 1.
Bonsoir,
Désolé effectivement j'ai loupé quelques détails.
Alors pour répondre à Framasky : non je n'ai pas utilisé mysqltuner.
Je me méfie toujours des scripts qui font tout et qui appliquent une politique générale selon des règles qui se voudraient universelles. Les règles de tuning MySQL sont compliquées à prédire puisqu'elles dépendent de l'applicatif et de la machine (il n'y a qu'à faire un tour sur le site de Percona pour se rendre compte que même la documentation officielle MySQL raconte parfois n'importe quoi).
Du coup, pour le réglage de MySQL, c'est simple : comme le projet de remplacement des machines étaient planifiées, j'ai travaillé sur les anciennes machine en modifiant quelques paramètres. Si la performance n'était pas dégradée, je validais le paramètre, sinon je revenais en arrière. Donc au moment de la migration, j'étais iso-configuration. Je n'ai pas été trop loin car nous ne cherchons pas la microseconde : nos machines sont très correctes et loin d'être saturées.
Avant l'entrée en fonction de cette machine, j'ai qualifié la modification de trois paramètres :
* 'innodb_io_capacity' qui était à 1200 et qui a été descendu à 200
* 'table_open_cache' et 'table_definition_cache' qui ont été augmentés à 1000 chacun (au lieu de 64 et 400 ; soit les valeurs par défaut). L'amélioration est très légère mais réelle caches chauds.
Pour cette migration j'ai, en plus, modifié 'innodb_buffer_pool_size' pour passer de 10G à 64G (testé et approuvé auparavant sur d'autres machines ; en prod à l'heure actuelle). J'ai quand même effectué le test à 10G (au cas ou) et ce n'est pas ça.
Comme les performances ne sont pas bonnes, j'ai :
Pour répondre à Francesco :
La seule différence est au niveau du raid.
La machine ne swap pas ; n'a pas d'io wait en particulier ; ne charge pas niveau cpu ; n'a aucune erreur dans les logs).
J'ai déjà eu des performances désastreuses sur MySQL avec des serveurs dont le raid était matériel mais c'était lié au cache de la carte raid qui n'était pas actif. Par contre, dans le cas de mdadm, je sèche complètement.
Je me demandais si l'option de montage 'data' ('ordered' à l'heure actuelle) n'était pas une piste mais je ne trouve pas grand chose qui explique les différentes options…
[^] # Re: Réponses aux questions
Posté par Framasky (site web personnel) . Évalué à 3.
Y a quand même des trucs que mysqltuner relève et qui sont universelles : quand il t'avertit que certaines tables sont fragmentées par exemple. Ou quand il donne des statistiques d'utilisation du cache.
Après, oui, c'est vrai, il propose parfois des trucs qui au final ne sont pas bonnes (alors qu'elles viennent de la doc de mysql), mais ça peut donner des idées quand même.
Je te conseille quand même de regarder ce qu'il te dit.
Being a sysadmin is easy. As easy as riding a bicycle. Except the bicycle is on fire, you’re on fire and you’re in Hell.
[^] # Re: Réponses aux questions
Posté par kortex . Évalué à 1.
Je vais regarder dès que j'arrive au bureau en espérant que ça me donne une bonne piste ; je te tiens au courant.
[^] # Re: Réponses aux questions
Posté par kortex . Évalué à 2. Dernière modification le 27 mars 2015 à 09:28.
Alors mysqltuner ne retourne rien de probant (sauf que mon cache n'est pas utilisé mais ça c'est pas un scoop pour moi).
Par contre, c'est bien ce que je pensais, les résultats de ce script sont à prendre avec des pincettes :
[…]
[--] Data in InnoDB tables: 411G (Tables: 1599)
[…]
Variables to adjust:
long_query_time (<= 10)
query_cache_limit (> 128M, or use smaller result sets)
innodb_buffer_pool_size (>= 411G)
[^] # Re: Réponses aux questions
Posté par kna . Évalué à 1.
Voir aussi, pour la rapidité d'insertion d'un dump :
- diminuer innodb_log_file_size (quitte à l'augmenter après insertion du dump)
- logs binaires désactivés (si possible)
Dans certains cas, ça m'a fait gagner pas mal de temps.
[^] # Re: Réponses aux questions
Posté par kortex . Évalué à 2.
Effectivement ça peut être des pistes mais temporaires.
Le problème est apparu en changeant de machine et ne s'est jamais posé avec une machine bien moins puissante. Il y a donc un soucis de fond et c'est celui-ci que j'essaie de résoudre.
Je reste persuadé qu'il y a quelque chose à faire au niveau /etc/fstab ou mdadm mais je sèche :-/
[^] # Re: Réponses aux questions
Posté par quent57 . Évalué à 3. Dernière modification le 27 mars 2015 à 11:45.
Bonjour !
J'avais mis quelques liens de coté à propos de l'optimisation de ext4 pour mysql :
- Il est possible d'utiliser relatime plutot que noatime pour presque les memes perf, et un peu plus de sécurité, source
(c'était déja par défaut sur mon ubuntu)
- Il existe des "barriere" à l'écriture sur le disque qui obligent un ordenancement des écriture si je me souviens bien, il est possible de les désactiver avec barrier=0 toujours dans le fstab source autre source
- utiliser data=writeback ou ordered peut aussi etre utile, mais c'était déja par défaut sur mon Ubuntu 14.04 source et source
Tiens nous au courant si tu trouve des paramètres qui améliorent effectivement les perf de mysql :)
[^] # Re: Réponses aux questions
Posté par kortex . Évalué à 1.
Bonjour,
Je vais surement tester le barrier = 0 mais il faut le prendre avec des pincettes celui-ci. Comme je n'ai pas de système de cache avec batterie, c'est relativement risqué non ?
Sinon entre 'noatime' et 'data=ordered' je suis bon quoi :-/
Il y un détail que je remarque :
- sur la nouvelle machine qui est lente, iotop pendant le chargement du dump :
1/ jbd2/md4-8 (entre 60 et 80%)
2/ mysqld (3%)
3/ mysqld (1,5%)
Les débits restent similaire (une trentaine de Mo/s) pendant la restauration ; je pense donc que c'est "normal".
Par contre c'est bizarre que dans un cas la journalisation soit le plus gros consommateur est pas dans l'autre.
[^] # Commentaire supprimé
Posté par Anonyme . Évalué à 1.
Ce commentaire a été supprimé par l’équipe de modération.
[^] # Re: Réponses aux questions
Posté par xulops (site web personnel) . Évalué à 1.
je plussoie. Si la machine est nouvellement installée, il va falloir quelques heures / jours pour que le RAID soit prêt.
Sur des petits disques, quelques heures suffissent. Sur des disques d'un To, il faut au moins une demi-journée. Sur plus gros, ça peut prendre un jour ou deux.
Sans lancer d'opération sur la machine, il faut effectivement regarder s'il y a déjà une activité disque.
[^] # Re: Réponses aux questions
Posté par B r u n o (site web personnel) . Évalué à 2.
As-tu la possibilité de monter le dossier de MySQL sur une partition qui n'est pas dans ton RAID ? Cela te permettrait de voir si le RAID est le coupable.
Ensuite je suis tombé là dessus il y a quelques jours : https://dev.mysql.com/doc/refman/5.5/en/optimizing-innodb-diskio.html en particulier le innodb_flush_method qui semble avoir un impact non négligeable en fonction des systèmes (ils disent qu'il faut benchmarker pour savoir quel est le mieux).
Autre point, tu sembles avoir beaucoup de tables (1599 d'après ton script d'analyse), est ce que tu as configuré innodb pour utiliser 1 fichier par table (innodb-file-per-table) ?
Ensuite si jbd2 prend autant de CPU, peut-être que passer à un autre fs sans journalisation serait pertinent ? Au moins pour MySQL. Les sgbd ont leur propre système de journalisation, c'est redondant. Tu peux trouver plusieurs tests sur le net sur l'influence du fs sur MySQL, mais les résultats ne sont jamais les mêmes :) Tu peux essayer de jouer sur les paramètres de ext4 au montage, du genre "nobarrier" mais juste pour tester, c'est un peu limite pour de la prod.
Si tu obtiens des résultats, tiens nous au courant, c'est un bon retour d'expérience !
[^] # Re: Réponses aux questions
Posté par kortex . Évalué à 1. Dernière modification le 27 mars 2015 à 13:06.
Pour le déplacement de MySQL, non ; pas sans réinstaller la machine. Ce sera mon dernier recours si vraiment je n'ai pas le choix.
Pour 'innodb_flush_method' je suis sur la valeur par défaut (donc 'fsync'). La documentation de MySQL dit quand même de faire son choix selon si tu es sur raid hard, san, etc. Du coup, soit je me trompe complètement, soit jouer avec ce paramètre revient à jouer avec le 'barrier' : c'est juste bon le temps d'un test.
Oui innodb-file-per-table est activé (sur les deux machines ; l'ancienne et la nouvelle).
J'ai eu exactement le même soucis sur une machine de prod un jour ; c'était un contrôleur raid matériel. Le soucis venait du cache qui était à 'WriteThrough' au lieu de 'WriteBack' (défaillance de la batterie = désactivation du cache). Les symptômes étaient les même : journalisation en top liste.
Du coup je me demande vraiment s'il n'y a pas un soucis au niveau de mdadm ; un réglage ; un "cache" à activer / désactiver. Si tu connais quelque chose à ce niveau je suis preneur.
[^] # Re: Réponses aux questions
Posté par cel . Évalué à 1.
Hello!
Tu peux toujours sortir un disque de ton RAID logiciel, faire tes tests de restauration sur ce disque puis le réintégrer à ton RAID.
Au moins tu sauras si le RAID logiciel à une quelconque influence quant à la baisse de performance constatée.
My 2 cents!
# Restauration à 30Mo/s sur les deux machines ?
Posté par X345 . Évalué à 2. Dernière modification le 27 mars 2015 à 13:58.
Avant de faire des corrections/réglages, j'aime bien avoir identifié de manière certaine la cause du problème de performance. T'as déjà fait des tests pertinents (hdparm, écriture RAM->disque), qui permettent déjà d'éliminer des causes probables de problèmes de performance.
Un truc me fait cependant tiquer. Sur les deux machines, l'écriture se fait à ~30 Mo/s pendant la restauration d'une sauvegarde. C'est étrange parce que si la restauration prend deux fois moins de temps sur une machine que sur l'autre, on s'attendrait à ce qu'une machine écrive 2 fois moins vite que l'autre (grosso modo).
Pour voir ce qu'il se passe exactement, tu peux mesurer la vitesse d'écriture disque pendant les 8h ou 14h de la restauration de la sauvegarde. Le plus simple est de bidouiller avec iotop : Measure I/O over time
S'il te reste du temps, je pense qu'il serait utile de tester la performance en termes d'IOPS des deux machines :
ioping
$ ioping -RL /dev/md4 # Débit en lecture
$ ioping -R /dev/md4 # IOPS en lecture
iozone
$ iozone -a # Tous test iozone (write, read, reread, rewrite)
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par kortex . Évalué à 1.
Bonjour,
Je te remercie pour ta réponse ; je ne connaissais pas ces utilitaires.
Voici quelques mesures :
débit en lecture sur la nouvelle machine (à vide) :
iops en lecture sur la nouvelle machine (à vide) :
débit en lecture sur l'ancienne machine (à vide) :
iops en lecture sur la nouvelle machine (à vide) :
Pour iozone, copier le contenu ici était compliqué alors j'ai placé le contenu sur pastebin :
J'ai forcé la taille du fichier à 64 KB pour avoir une première mesure. Je ne sais pas spécialement comment interpréter les résultats mais la conclusion est la même qu'avec MySQL : les performances sont meilleures sur l'ancienne machine. Qu'en penses-tu ?
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par X345 . Évalué à 3.
Pour ioping, tu as les même résultats sur l'ancienne et la nouvelle machine, ~160Mo/s en lecture et ~75 IOPS dans les deux cas, ce qui correspond à des performances normales.
Je vais essayer de regarder tes résultats
iozone, c'est toujours difficile à interpréter. Mais effectivement, y'a de sacré différences entre tes deux machines, et la différence de performance de MYSQL vient sûrement de là. Probablement la carte RAID de l'ancienne machine aidait vraiment.Pour avoir des résultats plus faciles à interpreter dans un premier temps, tu peux faire des tests avec ioping en écriture (ioping -W) mais lis la page de man avant pour ne pas perdre de données.
Pour ce que est de la configuration, j'irais jeter un œil du côté de readhead, désactivation du NCQ etc. plutot que dans la configuration de MySQL.
Une autre hypothèse à vérifier si tu as des disques avec des secteurs 4k sur la nouvelle machine (Advanced Format 512e etc.) : est-ce que mdadm ne fait pas n'importe quoi au niveau de l'alignement du RAID ?
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par Kerro . Évalué à 3.
Bien souvent les cartes RAID ignorent les syncs, ou ils sont stockés dans la mémoire sauvegardée sur batterie, alors que mdadm les honore correctement. Avec certaines charges ça fait une énorme différence.
Il y a quelques années j'avais supprimé le sync de Linux car une application en faisait des centaines par seconde. Je ne sais plus comment j'avais fait, mais l'application fonctionnait ensuite très bien.
Je crois que j'avais fait un wrapper qui filtrait les syncs, donc seule cette application était touchée par la modification.
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par kortex . Évalué à 1.
Est-ce qu'il ne s'agirait pas du paramètre 'async' à mettre dans /etc/fstab ? Quel est le risque en raid software ?
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par Kerro . Évalué à 2.
Non.
Le même qu'en RAID matériel qui n'a pas de batterie.
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par kortex . Évalué à 1.
Ce sont des secteurs à 512 bytes et les partitions sont alignées
[^] # Commentaire supprimé
Posté par Anonyme . Évalué à 0.
Ce commentaire a été supprimé par l’équipe de modération.
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par kortex . Évalué à 1.
Quand je fais des tests bruts, je gagne en performance.
Il ne s'agit pas de disques SSD effectivement mais bien de disques durs mécaniques.
Comment tu mesurerais ce temps d'accès moyen ?
[^] # Commentaire supprimé
Posté par Anonyme . Évalué à 1.
Ce commentaire a été supprimé par l’équipe de modération.
[^] # Re: Restauration à 30Mo/s sur les deux machines ?
Posté par Kerro . Évalué à 1.
Il serait peut-être utile de comparer la puissance de traitement des données des deux machines, sans utiliser les disques. Par exemple avec un script vite fait, loin d'être parfait:
Les valeurs retournées par time sont à prendre avec des pincettes car les sous-processus se terminent après le retour de time. Mais ça donne une idée générale.
Il faut choisir le chemin /XXX/test.random pour que ça tombe sur un système de fichiers en mémoire (tmpfs). L'objectif est d'utiliser le processeur et la mémoire.
# Problème "résolu"
Posté par kortex . Évalué à 3.
Bonjour,
J'ai résolu le soucis : j'ai commandé la même machine en prenant une option raid hardware.
Pour les chiffres du chargement d'un gros dump :
- ancienne machine (avant première migration) : 8H
- nouvelle machine en raid soft : 14H
- même machine en raid hard : 5H
Merci à tous ;)
Suivre le flux des commentaires
Note : les commentaires appartiennent à celles et ceux qui les ont postés. Nous n’en sommes pas responsables.