У меня есть две задачи от Sidekiq, которые, кажется, выполняются очень долго. Похоже, это один и тот же общий процесс, но в статусе RUNNING отображаются две задачи. Эти задачи выполняются уже 6 часов, и PostgreSQL зависает в CLI, когда я пытаюсь даже выполнить EXPLAIN ANALYZE для первого из этих запросов.
Есть ли идеи, что может быть причиной такого долгого выполнения запросов?
Не стесняйтесь отключать эти запросы, и система повторит попытку позже.
Я уже делал это ранее. Я остановил задачу, чтобы дать завершиться реиндексации, поэтому это был её перезапуск после этого.
Похоже, что сегодня утром задачи завершили выполнение где-то ночью и снова запущены. Текущий статус:
Этот подзапрос возвращает около 13 000 строк на нашем экземпляре:
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
Затем результат LEFT JOIN-ится с таблицей user_badges, в которой 84 000 строк. Похоже, что что-то в последнем условии WHERE ub.badge_id = 15 AND q.user_id IS NULL вызывает сбой этого запроса. Если убрать условие WHERE, то запрос выполняется за разумное время (около 20 секунд), но если включить хотя бы WHERE ub.badge_id = 15, то я даже не могу выполнить EXPLAIN для этого запроса за разумное время. EXPLAIN висит уже несколько минут без результатов. Фактическое выполнение полного запроса длится уже несколько часов. Есть ли что-то, что мы можем сделать для оптимизации этого запроса?
Просматривая темы на Meta с прошлой ночи, я понял, что никакая задача не должна выполняться более 8 часов, особенно для большой базы данных.
Но я не уверен, что ещё можно сделать для улучшения ситуации.
Для меня это безумие: мы не можем получить результат от EXPLAIN, потому что он зависает.
Посмотрев на этот файл: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
Вот код, который выполняет этот запрос, который сейчас завис. Если я изменю первое соединение с LEFT JOIN на INNER JOIN, запрос выполняется мгновенно. Есть ли причина, по которой это должно быть левое соединение?
sql = <<~SQL
DELETE FROM user_badges
WHERE id IN (
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
#{badge.query}
) q ON q.user_id = ub.user_id
#{post_clause}
WHERE ub.badge_id = :id AND q.user_id IS NULL
)
SQL
Можно ли ускорить эти запросы к бейджам?
Ещё один запрос, который выглядит плохо, — это, кажется, из еженедельной задачи очистки:
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank)
LIMIT 20000
) AS X
WHERE posts.id = X.id
План выполнения показывает, что он пытается отсортировать все 26 миллионов строк в таблице posts. Я не могу точно сказать, какой метод он будет использовать для этого запроса, но, исходя из того, что активное ожидание — «DataFileRead», я думаю, что он обращается к диску за чем-то…
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Nested Loop (cost=8312704.61..8627308.35 rows=20000 width=825)
-> Subquery Scan on x (cost=8312704.18..8464468.35 rows=20000 width=48)
-> Limit (cost=8312704.18..8464268.35 rows=20000 width=12)
-> Hash Join (cost=8312704.18..209445240.14 rows=26540908 width=12)
Hash Cond: (posts_1.topic_id = topics.id)
-> Nested Loop (cost=8277347.60..209340213.36 rows=26540908 width=16)
-> WindowAgg (cost=8277347.16..8809352.84 rows=26600284 width=24)
-> Sort (cost=8277347.16..8343847.87 rows=26600284 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..4542277.84 rows=26600284 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..7.52 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Hash (cost=23871.05..23871.05 rows=918842 width=4)
-> Index Only Scan using topics_pkey on topics (cost=0.42..23871.05 rows=918842 width=4)
-> Index Scan using posts_pkey on posts (cost=0.44..8.14 rows=1 width=781)
Index Cond: (id = x.id)
JIT:
Functions: 24
Options: Inlining true, Optimization true, Expressions true, Deforming true
(21 rows)
У меня стойкое ощущение, что вы идёте против течения: используемая вами база данных просто не обладает достаточными ресурсами для работы Discourse.
Эти запросы действительно ресурсоёмки, но мы размещаем множество крупных форумов (например, About - Straight Dope Message Board с 22 миллионами сообщений) и успешно выполняем все подобные запросы на том же экземпляре.
Это выделенный сервер со следующими характеристиками:
AMD Ryzen 7 3800X
64 ГБ ECC ОЗУ @ 2666 МГц
2 × 1,2 ТБ Intel P3600 NVMe SSD (ZFS RAID 1)
Виртуальной машине, на которой запущен Discourse, выделено 8 ядер процессора и 32 ГБ ОЗУ.
Полагаю, я обнаружил проблему с первым запросом, или, по крайней мере, способ подсказать планировщику запросов принять правильное решение. Вот запрос, который не завершался более 16 часов (это для значка «Первая цитата»):
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
) q ON q.user_id = ub.user_id
AND (q.post_id = ub.post_id OR NOT TRUE)
WHERE ub.badge_id = 15 AND q.user_id IS NULL
Если добавить одну строку ORDER BY в правильное место, этот запрос теперь выполняется за несколько секунд:
SELECT ub.id
FROM user_badges ub
LEFT JOIN (
SELECT ids.user_id, q.post_id, p3.created_at granted_at
FROM
(
SELECT p1.user_id, MIN(q1.id) id
FROM quoted_posts q1
JOIN badge_posts p1 ON p1.id = q1.post_id
JOIN badge_posts p2 ON p2.id = q1.quoted_post_id
WHERE (TRUE OR ( p1.id IN (-1) ))
GROUP BY p1.user_id
) ids
JOIN quoted_posts q ON q.id = ids.id
JOIN badge_posts p3 ON q.post_id = p3.id
ORDER BY ids.user_id
) q ON q.user_id = ub.user_id
AND (q.post_id = ub.post_id OR NOT TRUE)
WHERE ub.badge_id = 15 AND q.user_id IS NULL
Кажется, система должна быть достаточно умной, чтобы сама выполнить эту сортировку в нужном месте, но, похоже, это не так… Тем не менее, исправление на данном этапе выглядит довольно простым.
Пока ещё не начал разбираться с другим запросом, касающимся percent_rank.
Иногда планирование сходит с рельсов, когда статистика плохая… В некоторых исключительных случаях может помочь полная вакуумизация, а минимальная вакуумизация настоятельно рекомендуется после импорта. Мне кажется, вы сделали и то, и другое.
Есть ли причина, по которой вы работаете в виртуальной машине, а не в Docker непосредственно на хосте?
Можно ли узнать, какие ресурсы выделены Straight Dope, и есть ли у вас вакансии, требующие нескольких часов работы, как у нас на 27 миллионах?
Да, я несколько раз выполнял VACUUM ANALYZE. Статистика должна быть корректной, но, похоже, оптимизатор делает неоптимальный выбор даже после множества перестроений, настроек Postgres и операций VACUUM.
На этой машине-хосте запущены и другие виртуальные машины, но в данный момент у нас есть свободные ресурсы, поэтому здесь я развернул систему для тестирования Discourse.
На нашем большом экземпляре при просмотре: /sidekiq/scheduler
И
Вы выполняли FULL VACUUM?
Наш сервер базы данных примерно сопоставим по аппаратной производительности с вашим (хотя у нас более быстрый ввод-вывод, так как у нас массив RAID большего размера). Однако мы вообще не используем виртуализацию. Это одно существенное отличие.
Нет, я этого не делал. Могу попробовать и посмотреть, изменится ли поведение.
Безусловно, при работе в виртуальной машине есть некоторая потеря производительности, но ничего, что бы серьёзно нагружало оборудование, нет. Когда я запускал импорт для переноса всех данных из нашего другого ПО, мне удавалось задействовать 60–70% всех 8 ядер, одновременно запуская несколько процессов импорта.
Сейчас же, когда эти задачи простаивают и крутятся, я обычно не вижу, чтобы средняя нагрузка поднималась выше 2–3, то есть они даже не используют весь доступный процессор.
Полная вакуумизация — это то, что, как я видел, помогает после масштабных миграций. Интересно, окажет ли она какое-либо влияние.
В данный момент она выполняется.
Что касается запроса percent_rank, который, похоже, является частью еженедельной задачи, как выглядит EXPLAIN для вашего большого экземпляра? На моём экземпляре стоимость этого запроса превышает 8 миллионов, что выглядит довольно пугающе.
Есть ли у вас какие-либо рекомендации по настройке параметров PostgreSQL в app.yml? Сейчас я использую:
shared_buffers: 16GB
work_mem: 512MB
VACUUM FULL завершился, но, похоже, это не повлияло на производительность запросов. Запрос для значков всё ещё, кажется, будет выполняться часами, если не добавить предложение ORDER BY, а запрос percent_rank уже выполняется два часа и не завершается. Скорее всего, нам придётся изменить определение SQL для значка «Первая цитата», а затем мне нужно будет посмотреть, что можно сделать для исправления запроса percent_rank.
Есть ли у вас какие-либо предложения по запросу percent_rank на основе этого EXPLAIN?
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
WHERE (posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank)
LIMIT 20000
) AS X
WHERE posts.id = X.id
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
Update on posts (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Nested Loop (cost=6511439.82..6944253.09 rows=20000 width=828)
-> Subquery Scan on x (cost=6511439.38..6784765.09 rows=20000 width=48)
-> Limit (cost=6511439.38..6784565.09 rows=20000 width=12)
-> Nested Loop (cost=6511439.38..374544016.70 rows=26949684 width=12)
-> Nested Loop (cost=6511438.96..192122439.64 rows=26949684 width=16)
-> WindowAgg (cost=6511438.52..7050906.24 rows=26973386 width=24)
-> Sort (cost=6511438.52..6578871.98 rows=26973386 width=16)
Sort Key: posts_2.topic_id, posts_2.score DESC
-> Seq Scan on posts posts_2 (cost=0.00..2721272.86 rows=26973386 width=16)
-> Index Scan using posts_pkey on posts posts_1 (cost=0.44..6.84 rows=1 width=16)
Index Cond: (id = posts_2.id)
Filter: ((percent_rank IS NULL) OR ((percent_rank() OVER (?)) <> percent_rank))
-> Index Only Scan using topics_pkey on topics (cost=0.42..6.77 rows=1 width=4)
Index Cond: (id = posts_1.topic_id)
-> Index Scan using posts_pkey on posts (cost=0.44..7.97 rows=1 width=784)
Index Cond: (id = x.id)
JIT:
Functions: 21
Options: Inlining true, Optimization true, Expressions true, Deforming true
(20 rows)
Может, стоит поэкспериментировать с лимитом — возможно, при значении 1000 это будет достаточно быстро для вас.
Изменение лимита, похоже, мало влияет на план выполнения запроса (по затратам или иным параметрам). Проблема, судя по всему, в том, что запрос должен отсортировать всю таблицу posts (в нашем случае это около 26,5 млн строк), прежде чем сможет выполнить операцию. Здесь может быть возможность использования индекса. Я не вижу, чтобы столбец score входил в какие-либо индексы таблицы posts на данный момент.
Рейтинг рассчитывается по каждой теме отдельно, а не по всему набору.
Вы, возможно, можете ограничить выборку по идентификаторам тем … WHERE topic_id < 1000 … 2000 … 10000 и так далее… Вероятно, после завершения первоначального обновления это будет выполняться быстрее.