Как решить предупреждение mysql: "InnoDB: page_cleaner: цикл 1000 мс потребовал XXX мс. Настройки могут быть не оптимальными"?

Question

Я запустил mysql import mysql dummyctrad

Являются ли эти нормальные сообщения / статус "ожидание сброса таблицы" и сообщение InnoDB: page_cleaner: 1000ms intended loop took 4013ms. The settings might not be optimal

содержимое журнала 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)

Список процессов:

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)

Update-1

mysqlforum, innodb_lru_scan_depth

изменение значения innodb_lru_scan_depth на 256 улучшило время выполнения запросов на вставку + отсутствие предупреждающего сообщения в журнале, по умолчанию было задано innodb_lru_scan_depth=1024;

SET GLOBAL innodb_lru_scan_depth=256;

Answer 1

InnoDB: page_cleaner: цикл, рассчитанный на 1000 мс, занял 4013 мс. Настройки могут быть неоптимальными. (сбрасывается =1438 и выселяется =0 в течение времени.)

Проблема типична для экземпляра MySQL, где у вас высокий уровень изменений в базе данных. Запустив импорт 5 ГБ, вы быстро создаете грязные страницы. Когда создаются грязные страницы, поток очистки страниц отвечает за копирование грязных страниц из памяти на диск.

В вашем случае, я предполагаю, что вы не делаете импорт 5 ГБ все время. Так что это исключительно высокая скорость загрузки данных, и она временная. Вы, вероятно, можете игнорировать предупреждения, потому что InnoDB будет постепенно догонять.

---

Вот подробное объяснение внутренних органов, приводящих к этому предупреждению.

Один раз в секунду программа очистки страниц сканирует пул буферов на наличие грязных страниц, чтобы сбросить их из пула буферов на диск. Предупреждение, которое вы увидели, показывает, что на нем много грязных страниц, которые нужно очистить, и для их загрузки требуется более 4 секунд, когда эта операция завершится менее чем за 1 секунду. Другими словами, он откусывает больше, чем может жевать.

Вы скорректировали это, уменьшив innodb_lru_scan_depth с 1024 до 256. Это уменьшает, насколько далеко в пул буферов поток чистильщика страниц ищет грязные страницы во время своего цикла раз в секунду. Вы просите это, чтобы взять меньшие укусы.

Обратите внимание, что если у вас много экземпляров буферного пула, сброс приведет к дополнительной работе. Откусывает innodb_lru_scan_depth объем работы для каждого экземпляра буферного пула. Таким образом, вы могли непреднамеренно вызвать это узкое место, увеличив число пулов буферов без уменьшения глубины сканирования.

Документация для innodb_lru_scan_depth говорит: "Параметр меньше значения по умолчанию, как правило, подходит для большинства рабочих нагрузок". Похоже, они дали этой опции значение, которое слишком высоко по умолчанию.

Вы можете установить ограничение на IOPS, используемое для фоновой очистки, с помощью innodb_io_capacity а также innodb_io_capacity_max опции. Первый вариант - это мягкое ограничение пропускной способности ввода-вывода, которое будет запрашивать InnoDB. Но этот предел является гибким; если сбрасывание отстает от скорости создания новой грязной страницы, InnoDB будет динамически увеличивать скорость сброса сверх этого предела. Второй вариант определяет более строгий предел того, насколько далеко InnoDB может увеличить скорость очистки.

Если скорость сброса может идти в ногу со средней скоростью создания новых грязных страниц, то все будет в порядке. Но если вы последовательно создаете грязные страницы быстрее, чем их можно очистить, в конечном итоге ваш буферный пул будет заполняться грязными страницами, пока грязные страницы не превысят innodb_max_dirty_page_pct буферного пула. В этот момент скорость очистки автоматически увеличивается и может снова заставить page_cleaner отправлять предупреждения.

Другим решением было бы разместить MySQL на сервере с более быстрыми дисками. Вам нужна система ввода / вывода, которая может обрабатывать пропускную способность, требуемую при очистке страницы.

Если вы видите это предупреждение все время под средним трафиком, возможно, вы пытаетесь выполнить слишком много запросов записи на этом сервере MySQL. Возможно, пришло время масштабировать и разделять записи по нескольким экземплярам MySQL, каждый из которых имеет свою собственную дисковую систему.

Узнайте больше об очистителе страницы: https://blogs.oracle.com/mysqlinnodb/entry/introducing_page_cleaner_thread_in

Answer 2

Узким местом является сохранение данных на жесткий диск. Какой бы жесткий диск у вас не был: SSD, обычный, NVMe и т. Д.

Обратите внимание, что это решение относится в основном к InnoDB

У меня была такая же проблема, я применил несколько решений.

1-й: проверка, что не так

atop -d покажет вам использование диска. Если диск "занят", попробуйте остановить все запросы к базе данных (но не останавливайте службу сервера MySQL!)

Чтобы отслеживать, сколько запросов у вас есть, используйте mytop, innotop или эквивалентный.

Если у вас 0 запросов, но использование диска ЕЩЕ ОДНО 100% от нескольких секунд / нескольких минут, то это означает, что сервер mysql пытается очистить грязные страницы / выполнить некоторую очистку, как упоминалось ранее (отличный пост Билла Карвина),

ТОГДА можно попробовать применить такие решения:

2-й: оптимизация аппаратного обеспечения

Если ваш массив не поддерживает RAID 1+0, удвойте скорость сохранения данных с помощью такого решения. Попробуйте расширить возможности вашего жесткого диска с записью данных. Попробуйте использовать SSD или более быстрый HDD. Применение этого решения зависит от ваших аппаратных и бюджетных возможностей и может варьироваться.

3-й: настройка программного обеспечения

Если harware cotroller работает нормально, но вы хотите увеличить скорость сохранения данных, вы можете настроить их в конфигурационном файле mysql:

3.1.

innodb_flush_log_at_trx_commit = 2 -> если вы используете таблицы innodb, то лучше всего работает с одной таблицей на файл:innodb_file_per_table = 1

3.2.

продолжаем с InnoDB:innodb_flush_method = O_DIRECT innodb_doublewrite = 0 innodb_support_xa = 0 innodb_checksums = 0

Приведенные выше строки в целом сокращают объем данных, которые необходимо сохранить на жестком диске, поэтому производительность выше.

3,3

general_log = 0 slow_query_log = 0

Вышеуказанные строки отключают сохранение журналов, конечно, это еще один объем данных, который будет сохранен на жестком диске

3.4 еще раз проверьте, что происходит, например, tail -f /var/log/mysql/error.log

4-е: общие примечания

Общие примечания: это было протестировано в MySQL 5.6 и 5.7.22 ОС: Debian 9 RAID: диски SSD 1 + 0 База данных: таблицы InnoDBinnodb_buffer_pool_size = 120G innodb_buffer_pool_instances = 8 innodb_read_io_threads = 64 innodb_write_io_threads = 64Общий объем оперативной памяти на сервере: 200 ГБ

После этого вы можете наблюдать более высокую загрузку процессора; это нормально, потому что запись данных происходит быстрее, поэтому процессор будет работать сильнее.

Если вы делаете это, используя my.cnf, конечно, не забудьте перезапустить сервер MySQL.

5-е: дополнение

Будучи заинтригованным, я сделал эту причуду сSET GLOBAL innodb_lru_scan_depth=256;упомянутое выше.

Работая с большими таблицами, я не видел никаких изменений в производительности.

После исправлений выше я не избавился от предупреждений, однако вся система работает значительно быстрее. Все вышеперечисленное - всего лишь эксперимент, но я измерил результаты, они мне немного помогли, так что, надеюсь, это может быть полезно для других.

Answer 3

Это может просто указывать на плохую производительность файловой системы в целом - симптом не связанной проблемы. В моем случае я потратил час на изучение этого, анализ моих системных журналов и почти дошел до точки настройки конфигурации MySQL, когда решил проверить свой облачный хостинг. Оказалось, что от соседа произошли "оскорбительные всплески ввода-вывода". что мой хозяин быстро решил после того, как я обратил на это их внимание.

Я рекомендую узнать вашу базовую / ожидаемую производительность файловой системы, остановить MySQL и измерить производительность файловой системы, чтобы определить, есть ли более фундаментальные проблемы, не связанные с MySQL.