Стратегии базовой отпимизация Postgresql (с бенчмарками)


Как уже неоднократно говорилось, Postgresql поставляется с настройками по умолчанию (после установки пакета) которые позволяют запустить СУБД на сервере с минимально возможной конфигурацией и естественно, что вам сразу после установки необходимо сконфигурировать Postgres к реалиям сервера на котором он будет работать.

Оптимизация производительности Postgresql сервера

Давайте будем измерять!

Мы с коллегой эту настройку называем "Три блатных аккорда на Postgres" и я сформировал небольшую инструкцию которую периодически дополняю практиками которые подсматриваю у коллег по цеху. Недавно мне пришла в голову мысль провести замеры того как эти изменения влияют на производительность не со слов пользователей "ну вроде побыстрее стало работать", а реально создать базу и замерить время выполнения каких-то базовых операций.

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

  • Создает тестовую базу на 5-ть гигабайт с тремя таблицами с парой миллионов записей в каждой
  • Строит индексы
  • Выполняет хитрый Update с условиями на числовых полях
  • Выполняет еще одну операцию Update но уже со строчными данными (меняет букву a на b в рандомных строках длиной 512 символов)
  • Выполняет несколько типовых запросов по условиям сравнения в разных таблицах

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

Чистый тест без какого либо тюнинга

Тест проводится на чистой базе данных Postgresql 11 установленной из пакета для Ubuntu 18.04.

TEST STARTED
=====================================
RECOVERY FROM DUMP
START
33:11-40sec
END
35:11-13sec
=====================================
BUILD INDEXES
START
35:11-13sec
END
35:11-42sec
=====================================
MAGIC UPDATE
START
35:11-42sec
END
37:11-03sec
=====================================
STRING UPDATES
START
37:11-03sec
END
40:11-29sec
=====================================
MAGIC SELECT
START
40:11-29sec
END
41:11-08se

Итого: 7 минут 28 секунд

Основные параметры оптимизации Postgresql

Основные параметры которые должны быть настроены для любой СУБД Postgresql:

shared_buffers = RAM/2..8
temp_buffers = 256MB
work_mem = RAM/32..64
maintenance_work_mem = RAM/16..32 или work_mem * 4
effective_cache_size = RAM - shared_buffers

Если сервер выполняет еще какие-то операции помимо того, что является сервером СУБД то исходите из того, что RAM это минимально доступный объем свободной оперативной памяти. В моем случае я исходил из того, что памяти у меня 8GB.

Результаты тестирования после выполнения базовых настроек:

TEST STARTED
=====================================
RECOVERY FROM DUMP
START
21:12-58sec
END
23:12-44sec
=====================================
BUILD INDEXES
START
23:12-44sec
END
23:12-58sec
=====================================
MAGIC UPDATE
START
23:12-58sec
END
25:12-54sec
=====================================
STRING UPDATES
START
25:12-54sec
END
27:12-09sec
=====================================
MAGIC SELECT
START
27:12-09sec
END
27:12-28sec

Итак, пробежимся по результатам и попробуем их интерпретировать их:

  • Операция восстановления из дампа занимала 1 мин 33 секунды, а стала занимать 1 мин 46 сек, что довольно интересно ведь я думал, что мы банально упремся в производительность дискового накопителя и результат никак не должен был оказаться хуже исходного!
  • Построение индексов занимало 29 секунд, а стало 14 секунд и эа такое более чем двукратное ускорение я готов смириться, с потерей производительности на этапе восстановления из бэкапа.
  • Волшебный Update в свою очередь укладывался в 1 минуту 21 секунду, а стал занимать 1 минуту 56 секунд и здесь мы так же видим падение производительности
  • Строковые операции были выполнены без оптимизаций за 2 минуты 26 секунд, а после базового тюнинга 1 минуту 15 сек и опять мы видим двукратное ускорение!
  • Операции выборки из базы были выполнены за 39 секунд, а теперь они занимают 21 секунду, что так же довольно много

В итоге, я говорю, что эти параметры настраивать обязательно!

Итого: 5 минут 30 секунд

Копнем немного глубже

Если в первом случае мы рассматривали параметры напрямую связанные с оперативной памятью и кэшированием, то теперь поговорим о дисковой подсистеме.

effective_io_concurrency = 2 - сколько конкурирующих операций ввода вывода может выполняться одновременно большие значения имеет смысл задавать только при использовании SSD-дисков, для одиночного жесткого диска имеет смысл выставить в единицу, для дискового массива 2 и более (для хорошего северного жесткого диска можно выставить 2).

random_page_cost =2 - стоимость чтения рандомной страницы (по-умолчанию 4). Чем меньше время поиска дисковой системы тем меньше. Примерные значения: 1.5-2.0 для RAID, 1.1-1.3 для SSD и аналогично предыдущему параметру в моем случае имеет смысл выбрать 2.

bgwriter_delay = 60ms - время сна между циклами записи на диск фонового процесса записи. Данный процесс ответственен за синхронизацию страниц, расположенных в shared_buffers с диском. Слишком большое значение этого параметра приведет к возрастанию нагрузки на checkpoint процесс и процессы, обслуживающие сессии (backend), а малое значение приведет к полной загрузке одного из ядер.

bgwriter_lru_multiplier = 4 - количество грязных буферов записанных в каждой операции записи, основано на количестве новых буферов, которые были необходимы серверным процессам во время последних операций записи (значение по умолчанию 2). Большие значения обеспечивают некоторую защиту от всплесков операций записи.

bgwriter_lru_maxpages = 400 - максимальное число буферов которые могут быть записаны за один раунд работы bg_writer

Настроили и запускаем тест:

TEST STARTED
=====================================
RECOVERY FROM DUMP
START
24:13-42sec
END
25:13-56sec
=====================================
BUILD INDEXES
START
25:13-56sec
END
26:13-04sec
=====================================
MAGIC UPDATE
START
26:13-04sec
END
27:13-30sec
=====================================
STRING UPDATES
START
27:13-30sec
END
28:13-54sec
=====================================
MAGIC SELECT
START
28:13-54sec
END
29:13-20sec

Тест прошел и давайте интерпретировать, что у нас получилось:

  • Восстановление из бэкапа теперь занимает 1 минуту 14 секунд и теперь я можно сказать успокоился, теперь восстанавливается быстрее чем без тюнинга (нивелировали первоначальное нештатное увеличение времени выполнения)
  • Построение индексов занимает 8 секунд, а как вы помните изначально было 29 при первом тюнинге скинутые до 14-и
  • Волшебный Update 1 минута 26 секунд (без оптимизации 1 минуту 21 секунду, после первой итерации тюнинга стал занимать 1 минуту 56 секунд) и мы вышли на +/- исходные значения
  • Update строк теперь занимает 1 минуту 26 секунд (больше чем на первой оптимизации на 11 секунд)
  • По операциям выборки 26 секунд против 21-ой на пером этапе

Итого: 4 минуты 38 секунд

Итоговый выигрыш практически минута при некотором  незначительном падении ряда показателей!

Подведем итоги

Всеми приведенными выше оптимизациями я добился снижения времени выполнения всех тестов с 7-и минут 28 секунд до 4 минут 38 секунд, итого почти 3 минуты из 7 с половиной, а это на минуточку около 40%. Так что, не спешите покупать SSD, возможно у вашего сервера баз данных еще есть запас производительности.

И в дополнение немного про параметр bgwriter_delay который я рекомендовал выставить в 60ms. При увеличении его до 400ms мы выигрываем в общем времени выполнения теста еще 12 секунд, при этом мы еще и снижаем нагрузку на процессор. Это было выяснено уже после всех тестов, поэтому я не буду переписывать всю статью, а лишь скажу что все же лучше выбирать 400ms.

Статистика нагрузки на процессор при 60ms:

Нагрузка на процессор при bg_writer 60ms

И при 400ms:

Нагрузка на процессор при bg_writer 400ms