Как исправить замедление запросов в PostgreSQL?

Question

[Dannerty]

Привет всем.
Собственно такой вопрос возник.
Как можно исправить, или что можно сделать, чтобы не возникало замедление запросов к базе данных, если в это время выполняется какой-либо тяжелый SELECT, или идет создание индекса (CREATE INDEX CONCURRENTLY), или другая DDL-операция на большой таблице?
Есть у меня база данных - 100 Гб.
Есть приложения, которые делают множество запросов к базе и им важно время отклика.
При выполнении тяжелого запроса, все запросы от сервисов начинают в разы медленнее выполняться (замедление в 10-30 раз, очень критично). Причем запросы сервисов никак не связаны с таблицами, которые участвуют в запросе.
Также при создании индекса без блокировки или другой DDL-операции таблицы начинается замедление. Здесь также таблица не используется сервисами.
Можно ли как-то это обойти? С запросами вопрос еще решить можно, но что делать с DDL? Есть ли у PostgreSQL аналог MySQL'ского 'pt-online-schema' от Percona?

Сервер:
8 ядер, 16 Гб, ССД, Postgresql 11.3

Postgresql.conf:
shared_buffers - 6GB
effective_cache_size - 10GB
maintenance_work_mem - 1GB
work_mem - 48 MB

Comments

[Melkij]

Для начала посмотрите в графики мониторинга что именно растёт. И как именно. Интересны io latency на чтение/запись, CPU, график длительности транзакции и график среднего выполнения запросов. График чекпойнтов, кстати.

[Dannerty]

Растет потребление CPU (wait растет, user падает, idle падает)
Растет load average в 3 раза
Растет io write и read

[Алексей Черемисин]

Насколько я понимаю, у вас всего 16 гиг памяти. При этом в постгресе на всякие буферы используется 17 гиг. У вас случаем система в своп не проваливается?

[Dannerty]

Нет, не проваливается

[Melkij]

Растет io write и read

latency?
На каких показателях iops?

Вероятно менять диски на нормальные. Да, SSD бывают очень сильно разные. От нормальных до в принципе не подходящих под СУБД.

Алексей Черемисин,

При этом в постгресе на всякие буферы используется 17 гиг.

только 6.
effective_cache_size - оценка для оптимизатора, не выделяется реально.
а work_mem разные - наоборот, их может быть много выделено.

Answer 1

[ky0]

Выносить read-only запросы на реплику, ставить под базу SSD (или под наиболее активно используемые таблицы с помощью тейблспейсов) - вариантов много.

Comments

[Dannerty]

Запросы итак вынесены будут, база уже на SSD, но это никак не решит проблемы, когда будет выполняться DDL-операция на большой таблице.

[ky0]

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