Tuning PostgreSQL Database Parameters to Optimize Performance

Tuning PostgreSQL Database Parameters to Optimize Performance

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

По Ibrar Ahmed  базы данных с открытым исходным кодом , настройка производительности , PostgreSQL  производительность базы данных , параметры , PostgreSQL , настройка производительности PostgreSQL

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

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

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

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

shared_buffer

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

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

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

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

Check shared_buffer Value

Transact-SQL

1 2 3 4 5

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 операций сортировки   для всех пользователей. Установка этого параметра глобально может привести к очень высокому использованию памяти. Поэтому настоятельно рекомендуется изменить это на уровне сеанса.

work_mem = 2MB

Shell

1 2 3 4 5 6 7 8 9 10

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

Shell

1 2 3 4 5 6 7 8 9

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)

Стоимость запроса снижена до 360617,36 с 514431,86 - сокращение на 30%.

maintenance_work_mem

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

maintenance_work_mem = 10MB

Shell

1 2 3 4 5 6

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

Shell

1 2 3 4 5 6

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 на диск, прежде чем возвращать клиенту статус успеха. Это компромисс между производительностью и надежностью. Если ваше приложение разработано таким образом, что производительность важнее надежности, отключите синхронный_коммит . Это означает, что между состоянием успеха и гарантированной записью на диск будет временной промежуток. В случае сбоя сервера данные могут быть потеряны, даже если клиент получил сообщение об успешном завершении фиксации. В этом случае транзакция фиксируется очень быстро, потому что она не будет ожидать сброса файла WAL, но надежность будет поставлена ​​под угрозу.

checkpoint_timeout, checkpoint_completion_target

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

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

Заключение

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