Жестоко тормозит HouseKeeper

[Sentinel7]

В процессе срабатывания HouseKeeper все комплексные экраны темнеют, актуальность данных в режиме реального времени пропадает,
обновления экранов приходится ждать по 2-3 минуты. Всё восстанавливается после окончания процесса housekeep'инга.

OS: CentOS 6.4
Zabbix: Zabbix 2.0.4
SQL: MySQL 5.1.69

Узлы сети (пн/бн/ш): 54(30/1/23)
Элементы данных (а/д/нп): 2945(2634/1/310)
Да-да, совсем не много..

База ~2 Gb. HouseKeeper пока работает каждый час.

Грешу на базу, скорее всего надо оптимизировать.
Варианты для начала:
1) Скрипт на перле "mysqltuner.pl" для оптимизации MySQL, выдает советы по настройкам, так же показывает информацию об индексах в таблицах и фрагментации. Натравить на MySQL, послушать что скажет, далее делать выводы и изменения в my.cnf.
2) Использовать "innodb_file_per_table", чтобы данные и индексы хранились для каждой таблицы в отдельном файле table_name.ibd. Тут есть "за" и "против", но чаще проскакивает, что на стандартном MySQL такая опция не нужна, ибо хранение таблиц в отдельных файлах может снизить производительность.
3) Партицирование, как я понял, не подойдет. Да и вряд ли осилю сам. Пост одного товарища:
"Unfortunately, the Zabbix 2.0 database is using relational integrity, which doesn’t work with partitions. So the choice is to remove the relations, or not use partitioning."
4) Создание временных таблиц через tmpfs. Есть мнения, что "файловая система tmpfs не поддерживает innodb". Кто слышал про такое?
5) Простая оптимизация с помощью optimize table.. Может уже есть готовые запросы, чтобы не писать на каждую таблицу самому?
Какие есть еще варианты? Если ничего из вышеперечисленного проблему не решит, что можно сделать с HouseKeeper'ом?
Мне в голову пришло только это:
написать 2 конфига zabbix_server.conf. В одном HouseKeeper включен, в другом - выключен. По выходным дням ночью запускать через крон замену конфига и перезапускать сервер Zabbix через него же, потом так же на выключение HouseKeeper'а до следующих выходных. Больше ничего не придумывается.
Кто что посоветует? Нужна помощь..

[bga83]

2) Использовать "innodb_file_per_table", чтобы данные и индексы хранились для каждой таблицы в отдельном файле table_name.ibd. Тут есть "за" и "против", но чаще проскакивает, что на стандартном MySQL такая опция не нужна, ибо хранение таблиц в отдельных файлах может снизить производительность.

К этому вопросу надо подходить с умом. Само по себе разнесение таблиц по разным файлам ничего не даст. Прирост будет если эти табличные пространства будут разнесены по разным дискам, чтобы операци чтения/записи шли параллельно. Плюс надо понять какие именно таблицы разносить надо, хотя в документации наиболее часто используемые таблицы перечислены.

Насколько я понял используется innodb. Настройки стандартные для этого движка? как соотносится параметр innodb_buffer_pool_size с объемом памяти на сервере?

Вообще имеет смысл для начала настроить мониторинг СУБД, дисковой подсистемы и пр, то есть выявить какая именно подсистема приводит к снижению производительности.

кстати каково значение NVPS?

[Sentinel7]

К этому вопросу надо подходить с умом. Само по себе разнесение таблиц по разным файлам ничего не даст. Прирост будет если эти табличные пространства будут разнесены по разным дискам, чтобы операци чтения/записи шли параллельно. Плюс надо понять какие именно таблицы разносить надо, хотя в документации наиболее часто используемые таблицы перечислены.

Насколько я понял используется innodb. Настройки стандартные для этого движка? как соотносится параметр innodb_buffer_pool_size с объемом памяти на сервере?

Вообще имеет смысл для начала настроить мониторинг СУБД, дисковой подсистемы и пр, то есть выявить какая именно подсистема приводит к снижению производительности.

кстати каково значение NVPS?

Да, совершенно верно, я забыл указать про InnoDB. В целом всё стандартно, innodb_buffer_pool_size=8388608 (или 8Mb), на сервере 4Gb ОЗУ. Увы, большего получить не удалось пока. Мониторинг СУБД у меня сейчас условный, существует средствами самого Zabbix'а в виде графика. Общие VPS держатся на 37-40 в секунду.

P.S.: Я думаю, что тут не в железке дело, хоть она и виртуальная. Мне все же кажется, что-то в самих настройках MySQL или во фрагментации таблиц. Надо для начала оптимизировать, потом скажу что получилось. нет никаких пакетных скриптов для оптимизации всех таблиц в БД? Чтобы сразу уж...

[bga83]

Да, совершенно верно, я забыл указать про InnoDB. В целом всё стандартно, innodb_buffer_pool_size=8388608 (или 8Mb), на сервере 4Gb ОЗУ.

Если на этом сервере кроме zabbix больше ничего особо прожорливого нет, то в первую очередь innodb_buffer_pool_size стоит увеличить на значение ~2Гб, плюс посмотреть остальные. Стандартные параметры настройки innodb годятся разве что для крохотных баз с небольшой нагрузкой.

[Sentinel7]

Если на этом сервере кроме zabbix больше ничего особо прожорливого нет, то в первую очередь innodb_buffer_pool_size стоит увеличить на значение ~2Гб, плюс посмотреть остальные. Стандартные параметры настройки innodb годятся разве что для крохотных баз с небольшой нагрузкой.

Спасибо) Нет, только zabbix там живет. А что следует из стандартных еще поменять?

[Sentinel7]

Вот, кстати, mysqltuner.pl прогнал через базу. Он "посоветовал" вот что:

---Perfomance Metrics---<-тестирование и вывод результатов по проверке
[!!] Query cache is disabled
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 247K opened)
[!!] InnoDB data size / buffer pool: 3.3G/8.0M
--- Recommendations ---<-Рекомендации, соответственно
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 3G)

[lentyai]

Partitioning и только он

Если Вы дошли до того, что при housekeeping у Вас тормозит база, то единственный реальный выход - партишионинг.
Другая оптимизация база тоже не помешает, конечно.
Просто сам по себе housekeeping сделан очень не оптимально. Запросы типа "delete from history where clock < ....." работают очень долго. При этом еще лочится таблица.
Сейчас у вас только отрисовка страдает. При увеличении объема данных будут лаги в данных, что еще хуже.

Шаги очень простые:
1. Разделяем базу так, чтобы табицы хранились в отдельных файлах.
2. Делаем partitioning на таблицах: history, history_*, trends и trends_uint.
3. Подключаем скрипт для удаления партиций и создания новых
4. Выключаем housekeeping на zabbix_server.

После этого удаление старых данных будет происходить мгновенно.

Ну и общие рекомендации по оптимизации никто не отменяет:
- Установка MySQL на отдельном сервере
- Разделение на нем логов и данных по разным дискам
- Выделение максимального значение памяти но так, чтобы своп не трогался
- создание tmpfs в памяти и указание MySQL создавать временные файлы там.


-
Dmitry Matyushin
Zabbix sertified specialist

[Sentinel7]

Если Вы дошли до того, что при housekeeping у Вас тормозит база, то единственный реальный выход - партишионинг.
Другая оптимизация база тоже не помешает, конечно.
Просто сам по себе housekeeping сделан очень не оптимально. Запросы типа "delete from history where clock < ....." работают очень долго. При этом еще лочится таблица.
Сейчас у вас только отрисовка страдает. При увеличении объема данных будут лаги в данных, что еще хуже.

Шаги очень простые:
1. Разделяем базу так, чтобы табицы хранились в отдельных файлах.
2. Делаем partitioning на таблицах: history, history_*, trends и trends_uint.
3. Подключаем скрипт для удаления партиций и создания новых
4. Выключаем housekeeping на zabbix_server.

После этого удаление старых данных будет происходить мгновенно.

Ну и общие рекомендации по оптимизации никто не отменяет:
- Установка MySQL на отдельном сервере
- Разделение на нем логов и данных по разным дискам
- Выделение максимального значение памяти но так, чтобы своп не трогался
- создание tmpfs в памяти и указание MySQL создавать временные файлы там.


-
Dmitry Matyushin
Zabbix sertified specialist

Спасибо за ответ) По общим рекомендациям я с вами полностью согласен - распределёнка позволит высвободить много ресурсов. Но в моем случае, от сервера Zabbix требуется мониторинг не более 100 узлов. Это конечно, на текущий момент, кто знает, что будет потом. Однако, сейчас отталкиваются именно от этой цифры и на такие объемы мониторинга попросту никто не станет выделять отдельные сервера для распределенной архитектуры. Так что, остаюсь пока что с теми ресурсами, что уже имеются.
Разделение логов - опять согласен, но увы, у меня всего 1 диск объёмом 50Gb под всё, т.е система+zabbix+mysql+по мелочи. Больше места пока не предвидится.
Сейчас внес коррективы в my.cnf:
thread_cache_size=16
query_cache_size=8388608
query_cache_limit=83886608
innodb_buffer_pool_size=2147483648
table_cache = 64
Посмотрим, что будет..
Еще планирую сделать optimize table. Видимо, придется сидеть писать запросы на все таблицы.
Вы можете как-то прокомментировать вот это:
3) Партицирование, пост одного товарища:
"Unfortunately, the Zabbix 2.0 database is using relational integrity, which doesn’t work with partitions. So the choice is to remove the relations, or not use partitioning."
4) Создание временных таблиц через tmpfs. Есть мнения, что "файловая система tmpfs не поддерживает innodb".
Стоит беспокоиться или всё работает и проблем нет?

[lentyai]

Вы можете как-то прокомментировать вот это:
3) Партицирование, пост одного товарища:
"Unfortunately, the Zabbix 2.0 database is using relational integrity, which doesn’t work with partitions. So the choice is to remove the relations, or not use partitioning."
4) Создание временных таблиц через tmpfs. Есть мнения, что "файловая система tmpfs не поддерживает innodb".
Стоит беспокоиться или всё работает и проблем нет?

Про FOREIGN KEYs могу сказать следующее, если они и есть по-умолчанию на таблицы history* и trends*, то их можно смело удалить. Данные будут все равно удаляться по-расписанию.
Если же хотите сделать партиционирование для евентов и пр., то тут надо будет больше думать, хотя, я просто удалил ненужные индексы и все. Да, база, возможно, стала немного неконсистента (могут храниться лишние данные), но это с лихвой компенсируется увеличением производительности.

по п.4, можно не париться, там создаются стандартные файлы. И вовсе не обязательно, что это временные таблицы. Просто временные файлы MySQL. Ты же не сам таблицы там создаешь.