Tengo dos trabajos de Sidekiq que parecen estar tardando mucho en completarse. Parece ser el mismo proceso general, pero hay dos trabajos que los muestran en estado EJECUTANDO. Estos trabajos llevan 6 horas ejecutándose y PostgreSQL se queda colgado en la CLI incluso cuando intento ejecutar un EXPLAIN ANALYZE contra la primera de estas consultas.
¿Alguna idea sobre qué podría estar causando que estas consultas tardaran tanto en ejecutarse?
Siéntete libre de cancelar esas consultas y dejar que el sistema las reintente más tarde.
De hecho, ya había hecho eso antes. Maté el trabajo para permitir que finalizara una reindexación, así que esto es simplemente que se está reiniciando después de eso.
Parece que esta mañana los trabajos finalizaron en algún momento durante la noche y ahora se están ejecutando de nuevo. Estado actual:
Esta subconsulta devuelve alrededor de 13.000 filas en nuestra instancia:
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
Luego se realiza un LEFT JOIN con la tabla user_badges, que tiene 84.000 filas. Parece que algo en la última condición WHERE ub.badge_id = 15 AND q.user_id IS NULL está causando que esta consulta falle estrepitosamente. Si omito la cláusula WHERE, se ejecuta en un tiempo razonable (unos 20 segundos), pero si incluyo incluso solo WHERE ub.badge_id = 15, ni siquiera puedo ejecutar un EXPLAIN contra esta consulta en un tiempo razonable. El EXPLAIN lleva colgado varios minutos sin devolver resultados. La ejecución real de la consulta completa lleva horas en marcha. ¿Hay algo que podamos hacer para optimizar esta consulta?
Al leer los temas desde anoche aquí en Meta, parece que ninguna tarea debería ejecutarse durante más de 8 horas, especialmente para una base de datos de gran tamaño.
Pero no estoy seguro de qué más podemos hacer para mejorar la situación.
Me resulta increíble que no podamos obtener nada con EXPLAIN porque, sin embargo, se queda colgado.
Al examinar este archivo: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
Hay el siguiente código que ejecuta esta consulta que actualmente está bloqueada. Si cambio el primer JOIN de LEFT JOIN a INNER JOIN, la consulta se ejecuta instantáneamente. ¿Hay alguna razón por la que esto necesite ser un LEFT 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
¿Es posible hacer estas consultas de insignias más rápidas?
Otra consulta que parece problemática es esta, creo que proviene del trabajo de limpieza semanal:
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
El explicación de esta consulta muestra que está intentando ordenar todas las 26 millones de filas de la tabla posts. No puedo determinar qué método utilizará para esta consulta, pero dado que la espera activa es “DataFileRead”, creo que está accediendo al disco para algo…
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)
Tengo la fuerte sensación de que estás nadando contra la corriente aquí y que la base de datos que estás utilizando simplemente no tiene suficientes recursos para ejecutar Discourse.
Estas consultas son, efectivamente, costosas, pero alojamos foros muy grandes (por ejemplo: About - Straight Dope Message Board 22 millones de publicaciones) y somos capaces de ejecutar todas estas consultas sin problemas en esa instancia.
Es un servidor dedicado con las siguientes especificaciones:
AMD Ryzen 7 3800X
64 GB de RAM ECC @ 2666 Mhz
2 x 1.2 TB Intel P3600 NVMe SSD (ZFS RAID 1)
A la máquina virtual que ejecuta Discourse se le han asignado 8 núcleos de CPU y 32 GB de RAM.
Creo que he descubierto el problema con la primera consulta, o al menos una forma de indicar al planificador de consultas que tome la decisión correcta. Aquí está la consulta que no se completaba en más de 16 horas (esto es para la insignia First Quote):
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
Si añado una sola línea ORDER BY en la ubicación correcta, esta consulta ahora se completa en unos pocos segundos:
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
Siento que debería ser lo suficientemente inteligente para hacer este ordenamiento en el lugar correcto, pero parece que no lo es… Aun así, la solución parece bastante sencilla en este punto.
Aún no he comenzado a profundizar en la otra consulta sobre percent_rank.
A veces la planificación falla cuando las estadísticas son malas… en algunos casos excepcionales, un vaciado completo puede ayudar; se recomienda encarecidamente realizar un vaciado mínimo después de las importaciones. Creo que hiciste ambas cosas.
¿Hay alguna razón por la que estás ejecutando en una máquina virtual en lugar de usar Docker directamente en el host?
¿Podrías decirme qué recursos se asignan a Straight Dope y si tiene trabajos que requieren varias horas, como los nuestros en 27 millones?
Sí, he ejecutado VACUUM ANALYZE varias veces. Las estadísticas deberían ser correctas, pero parece que está eligiendo mal en múltiples reconstrucciones, ajustes de afinación de Postgres y vaciados.
Estamos ejecutando otras máquinas virtuales en esta máquina host, pero por el momento tenemos recursos sobrantes, así que aquí es donde he construido un sistema para probar Discourse.
Desde nuestra instancia grande, al observar: /sidekiq/scheduler
Y
¿Realizaste un VACUUM FULL?
Nuestro servidor de base de datos tiene un rendimiento de hardware aproximadamente similar al tuyo (aunque contamos con un IO más rápido debido a que tenemos una matriz RAID más grande). Sin embargo, no utilizamos virtualización en absoluto. Esa es una gran diferencia.
No lo he hecho. Puedo probarlo y ver si el comportamiento cambia.
Estoy seguro de que hay cierta pérdida de rendimiento al ejecutarlo en una máquina virtual, pero nada está saturando el hardware intensivamente. Cuando ejecuté la importación para traer todos nuestros datos desde nuestro otro software, logré utilizar entre el 60 % y el 70 % de los 8 núcleos simultáneamente, ejecutando múltiples procesos de importación.
Ahora, cuando estos trabajos están en espera y girando, normalmente no veo que el promedio de carga supere 2-3, por lo que ni siquiera están utilizando toda la CPU disponible.
El vacuum completo es algo que he visto que ayuda después de migraciones masivas; tengo curiosidad por ver si tiene algún impacto.
Actualmente se está ejecutando.
En la consulta percent_rank que parece ser parte del trabajo semanal, ¿cómo se ve el EXPLAIN en tu instancia grande? Mi instancia reporta un costo de consulta superior a 8 millones en esa consulta, lo cual parece un poco alarmante.
¿Tienes alguna recomendación para los valores de ajuste de PostgreSQL en app.yml? Actualmente estoy usando:
shared_buffers: 16GB
work_mem: 512MB
El VACUUM FULL se completó, pero parece que no ha marcado ninguna diferencia en el rendimiento de la consulta. La consulta de insignias todavía parece que se ejecutará durante horas y horas a menos que agregue la cláusula ORDER BY, y la consulta percent_rank lleva dos horas ejecutándose sin completarse. Probablemente necesitemos cambiar la definición SQL para la insignia “First Quote” y luego tendré que ver qué se puede hacer para solucionar la consulta percent_rank.
¿Tienes alguna sugerencia para la consulta percent_rank basándote en este 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 filas)
Podrías probar ajustando el límite; quizás con un límite de 1000 esto sea lo suficientemente rápido para ti.
Cambiar el límite no parece alterar mucho el plan de consulta (ni los costos ni lo demás). El problema parece ser que la consulta debe ordenar toda la tabla de publicaciones (que en nuestro caso tiene alrededor de 26,5 millones de filas) antes de poder realizar la operación. Podría haber una oportunidad para crear un índice aquí. No veo que la columna score esté incluida en ninguno de los índices de la tabla de publicaciones en este momento.
La clasificación es por tema, no clasifica todo el conjunto.
Podrías quizás filtrar por IDs de tema … WHERE topic_id < 1000 … 2000 … 10000 y así sucesivamente… Probablemente, una vez completada la actualización inicial, esto se ejecutará más rápido.