Postgres: контрольные точки случаются слишком часто

У нас есть мощный сервер Postgres (64 ядра, оперативная память 384 ГБ, 16 жестких дисков SAS 15 КБ, RAID 10), и несколько раз в течение дня мы перестраиваем несколько больших наборов данных, которые очень интенсивно пишут. Apache и Tomcat также работают на одном сервере.

Мы получаем это предупреждение около 300 раз в день при восстановлении этих наборов данных с длинными отрезками, где ошибки усредняются на расстоянии 2 - 5 секунд:

2015-01-15 12:32:53 EST [11403]: [10841-1] LOG:  checkpoints are occurring too frequently (2 seconds apart)
2015-01-15 12:32:56 EST [11403]: [10845-1] LOG:  checkpoints are occurring too frequently (3 seconds apart)
2015-01-15 12:32:58 EST [11403]: [10849-1] LOG:  checkpoints are occurring too frequently (2 seconds apart)
2015-01-15 12:33:01 EST [11403]: [10853-1] LOG:  checkpoints are occurring too frequently (3 seconds apart)

Это связанные настройки:

checkpoint_completion_target    0.7
checkpoint_segments 64
checkpoint_timeout  5min
checkpoint_warning  30s
wal_block_size  8192
wal_buffers     4MB
wal_keep_segments   5000
wal_level   hot_standby
wal_receiver_status_interval    10s
wal_segment_size    16MB
wal_sync_method     fdatasync
wal_writer_delay    200ms
work_mem    96MB
shared_buffers  24GB
effective_cache_size    128GB

Итак, это означает, что мы пишем 1024 файла WAL файлов WAL каждые 2 - 5 секунд, иногда поддерживаемые в течение 15 - 30 минут.

1) Вы видите какие-либо настройки, которые мы можем улучшить? Дайте мне знать, если вам требуются другие настройки.

2) Можно ли использовать "SET LOCAL synchronous_commit TO OFF", в начале этих транзакций с интенсивной записью, чтобы эти записи WAL происходили немного в фоновом режиме, оказывая меньшее влияние на остальные операции?

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

Будет ли "SET LOCAL synchronous_commit TO OFF"; вызывают проблемы, если это продолжается в течение 15 - 30 минут? Или вызвать проблемы с нашей потоковой репликацией, в которой используются отправители WAL?

Спасибо!

PS. Я надеюсь, что Samsung начнет отправлять свои SSD-карты SM1715 3.2 TB PCIe, так как я думаю, что это решило бы наши проблемы.

Ответ 1

Ваш сервер генерирует так много данных WAL, потому что для wal_level установлено значение hot_standby. Я предполагаю, что вам это нужно, поэтому лучший способ избежать предупреждений - увеличить ваши checkpoint_segments. Но это всего лишь предупреждения - это довольно часто и совершенно нормально видеть их во время массовых обновлений и загрузки данных. Вы просто часто обновляетесь.

Изменение synchronous_commit не изменяет то, что записано в WAL, а скорее время, когда коммит возвращается, чтобы позволить ОС буферизовать эти записи.

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