Техническое обслуживание сервера

Zabbix: Оптимизация и чистка базы данных

Содержание

Спустя 2 года решил обновить запись Zabbix: Чистка базы данных, так как есть более эффективные методы борьбы с распуханием базы , да и в целом информации накопилось больше чем можно удержать в голове и evernote.

Основная задача, которую пытаемся решить — малой кровью обеспечить хорошую производительность сервера и предотвратить неконтролируемый рост базы.

Характеристики текущего сервера:

Ubuntu 14.04.4
psql (PostgreSQL) 9.3.13
Zabbix 3.4.4
Cpu: 8 x Intel(R) Xeon(R) CPU E5-2660 v4 @ 2.00GHz
MemTotal: 16Gb
Disk: 300Gb Raid 10 4x15k

Состояние Zabbix:

Количество узлов сети (активированных/деактивированных/шаблонов) 1k
Количество элементов данных (активированных/деактивированных/неподдерживаемых) 60k
Количество триггеров (активированных/деактивированных [проблема/ок]) 20k
Требуемое быстродействие сервера, новые значения в секунду 748

Расчет планируемого места в базе:

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

60000 событий x 60 минут x 24 часа = 86400000 событий в день.

Одно событие в среднем занимает около 90 байт, т.е.

86400000 x 90 байт = 7,77Гб в день.

Для трендов Zabbix хранит только ежечасную статистику для каждого элемента (max/min/avg/count), выкидываем из формулы минуты:

60000 событий x 24 часа x 90 байт = 0,13Гб в день.

Итог: месяц хранения истории обойдется нам в 230Гб и 4Гб в виде трендов.

Если сделать запрос в базу, то в столбце size можно увидеть большие таблицы и их размер, который в целом соответствует расчетам.

select result.table_name, result.freespace as fororder, pg_size_pretty(result.size) as size, pg_size_pretty(result.freespace) as freespace, result.freespace * 100 / result.size as percent_free from (select t.table_name, pg_total_relation_size(t.table_name) as size, sum(s.avail) as freespace from (select table_name from information_schema.tables where table_schema = ‘public’) as t, LATERAL pg_freespace(t.table_name) as s group by t.table_name) as result order by fororder desc limit 20; table_name | fororder | size | freespace | percent_free ———————+————-+———+————+————— history | 12331130656 | 59 GB | 11 GB | 19 history_uint | 146627744 | 212 GB | 140 MB | 0 auditlog | 11642944 | 2891 MB | 11 MB | 0 trends_uint | 10216192 | 9845 MB | 9977 kB | 0 trends | 3569984 | 3208 MB | 3486 kB | 0 sessions | 3255808 | 3603 MB | 3180 kB | 0 alerts | 1384288 | 134 MB | 1352 kB | 0 item_discovery | 1074912 | 408 MB | 1050 kB | 0 history_str | 585952 | 651 MB | 572 kB | 0 triggers | 566656 | 67 MB | 553 kB | 0 items | 379808 | 55 MB | 371 kB | 0 history_text | 196992 | 232 MB | 192 kB | 0 history_log | 152128 | 38 MB | 149 kB | 0 hosts | 133312 | 13 MB | 130 kB | 0 problem | 58272 | 41 MB | 57 kB | 0 profiles | 26080 | 5560 kB | 25 kB | 0 users | 20064 | 96 kB | 20 kB | 20 images | 8736 | 1200 kB | 8736 bytes | 0 interface_discovery | 8448 | 96 kB | 8448 bytes | 8 dbversion | 8128 | 40 kB | 8128 bytes | 19 (20 rows)

Лечение:

1) Запросы для чистки базы:

— keep 1 week of history and 3 months of trends \set history_interval 7 \set trends_interval 90 DELETE FROM alerts where age(to_timestamp(alerts.clock)) (:history_interval * interval ‘1 day’); DELETE FROM acknowledges where age(to_timestamp(acknowledges.clock)) (:history_interval * interval ‘1 day’); DELETE FROM events where age(to_timestamp(events.clock)) (:history_interval * interval ‘1 day’); DELETE FROM history where age(to_timestamp(history.clock)) (:history_interval * interval ‘1 day’); DELETE FROM history_uint where age(to_timestamp(history_uint.clock)) (:history_interval * interval ‘1 day’) ; DELETE FROM history_str where age(to_timestamp(history_str.clock)) (:history_interval * interval ‘1 day’) ; DELETE FROM history_text where age(to_timestamp(history_text.clock)) (:history_interval * interval ‘1 day’) ; DELETE FROM history_log where age(to_timestamp(history_log.clock)) (:history_interval * interval ‘1 day’) ; DELETE FROM trends where age(to_timestamp(trends.clock)) (:trends_interval * interval ‘1 day’); DELETE FROM trends_uint where age(to_timestamp(trends_uint.clock)) (:trends_interval * interval ‘1 day’) ;

time sudo -u postgres psql -A -R ‘ : ‘ -P ‘footer=off’ zabbix delete-old-data.pg.sql

2) Обработка базы при помощи pgtoolkit:

pgtoolkit — инструмент для уменьшения раздувания таблиц и индексов без тяжелых блокировок и полной перестройки таблицы (https://github.com/grayhemp/pgtoolkit)

Иногда может возникнуть необходимость проверить и удалить временные индексы, оставшиеся от работы pgcompact:

select * from pg_indexes where indexname like ‘%pgcompact%’; schemaname | tablename | indexname | tablespace | indexdef ————+——————+————————+————+———————————————————————————————— public | hostmacro | pgcompact_index_12057 | | CREATE UNIQUE INDEX pgcompact_index_12057 ON hostmacro USING btree (hostid, macro) public | graph_discovery | pgcompact_index_29490 | | CREATE UNIQUE INDEX pgcompact_index_29490 ON graph_discovery USING btree (graphid) public | hosts_templates | pgcompact_index_4305 | | CREATE UNIQUE INDEX pgcompact_index_4305 ON hosts_templates USING btree (hostid, templateid) public | hosts_templates | pgcompact_index_21016 | | CREATE UNIQUE INDEX pgcompact_index_21016 ON hosts_templates USING btree (hostid, templateid) drop index pgcompact_index_12057; drop index pgcompact_index_29490; drop index pgcompact_index_4305; drop index pgcompact_index_21016;

Результат работы можно увидеть на графиках.

В данном случае я пропустил момент распухания базы и на раздел пришлось экстренно добавлять место.

Чтобы не пропускать переполнение диска, рекомендуется добавить задание в cron:

Если у вас mysql:

При использовании движка базы данных InnoDB, все таблицы и индексы хранятся в системном табличном пространстве (в одном файле /var/lib/mysql/ibdata).

Для того, чтобы хранить каждую таблицу InnoDB и связанные индексы в отдельных файлах — нужно активировать опцию innodb_file_per_table.

Алгоритм включения опции следующий:

  • останавливаем приложения, использующие базу данных;
  • делаем резервную копию базы данных;
  • удаляем базу данных;
  • останавливаем MySQL-сервер;
  • в конфигурационном файле /etc/mysql/my.cnf удаляем параметр innodb_data_file_path и добавляем параметр innodb_file_per_table;
  • удаляем файлы /var/lib/mysql/ibdata, /var/lib/mysql/ib_logfile0, /var/lib/mysql/ib_logfile1 и т. д.;
  • запускаем MySQL-сервер;
  • создаем базу данных;
  • восстанавливаем данные из резервной копии;
  • проверяем наличие файлов с расширением *.ibd для каждой таблицы;
  • запускаем остановленные в первом пункте сервисы.

Категория: Администрирование

Источник: shurshun.ru