Forum général.général MySQL - Gros problème de performance

Posté par . Licence CC by-sa
5
26
mar.
2015

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 (page perso) . É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.

    It's a fez. I wear a fez now. Fezes are cool !

  • # Versions des logiciels

    Posté par (page perso) . Évalué à 1.

    Quelles sont les versions des logiciels ? Version de MySQL sur l'ancienne et sur la nouvelle ? Version du kernel ?

    Est-ce que l'environnement de compilation est le même sur l'ancienne et la nouvelle machine (USE Flag, profile, etc…)

    Options de montage des systèmes de fichiers ?

  • # Réponses aux questions

    Posté par . É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 :

    • modifié 'innodb_log_file_size' pour passer de 500 Mo à 2047 Mo : aucune amélioration
    • modifié 'innodb_io_capacity' pour remonter à 1200 le temps d'un test : aucune amélioration

    Pour répondre à Francesco :

    • MySQL 5.5.40 des deux côtés et même configuration (sauf 'innodb_buffer_pool_size' mais j'ai testé avec la même valeur et ça ne vient pas de ça)
    • kernel 3.17.7-r1 hardened des deux côtés et même configuration
    • oui l'environnement est le même des deux côtés (gcc 4.8.3 / profile hardened / use flag identiques sur tous nos serveurs SQL)
    • niveau système de fichier : EXT4 des deux côtés, 'noatime' des deux côtés.

    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 (page perso) . É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.

      It's a fez. I wear a fez now. Fezes are cool !

      • [^] # Re: Réponses aux questions

        Posté par . É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 . Évalué à 2. Dernière modification le 27/03/15 à 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 . É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 . É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 . Évalué à 3. Dernière modification le 27/03/15 à 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 . É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%)

                  • sur l'ancienne machine qui est rapide, iotop pendant le chargement du dump : 1/ mysqld 2/ mysqld 3/ jbd2/md4-8

                  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.

                  • [^] # Re: Réponses aux questions

                    Posté par (page perso) . Évalué à 1.

                    Sur la nouvelle machine, est-ce qu'en ne faisant rien (pas de restauration) jbd2 est actif ou pas ?
                    J'ai lu que parfois, après un formatage, cela pouvait arriver. Il semblerait que le formatage fasse une partie du boulot en faisant un formatage rapide et que jbd2 termine le formatage ensuite (en faisant quoi, je ne me souviens pas)

                    • [^] # Re: Réponses aux questions

                      Posté par . É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 (page perso) . É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 . Évalué à 1. Dernière modification le 27/03/15 à 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 . Évalué à 1.

                Hello!

                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.

                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 . Évalué à 2. Dernière modification le 27/03/15 à 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
    Idéalement, il faudrait lancer ces deux tests "à vide" et pendant la restauration d'une sauvegarde. Tu peux aussi faire quelques tests en écriture avec ioping (4k, 128k, 1M, 16M, 128M par exemple).

    iozone

    $ iozone -a # Tous test iozone (write, read, reread, rewrite)
    Ça te permettra de caractériser plus précisément la performance du système de stockage des deux machines.

    • [^] # Re: Restauration à 30Mo/s sur les deux machines ?

      Posté par . É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) :

        ioping -RL /dev/md4

        --- /dev/md4 (device 2.7 TiB) ioping statistics ---
        2.0 k requests completed in 3.0 s, 654 iops, 163.5 MiB/s
        min/avg/max/mdev = 33 us / 1.5 ms / 98.6 ms / 2.4 ms

      • iops en lecture sur la nouvelle machine (à vide) :

        ioping -R /dev/md4

        --- /dev/md4 (device 2.7 TiB) ioping statistics ---
        253 requests completed in 3.0 s, 84 iops, 336.0 KiB/s
        min/avg/max/mdev = 2.9 ms / 11.9 ms / 41.1 ms / 4.0 ms

      • débit en lecture sur l'ancienne machine (à vide) :

        ioping -RL /dev/sda3

        --- /dev/sda3 (device 2.7 TiB) ioping statistics ---
        1.9 k requests completed in 3.0 s, 652 iops, 163.2 MiB/s
        min/avg/max/mdev = 70 us / 1.5 ms / 17.9 ms / 459 us

      • iops en lecture sur la nouvelle machine (à vide) :

        ioping -R /dev/sda3

      --- /dev/sda3 (device 2.7 TiB) ioping statistics ---
      226 requests completed in 3.0 s, 75 iops, 301.8 KiB/s
      min/avg/max/mdev = 171 us / 13.3 ms / 41.7 ms / 4.4 ms

      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 . É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 (page perso) . Évalué à 3.

          Probablement la carte RAID de l'ancienne machine aidait vraiment.

          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 . Évalué à 1.

          Ce sont des secteurs à 512 bytes et les partitions sont alignées

      • [^] # Re: Restauration à 30Mo/s sur les deux machines ?

        Posté par (page perso) . Évalué à 0.

        Les disques dur de la nouvelle machine sont-ils réellement plus rapide ? Vu la vitesse de lecture, je dirais que ce sont bien des disques dur et non des SSD.

        Ensuite, en terme de rotation et de temps d'accès moyens, ça donne quoi ? Le temps d'accès moyen pourrait grandement réduire les performances dans le cas d'écriture aléatoire (ce qui pourrait être le cas lors de la restauration d'un dump et les écritures dans le journal).

        • [^] # Re: Restauration à 30Mo/s sur les deux machines ?

          Posté par . É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 ?

          • [^] # Re: Restauration à 30Mo/s sur les deux machines ?

            Posté par (page perso) . Évalué à 1.

            Le temps d'accès moyen est normalement une donnée du constructeur, qui prend en compte notamment le temps de déplacement de la tête de lecture pour accéder à une portion précise du disque dur. Mais en fait, ma remarque était inutile. J'ai lu trop rapidement les résultats hier et j'ai interverti les résultats de la nouvelle machine et de l'ancienne… Si on regarde les différents tests de lecture, écriture, etc… ta nouvelle machine poutre largement l'ancienne (au minimum, d'un facteur 1.5).

            Les résultats qui pourraient mettre en avant un temps d'accès moyen sont les accès aléatoires. Mais le débit est plus grand dans le cadre de la nouvelle machine dans cette configuration là aussi.

            Si on regarde le temps d'exécution pour effectuer les tests, 8s pour l'ancienne machine, et 36s pour la nouvelle ! La différence me parait énorme (mais ensuite, cela dépend peut être de comment les statistiques sont réalisées par iozone.)

            • [^] # Re: Restauration à 30Mo/s sur les deux machines ?

              Posté par (page perso) . Évalué à 1.

              Si on regarde le temps d'exécution pour effectuer les tests, 8s pour l'ancienne machine, et 36s pour la nouvelle !

              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:

              chemin=/XXX/test.random
              time (
                  head -c 100000000 /dev/urandom | tee           \
                      "$chemin"                                  \
                      >(gzip --best --to-stdout > /dev/null)     \
                      >(md5sum --binary > /dev/null)             \
                      > /dev/null
              )
              rm -f "$chemin"

              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 . É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 à ceux qui les ont postés. Nous n'en sommes pas responsables.