Сaжаем на диету индексы PostgreSQL для Zabbix

Недавно мы перевели 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, и вот тут мы наконец нащупали решение. Алгоритм простой:

1. Создаем новый индекс с конструкцией: «CREATE INDEX CONCURRENTLY», что позволяет не накладывать долгий lock на БД, поскольку в эту партицию уже никто не пишет, то индекс создается успешно.
   
2. Удаляем старый индекс.
3. Переименовываем новый индекс в старый.
4. Радостно бьем себя в грудь.

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

Скрипт

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

Прошу строго не критиковать скрипт — это черновик для наглядности статьи.

Спасибо за внимание!