Недавно мы перевели Zabbix на работу с БД PostgreSQL. Вместе с переездом на сервер с SSD это дало существенный прирост скорости работы. Также решили проблему с дублирующими хостами в базе данных, bug request. Здесь статья могла бы закончиться, но мы заметили, что Zabbix использует много дискового пространства, поэтому ниже я расскажу, как это вышло. И как мы с этим разобрались.У нас Zabbix с относительно большой БД. Он следит почти за 1500 хостами и собирает около 180 тысяч метрик. В базе данных используем партиционирование, что облегчает очистку исторических данных. При этом для каждой партиции есть свой индекс. Ну, вы понимаете, на что я намекаю.
Да, сейчас речь пойдет про индексы. Мы выяснили, что ряд индексов разрастается почти в 2 раза, при наших объемах они занимают 5-7 Gb для каждой партиции. А при условии, что мы храним исторические данные за 10 дней и тренды за 3 месяца, суммарно получается порядка 70 Gb лишних. При общем объеме БД около 220 Gb, и использование SSD — очень ощутимо.
Подход номер один. Решали задачу в лоб и запустили полный reindex. Получилось хорошо, освободили почти 70 Gb, как и ожидалось. Недостаток: время выполнения операции. А точнее даже то, что на это время выставляется lock на таблицу, а reindex занимает около 3 часов.
Подход номер два. Посмотрели в сторону pg_repack. Эта утилита дает произвести vacumm full и reindex без lock на таблицы. Установили ее, настроили, запустили и приготовились ликовать. Каково же было наше разочарование, когда мы увидели, что освободилось меньше гигабайта. Открываем документацию и читаем очень внимательно, там есть пункт — дословно:
«Target table must have a PRIMARY KEY, or at least a UNIQUE total index on a NOT NULL column». Дальше открываем БД и видим, что у таблиц, которые нам нужны, ни того, ни другого нет.
Можно, конечно, было бы их добавить, но это увеличит размер БД и у разработчиков явно была причина не добавлять PRIMARY KEY. Мы поигрались и выяснили, что, например, в таблице, которая содержит информацию по мониторингу логов, могут полностью дублироваться строки. В итоге нам пришлось отказаться от pg_repack.
Подход третий, победный. Если брать партицию за предыдущий день, то в нее уже никто не пишет, и с ней можно сделать reindex без простоя Zabbix-сервера. Придумано — проверено. Неа, оказывается, lock накладывается на всю таблицу, а не на партицию. Что ж, ну если нельзя сделать reindex, почему бы не сделать новый index, и вот тут мы наконец нащупали решение. Алгоритм простой:
- Создаем новый индекс с конструкцией: «CREATE INDEX CONCURRENTLY», что позволяет не накладывать долгий lock на БД, поскольку в эту партицию уже никто не пишет, то индекс создается успешно.
- Удаляем старый индекс.
- Переименовываем новый индекс в старый.
- Радостно бьем себя в грудь.
Теперь у нас каждую ночь запускается скрипт, который проходит по таблицам и проделывает все эти операции.
for i in `seq 1 10`; do
dd=`date +%Y_%m_%d -d "$i day ago"`
index_name="history_p$dd""_1"
index_name_new="history_p$dd""_1_new"
echo "$index_name"
psql -U postgres -d zabbix -c "CREATE INDEX CONCURRENTLY $index_name_new ON partitions.history_p$dd USING btree (itemid, clock); "
echo "Done CREATE"
psql -U postgres -d zabbix -c "DROP INDEX partitions.$index_name"
echo "Done DROP"
psql -U postgres -d zabbix -c "ALTER INDEX IF EXISTS partitions.$index_name_new RENAME TO $index_name"
echo "Done ALTER"
done
echo "Reindex history_uint_p Start \n"
for i in `seq 1 10`; do
dd=`date +%Y_%m_%d -d "$i day ago"`
index_name="history_uint_p$dd""_1"
index_name_new="history_uint_p$dd""_1_new"
echo "$index_name"
psql -U postgres -d zabbix -c "CREATE INDEX CONCURRENTLY $index_name_new ON partitions.history_uint_p$dd USING btree (itemid, clock); "
echo "Done CREATE"
psql -U postgres -d zabbix -c "DROP INDEX partitions.$index_name"
echo "Done DROP"
psql -U postgres -d zabbix -c "ALTER INDEX IF EXISTS partitions.$index_name_new RENAME TO $index_name"
echo "Done ALTER"
done
echo "Reindex trends_p Start \n"
for i in `seq 1 2`; do
dd=`date +%Y_%m -d "1 month ago"`
index_name="trends_p$dd""_1"
index_name_new="trends_p$dd""_1_new"
echo "$index_name"
psql -U postgres -d zabbix -c "CREATE INDEX CONCURRENTLY $index_name_new ON partitions.trends_p$dd USING btree (itemid, clock); "
echo "Done CREATE"
psql -U postgres -d zabbix -c "DROP INDEX partitions.$index_name"
echo "Done DROP"
psql -U postgres -d zabbix -c "ALTER INDEX IF EXISTS partitions.$index_name_new RENAME TO $index_name"
echo "Done ALTER"
done
echo "Reindex trends_uint_p Start \n"
for i in `seq 1 2`; do
dd=`date +%Y_%m -d "1 month ago"`
index_name="trends_uint_p$dd""_1"
index_name_new="trends_uint_p$dd""_1_new"
echo "$index_name"
psql -U postgres -d zabbix -c "CREATE INDEX CONCURRENTLY $index_name_new ON partitions.trends_uint_p$dd USING btree (itemid, clock); "
echo "Done CREATE"
psql -U postgres -d zabbix -c "DROP INDEX partitions.$index_name"
echo "Done DROP"
psql -U postgres -d zabbix -c "ALTER INDEX IF EXISTS partitions.$index_name_new RENAME TO $index_name"
echo "Done ALTER"
done
Прошу строго не критиковать скрипт — это черновик для наглядности статьи.
Спасибо за внимание!
Комментарии (14)
zuborg
19.10.2016 11:42Два замечания:
Во первых, создание параллельного индекса может не удаться (из-за дедлока, например) — нужно проверять состояние индексов после выполнения CREATE INDEX CONCURRENTLY, в частности, состояние может быть INVALID. Само-собой, поломанный индекс использоваться не будет и удаление рабочего приведет к тому, что у нас вообще не будет доступного для использования индекса.
Во вторых — при удалении индекса сломаются prepared statements, которые пользуются этим индексом. Ну, как сломаются — работать они продолжат, но без использования индекса, т.е. медленно (возможно, в свежих версиях постгреса уже починили, не проверял).
s_korobeiko
19.10.2016 12:12+2Я понимаю, что статья больше про индексы, чем про zabbix, но не лучше ли отказаться от партиционирования на SSD и вернуться к встроенному механизму очистки и отбросить все минусы партиционирования. SSD вполне может позволить такую операцию без потери производительности самого zabbix. Если кто-то думает, что при этом быстро износится SSD, то нет. За два года работы Remaining Rated Write Endurance: 96%.
Zabbix: 2400vps, 114000 метрик, БД 260 Gb
berlevdv
19.10.2016 16:56+1Такая БД у Вас лежит на одном SSD или используете RAID? Можете озвучить модель? Не могли бы Вы привести статистику iowait на хосте, где лежит БД (конечно, если только её и обслуживает)?
s_korobeiko
20.10.2016 11:33БД работает на 2х SSD INTEL SSDSC2BA400G3T в RAID-1 только под zabbix.
%util from iostat
Каждый час запускается zabbix Housekeeper.
В 3:00 10-19 запустился бэкап на 3.5 часа.sfw
20.10.2016 12:51Да, у нас используется RAID-1. База данных и сам Zabbix расположены на одном сервере.
Статистика iowait:
На графике видны пики, самые крупные — бекапы, те что поменьше, это служебные запросы через api для получения служебной информации и для автоматизации ряда процессов.
varnav
19.10.2016 16:06+1Ещё несколько назад в Zabbix-е любые поддерживаемые им СУБД уступали MySQL. Такая у него была особенность.
Действительно ли ситуация сейчас настолько изменилась, что PostgreSQL стал оптимальным выбором для Zabbix?sfw
20.10.2016 12:19+1Zabbix уже достаточно давно рекомендует для больших БД использовать PostgreSQL.
ky0
19.10.2016 20:27Хотелось бы подробностей, откуда взялась такая мощная экономия места — в заббиксе же данные из середины таблиц практически не апдейтятся и не удаляются, откуда взяться неоптимальным индексам? Попробовали у себя проделать реиндекс одного из партишенов — никакой разницы pg_relation_size в размерах индексов не показал.
sfw
20.10.2016 12:23Тут сразу однозначно ответить сложно, многое зависит от версий PostgreSQL и Zabbix, от типа данных которые вы собираете, размера БД. Так же, возможно зависит от того, чистая ли у вас установка или обновление со старых версий, структура БД немного разная при чистой установке и при обновлении. У нас, например, для некоторых таблиц индексы весят 5-7GB, после reindex их размер уменьшается до 2-3 GB. Версии ПО, используемые в данной статье: Zabbix 3.0.4 и PostgreSQL 9.5.
Komzpa
А можно было взять https://github.com/kostya/pg_reindex :)
D1abloRUS
Всю малину сломали парням.
kemko
Или https://github.com/grayhemp/pgtoolkit
Он и индексы умеет пересоздавать (только btree), и bloat из таблиц пытается убрать.
sfw
Когда разбирались, предложенная выше утилита была на старте разработки.