Настройка параметров PostgreSQL для оптимизации производительности

08.07.19

База данных - HighLoad оптимизация

Предлагаю вашему вниманию перевод статьи Ibrar Ahmed "Tuning PostgreSQL Database Parameters to Optimize Performance". Оригинал доступен по ссылке https://www.percona.com/blog/2018/08/31/tuning-postgresql-database-parameters-to-optimize-performance/

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

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

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

Настраиваемые параметры PostgreSQL


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

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

Рекомендуемое значение составляет 25% от общего объема оперативной памяти компьютера. Вам следует попробовать некоторые более низкие и более высокие значения, потому что в некоторых случаях можно получить хорошую производительность с настройкой более 25%. Но реальная конфигурация зависит от вашей машины и рабочего набора данных. Если ваш рабочий набор данных может легко поместиться в вашу оперативную память, вы можете увеличить значение shared_buffer, чтобы оно содержало всю вашу базу данных и чтобы весь рабочий набор данных мог находиться в кеше. Тем не менее, вы, очевидно, не хотите резервировать всю оперативную память для PostgreSQL.

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

Проверка значения shared_buffer

testdb=# SHOW shared_buffers;
shared_buffers
----------------
128MB
(1 row)

Примечание: Будьте осторожны, так как некоторые ядра не поддерживают большее значение, особенно в Windows.
 

wal_buffers


PostgreSQL сначала записывает записи в WAL (журнал предзаписи) в буферы, а затем эти буферы сбрасываются на диск. Размер буфера по умолчанию, определенный wal_buffers, составляет 16 МБ. Но если у вас много одновременных подключений, то более высокое значение может повысить производительность.
 

effective_cache_size


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

work_mem


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

work_mem * total sort operations

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

work_mem = 2MB

testdb=# SET work_mem TO "2MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=509181.84..1706542.14 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=508181.79..514431.86 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)
(5 rows)

Первоначальный узел сортировки запроса оценивается в 514431,86. Стоимость — это произвольная вычисляемая единица. Для приведенного выше запроса у нас work_mem всего 2 МБ. В целях тестирования давайте увеличим это значение до 256 МБ и посмотрим, повлияет ли это на стоимость.

work_mem = 256MB

testdb=# SET work_mem TO "256MB";
testdb=# EXPLAIN SELECT * FROM bar ORDER BY bar.b;
                                    QUERY PLAN                                     
-----------------------------------------------------------------------------------
Gather Merge  (cost=355367.34..1552727.64 rows=10000116 width=24)
   Workers Planned: 4
   ->  Sort  (cost=354367.29..360617.36 rows=2500029 width=24)
         Sort Key: b
         ->  Parallel Seq Scan on bar  (cost=0.00..88695.29 rows=2500029 width=24)


Стоимость запроса снижена с 514431,86 до 360617,36, то есть уменьшилась на 30%.
 

maintenance_work_mem


maintenance_work_mem — это параметр памяти, используемый для задач обслуживания. Значение по умолчанию составляет 64 МБ. Установка большого значения помогает в таких задачах, как VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY и ALTER TABLE.

maintenance_work_mem = 10MB

postgres=# CHECKPOINT;
postgres=# SET maintenance_work_mem to '10MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 170091.371 ms (02:50.091)


maintenance_work_mem = 256MB

postgres=# CHECKPOINT;
postgres=# set maintenance_work_mem to '256MB';

postgres=# CREATE INDEX foo_idx ON foo (c);
CREATE INDEX
Time: 111274.903 ms (01:51.275)


Время создания индекса составляет 170091,371 мс, если для параметра maintenance_work_mem установлено значение только 10 МБ, но оно уменьшается до 111274,903 мс, когда мы увеличиваем значение параметра maintenance_work_mem до 256 МБ.
 

synchronous_commit


Используется для обеспечения того, что фиксация транзакции будет ожидать записи WAL на диск, прежде чем вернуть клиенту статус успешного завершения. Это компромисс между производительностью и надежностью. Если ваше приложение разработано таким образом, что производительность важнее надежности, отключите synchronous_commit. В этом случае транзакция фиксируется очень быстро, потому что она не будет ожидать сброса файла WAL, но надежность будет поставлена R03;R03;под угрозу. В случае сбоя сервера данные могут быть потеряны, даже если клиент получил сообщение об успешном завершении фиксации транзакции.
 

checkpoint_timeout, checkpoint_completion_target


PostgreSQL записывает изменения в WAL. Процесс контрольной точки сбрасывает данные в файлы. Это действие выполняется, когда возникает контрольная точка (CHECKPOINT). Это дорогостоящая операция и может вызвать огромное количество операций IO. Весь этот процесс включает в себя дорогостоящие операции чтения/записи на диск. Пользователи могут всегда запустить задание контрольной точки (CHECKPOINT), когда это необходимо, или автоматизировать запуск с помощью параметров checkpoint_timeout и checkpoint_completion_target.

Параметр checkpoint_timeout используется для установки времени между контрольными точками WAL. Установка слишком низкого значения уменьшает время восстановления после сбоя, поскольку на диск записывается больше данных, но это также снижает производительность, поскольку каждая контрольная точка в конечном итоге потребляет ценные системные ресурсы. checkpoint_completion_target — это доля времени между контрольными точками для завершения контрольной точки. Высокая частота контрольных точек может повлиять на производительность. Для плавного выполнения задания контрольной точки, checkpoint_timeout должен иметь низкое значение. В противном случае ОС будет накапливать все грязные страницы до тех пор, пока соотношение не будет соблюдено, а затем производить большой сброс.
 

Заключение


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

 

Источник Хабр

postgresql perfomance optimization

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2993    spyke    27    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5117    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7649    158    ZAOSTG    68    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5985    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8873    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5105    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16190    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Fox-trot 156 08.07.19 12:26 Сейчас в теме
а затем производить на большой сброс

имхо звучит не совсем по-русски
kser87; w.r.; +2 Ответить
2. w.r. 643 08.07.19 12:32 Сейчас в теме
(1) Исправил. Убрал предлог на
3. capitan 2466 08.07.19 13:14 Сейчас в теме
Все это конечно круто, но первой строкой перед любой оптимизацией должно идти для какой системы эта оптимизация происходит. Postgres для веб сервера совсем другие настройки имеет чем Postgres для OLTP и совсем другие для OLAP как в принципе и другая СУБД.
Так как это перевод то возможно в том контексте откуда он взят это и было понятно.
По хорошему настройка на pgtune.leopard.in.ua закроет 80% для начала ,а потом когда база вырастет нужен конкретный тюнинг
И кстати Postgres Pro для 1С сейчас приходит с настройками которые очень даже приличные.
Сравнение есть но пиариться не буду )
Дмитрий74Чел; w.r.; +2 Ответить
4. Fox-trot 156 08.07.19 13:28 Сейчас в теме
(3) почитал оригинал, но там ни слова про веб или сценарии использования
а так то да можно писать не останавливаясь, вот только надо ли нам лишняя вода
5. cleaner_it 220 13.07.19 15:43 Сейчас в теме
(3)
И кстати Postgres Pro для 1С сейчас приходит с настройками которые очень даже приличные


Postgres Pro стоит весьма прилично) 107 800 для версии Standart, на одно виртуальное или аппаратное ядро
6. capitan 2466 13.07.19 20:54 Сейчас в теме
(5)Тема уже мусолена перемусолена.
Postgres Pro для 1С бесплатный всегда был есть и будет
7. Fox-trot 156 13.07.19 22:24 Сейчас в теме
(6) про жабу тож такое говорили, а оно вона как
8. cleaner_it 220 14.07.19 11:07 Сейчас в теме
(6) На прошлой неделе отправлял запрос через форму обратной связи
Прикрепленные файлы:
9. capitan 2466 14.07.19 11:27 Сейчас в теме
(8)Как спрашивали, такой ответ и получили.

Вот ответ для инфостарт:

«Postgres Pro для 1С» - это ошибочное название на сайте 1С. Такой версии нет и не было. Мы собирали PostgreSQL с патчами 1С, который распространялся свободно.

У нас имеются разные варианты СУБД Postgres Pro которые совместимы с 1С, но они все платные.

Сейчас новых версий PostgreSQL для 1С не делаем. Однако потребность в нем видимо очень высока, поскольку после приостановки его изготовления мы получили массу отзывов.

Поэтому сейчас решаем что дальше делать с этой версией. Думаю в течение недели все определиться
10. cleaner_it 220 18.07.19 08:08 Сейчас в теме
(9)
«Postgres Pro для 1С»
про эту версию вопросов и не было - её не существует. Вопрос был про Postgres Pro Standart
11. capitan 2466 18.07.19 09:32 Сейчас в теме
(10)Postgres Pro Standart для 1С не подходит
12. cleaner_it 220 18.07.19 12:05 Сейчас в теме
(11) С чего бы вдруг?) Сначала попробуйте, всё подходит. Даже из этой фразы "У нас имеются разные варианты СУБД Postgres Pro которые совместимы с 1С" следует обратное.
Fox-trot; +1 Ответить
13. a.doroshkevich 1407 19.07.19 07:20 Сейчас в теме
Сейчас из готовых сборок есть 3 версии для 1С:
1. С сайта 1С - бесплатно, но нельзя использовать тем кто под импортозамещением
2. С сайта postgrepro.ru, только эти сборки можно использовать при импортозамещении:
2.1. Postgres Pro Standart - платно, но можно скачать и использовать для целей тестирования
2.2. Postgres Pro Enterprise - платно, содержит улучшения и фишки от PostgrePro


3. Команда PostgresPro обещает в ближайшее время выложить свою полностью бесплатную сборку (скорее всего на другом ресурсе, чтобы не имелось разночтение с реестром минсвязи). Ждём
cleaner_it; +1 Ответить
14. starik-2005 3033 22.05.20 14:05 Сейчас в теме
15. w.r. 643 22.05.20 15:18 Сейчас в теме
(14) что за сайт? Там максимальная сборка постгри всего 9.6.17. У 1с официально доступна на портале юзерс уже стабильная 11.5.19
16. starik-2005 3033 22.05.20 15:52 Сейчас в теме
(15)
Там максимальная сборка постгри всего 9.6.17
У меня выбирается 12.2 последняя, так что не знаю, куда Вы там жмете...
17. w.r. 643 22.05.20 16:05 Сейчас в теме
(16) я выбираю разрядность x86_64 ОС Ubuntu 16.04
18. starik-2005 3033 22.05.20 16:24 Сейчас в теме
(17) да, у них странно названо. Выберите amd64 - это обычная линуксовая версия для 64-битных ОСей. В принципе x86_64 - это вообще бред какой-то.
19. starik-2005 3033 22.05.20 19:23 Сейчас в теме
x86-64 (также AMD64/Intel64/EM64T) — 64-битное расширение, набор команд для архитектуры x86, разработанное компанией AMD и представленное в 2000 году[1], позволяющее выполнять программы в 64-разрядном режиме.

Это расширение архитектуры x86 с почти полной обратной совместимостью.

Корпорации Microsoft и Oracle используют для обозначения этого набора инструкций термин «x64», однако каталог с файлами для архитектуры в 64-разрядных Microsoft Windows называется «amd64» («i386» для архитектуры x86).
Оставьте свое сообщение