J’ai deux tâches Sidekiq qui semblent prendre beaucoup de temps à s’exécuter. Il semble qu’il s’agisse du même processus global, mais deux tâches apparaissent avec le statut RUNNING. Ces tâches tournent depuis 6 heures maintenant, et PostgreSQL se bloque en ligne de commande lorsque j’essaie même d’exécuter un EXPLAIN ANALYZE sur la première de ces requêtes.
Avez-vous des idées sur ce qui pourrait causer un tel temps d’exécution pour ces requêtes ?
N’hésitez pas à supprimer ces requêtes et laissez le système les réessayer plus tard.
Je l’avais déjà fait, en fait. J’ai arrêté le travail pour permettre à une réindexation de se terminer, donc c’était son redémarrage après cela.
Il semble que ce matin, les travaux se soient achevés à un moment donné durant la nuit et qu’ils soient à nouveau en cours d’exécution. Statut actuel :
Cette sous-requête retourne environ 13 000 lignes sur notre instance :
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
Elle est ensuite jointe via LEFT JOIN à la table user_badges, qui contient 84 000 lignes. Il semble que quelque chose dans la dernière condition WHERE ub.badge_id = 15 AND q.user_id IS NULL fasse exploser cette requête. Si j’omets la clause WHERE, elle s’exécute dans un délai raisonnable (environ 20 secondes), mais si j’inclus ne serait-ce que WHERE ub.badge_id = 15, je ne peux même pas obtenir d’EXPLAIN s’exécutant dans un délai raisonnable sur cette requête. L’EXPLAIN est en attente depuis plusieurs minutes maintenant sans aucun résultat. L’exécution réelle de la requête complète tourne depuis des heures. Y a-t-il quelque chose que nous puissions faire pour optimiser cette requête ?
En lisant les sujets depuis hier soir ici sur Meta, il semble qu’aucune tâche ne devrait s’exécuter pendant plus de 8 heures, en particulier pour une base de données de grande taille.
Mais je ne suis pas sûr de ce que nous pouvons faire de plus pour l’améliorer.
C’est fou pour moi que nous ne puissions pas obtenir EXPLAIN pour afficher quoi que ce soit, car cela se fige de toute façon.
En examinant ce fichier : discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
Voici le code qui exécute la requête actuellement bloquée. Si je remplace la première jointure LEFT JOIN par INNER JOIN, la requête s’exécute instantanément. Y a-t-il une raison pour laquelle cela doit être une jointure gauche ?
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
Est-il possible d’accélérer ces requêtes de badges ?
Une autre requête qui semble problématique est celle-ci, je pense qu’elle provient du job de nettoyage hebdomadaire :
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
L’explication de cette requête montre qu’elle tente de trier les 26 millions de lignes de la table posts. Je ne peux pas déterminer quelle méthode sera utilisée pour cette requête, mais étant donné que l’attente active est “DataFileRead”, je pense qu’elle accède au disque pour récupérer quelque chose…
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)
J’ai un fort sentiment que vous nagez à contre-courant ici et que la base de données que vous utilisez n’a tout simplement pas assez de ressources pour exécuter Discourse.
Ces requêtes sont en effet coûteuses, mais nous hébergeons de nombreux grands forums (par exemple : About - Straight Dope Message Board 22 millions de messages) et nous parvenons à exécuter toutes ces requêtes sans problème sur cette instance.
C’est un serveur dédié avec la configuration suivante :
AMD Ryzen 7 3800X
64 Go de RAM ECC @ 2666 MHz
2 x 1,2 To Intel P3600 NVMe SSD (ZFS RAID 1)
La machine virtuelle exécutant Discourse dispose de 8 cœurs CPU et de 32 Go de RAM.
Je pense avoir identifié le problème avec la première requête, ou du moins trouvé un moyen d’indiquer au planificateur de requêtes de prendre la bonne décision. Voici la requête qui ne se terminait pas après plus de 16 heures (il s’agit de la première médaille de citation) :
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 j’ajoute une seule ligne ORDER BY au bon endroit, cette requête s’exécute maintenant en quelques secondes :
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
J’aurais pensé qu’il serait assez intelligent pour effectuer ce tri au bon endroit, mais il semble que ce ne soit pas le cas… Quoi qu’il en soit, la correction semble plutôt simple pour le moment.
Je n’ai pas encore vraiment commencé à examiner l’autre requête sur percent_rank.
Parfois, la planification est perturbée lorsque les statistiques sont mauvaises… Dans certains cas exceptionnels, un vide complet peut aider ; un vide minimal est totalement recommandé après les importations. Je pense que vous avez fait les deux.
Y a-t-il une raison pour laquelle vous exécutez cela dans une machine virtuelle plutôt que Docker directement sur l’hôte ?
Quelles sont les ressources allouées à Straight Dope, si je puis demander, et propose-t-elle des postes qui nécessitent plusieurs heures de travail, comme c’est le cas chez nous avec nos 27 millions de messages ?
Oui, j’ai exécuté VACUUM ANALYZE plusieurs fois. Les statistiques devraient être correctes, mais cela semble faire de mauvais choix à travers plusieurs reconstructions, ajustements de réglage de Postgres et VACUUM.
Nous exécutons d’autres machines virtuelles sur cette machine hôte, mais nous disposons de ressources disponibles pour le moment, c’est pourquoi j’ai construit ici un système pour tester Discourse.
Depuis notre grande instance, en regardant : /sidekiq/scheduler
Et
Avez-vous effectué un VACUUM FULL ?
Notre serveur de base de données a des performances matérielles similaires aux vôtres (bien que nous disposions d’un IO plus rapide grâce à notre plus grand ensemble RAID). Cependant, nous n’utilisons pas du tout la virtualisation. C’est une grande différence.
Non, je ne l’ai pas fait. Je peux essayer et voir si le comportement change.
Je suis sûr qu’il y a une certaine perte de performance due à l’exécution dans une machine virtuelle, mais rien ne sollicite excessivement le matériel. Lorsque j’ai exécuté l’importation pour récupérer toutes nos données depuis notre autre logiciel, j’ai pu atteindre une utilisation de 60 à 70 % des 8 cœurs en lançant plusieurs processus d’importation simultanément.
Lorsque ces tâches sont actuellement en attente et tournent en boucle, je ne vois généralement pas la charge moyenne dépasser 2-3, ce qui signifie qu’elles n’utilisent même pas toute la capacité CPU disponible.
Le vacuum complet est quelque chose que j’ai vu aider après des migrations massives ; je suis curieux de voir s’il a un impact.
Il s’exécute actuellement.
Concernant la requête percent_rank qui semble faire partie du travail hebdomadaire, à quoi ressemble le EXPLAIN pour votre grande instance ? Mon instance indique un coût de requête supérieur à 8 millions pour celle-ci, ce qui semble un peu effrayant.
Avez-vous des recommandations pour les valeurs de réglage de PostgreSQL dans app.yml ? Pour le moment, j’utilise :
shared_buffers : 16 Go
work_mem : 512 Mo
Le VACUUM FULL s’est terminé, mais il ne semble pas avoir fait de différence sur les performances de la requête. La requête du badge semble toujours devoir s’exécuter pendant des heures et des heures à moins que j’ajoute la clause ORDER BY, et la requête percent_rank tourne depuis deux heures sans se terminer. Nous devrons probablement modifier la définition SQL du badge « First Quote », puis je devrai examiner ce qui peut être fait pour corriger la requête percent_rank.
Avez-vous des suggestions pour la requête percent_rank basées sur ce 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)
Vous pourriez essayer de modifier la limite ; peut-être qu’avec une limite de 1000, cela sera suffisamment rapide pour vous.
Changer la limite ne semble pas modifier beaucoup le plan de requête (coûts ou autres). Le problème semble être que la requête doit trier l’intégralité de la table posts (qui, dans notre cas, contient environ 26,5 millions de lignes) avant de pouvoir exécuter l’opération. Il pourrait y avoir une opportunité d’ajouter un index ici. Je ne vois pas la colonne score incluse dans l’un des index de la table posts pour le moment.
Le classement est effectué par sujet, il ne classe pas l’ensemble complet.
Vous pourriez éventuellement filtrer par identifiants de sujet … WHERE topic_id < 1000 … 2000 … 10000 et ainsi de suite… Probablement qu’une fois la mise à jour initiale terminée, cela s’exécutera plus rapidement.