company_banner

С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
    


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

    Спасибо за внимание!
    Southbridge
    527.60
    Обеспечиваем стабильную работу highload-проектов
    Share post

    Comments 14

      +5
      А можно было взять https://github.com/kostya/pg_reindex :)
        0
        Всю малину сломали парням.
          +1

          Или https://github.com/grayhemp/pgtoolkit
          Он и индексы умеет пересоздавать (только btree), и bloat из таблиц пытается убрать.

            0
            Когда разбирались, предложенная выше утилита была на старте разработки.
            0
            Два замечания:
            Во первых, создание параллельного индекса может не удаться (из-за дедлока, например) — нужно проверять состояние индексов после выполнения CREATE INDEX CONCURRENTLY, в частности, состояние может быть INVALID. Само-собой, поломанный индекс использоваться не будет и удаление рабочего приведет к тому, что у нас вообще не будет доступного для использования индекса.
            Во вторых — при удалении индекса сломаются prepared statements, которые пользуются этим индексом. Ну, как сломаются — работать они продолжат, но без использования индекса, т.е. медленно (возможно, в свежих версиях постгреса уже починили, не проверял).
              +2
              Я понимаю, что статья больше про индексы, чем про zabbix, но не лучше ли отказаться от партиционирования на SSD и вернуться к встроенному механизму очистки и отбросить все минусы партиционирования. SSD вполне может позволить такую операцию без потери производительности самого zabbix. Если кто-то думает, что при этом быстро износится SSD, то нет. За два года работы Remaining Rated Write Endurance: 96%.
              Zabbix: 2400vps, 114000 метрик, БД 260 Gb
                +1
                Такая БД у Вас лежит на одном SSD или используете RAID? Можете озвучить модель? Не могли бы Вы привести статистику iowait на хосте, где лежит БД (конечно, если только её и обслуживает)?
                  0
                  БД работает на 2х SSD INTEL SSDSC2BA400G3T в RAID-1 только под zabbix.
                  %util from iostat
                  image
                  Каждый час запускается zabbix Housekeeper.
                  В 3:00 10-19 запустился бэкап на 3.5 часа.
                    +1
                    Спасибо, очень полезная для меня информация.
                    0
                    Да, у нас используется RAID-1. База данных и сам Zabbix расположены на одном сервере.
                    Статистика iowait: image
                    На графике видны пики, самые крупные — бекапы, те что поменьше, это служебные запросы через api для получения служебной информации и для автоматизации ряда процессов.
                  +1
                  Ещё несколько назад в Zabbix-е любые поддерживаемые им СУБД уступали MySQL. Такая у него была особенность.
                  Действительно ли ситуация сейчас настолько изменилась, что PostgreSQL стал оптимальным выбором для Zabbix?
                    +1
                    Zabbix уже достаточно давно рекомендует для больших БД использовать PostgreSQL.
                    0
                    Хотелось бы подробностей, откуда взялась такая мощная экономия места — в заббиксе же данные из середины таблиц практически не апдейтятся и не удаляются, откуда взяться неоптимальным индексам? Попробовали у себя проделать реиндекс одного из партишенов — никакой разницы pg_relation_size в размерах индексов не показал.
                      0
                      Тут сразу однозначно ответить сложно, многое зависит от версий PostgreSQL и Zabbix, от типа данных которые вы собираете, размера БД. Так же, возможно зависит от того, чистая ли у вас установка или обновление со старых версий, структура БД немного разная при чистой установке и при обновлении. У нас, например, для некоторых таблиц индексы весят 5-7GB, после reindex их размер уменьшается до 2-3 GB. Версии ПО, используемые в данной статье: Zabbix 3.0.4 и PostgreSQL 9.5.

                    Only users with full accounts can post comments. Log in, please.