Postgres DBA

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Постановка задачи

Начало работ по использованию результатов корреляционного анализа ожиданий СУБД для подготовке процесса Continual improvement .

Постановка эксперимента

Провести тестирование результатов корреляционного анализа ожиданий на продуктивной СУБД по инцидентам производительности в течении недели .

Конфигурация ВМ и СУБД

• Postgres Pro (enterprise certified) 15.10.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20230605 (Red Soft 11.4.0-1), 64-bit

• CPU 50

• RAM 88GB

• RED OS 7.3

Приоритеты инцидентов

Подробнее о приоритетах

Результат

• свыше 80% инцидентов производительности имеют приоритет 4

Количество SQL запросов по инцидентам

SQL запросы участвующие в более 80% инцидентов

• Количество SQL запросов участвующих во всех инцидентах = 5

• Количество SQL запросов участвующих в 80% инцидентов = 29

Ожидания СУБД

wait_event_type

wait_event

SQL запросы для оптимизации

queryid = 1214551160677155501

План выполнения запроса

Статистика ожиданий по типу IO

История выполнения и событий ожидания по типу IO для queryid = 1214551160677155501

Статистика ожиданий по типу IPC

История выполнения и событий ожидания по типу IPC для queryid = 1214551160677155501

Результаты анализа по SQL queryid = 1214551160677155501

1. Событий ожидания типа IPC существенно больше чем событий по типу IO.

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

3. После добавления индексов , провести анализ результатов .

Взято с основного технического канала Postgres DBA ( возможны правки и дополнения в исходной статье ).

Задача

Определить причину аномальной утилизации CPU и снижения производительности СУБД

Симптомы

Аномальная утилизация CPU сервера СУБД

Наблюдаемая проблема

Снижение операционной скорости СУБД

Корреляционный анализ

Отрицательная корреляция между снижением операционной скорости и ростом ожиданий - отсутствует.

Подробнее об индикаторе

https://dzen.ru/a/Z-FQYUcB3gepNYzA

Отчеты pgpro_pwr

G.3.11.2. Load distribution (Распределение нагрузки)

Этот раздел отчёта pgpro_pwr основан на представлении pgpro_stats_totals расширения pgpro_stats, если оно было доступно в течение отчётного интервала. Каждая таблица в данном разделе предоставляет данные за отчётный интервал о распределении нагрузки для определённого типа объектов, для которых собирается агрегированная статистика, например, баз данных, приложений, узлов или пользователей. Каждая таблица содержит по одной строке для каждого из ресурсов (таких, как общее время или общее число записанных разделяемых блоков), где распределение нагрузки показано на графике в виде линейчатой диаграммы с накоплением для объектов с наибольшей нагрузкой по этому ресурсу. Если область диаграммы, соответствующая объекту, слишком узка для включения заголовков, наведите указатель на эту область, чтобы получить подсказку с заголовком, значением и процентом. Таблицы «Load distribution among heavily loaded databases», «Load distribution among heavily loaded applications», «Load distribution among heavily loaded hosts» и «Load distribution among heavily loaded users» показывают распределение нагрузки для соответствующих объектов.

Статистика утилизации CPU

G.3.11.4.1. rusage statistics (Статистика использования ресурсов)

Этот раздел добавляется в отчёт, только если в отчётном интервале было доступно расширение pgpro_stats или pg_stat_kcache.

Таблица отчёта «Top SQL by system and user time» показывает запросы с наибольшей суммой значений полей user_time и system_time в представлении pg_stat_kcache или pgpro_stats_totals.

Наиболее длительные SQL

Таблица отчёта «Top SQL by execution time» показывает запросы с наибольшей длительностью выполнения, определяемой по значению поля total_time представления pgpro_stats_statements или pg_stat_statements.

Причина инцидента и проблемный запрос

Причина аномальной утилизации CPU и снижения операционной скорости СУБД является массовый вызов хранимой функции, требующей для выполнения высоких вычислительных ресурсов.

Иногда случается ситуация : нужно завершить долгий SQL запрос (кильнуть сессию).

Стандартный способ, рекомендованный в документации :
select pg_terminate_backend( pid );

Однако, иногда, если запрос очень долгий , даже после прерывания процесса, pid всё равно еще остается в результате запроса select ... from pg_stat_activity ;

И в этой ситуации может возникнуть соблазн "kill -9 pid"
Чтобы побыстрее ...

После выполнения kill , кластер PostgreSQL аварийно перезапустится , со всеми вытекающими последствиями .
Быстрее - не получится .

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

P.S. Ну и как обычно , мнение ИИ. Страшилка о 'kill -9" от DeepSeek:

После выполнения команды `kill -9` для процесса PostgreSQL, выполняющего долгий запрос, возникают следующие последствия:

### 1. **Прерывание транзакции**
- Транзакция, связанная с убитым процессом, **не будет завершена корректно**. Поскольку `SIGKILL` (сигнал 9) нельзя перехватить или обработать, PostgreSQL не успевает выполнить откат (`ROLLBACK`) транзакции. Это может привести к:
- **Незавершенным изменениям данных**: Если транзакция выполняла модификацию данных (INSERT/UPDATE/DELETE), изменения останутся в состоянии "под вопросом" до восстановления.
- **Блокировкам (locks)**: Ресурсы (строки, таблицы), заблокированные транзакцией, могут остаться занятыми до очистки процесса.

### 2. **Автоматическое восстановление PostgreSQL**
- PostgreSQL обнаруживает аварийное завершение процесса и автоматически выполняет:
- **Очистку блокировок**: Все удерживаемые процессом блокировки освобождаются.
- **Откат транзакции**: Незавершенная транзакция откатывается с помощью механизма восстановления на основе **WAL (Write-Ahead Logging)**. Это гарантирует целостность данных, но может занять время, особенно если транзакция была крупной.

### 3. **Влияние на производительность**
- **Долгое восстановление**: Если убитый процесс работал с большим объемом данных, откат транзакции через WAL может задержать другие операции.
- **Возможные задержки**: Другие сессии, ожидавшие блокировок от убитого процесса, получат доступ к ресурсам только после завершения восстановления.

### 4. **Ошибки в клиентских приложениях**
- Клиент, инициировавший запрос, получит ошибку соединения (например, `connection terminated unexpectedly`). Приложение должно быть готово к таким сценариям (повторные попытки, обработка исключений).

### 5. **Риск для репликации (если используется)**
- В режимах синхронной репликации аварийное завершение процесса может временно нарушить синхронизацию данных между primary и standby-серверами. Однако механизм WAL гарантирует восстановление согласованности.

### 6. **Журналирование (логи)**
- В логах PostgreSQL появится запись об аварийном завершении процесса, например:
```
FATAL: terminating connection due to administrator command
LOG: server process (PID 12345) was terminated by signal 9: Killed
LOG: aborting any active transactions
```

---

### Рекомендации:
1. **Избегайте `kill -9`**, если это возможно. Используйте `pg_cancel_backend()` или `pg_terminate_backend()` для безопасного завершения запросов.
2. Для долгих транзакций настройте `statement_timeout` или `idle_in_transaction_session_timeout`.
3. Мониторьте активные транзакции через `pg_stat_activity`.

**Итог**: `kill -9` не повредит данные благодаря WAL, но может вызвать временные задержки и требует времени на автоматическое восстановление.