Устранение узких мест в PostgreSQL при высоких нагрузках

Автор оригинала: Robert Bernier
  • Перевод
Всем привет. Прямо сейчас в OTUS открыт набор на новый поток курса «PostgreSQL». В связи с этим, мы традиционно подготовили для вас перевод полезного материала по теме.





Взяв за основу статью Петра Зайцева об узких местах в производительности MySQL (MySQL Performance Bottlenecks), я хочу немного рассказать о PostgreSQL.

В наши дни для работы с PostgreSQL часто используются ORM-фреймворки. Обычно они работают хорошо, но со временем нагрузка увеличивается и возникает необходимость тюнить сервер базы данных. Каким бы надежным ни был PostgreSQL, но и он может тормозить при увеличении трафика.

Есть много способов устранения узких мест в производительности, но в этой статье мы обратим внимание на следующее:

  • Параметры сервера
  • Управление подключениями
  • Настройка автоочистки (autovacuum)
  • Дополнительная настройка автоочистки
  • Раздувание таблиц (bloat)
  • «Горячие точки» в данных
  • Сервера приложений
  • Репликация
  • Серверное окружение

О «категориях» и «потенциальном влиянии»


«Сложность» говорит о том насколько просто реализовать предлагаемое решение. А «потенциальное влияние» дает представление о степени улучшений в производительности системы. Однако в силу возраста системы, ее типа, технического долга и т.п. точное описание сложности и влияния может быть проблематичным. В конце концов, в сложных ситуациях окончательный выбор всегда за вами.

Категории:

  • Сложность
    • Низкая
    • Средняя
    • Высокая
    • Низкая-средняя-высокая
  • Потенциальное влияние

    • Низкое
    • Среднее
    • Высокое
    • Низкое-среднее-высокое

Параметры сервера


Сложность: низкая.
Потенциальное влияние: высокое.

Еще не так давно были времена, когда актуальные версии postgres могли работать на i386. С тех пор настройки по умолчанию были изменены, но они по-прежнему сконфигурированы на использование наименьшего количества ресурсов.

Эти параметры изменить очень просто и обычно они настраиваются при первоначальной установке. Неправильные значения этих параметров могут привести к высокой загрузке процессора и ввода-вывода:

  • Параметр effective_cache_size ~ от 50 до 75%
  • Параметр shared_buffers ~ 1/4 – 1/3 объема оперативной памяти
  • Параметр work_mem ~ 10МБ

Рекомендуемое значение effective_cache_size хотя и является типичным, но может быть посчитано более точно, если обратиться к «top» — free+cached.

Вычисление значения shared_buffers — интересная головоломка. На нее можно смотреть с двух сторон: если у вас небольшая база данных, то можно установить значение shared_buffers достаточно большим, чтобы вся база данных поместилась в оперативной памяти. С другой стороны, можно настроить загрузку в память только часто используемых таблиц и индексов (вспоминайте правило 80/20). Раньше рекомендовалось устанавливать значение в 1/3 объема оперативной памяти, но со временем, так как объемы памяти росли, оно было уменьшено до 1/4. Если памяти выделено мало, то будет увеличиваться ввод-вывод и нагрузка на процессор. О слишком большом выделении памяти будет свидетельствовать выход на плато загрузки процессора и ввода-вывода.



Еще один фактор, который следует учитывать — это кэш ОС. При достаточном объеме оперативной памяти Linux будет кэшировать таблицы и индексы в памяти и, в зависимости от настроек, может заставить PostgreSQL поверить в то, что он читает данные с диска, а не из оперативной памяти. Одна и та же страница находится и в буфере postgres и в кэше ОС, и это одна из причин не делать shared_buffers очень большим. С помощью расширения pg_buffercache можно посмотреть использование кэша в реальном времени.

Параметр work_mem задает объем памяти, используемый для операций сортировки. Установка слишком низкого значения гарантирует плохую производительность, так как сортировка будет выполняться с использованием временных файлов на диске. С другой стороны, хотя установка большого значения и не влияет на производительность, но при большом количестве подключений есть риск нехватки оперативной памяти. Проанализировав память, используемую всеми запросами и сеансами, можно посчитать необходимое значение.

Используя EXPLAIN ANALYZE можно увидеть, как выполняются операции сортировки, и, изменяя значение для сеанса, определить момент, когда начинается слив на диск.

Можно также использовать бенчмарки системы.

Управление подключениями


Сложность: низкая.
Потенциальное влияние: низкое-среднее-высокое

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

Простое решение — увеличить максимальное количество одновременных подключений:

# postgresql.conf: default is set to 100<br />max_connections



Но более эффективный подход — пул соединений. Существует множество решений, но наиболее популярное — pgbouncer. PgBouncer может управлять соединениями, используя один из трёх режимов:

  • Пул сеансов (session pooling). Наиболее корректный подход. При подключении клиента ему выдается соединение и остается за ним, пока он не отключится. Когда клиент отключается, подключение возвращается в пул. Это метод по умолчанию.
  • Пул транзакций (transaction pooling). Подключение назначается клиенту только на время транзакции. Когда PgBouncer замечает, что транзакция завершена, подключение возвращается в пул.
  • Пул операторов (statement pooling). Наиболее агрессивный метод. Подключение к серверу будет возвращаться в пул сразу после завершения запроса. Транзакции с несколькими операторами в этом режиме запрещены, так как они будут прерываться.

Также необходимо обратить внимание на Secure Socket Layer (SSL). При его включении соединения по умолчанию будут использовать SSL, что приведет к увеличению нагрузки на процессор по сравнению с незашифрованными подключениями. Для обычных клиентов можно настроить аутентификации по имени узла (host-based) без SSL (pg_hba.conf), а SSL использовать для выполнения административных задач или для потоковой репликации.

Настройка автоочистки (autovacuum)


Сложность: средняя.
Потенциальное влияние: низкое-среднее.

Управление параллельным доступом посредством многоверсионности (Multi-Version Concurrency Control) — один из основополагающих принципов, делающих PostgreSQL таким популярным решением среди СУБД. Однако одним из неприятных моментов является то, что для каждой измененной или удаленной записи создаются неиспользуемые копии, от которых в конечном итоге надо избавляться. Неправильно настроенный процесс автоочистки (autovacuum) может снижать производительность. При этом чем сервер загруженнее, тем сильнее проявляется проблема.

Для управления демоном автоочистки используются следующие параметры:

  • Параметр autovacuum_max_workers. При наличии большого количества огромных таблиц стоит увеличить количество одновременно работающих процессов автоочистки (по умолчанию три). В идеале должен быть один рабочий процесс на один процессор, но не больше количества процессоров. Слишком большое количество может увеличить нагрузку на процессор. Обычно берется значение между двумя этими числами. Это баланс между максимальной эффективностью автоочистки и общей производительностью системы.
  • Параметр maintenance_work_mem. Чем больше значение, тем эффективнее процесс очистки. Имейте в виду, что есть закон убывающей отдачи. Слишком большое значение в лучшем случае станет пустой тратой оперативной памяти, а в худшем может исчерпать всю доступную память.
  • Параметр autovacuum_freeze_max_age уменьшает вероятность TXID WRAPAROUND. Чем значение больше, тем реже он запускается, что снижает нагрузку на систему. Но, как и со всеми параметрами автоочистки, упомянутыми выше, есть нюанс. Если сделать задержку слишком большой, то и вы рискуете исчерпать txid, что приведет к принудительному завершению работы сервера в целях защиты целостности данных. Для определения правильного значения необходимо сопоставлять самый большой/старый txid с процессом автоочистки используя pg_stat_activity на предмет WRAPAROUND.

Остерегайтесь перегрузки оперативной памяти и процессора. Чем выше изначально заданное значение, тем больше риск исчерпания ресурсов при увеличении нагрузки на систему. Если установить слишком большие значения, то производительность может резко упасть при превышении определенного уровня нагрузки.

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

Дополнительная настройка автоочистки


Сложность: высокая.
Потенциальное влияние: высокое.

Этот метод, из-за его сложности, следует использовать только когда производительность системы уже на грани физических пределов хоста и это действительно стало проблемой.

Runtime-параметры автоочистки настраиваются в postgresql.conf. К сожалению, нет единого универсального решения, которое будет работать в любой высоконагруженной системе.

Параметры хранения для таблиц. Часто в базе данных значительная часть нагрузки ложится только на несколько таблиц. Настройка индивидуальных параметров автоочистки для таблицы — отличный способ не прибегать к ручному запуску VACUUM, который может существенно влиять на систему.

Настроить таблицы можно с помощью команды:

ALTER TABLE .. SET STORAGE_PARAMETER

Раздувание таблиц (bloat)


Сложность: низкая.
Потенциальное влияние: среднее-высокое.

Со временем производительность системы может ухудшаться из-за неправильных политик очистки, вследствие чрезмерного раздувания (bloat) таблиц. Так что даже настройка демона автоочистки и ручной запуск VACUUM не решает проблему. В этих случаях на помощь приходит расширение pg_repack.

С помощью расширения pg_repack можно перестроить и реорганизовать таблицы и индексы в условиях продакшена

«Горячие точки» в данных


Сложность: высокая.
Потенциальное влияние: низкое-среднее-высокое.

Как и в случае с MySQL, избавление PostgreSQL от «горячих точек» зависит от ваших потоков данных и может даже повлечь за собой изменение архитектуры системы.

В первую очередь следует обращать внимание на следующее:

  • Индексы. Убедитесь, что для столбцов, по которым осуществляется поиск есть индексы. Можно использовать системные каталоги и представления для мониторинга и проверки, что запросы используют индексы. Для анализа производительности запросов используйте расширения pg_stat_statement и pgbadger.
  • Heap Only Tuples (HOT). Индексов может быть и слишком много. Снизить потенциальное раздувание и уменьшить размер таблицы можно, удалив неиспользуемые индексы.
  • Секционирование таблиц. Ничто так не влияет на производительность, как огромная таблица, размер которой в несколько раз превышает средний размер других таблиц. Разбивка большой таблицы на более мелкие секции поможет повысить производительность запросов, например, при запросе данных, секционированных по дате. И так как таблица может обрабатываться только одним процессом автоочистки, то разбивка его на множество меньших таблиц позволяет более чем одному процессу автоочистки выполнять автоматическое удаление. Еще одно преимущество секционирования в том, что удаление большого количества строк намного эффективнее и быстрее, чем из единой огромной таблицы.
  • Параллельные запросы. Появились в последних версиях postgres. Теперь для выполнения одного запроса может использоваться несколько процессоров, тогда как раньше запрос обрабатывался только одним.
  • Денормализация. Можно повысить производительность, объединив столбцы из нескольких таблиц в одну таблицу. Повышение производительности достигается за счет увеличения избыточности данных. Тщательно обдумайте этот вариант, прежде чем его использовать!



Сервера приложений


Сложность: низкая.
Потенциальное влияние: высокое.

Избегайте запуска приложений (PHP, Java и Python) и postgres на одном хосте. Относитесь внимательно к приложениям на этих языках, так как они могут потреблять большие объемы оперативной памяти, особенно сборщик мусора, что влечет за собой конкуренцию с системами баз данных за ресурсы и снижение общей производительности.

Репликация


Сложность: низкая.
Потенциальное влияние: высокое.

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

Серверное окружение


И последнее, но не менее важное — это простое увеличение мощности хоста. Давайте рассмотрим, на что влияет каждый из ресурсов в плане производительности PostgreSQL:

  • Оперативная память. Чем больше больше, тем лучше. Большое количество ОЗУ позволяет выделить больше памяти для запросов и увеличить количество одновременных сеансов. Чем больше оперативной памяти, тем больше кэшируется база данных, оптимизируя ввод-вывод.
  • Процессор. Больше процессоров означает больше параллельных процессов, в том числе для очистки, подключений и т. д.
  • Жесткий диск. Увеличение размера и скорости.
    • увеличение возможного размера базы данных
    • улучшение общей производительности за счет более быстрого ввода-вывода, особенно для таких операций как сортировка слиянием, использующуя диск
  • Разбивка на разделы.

    • Использование нескольких разделов изолирует одновременно выполняемые операции. Например, можно разнести индексы и таблицы по разным разделам с разной производительностью.
    • Для временных сеансовых таблиц и таких операций как сортировка слиянием можно выделить отдельный высокоскоростной раздел или распределить их по несколькими разделами.
    • На отдельном разделе можно разместить логи. В случае нехватки места это не повлияет на СУБД.
    • WAL-логи, как и обычные логи, тоже можно разместить на отдельном разделе, так как они работают только в режиме записи. Если место на разделе закончится, что может произойти при трансляции журнала (log shipping) и разрыве соединения с резервным сервером, то таблицы не пострадают, так как они расположены в другом месте.






Читать ещё:


OTUS. Онлайн-образование
Цифровые навыки от ведущих экспертов

Комментарии 3

    +1
    WAL-логи

    таблицы не пострадают, так как они расположены в другом месте

    Просто для справки, без этих файлов база не поднимется сама после аварии. Более того, поднятие СУБД без WAL — исключительно сложная задача, получить консистентное состояние базы на какой-то момент, предшествующий аварии, практически невозможно.
      +1
      Уж лучше пусть не поднимется после аварии, чем не сможет сохранить состояние после исчерпания места на диске из-за WAL логов.

      Если WAL-лог лежит на другом диске и диск переполнился, то PostgreSQL можно корректно остановить, чтобы он скинул текущее состояние на диск с таблицами. Если WAL-лог лежит на диске с таблицами, то PostgreSQL некуда будет писать обновления по текущему состоянию в памяти.
        0
        Интерено читать «пусть не поднимется после аварии», ведь WAL как раз для того, чтобы база поднялась после аварии.
        Если способность подняться после аварии не имеет ценности, то WAL можно и на tmpfs держать, и fsync выключить. Но не делайте так (wal только на диске/ssd и fsync включён). Потому что без WAL шансы восстановить консистентные данные невелики.

        Против держания WAL на отдельном диске ничего не имею, мой комментарий лишь о том, что сохранность WAL так же важна, как и самих файлов данных.

    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

    Самое читаемое