Садимо на дієту індекси PostgreSQL для Zabbix

Нещодавно ми перевели Zabbix на роботу з БД PostgreSQL. Разом з переїздом на сервер з SSD це дало істотний приріст швидкості роботи. Також вирішили проблему з дублюючими хостами в базі даних, bug request. Тут стаття могла б закінчитися, але ми помітили, що Zabbix використовує багато дискового простору, тому нижче я розповім, як це вийшло. І як ми з цим розібралися.


У нас Zabbix з відносно великою БД. Він стежить майже за 1500 хостами і збирає близько 180 тисяч метрик. В базі даних використовуємо партиционирование, що полегшує очищення історичних даних. При цьому для кожної партіціі є свій індекс. Ну, ви розумієте, на що я натякаю :-)

Так, зараз мова піде про індекси. Ми з'ясували, що ряд індексів розростається майже в 2 рази, при наших обсягах вони займають 5-7 Gb для кожної партіціі. А за умови, що ми зберігаємо історичні дані за 10 днів і тренди за 3 місяці, сумарно виходить близько 70 Gb зайвих. При загальному обсязі БД близько 220 Гб, і використання 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 partitions ON.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 partitions ON.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 partitions ON.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 partitions ON.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



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

Спасибі за увагу!
Джерело: Хабрахабр

0 коментарів

Тільки зареєстровані та авторизовані користувачі можуть залишати коментарі.