Дэйв Пэйдж. Настройка max_wal_size в PostgreSQL

Это перевод статьи: Dave Page. Tuning max_wal_size in PostgreSQL

Содержание

Введение

Моя текущая задача на работе - автоматическая настройка PostgreSQL, в которой используется много тестов производительности и имеющиеся знания о ручной настройке PostgreSQL.

Одна из главных неожиданностей заключается в эффекте от настройки параметра max_wal_size. Мы провели тесты TPROC-C из пакета HammerDB, которые показали, что на системах с магнитными дисками для оптимизации производительности правильная настройка этого параметра имеет второе значение по важности после правильного подбора shared_buffers. Настройка shared_buffers показывает увеличение производительности в 3.5 раза по сравнению с ненастроенным значением, а настройка max_wal_size даёт увеличение ещё примерно в 1.5 раза на нашем оборудовании. В одном из тестов на эфемерном хранилище в облаке после настройки max_wal_size мы зафиксировали увеличение производительности даже в 10 раз!

Небольшое пояснение

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

Когда в PostgreSQL происходят изменения в данных, они записываются в журнал опережающей записи (Write Ahead Log - WAL), и только когда они попали на диск, транзакцию можно считать надёжной - так обеспечивается буква D в свойствах ACID. На этом этапе сервер информирует клиента о том, что транзакция подтверждена. Некоторое время спустя будет создана контрольная точка и изменения из журнала попадут в саму базу данных, также называемую кучей.

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

Итак, предположим, что WAL не используется для репликации или задач доставки журнала. Но и в этом случае всегда нужно иметь достаточное количество сегментов WAL, чтобы записывать все изменения с момента создания последней контрольной точки. На самом же деле обычно хранится 2-3 контрольных точки, но сейчас это не имеет значения.

Что такое max_wal_size?

Параметр max_wal_size говорит PostgreSQL, каким должен быть примерный максимальный размер сегментов, хранящихся в WAL. Как только доступное место закончится, произойдёт создание контрольной точки по запросу, после чего место можно будет использовать повторно.

На правильно настроенной системе основная масса контрольных точек должна создаваться по истечении времени (на основании параметра checkpoint_timeout), а не по запросу. Это позволяет создавать контрольные точки периодически и предсказуемо по времени, благодаря чему нагрузка распределяется на всё время работы системы. Контрольные точки, создаваемые по запросу, непредсказуемы и поэтому могут вызывать перепады производительности, добавляя дополнительную нагрузку в моменты, когда она не ожидается. Это особенно тяжело для систем с медленной подсистемой ввода-вывода, таких как магнитные диски или виртуальные машины с ограничением по количеству операций ввода-вывода в секунду.

Настройка

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

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

Важно отметить, что чем больше WAL генерируется между контрольными точками, тем больше становится время восстановления после аварий, поскольку требуется больше операций ввода-вывода для создания каждой контрольной точки. Смягчить последствия можно путём уменьшения значения checkpoint_timeout (обычно мы рекомендуем начать с 15 минут), так чтобы создание контрольных точек по времени происходило чаще. Также стоит убедиться, что значение checkpoint_completion_target равно 0,9, чтобы растянуть создание контрольной точки на как можно более длинный интервал времени. С другой стороны, если включен режим full_page_writes, что должно быть так, то страница после первого изменения записывается в WAL целиком и при создании контрольной точки страница тоже записывается целиком вне зависимости от того, как много на самом деле в ней изменилось, а это явно требует больше операций ввода-вывода, поэтому нам не нужно, чтобы контрольные точки создавались слишком часто!

Мониторинг

В PostgreSQL есть представление под названием pg_stat_bgwriter, которое можно использовать для мониторинга количества контрольных точек по запросу и периодических контрольных точек (столбцы checkpoints_req и checkpoints_timed). Очень важно отслеживать эти значения и доступное место на разделе с WAL, чтобы быть уверенным, что количество контрольных точек по запросу и периодических контрольных точек поддерживается на оптимальном уровне при заданном объёме доступного места на диске.

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

tprocc=# SELECT checkpoints_timed, checkpoints_req FROM pg_stat_bgwriter;
 checkpoints_timed | checkpoints_req 
-------------------+-----------------
                 0 |             188
(1 row)

Заключение

Параметр конфигурации PostgreSQL max_wal_size может значительно влиять на производительность изменений в базе данных. Его настройка зависит от специфики системы и нагрузки, и требует поиска оптимального баланса между доступным местом на диске для хранения WAL и достижением максимального соотношения периодических контрольных точек и контрольных точек по запросу.