Не хватает информации, но попробую.
В чем может быть проблема?
Имеем:
- 128Гб всего;
- 50Гб отдано под кэш, т.е. системе эта память недоступна.
Итого
78Гб используемой памяти.
База может исполнять до 500 (max_connections) запросов параллельно. Каждый из них может использовать как минимум 768Мб (work_mem) для операций сортировки/группировки/хэширования. “Как минимум” потому, что если запрос требует
нескольких таких операций, то 768Мб съест
каждая из них.
Если предположить, что запросы простые и используют не более одной подобной операции, то при доступной памяти (78Гб) можно себе позволить 104 таких запроса. Т.е. параметр `max_connections` должен быть равен (78*1024/768) =
104, или система уйдет в своппинг при такой нагрузке.
Если установленное кол-во возможных сессий необходимо, то память нужно уменьшить до (78*1024/500) =
160, это если не будет сложных запросов.
Мне кажется, что в те моменты, когда база “стоит на коленях” как раз и происходит такая ситуация, когда выделенные ей ресурсы превосходят физически доступные и ядро уходит в своп. Это если исключить возможность любых других процессов в системе — мало-ли, запускается ручной VACUUM в такие моменты...
А как проверить, что же происходит на самом деле?
Мониторить. Прежде всего ось. Либо готовыми системами (zabbix и nagios самые распространенные в моей практике), либо использовать `vmstat` или `dstat` (я их использую в 95% случаев). Это позволит “увидеть” что происходит в системе, как работает память и подсистема ввода-вывода, своппинг.
Выжать больше информации из самой базы. Как минимум я всегда включаю следующие опции:
- log_min_duration_statement = 300
- log_checkpoints = on
- log_connections = on
- log_disconnections = on
- log_lock_waits = on
- log_temp_files = 0
- track_functions = pl
- track_io_timing = on
- log_autovacuum_min_duration = 5000
Да — в логах будет гораздо больше информации, но так удобнее. Если известна конкретная база с проблемами (из многих), то избыточное логгирование можно включить только для нее через
`ALTER DATABASE ... SET ...`. Тоже самое применимо и для
конкретных пользователей. Однако я предпочитаю полное логгирование на уровне всего кластера.
Также рекомендую просматривать (и, возможно, сохранять снимки) статистических таблиц, как минимум `pg_stat_activity` и `pg_locks`.
Что можно сделать сразу?
Все нижесказанное мое мнение, я очень рекомендую изменять настройки обдуманно и, желательно, проверять новую конфигурацию через нагрузочные тесты.
- `shared_buffers` уменьшить, до 8Гб (на крайний случай 16). Чем больше размер, тем больше расходы базы на обслуживание кэша. PostgreSQL страдает от избыточной конкуренции за доступ к кэшу при его больших размерах. Сравнительно небольшой кэш при большом объеме памяти будет вести себя хорошо, т.к. ОС тоже кэширует файлы и доступ к блокам будет быстрым (медленнее, чем сразу из shared_buffers, но быстрее, чем при холодном старте системы);
- `default_statistics_target` увеличить. Понижать этот параметр я в принципе не вижу смысла. Учитывая размер базы в 200Гб предположу, что есть несколько таблиц в сумме занимающих более 50% это объема. Потому увеличил бы этот параметр до 200 для всей системы, и для индивидуальных колонок в больших таблицах еще выше, но это уже при настройке индивидуальных запросов. Главное после изменений проанализировать базу;
- `max_connections` нужно уменьшить. Я бы оставил этот параметр на уровне 150-200 сессий. Также непонятно наличие нескольких pgbouncer-ов. Нужно оставить только один и, если необходимо, настроить в нем несколько пулов. Эффект будет лучший при меньших накладных расходах;
- `work_mem` нужно уменьшить. Я бы его установил в 32Мб, это уже очень хорошо. Данный параметр меняется на уровне сессий, потому обычной практикой для больших скриптов/отчетов является его выкручивание под нужды конкретного отчета. Скажем, у меня есть отчет, где первым делом этот параметр устанавливается в 900Мб под нужды этого отчета, значение получено через `EXPLAIN ANALYZE`. Как вариант, можно завести пользователя (или нескольких) для отчетов (или тяжелых запросов) и им в настройках прописать более высокое значение `work_mem`.
В заключении — я думаю вам нужно изменить параметры базы и получить больше информации о том, что происходит. После этого можно будет говорить о ситуации дальше.