لدي عميلان من Sidekiq يبدوان أنهما يستغرقان وقتًا طويلاً للتنفيذ. يبدو أن العملية الإجمالية هي نفسها، لكن هناك عميلين يظهران في حالة RUNNING. هذان العميلان يعملان منذ 6 ساعات الآن، وPostgres يتوقف عند محاولة تنفيذ 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
ثم يتم ربطها بجدول user_badges باستخدام LEFT JOIN، والذي يحتوي على 84,000 صف. يبدو أن شيئًا ما في الشرط الأخير WHERE ub.badge_id = 15 AND q.user_id IS NULL يتسبب في تعطل هذا الاستعلام. إذا تركت شرط WHERE، فسيتم تنفيذه في وقت معقول (حوالي 20 ثانية)، ولكن إذا قمت بتضمين حتى مجرد WHERE ub.badge_id = 15, فلن أستطيع حتى تنفيذ أمر EXPLAIN على هذا الاستعلام في وقت معقول. أمر EXPLAIN معلق منذ عدة دقائق دون أي نتائج. التنفيذ الفعلي للاستعلام الكامل يعمل منذ ساعات. هل هناك أي شيء يمكننا فعله لتحسين هذا الاستعلام؟
من خلال قراءة المواضيع هنا في ميتا منذ الليلة الماضية، يبدو أنه لا ينبغي أن يستمر أي عمل لأكثر من 8 ساعات، خاصةً لقاعدة بيانات ضخمة الحجم.
لكنني لست متأكدًا مما يمكننا فعله المزيد لتحسين ذلك.
من الغريب بالنسبة لي أننا لا نستطيع الحصول على أي شيء من أمر EXPLAIN لأنه يتعطل في كل مرة.
بالنظر إلى هذا الملف: discourse/app/services/badge_granter.rb at main · discourse/discourse · GitHub
هناك الكود التالي الذي ينفذ الاستعلام الذي علق حاليًا. إذا قمت بتغيير الانضمام الأول من LEFT JOIN إلى INNER JOIN، فإن الاستعلام ينفذ فورًا. هل هناك سبب يجعل هذا الانضمام يجب أن يكون من نوع 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
هل من الممكن تسريع استعلامات هذه الشارات؟
استعلام آخر يبدو سيئًا هو هذا، أعتقد أنه من مهمة التنظيف الأسبوعية:
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)
تم تخصيص 8 أنوية معالج و 32 جيجابايت من الذاكرة العشوائية للآلة الافتراضية التي تشغل Discourse.
أعتقد أنني اكتشفت المشكلة في الاستعلام الأول، أو على الأقل وجدت طريقة لإرشاد مخطط الاستعلام لاتخاذ القرار الصحيح. إليك الاستعلام الذي لم يكتمل خلال أكثر من 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 عدة مرات. يجب أن تكون الإحصائيات صحيحة، لكن يبدو أن النظام يختار بشكل سيئ عبر إعادة البناء المتعددة، وضبطات تحسين PostgreSQL، وعمليات التفريغ.
نحن ندير آلات افتراضية أخرى على جهاز المضيف هذا، لكن لدينا موارد متاحة حاليًا، لذا قمت ببناء نظام هنا لاختبار Discourse.
من مثيلنا الكبير، عند النظر إلى: /sidekiq/scheduler
و
هل قمت بتشغيل FULL VACUUM؟
خادم قاعدة البيانات لدينا متشابه تقريبًا من حيث أداء الأجهزة مع خادمكم (على الرغم من أن لدينا سرعة إدخال/إخراج أسرع نظرًا لأننا نمتلك مصفوفة RAID أكبر). ومع ذلك، لا نستخدم التشغيل الافتراضي على الإطلاق. وهذا فرق كبير واحد.
لم أفعل ذلك. يمكنني تجربته ورؤية ما إذا كان السلوك مختلفًا.
أنا متأكد من أن هناك بعض فقدان الأداء بسبب التشغيل في آلة افتراضية، لكن لا يوجد شيء يستهلك موارد العتاد بشكل كبير. عندما قمت بتشغيل عملية الاستيراد لإدخال جميع بياناتنا من برنامجنا الآخر، تمكّنت من تحقيق استخدام بنسبة 60-70% من جميع الأنوية الثمانية بتشغيل عمليات استيراد متعددة في وقت واحد.
أما الآن، عندما تكون هذه المهام في حالة انتظار ودوران، فإن متوسط الحمل لا يتجاوز عادةً 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 بناءً على مخطط التفسير هذا؟
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 سيكون هذا سريعًا بما يكفي لك.
يبدو أن تغيير الحد لا يغير خطة الاستعلام كثيرًا (من حيث التكلفة أو غير ذلك). يبدو أن المشكلة تكمن في أن الاستعلام يجب أن يقوم بفرز جدول المنشورات بالكامل (الذي يحتوي في حالتنا على حوالي 26.5 مليون صف) قبل أن يتمكن من تنفيذ العملية. قد تكون هناك فرصة لإنشاء فهرس هنا. لا أرى عمود score مدرجًا في أي من الفهارس على جدول المنشورات حاليًا.
الترتيب يتم حسب الموضوع، ولا يشمل الترتيب المجموعة بأكملها.
يمكنك ربما استخدام شرط على معرفات المواضيع … WHERE topic_id < 1000 … 2000 … 10000 وهكذا… من المرجح أن هذا الأمر سيعمل بسرعة أكبر بمجرد الانتهاء من التحديث الأولي.