Изменение этих параметров прямо не повлияет на скорость чтения, но может принести большую пользу, если данные в базе активно изменяются.
Уменьшение количества контрольных точек: checkpoint_segments
Если в базу заносятся большие объёмы данных, то контрольные точки могут происходить слишком часто1. При этом производительность упадёт из-за постоянного сбрасывания на диск данных из буфера.
Для увеличения интервала между контрольными точками нужно увеличить количество сегментов журнала транзакций (checkpoint_segments). Данный параметр определяет количество сегментов (каждый по 16 МБ) лога транзакций между контрольными точками. Этот параметр не имеет особого значения для базы данных, предназначенной преимущественно для чтения, но для баз данных со множеством транзакций увеличение этого параметра может оказаться жизненно необходимым. В зависимости от объема данных установите этот параметр в диапазоне от 12 до 256 сегментов и, если в логе появляются предупреждения (warning) о том, что контрольные точки происходят слишком часто, постепенно увеличивайте его. Место, требуемое на диске, вычисляется по формуле (checkpoint_segments * 2 + 1) * 16 МБ, так что убедитесь, что у вас достаточно свободного места. Например, если вы выставите значение 32, вам потребуется больше 1 ГБ дискового пространства.
Следует также отметить, что чем больше интервал между контрольными точками, тем дольше будут восстанавливаться данные по журналу транзакций после сбоя.
fsync, synchronous_commit и стоит ли их трогать
Наиболее радикальное из возможных решений выставить значение «off» параметру fsync. При этом записи в журнале транзакций не будут принудительно сбрасываться на диск, что даст большой прирост скорости записи. Учтите: вы жертвуете надёжностью, в случае сбоя целостность базы будет нарушена, и её придётся восстанавливать из резервной копии!
Использовать этот параметр рекомендуется лишь в том случае, если вы всецело доверяете своему «железу» и своему источнику бесперебойного питания. Ну или если данные в базе не представляют для вас особой ценности.
Параметр synchronous_commit определяет нужно ли ждать WAL записи на диск перед возвратом успешного завершения транзакции для подключенного клиента. По умолчанию и для безопасности данный параметр установлен в «on» (включен). При выключении данного параметра («off») может существовать задержка между моментом, когда клиенту будет сообщенно об успехе транзакции и когда та самая транзакция действительно гарантированно и безопастно записана на диск (максимальная задержка - wal_writer_delay * 3). В отличие от fsync, отключение этого параметра не создает риск краха базы данных: данные могут быть потеряны (последний набор транзакций), но базу данных не придется восстанавливать после сбоя из бэкапа. Так что synchronous_commit может быть полезной альтернативой, когда производительность важнее, чем точная уверенность в согласовании данных (данный режим можно назвать «режимом MongoDB»: изначально все клиенты для MongoDB не проверяли успешность записи данных в базу и за счет этого достигалась хорошая скорость для бенчмарков).
Прочие настройки
commit_delay (в микросекундах, 0 по умолчанию) и commit_siblings (5 по умолчанию) определяют задержку между попаданием записи в буфер журнала транзакций и сбросом её на диск. Если при успешном завершении транзакции активно не менее
commit_siblings транзакций, то запись будет задержана на время commit_delay. Если за это время завершится другая транзакция, то их изменения будут сброшены на диск вместе, при помощи одного системного вызова. Эти параметры позволят ускорить работу, если параллельно выполняется много «мелких» транзакций.
wal_sync_method Метод, который используется для принудительной записи данных на диск. Если fsync=off, то этот параметр не используется. Возможные значения:
open_datasync запись данных методом open() с параметром O_DSYNC;
fdatasync вызов метода fdatasync() после каждого commit;
fsync_writethrough вызов fsync() после каждого commit, игнорируя параллельные процессы;
fsync вызов fsync() после каждого commit;
open_sync запись данных методом open() с параметром O_SYNC;
Не все эти методы доступны на разных ОС. По умолчанию устанавливается первый, который доступен для системы.
full_page_writes Установите данный параметр в off, если fsync=off. Иначе, когда этот параметр on, PostgreSQL записывает содержимое каждой записи в журнал транзакций при первой модификации таблицы. Это необходимо, поскольку данные могут записаться лишь частично, если в ходе процесса «упала» ОС. Это приведет к тому, что на диске окажутся новые данные смешанные со старыми. Строкового уровня записи в журнал транзакций может быть недостаточно, чтобы полностью восстановить данные после «падения». full_page_writes гарантирует корректное восстановление, ценой увеличения записываемых данных в журнал транзакций (Единственный способ снижения объема записи в журнал транзакций заключается в увеличении checkpoint_interval).
wal_buffers Количество памяти используемое в SHARED MEMORY для ведения транзакционных логов2. Стоит увеличить буфер до 256512 кБ, что позволит лучше работать с большими транзакциями. Например, при доступной памяти 14 ГБ рекомендуется устанавливать 2561024 КБ.