Пути более эффективного использования PostgreSQL

Прочитав статью Базы данных в онлайн играх ^[1] и особенно комменты к ней, я в очередной расстроился от мысли, что многие разработчики меняют БД в своём проекте, пытаясь этой сменой решить свои проблемы, не исчерпав, однако, всех возможностей, предоставляемой заменяемой БД. Я принимаю участие в работе над проектом, БД которого характеризуется:

• Количеством транзакций порядка 5'000 — 10'000 в секунду
• Объемом примерно в 100ГБ (который бодро растёт)
• Примерно равным количеством операций на чтение/запись
• Преимущественно мелкими транзакциями

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

Я опишу некоторые проблемы, с которыми мы сталкиваемся, и применённые решения. Если кто-то сочтёт это полезным — ради Бога. Если кто-то поправит — я буду только рад обнаружить уязвимость в используемых подходах и увеличить эффективность работы БД. Описанное может быть применено без вмешательства в логику приложения, исключительно путём модификации схемы БД.

Речь пойдёт о PostgreSQL 9.2 (9.3 еще не щупали). И, да, здесь не будет админских советов по оптимальной настройке конфигов — я бы сам рад был узнать, как правильно их точить.

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

Большие размеры таблиц и индексов

Не секрет, что чем интенсивнее производится запись в БД, тем быстрее уменьшается в среднем скорость выборок и база начинает «тупить». Связано это напрямую с транзакционной сущностью БД. Поскольку строки (кортежи) имеют версионность, при изменении значения в некой строке это значение нельзя просто так взять и записать вместо предыдущего. PostgreSQL вместо этого дублирует строку и её новый, изменённый вариант записывает отдельно. Т.е., грубо, если взять таблицу в 1ГБ и единой операцией сделать UPDATE любому полю, то таблица «внезапно» увеличится в размере до 2ГБ. То же самое примерно происходит и с индексами. Увеличение размеров БД сразу сказывается и на скорости выполнения запросов.

Разумеется, регулярно будет работать AUTOVACUUM (если настроен), ну или можно запускать руками VACUUM по мере необходимости.

Что можно сделать для смягчения проблемы?

• Использовать fill factor. Этот параметр определяет степень заполненности страниц (блоков данных) таблиц на диске и то, сколько нужно оставить свободного места в блоках для записи туда изменённых версий кортежей. Наш опыт (обновления, в основном, по одной записи, но много) показывает, что для получения заметного положительного эффекта достаточно значения 90% (по умолчанию он равен 100%, т.е. таблица максимально плотно упакована). Это означает, что таблица будет заведомо больше как минимум примерно на 10%, зато при апдейте строк их новая версия может быть записана не абы куда, а в тот блок, в котором и находилась, что и быстрее, и компактнее. Документация упоминает, что для таблиц, в которых записи не изменяются никогда (или крайне редко), смысла менять fill factor нет, что есть правильно. Кстати, для индексов fill factor уже по умолчанию равен 90.
• Использовать частичные индексы (partial indexes). Зачастую может оказаться, что вас в выборках интересуют только записи с определенным значением булевого поля или определённого значения перечисления (enum). Например, вы в базе организовали хранение тасков для выполнения фоновых задач (что, правда, не лучшая идея, но уже по другим причинам) и чаще всего выбираете оттуда таски со статусам «new». Так зачем индексировать всю таблицу, если фактически выборка производится из ничтожно малого количества записей (явно новых тасков будет сильно меньше, чем уже завершённых)? Установка ограничения на индекс уменьшит его размеры в разы, если не на порядки.
• Использовать функциональные индексы (functional indexes). Функциональные индексы — мощный инструмент, который вообще может позволить избежать создания некоторых колонок в таблице. Например, есть в таблице поле типа timestamp с датой и временем. И нужно часто делать выборки, положим, с выборкой данных по часам. Можно выполнять поиск прямо по колонке timestamp (и повесить на неё индекс). Можно создать отдельную колонку, куда по триггеру или руками сохранять отдельно значение часа. А можно создать функциональный индекс, который будет содержать только значения часов и строиться на основе уже имеющейся колонки timestamp. В результате и колонок лишних не создали, и индекс эффективный есть.

Медленные выборки

• Не теряют своей актуальности два предыдущих пункта — использование частичных и функциональных индексов.
• Кластеризация (CLUSTER ^[2]). Полезная тема, если вы знаете типовые варианты обращения к своим объектам. В процессе кластеризации таблиц по указанному индексу PostgreSQL распределяет данные таблицы на жёстком диске упорядоченно, в соответствии с индексом. К примеру, есть таблица со списком квартир, привязанных к домам. Можно создать индекс по полю «id дома» и произвести по нему кластеризацию, в результате записи о квартирах будут физически сгруппированы по домам и при запросах вида «верни мне все квартиры дома №777» базе уже не потребуется собирать квартиры по всей таблице. Они будут храниться компактно. Правда, кластеризацию надо регулярно повторять. В процессе AUTOVACUUM она не производится.

Превышение счётчика транзакций

Работа механизмов MVCC была бы невозможна без существования счётчика транзакций. Загадка, почему до сих пор счётчик этот 32-х битный, но имеем то, что имеем — через каждые 2 миллиарда с хвостом транзакций счётчику полагается обнулиться. А для того, чтобы не произошло непоправимое, к этому моменту должны обнулиться номера транзакций у всех строк в БД. Точнее, этим строкам ставится в соответствие некий зарезервированный FrozenXID. Что характерно, AUTOVACUUM и ручной VACUUM не занимаются простановкой FrozenXID и для нормальной работы БД при достижении счётчиком транзакций определённого конфигом значения запускается автовакуум с красивым комментом «to prevent wraparound». При больших таблицах (в десятки ГБ) этот процесс может занять томительные часы, в течение которых таблица будет недоступна ни для чтения, ни для записи.

Сделать тут что-либо, не задевая логику и архитектуру приложения, не представляется возможным (мне). Надо либо уменьшать количество транзакций, укрупняя их (что может обернуться другими проблемами и вообще неэффективно), либо разбивать БД на куски, что тоже на оперативный способ решения проблемы не тянет.

Длинные транзакции

Это достаточно очевидно, но на всякий случай упомяну.

Речь идёт не о длинных транзакциях как таковых, а о ситуациях, когда приложение из-за бага не закрыло транзакцию и ушло делать свои дела дальше (к примеру, фоновые обработчики задач, способные висеть сутками). В списке активных процессов PostgreSQL такое соединение будет висеть со статусом «idle in transaction». Регулярно отрабатывающий VACUUM освобождает место, занятое старыми версиями строк (оставшимися после редактирования или удаления), ориентируясь по номеру транзакции, в которой было произведено удаление или редактирование. Грубо говоря, если были строки, удалённые транзакциями 1 и 2, а минимальный номер среди текущих открытых транзакций — 3, то эти две строки можно удалить «по-настоящему», т.к. они не попадают в область видимости любой текущей открытой транзакции. В случае подвисшей транзакции VACUUM не сможет удалить ни одну строку, отредактированную после запуска этой транзакции, в результате чего база за несколько часов может чудовищно вырасти.

Совет тут один — мониторить текущие процессы и отлавливать негодяев с «idle in transaction».

Длительные блокирующие бекапы

Репликации, при всей своей привлекательности, не спасают от ситуаций, когда база была побита, к примеру, кривым патчем. Сделали патч по какой-нибудь там перелинковке объектов, всё добро среплицировалось — и привет. Поэтому бекапы, как ни крути, нужны. Большая база бекапится у нас около часа. Практически всё это время проект бездействует. Тут, откровенно говоря, посоветовать ничего не могу. Наоборот, буду рад выслушать, кто и как с ситуацией справляется. Единственная мысль — бить на шарды и бекапить кусками, думая, как при этом сохранить консистентность данных.

В версии 9.3 произведена заметная работа над представлениями, которые тоже могут позволить оптимизировать работу с БД, но это уже отдельная тема.

Автор: TerminusMKB

Источник ^[3]