Здоровье индексов в PostgreSQL глазами Java-разработчика

Привет.

Меня зовут Ваня, и я Java-разработчик. Так получилось, что я много работаю с PostgreSQL – занимаюсь настройкой БД, оптимизацией структуры, производительностью и немного играю в DBA по выходным.

За последнее время я привёл в порядок несколько баз данных в наших микросервисах и написал java-библиотеку pg-index-health, которая облегчает эту работу, экономит моё время и помогает избежать некоторых типовых ошибок, допускаемых разработчиками. Именно об этой библиотеке сегодня и пойдёт речь.

Disclaimer

Основная версия PostgreSQL, с которой я работаю, это 10-ка. Все используемые мною SQL-запросы также проверены на 11-й версии. Минимальная поддерживаемая версия — это 9.6.

Предыстория

Началось всё почти год назад со странной для меня ситуации: конкурентное создание индекса на ровном месте завершилось с ошибкой. Сам индекс, как водится, в невалидном состоянии остался в базе. Анализ логов показал нехватку temp_file_limit. И понеслось… Копнув поглубже, я обнаружил целый ворох проблем в конфигурации БД и, засучив рукава, с блеском в глазах принялся их чинить.

Проблема первая – дефолтная конфигурация

Наверное, метафора про Postgres, который можно запустить на кофеварке, всем уже изрядно надоела, но… конфигурация по умолчанию действительно вызывает ряд вопросов. Как минимум, стоит обратить внимание на maintenance_work_mem, temp_file_limit, statement_timeout и lock_timeout.

В нашем случае maintenance_work_mem был по умолчанию 64 Мбайта, а temp_file_limit что-то около 2 Гбайт – нам банально не хватало памяти для создания индекса на большой таблице.

Поэтому в pg-index-health я собрал ряд ключевых, на мой взгляд, параметров, которые стоит настроить под каждую БД.

Проблема вторая – дублирующиеся индексы

Наши базы живут на SSD дисках, и мы используем HA-конфигурацию с несколькими дата-центрами, мастер-хостом и n-ным количеством реплик. Место на диске – очень ценный ресурс для нас; оно не менее важно, чем производительность и потребление CPU. Поэтому, с одной стороны, нам нужны индексы для быстрого чтения, а с другой стороны, мы не хотим видеть в БД лишних индексов, так как они пожирают место и замедляют обновление данных.

И вот, восстановив все невалидные индексы и насмотревшись докладов Олега Бартунова, я решил устроить «великую» чистку. Оказалось, что разработчики не любят читать документацию к БД. Очень не любят. Из-за этого возникают две типовые ошибки – вручную созданный индекс на первичный ключ и аналогичный «ручной» индекс на уникальный столбец. Дело в том, что они не нужны – Postgres всё сделает сам. Такие индексы можно смело удалять, и для этого появилась диагностика duplicated_indexes.

Проблема третья – пересекающиеся индексы

Большинство начинающих разработчиков создаёт индексы на один столбец. Постепенно, как следует распробовав это дело на вкус, люди начинают оптимизировать свои запросы и добавлять более сложные индексы, включающие в себя несколько столбцов. Так появляются индексы на столбцы A, A+B, A+B+C и т.п. Первые два из этих индексов можно смело выкинуть, так как они являются префиксами третьего. Это тоже прилично экономит место на диске и для этого есть диагностика intersected_indexes.

Проблема четвертая – внешние ключи без индексов

Postgres позволяет создавать ограничения внешнего ключа без указания поддерживающего индекса. Во многих ситуациях это не является проблемой, и даже может себя никак не проявлять… До поры до времени…

Так было и у нас: просто в какой-то момент времени job’а, выполняющаяся по расписанию и очищающая базу от тестовых заказов, стала «складывать» нам мастер хост. CPU и IO улетали в полку, запросы тормозили и прерывались по таймауту, сервис пятисотил. Быстрый анализ pg_stat_activity показал, что зависали запросы вида:

delete from <table> where id in (…)

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

На помощь пришёл чудесный explain analyze и рассказал, что помимо удаления записей в целевой таблице, ещё идёт проверка ссылочной целостности, и на одной из связанных таблиц эта проверка сваливается в sequential scan из-за отсутствия подходящего индекса. Так родилась диагностика foreign_keys_without_index.

Проблема пятая – null value в индексах

По умолчанию Postgres включает null значения в btree-индексы, но они там, как правило, не нужны. Поэтому я усердно стараюсь эти null’ы выкидывать (диагностика indexes_with_null_values), создавая частичные индексы на nullable-столбцы по типу where <A> is not null. Таким способом мне удалось сократить размер одного из наших индексов с 1877 Мбайт до 16 Кбайт. А в одном из сервисов размер БД суммарно уменьшился на 16% (на 4.3 ГБайт в абсолютных цифрах) за счёт исключения null значений из индексов. Колоссальная экономия дискового пространства при весьма несложных доработках. 🙂

Проблема шестая – отсутствие первичных ключей

В силу особенностей механизма MVCC в Postgres’е возможна такая ситуация, как bloat, когда размер вашей таблицы быстро растёт за счёт большого количества мертвых записей. Я наивно полагал, что нам это не грозит, и что с нашей базой такого не случится, ведь мы, ого-го!!!, нормальные же разработчики… Каким глупым и наивным я был…

В один прекрасный день одна чудесная миграция взяла и обновила все записи в большой и активно использующейся таблице. Мы получили +100 Гбайт к размеру таблицы на ровном месте. Было чертовски обидно, но наши злоключения на этом не закончились. После того, как через 15 часов завершился автовакуум на этой таблице, стало понятно, что физическое место не вернётся. Остановить сервис и сделать VACUUM FULL мы не могли, поэтому было принято решение использовать pg_repack. И тут выяснилось, что pg_repack не умеет обрабатывать таблицы без первичного ключа или иного ограничения уникальности, а на нашей таблицы первичного ключа не оказалось. Так родилась диагностика tables_without_primary_key.

В версии библиотеки 0.1.5 добавилась возможность собирать данные по bloat’у таблиц и индексов и своевременно реагировать на него.

Проблемы семь и восемь – нехватка индексов и неиспользуемые индексы

Две следующие диагностики — tables_with_missing_indexes и unused_indexes – в своём финальном виде появились относительно недавно. Дело в том, что их нельзя было просто так взять и добавить.

Как я уже писал, мы используем конфигурацию с несколькими репликами, и читающая нагрузка на разных хостах принципиально разная. В итоге получается ситуация, что какие-то таблицы и индексы на каких-то хостах практически не используются, и для анализа нужно собирать статистику со всех хостов в кластере. Сбрасывать статистику тоже нужно на каждом хосте в кластере, нельзя сделать это только на мастере.

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

В качестве заключения

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

Часть диагностик могут выполняться уже в функциональных тестах сразу после наката миграций БД. И это, пожалуй, одна из самых мощных возможностей моей библиотеки. Пример использования можно посмотреть в демо.

Проверки на неиспользуемые или отсутствующие индексы, а также на bloat, имеет смысл выполнять только на реальной БД. Собранные значения могут быть записаны в ClickHouse или отправлены в систему мониторинга.

Я очень надеюсь, что pg-index-health будет полезна и востребована. Вы также можете поспособствовать развитию библиотеки, сообщая об обнаруженных проблемах и предлагая новые диагностики.

Источник: habr.com