Привет всем.
Накопилось у меня несколько вопросов по PostgreSQL, а ответа на них найти я не смог, как гуглением, так и методом экспериментов.
Сначала исходные данные:
Сервер PostgreSQL (11.3) в режиме логической репликации с помощью плагина 'wal2json'. Публикация и подписка настроены.
Конфиг мастера и параметры:
shared_buffers = 4GB
work_mem = 6MB
max_connection = 500
max_wal_size = 4GB
min_wal_size = 1GB
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
effective_cache_size = 8GB
effective_io_concurrency = 200
RAM = 16GB
CPU = 8
SSD
К базе данных на этом сервере подключаются сервисы, которые в больших количествах генерируют UPDATE, DELETE и INSERT (сервисы с SELECT вынесены на реплику). Индексы на всех задействованных таблицах для ускорения выборок DELETE и UPDATE присутствуют.
Поначалу, когда общего трафика было мало, то работало все замечательно, запросы выполнялись быстро, дэдлоков не было, все было хорошо.
Затем на данные сервисы переключили значительный объем трафика, и запросов к базе стало в разы больше. Сразу после этого началась деградация, очень ощутимая.
Запросы к базе стали выполняться медленно - с 0,02 сек. до 5-15 сек выросло. В связи с этим стали возникать дэдлоки в огромных количествах.
Кроме этого начали копиться wal-журналы, например, за ночь число журналов выросло до 6,5к, в то время, как репликация не отставала и никаких ошибок с ней не было (пришлось на реплике сделать ALTER SUBSCRIPTION REFRESH PUBLICATION). С текущими настройками чекпоинты выполняются 1 раз в 15 минут, т.е. не чаще, чем указано, но при чекпоинте получается что-то типа '0-2 WAL file(s) added, 0 removed, 0 recycled'.
Кроме этого часть процессов postgres уходит в swap.
for file in /proc/*/status ; do awk '/VmSwap|Name/{printf $2 " " $3}END{ print ""}' $file; done | sort -k 2 -n -r | head -n 20
mcollectived 32604 kB
nessusd 16668 kB
sssd_be 5460 kB
snapd 5256 kB
collectd 4544 kB
postgres 4200 kB
postgres 4180 kB
postgres 4080 kB
postgres 4068 kB
postgres 3984 kB
postgres 3856 kB
postgres 3840 kB
postgres 3836 kB
postgres 3780 kB
postgres 3740 kB
postgres 3732 kB
postgres 3660 kB
postgres 3648 kB
postgres 3580 kB
postgres 3512 kB
Load average выше 1.0 очень редко поднимается. Оперативной памяти также хватает (хотя непонятно почему уходит в swap). IO write в среднем 50 Mbps, IO read - 20 Mbps, но это видимо по большей части из-за постоянной записи wal-журналов (примерно 1 новый раз в 10-20 секунд). В среднем число коннектов постоянных 100-150.
Что я пытался делать, чтоб хоть как-то улучшить ситуацию:
1. Менял параметры 'work_mem' как в большую, так и в меньшую сторону - эффекта не дало.
2. Менял параметры 'checkpoint_timeout, checkpoint_completion_target, min_wal_size, max_wal_size' - эффекта не дало, wal-журналы все также продолжают копиться.
3. Менял параметр 'shared_buffers' - эффекта не дало, запросы все также медленно выполняются.
4. Менял параметр 'effective_io_concurrency' - все также не дало никакого эффекта.
Идей у меня больше не осталось, с чем еще можно поэкспериментировать, чтоб попытаться улучшить работу.
Может мне кто-то подсказать, что мне можно попытаться сделать с данной проблемой? Может еще какие-то данные нужны?
Средний
