ChatPPG : Практические рекомендации по ожиданиям СУБД PostgreSQL

Статистический анализ документации и текстов - стандартная область применения чат-ботов.

В ходе работ по задаче

получены ответы от ChatPPG на стандартный вопрос по итогам корреляционного анализа - "Как снизить ожидания XXX ?"

Ниже представлены ответы, имеющие практическое применение(исключены общие фразы типа "оптимизируйте запрос и т.п.")

DSMFillZeroWrite

Увеличьте размер разделяемой памяти (shared_buffers), чтобы уменьшить частоту операций записи в разделяемую память.

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

DataFileRead

Увеличение параметра shared_buffers

Выполнение команды VACUUM ANALYZE на таблицах

Проверьте, используют ли ваши запросы индексы эффективно

Увеличение параметра maintenance_work_mem

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

SLRUWrite

Увеличить размер буферов для конкретных SLRU (Shared Local Recovery Unit) буферов

Для мониторинга и определения, какие буферы требуют корректировки, можно использовать системный каталог pg_stat_slru.

Практические применение

BgWorkerShutdown

bgwriter_delay определяет интервал между запусками фонового процесса записи. Увеличение этого значения может уменьшить частоту запуска фонового процесса, что может снизить нагрузку на систему.

BufferIO

shared_buffers: Увеличьте значение параметра shared_buffers

work_mem: Увеличьте значение work_mem

maintenance_work_mem: Увеличьте значение maintenance_work_mem

effective_cache_size: Установите значение effective_cache_size на значение, которое отражает примерно половину вашего общего объёма оперативной памяти

effective_io_concurrency: Настройте значение effective_io_concurrency в зависимости от вашего хранилища. Для NVMe хранилищ можно установить значение до 200 или выше

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

ExecuteGather

max_parallel_workers_per_gather: Уменьшите количество параллельных процессов, используемых для выполнения операций gather.

parallel_setup_cost и parallel_tuple_cost: Увеличьте эти параметры, чтобы PostgreSQL менее часто использовал параллельные планы.

Увеличьте параметр work_mem

Убедитесь, что статистика по таблицам актуальна.

Если параллелизм не приносит значительных улучшений, можно полностью отключить его, установив параметр max_parallel_workers_per_gather в 0

Если таблица физически неупорядочена, выполните команду CLUSTER, чтобы упорядочить строки таблицы по индексу.

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

ParallelFinish

Параметр max_parallel_workers_per_gather определяет максимальное количество параллельных рабочих процессов, которые могут быть использованы для одного оператора Gather. Уменьшение этого параметра может снизить количество параллельных операций и, следовательно, количество ожиданий ParallelFinish.

Параметр parallel_setup_cost определяет стоимость настройки параллельного плана. Увеличение этого параметра может сделать параллельные планы менее привлекательными для оптимизатора, что может привести к использованию менее параллельных планов.

Если запросы включают агрегацию, убедитесь, что агрегатные функции поддерживают параллелизм. Если они не поддерживают, это может привести к увеличению ожиданий ParallelFinish.

BufferContent

Увеличение параметра shared_buffers может помочь уменьшить количество ожиданий BufferContent

Параметры автовакуума, такие как autovacuum_vacuum_cost_delay и autovacuum_vacuum_cost_limit, могут влиять на производительность и количество ожиданий BufferContent.

Расширение pg_prewarm может помочь предзагрузить данные в кэш, что может уменьшить количество ожиданий BufferContent при первом чтении таблицы.

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

BufferMapping

Увеличение shared_buffers

Увеличение effective_cache_size

Увеличение work_mem и maintenance_work_mem

Убедитесь, что у вас есть правильные индексы для ваших запросов.

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

LockManager

Увеличение shared_buffers

Используйте подходящий уровень изоляции транзакций. Например, уровень READ COMMITTED обычно требует меньше блокировок, чем REPEATABLE READ.

Если ожидания LockManager связаны с доступами к TOAST таблицам, попробуйте явно приобретать блокировки на эти таблицы и их индексы.

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

MultiXactGen

Регулярное выполнение VACUUM

Увеличение параметра max_worker_processes

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

MultixactMemberSLRU

Увеличьте размер буферов для Subtrans и Xact SLRU

Регулярное выполнение VACUUM

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

MultixactOffsetSLRU

Увеличение размера буферов SLRU

Увеличьте параметр max_wal_size

Настройте параметр checkpoint_completion_target для более равномерного распределения операций записи

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

ParallelHashJoin

Отключить параллельные запросы, установив параметр max_parallel_workers_per_gather в 0

Убедитесь, что у вас есть правильные индексы на таблицах, участвующих в запросе.

Периодически выполняйте команды ANALYZE и VACUUM для обновления статистики и очистки мертвых строк.

Настройте параметры, такие как random_page_cost и cpu_tuple_cost, чтобы повлиять на выбор плана выполнения запроса. Например, уменьшение random_page_cost может сделать индексные сканирования более привлекательными. SET random_page_cost = 1.1;

Попробуйте использовать другие типы соединений, такие как Nested Loop или Merge Join, если они подходят для вашего запроса. Вы можете временно отключить Hash Join, установив параметр enable_hashjoin в off. SET enable_hashjoin = off;

ProcArray

Уменьшите значение параметра max_standby_streaming_delay, чтобы уменьшить задержку репликации.

Настройте параметры, связанные с параллелизмом, такие как max_parallel_workers_per_gather и max_worker_processes, чтобы управлять количеством рабочих процессов.

Для снижения количества ожиданий ProcArray, вам,скорее всего, нужно уменьшить значение параметра max_parallel_workers_per_gather.Этот параметр определяет максимальное количество рабочих процессов,которые могут быть запущены для одного оператора Gather.

Рекомендации по настройке max_worker_processes:

1. Определите количество ядер процессора:

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

2. **Установите значение max_worker_processes:**

- Обычно рекомендуется установить max_worker_processes равным количеству ядер процессора или немного меньше, чтобы избежать перегрузки системы. Например, если у вас 4 ядра, можно установить max_worker_processes в 3 или 4.

- Если вы используете гиперпоток (hyperthreading), то можно учитывать и виртуальные ядра, но это может привести к увеличению контекстных переключений, что может снизить производительность.

3. Настройте другие параметры:

- Убедитесь, что другие параметры, такие как max_parallel_workers, max_parallel_workers_per_gather, и max_parallel_maintenance_workers, также настроены правильно. Например:

- max_parallel_workers — максимальное количество параллельных работников для всех операций.

- max_parallel_workers_per_gather — максимальное количество параллельных работников для одной операции gather.

- max_parallel_maintenance_workers — максимальное количество параллельных работников для операций обслуживания (например, создания индексов).

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

WALInsert

Увеличение значения wal_buffers может помочь сгладить задержки при записи в журнал предзаписи, особенно при высокой нагрузке

Увеличение max_wal_size

Увеличение checkpoint_completion_target

XidGen

Увеличьте количество автовакуумных процессов с помощью параметра autovacuum_max_workers, чтобы управлять транзакциями более эффективно.

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

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

extend

Увеличьте параметр maintenance_work_mem, чтобы улучшить производительность операций поддержки базы данных, таких как VACUUM и CREATE INDEX.

Увеличьте параметр max_wal_size и checkpoint_completion_target, чтобы уменьшить частоту выполнения чекпоинтов, которые могут вызывать ожидания extend.