Comment résoudre l'avertissement mysql : "InnoDB : page_cleaner : 1000ms intended loop took XXX ms. Les paramètres ne sont peut-être pas optimaux " ?

Question

J'ai exécuté une importation mysql dummyctrad < dumpfile.sql sur le serveur et cela prend trop de temps pour se terminer. Le fichier dump fait environ 5G. Le serveur est un Centos 6, mémoire=16G et processeurs 8core, mysql v 5.7 x64-.

S'agit-il de messages normaux/état "en attente de vidange de la table" et du message InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal

Contenu du journal mysql

2016-12-13T10:51:39.909382Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal. (flushed=1438 and evicted=0, during the time.)
2016-12-13T10:53:01.170388Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4055ms. The settings might not be optimal. (flushed=1412 and evicted=0, during the time.)
2016-12-13T11:07:11.728812Z 0 [Note] InnoDB: page_cleaner: 1000ms intended loop took 4008ms. The settings might not be optimal. (flushed=1414 and evicted=0, during the time.)
2016-12-13T11:39:54.257618Z 3274915 [Note] Aborted connection 3274915 to db: 'dummyctrad' user: 'root' host: 'localhost' (Got an error writing communication packets)

Liste de processus :

mysql> show processlist \G;
*************************** 1. row ***************************
     Id: 3273081
   User: root
   Host: localhost
     db: dummyctrad
Command: Field List
   Time: 7580
  State: Waiting for table flush
   Info: 
*************************** 2. row ***************************
     Id: 3274915
   User: root
   Host: localhost
     db: dummyctrad
Command: Query
   Time: 2
  State: update
   Info: INSERT INTO `radacct` VALUES (351318325,'kxid ge:7186','abcxyz5976c','user100
*************************** 3. row ***************************
     Id: 3291591
   User: root
   Host: localhost
     db: NULL
Command: Query
   Time: 0
  State: starting
   Info: show processlist
*************************** 4. row ***************************
     Id: 3291657
   User: remoteuser
   Host: portal.example.com:32800
     db: ctradius
Command: Sleep
   Time: 2
  State: 
   Info: NULL
4 rows in set (0.00 sec)

Mise à jour 1

mysqlforum , innodb_lru_scan_depth

Le changement de la valeur de innodb_lru_scan_depth à 256 a amélioré le temps d'exécution des requêtes d'insertion + aucun message d'avertissement dans le journal, la valeur par défaut était innodb_lru_scan_depth=1024 ;

SET GLOBAL innodb_lru_scan_depth=256;

Answer 1

InnoDB : page_cleaner : la boucle prévue de 1000 ms a pris 4013 ms. Les paramètres ne sont peut-être pas optimaux. (flushed=1438 et evicted=0, pendant ce temps).

Le problème est typique d'une instance MySQL où le taux de modification de la base de données est élevé. En exécutant votre importation de 5 Go, vous créez rapidement des pages sales. Au fur et à mesure que les pages sales sont créées, le thread de nettoyage des pages est chargé de copier les pages sales de la mémoire vers le disque.

Dans votre cas, je suppose que vous ne faites pas des importations de 5 Go tout le temps. Il s'agit donc d'un taux de charge de données exceptionnellement élevé, et c'est temporaire. Vous pouvez probablement ignorer les avertissements, car InnoDB va progressivement rattraper son retard.

---

Voici une explication détaillée des éléments internes qui conduisent à cet avertissement.

Une fois par seconde, le nettoyeur de pages scanne le pool de mémoire tampon à la recherche de pages sales à évacuer du pool de mémoire tampon vers le disque. L'avertissement que vous avez vu montre qu'il a beaucoup de pages sales à évacuer, et qu'il lui faut plus de 4 secondes pour évacuer un lot de ces pages sur le disque, alors qu'il devrait effectuer ce travail en moins d'une seconde. En d'autres termes, il a les yeux plus gros que le ventre.

Vous avez ajusté cela en réduisant innodb_lru_scan_depth de 1024 à 256. Cela permet de réduire la distance parcourue par le fil du nettoyeur de pages dans le pool de tampons à la recherche de pages sales pendant son cycle d'une fois par seconde. Vous lui demandez de prendre de plus petites bouchées.

Notez que si vous avez beaucoup d'instances de pool de mémoire tampon, le vidage fera plus de travail. Il mord sur innodb_lru_scan_depth quantité de travail pour chaque instance de pool de mémoire tampon. Vous avez donc pu provoquer ce goulot d'étranglement par inadvertance en augmentant le nombre de pools de mémoire tampon sans diminuer la profondeur d'analyse.

La documentation pour innodb_lru_scan_depth dit "Un paramètre plus petit que la valeur par défaut est généralement adapté à la plupart des charges de travail." Il semble qu'ils aient donné à cette option une valeur trop élevée par défaut.

Vous pouvez imposer une limite aux IOPS utilisées par le vidage en arrière-plan, à l'aide de la commande innodb_io_capacity y innodb_io_capacity_max options. La première option est une limite souple sur le débit d'E/S qu'InnoDB demandera. Mais cette limite est flexible ; si la purge est inférieure au taux de création de nouvelles pages sales, InnoDB augmentera dynamiquement le taux de purge au-delà de cette limite. La deuxième option définit une limite plus stricte sur la façon dont InnoDB peut augmenter le taux de vidage.

Si le taux de vidange peut suivre le taux moyen de création de nouvelles pages sales, alors tout ira bien. Mais si vous créez constamment des pages sales plus rapidement qu'elles ne peuvent être vidées, votre pool de mémoire tampon finira par se remplir de pages sales, jusqu'à ce que le nombre de pages sales dépasse le nombre de pages de la base de données. innodb_max_dirty_page_pct de la réserve tampon. À ce stade, le taux de vidange augmentera automatiquement, et pourra à nouveau provoquer l'envoi d'avertissements par le nettoyeur de pages.

Une autre solution serait de mettre MySQL sur un serveur avec des disques plus rapides. Vous avez besoin d'un système d'E/S capable de gérer le débit exigé par le vidage des pages.

Si vous voyez cet avertissement en permanence dans le cadre d'un trafic moyen, il se peut que vous essayiez d'effectuer trop de requêtes en écriture sur ce serveur MySQL. Il est peut-être temps de réduire la taille du serveur et de répartir les écritures sur plusieurs instances MySQL, chacune disposant de son propre système de disque.

En savoir plus sur le nettoyeur de pages :

• Introduction du fil de page_cleaner dans InnoDB (copie archivée)
• MySQL-5.7 améliore les charges de travail orientées DML

Answer 2

Le goulot d'étranglement est la sauvegarde des données sur le disque dur. Quel que soit le disque dur que vous avez : SSD, normal, NVMe, etc.

Notez que cette solution s'applique principalement à InnoDB.

J'ai eu le même problème, j'ai appliqué quelques solutions.

1er : vérifier ce qui ne va pas

atop -d vous montrera l'utilisation du disque. Si le disque est "occupé", essayez d'arrêter toutes les requêtes vers la base de données (mais n'arrêtez pas le service du serveur mysql !).

Pour surveiller le nombre de requêtes que vous avez, utilisez mytop, innotop ou équivalent.

Si vous n'avez pas de requêtes, mais que l'utilisation du disque est TOUJOURS proche de 100% pendant quelques secondes / quelques minutes, alors cela signifie que le serveur mysql essaie de vider les pages sales / de faire un peu de nettoyage comme mentionné précédemment (excellent post de Bill Karwin).

ALORS vous pourrez essayer d'appliquer ces solutions :

2e : optimisation du matériel

Si votre réseau n'est pas en RAID 1+0, envisagez de doubler la vitesse de sauvegarde des données en utilisant ce type de solution. Essayez d'étendre les possibilités d'écriture des données sur votre disque dur. Essayez d'utiliser un SSD ou un disque dur plus rapide. L'application de cette soultion dépend de votre matériel et de vos possibilités budgétaires et peut varier.

3ième : mise au point du logiciel

Si le harware cotroller fonctionne bien, mais que vous voulez augmenter la vitesse de sauvegarde des données, vous pouvez le configurer dans le fichier de configuration mysql :

3.1.

innodb_flush_log_at_trx_commit = 2 -> si vous utilisez des tables innodb D'après mon expérience, cela fonctionne le mieux avec une table par fichier : innodb_file_per_table = 1

3.2.

continuer avec InnoDB : innodb_flush_method = O_DIRECT innodb_doublewrite = 0 innodb_support_xa = 0 innodb_checksums = 0

Les lignes ci-dessus réduisent en général la quantité de données à sauvegarder sur le disque dur, ce qui augmente les performances.

3.3

general_log = 0 slow_query_log = 0

Les lignes ci-dessus désactivent la sauvegarde des journaux, bien sûr, c'est encore une autre quantité de données à sauvegarder sur le disque dur.

3.4 Vérifiez à nouveau ce qui se passe en utilisant, par exemple, les commandes suivantes tail -f /var/log/mysql/error.log

4ème : notes générales

Notes générales : Ceci a été testé sous MySQL 5.6 ET 5.7.22 Système d'exploitation : Debian 9 RAID : 1 + 0 disques SSD Base de données : Tables InnoDB innodb_buffer_pool_size = 120G innodb_buffer_pool_instances = 8 innodb_read_io_threads = 64 innodb_write_io_threads = 64 Quantité totale de RAM dans le serveur : 200GB

Après avoir fait cela, vous pouvez observer une utilisation plus élevée du CPU ; c'est normal, car l'écriture des données est plus rapide, donc le CPU travaille plus fort.

Si vous utilisez le fichier my.cnf, n'oubliez pas de redémarrer le serveur MySQL.

5ème : supplément

Étant intriguée, j'ai fait cette bizarrerie avec SET GLOBAL innodb_lru_scan_depth=256; mentionné ci-dessus.

En travaillant avec de grandes tables, je n'ai constaté aucun changement dans les performances.

Après les corrections ci-dessus, je ne me suis pas débarrassé des avertissements, mais l'ensemble du système fonctionne beaucoup plus rapidement. Tout ce qui précède n'est qu'une expérimentation, mais j'ai mesuré les résultats, cela m'a aidé un peu, alors j'espère que cela peut être utile à d'autres.

Answer 3

Cela peut simplement indiquer une mauvaise performance du système de fichiers en général - un symptôme d'un problème sans rapport. Dans mon cas, j'ai passé une heure à faire des recherches, à analyser les journaux de mon système, et j'en étais presque arrivé à modifier la configuration de MySQL, lorsque j'ai décidé de vérifier auprès de mon hébergeur en ligne. Il s'est avéré qu'il y avait des "pics d'E/S abusifs de la part d'un voisin", que mon hébergeur a rapidement résolu après que je lui en ai fait part.

Je vous recommande de connaître les performances de base/attendues de votre système de fichiers, d'arrêter MySQL et de mesurer les performances de votre système de fichiers afin de déterminer s'il existe des problèmes plus fondamentaux non liés à MySQL.