أعتقد أن كل مشروع تقريبًا يستخدم Ruby on Rails و Postgres كسلاح رئيسي على الواجهة الخلفية في صراع مستمر بين سرعة التطوير وقراءة / الحفاظ على الكود وسرعة المشروع في الإنتاج. سأخبرك عن تجربتي في الموازنة بين هذه الحيتان الثلاثة في حالة عانت فيها القراءة وسرعة العمل عند المدخل ، وفي النهاية اتضح أن يفعل ما حاول العديد من المهندسين الموهوبين القيام به قبلي دون جدوى.
ستأخذ القصة كلها عدة أجزاء. هذا هو السؤال الأول حيث سأتحدث عن ما هو PMDSC لتحسين استعلامات SQL ، ومشاركة الأدوات المفيدة لقياس أداء الاستعلام في postgres ، وتذكيرني بورقة خداع قديمة مفيدة لا تزال ذات صلة.
الآن ، بعد مرور بعض الوقت ، "بعد فوات الأوان" أفهم أنه عند مدخل هذه الحالة لم أتوقع على الإطلاق أن أنجح. لذلك ، ستكون هذه المشاركة مفيدة بدلاً من الجرأة وليس المطورين الأكثر خبرة من كبار السن الذين رأوا سككًا باستخدام SQL عارية.
ادخال البيانات
نحن في Appbooster نشجع تطبيقات الجوال. لتقديم الفرضيات بسهولة واختبارها ، نقوم بتطوير العديد من تطبيقاتنا. الواجهة الخلفية لمعظمها هي Rails API و Postgresql.
تم تطوير بطل هذا المنشور منذ نهاية عام 2013 - ثم تم إصدار القضبان 4.1.0.beta1 للتو. منذ ذلك الحين ، نما المشروع ليصبح تطبيق ويب محمل بالكامل يعمل على خوادم متعددة في Amazon EC2 مع نسخة قاعدة بيانات منفصلة في Amazon RDS (db.t3.xlarge مع 4 vCPUs و 16 GB RAM). تصل الأحمال القصوى إلى 25 ألف دورة في الدقيقة ، ومتوسط الحمل اليومي 8-10 ألف دورة في الدقيقة.
بدأت هذه القصة بمثيل قاعدة بيانات ، أو بالأحرى ، برصيدها الائتماني.
كيف يعمل مثيل Postgres من نوع "t" في Amazon RDS: إذا كانت قاعدة بياناتك تعمل بمتوسط استهلاك وحدة المعالجة المركزية أقل من قيمة معينة ، فعندئذٍ تراكم أرصدة على حسابك ، والتي يمكن للمثيل أن ينفقها على استهلاك وحدة المعالجة المركزية خلال ساعات التحميل المرتفعة - وهذا يوفر عليك زيادة في الدفع لسعة الخادم والتعامل مع الحمل المرتفع. يمكن العثور على مزيد من التفاصيل حول المبلغ الذي يدفعونه باستخدام AWS ومقدارهم في مقالة CTO الخاصة بنا .
تم استنزاف رصيد القروض عند نقطة معينة. لبعض الوقت ، لم يتم إعطاء هذا أهمية كبيرة ، لأن رصيد القروض يمكن تجديده بالمال - يكلفنا حوالي 20 دولارًا في الشهر ، وهو أمر لا يمكن ملاحظته للغاية بالنسبة للتكلفة الإجمالية لاستئجار القوة الحاسوبية. في تطوير المنتج ، من المعتاد الاهتمام في المقام الأول بالمهام التي صيغت من متطلبات العمل. يتناسب الاستهلاك المتزايد لوحدة المعالجة المركزية لخادم قاعدة البيانات مع الدين التقني ويقابله التكلفة الصغيرة لشراء رصيد ائتماني.
في أحد الأيام الجميلة ، كتبت في الملخص اليومي أنني تعبت جدًا من إخماد "الحرائق" التي ظهرت بشكل دوري في أجزاء مختلفة من المشروع. إذا استمر ذلك ، فإن المطور المحترق سيخصص وقتًا لمهام العمل. في نفس اليوم ، ذهبت إلى مدير المشروع الرئيسي ، وشرحت المحاذاة وطلبت بعض الوقت للتحقيق في أسباب الحرائق الدورية والإصلاح. بعد أن تلقيت الضوء الأخضر ، بدأت في جمع البيانات من أنظمة المراقبة المختلفة.
نستخدم Newrelic لتتبع إجمالي وقت الاستجابة في اليوم. بدت الصورة كما يلي:
جزء من وقت الاستجابة الذي يستغرقه Postgres مظلل باللون الأصفر في الرسم البياني. كما ترى ، بلغ وقت الاستجابة أحيانًا 1000 مللي ثانية ، وفي معظم الأحيان كانت قاعدة البيانات هي التي فكرت في الاستجابة. لذلك تحتاج إلى إلقاء نظرة على ما يحدث مع استعلامات SQL.
PMDSC هي ممارسة بسيطة ومباشرة لأي مهمة تحسين SQL مملة
العبها!
قيسها، قم بقياسها!
ارسمها!
افترض ذلك!
افحصها!
العبها!
ولعل أهم جزء من الممارسة برمتها. عندما يقول أحد الأشخاص عبارة "تحسين استعلامات SQL" - فإنه يتسبب في نوبة من التثاؤب والملل في الغالبية العظمى من الناس. عندما تقول "التحقيق البوليسي والبحث عن الأشرار الخطرين" - يجذبك أكثر ويضعك في مزاج مناسب. لذا من المهم الدخول في اللعبة. لقد استمتعت بلعب المحقق. تخيلت أن مشاكل قاعدة البيانات هي إما المجرمين الخطرين أو الأمراض النادرة. وتخيل نفسه في دور شيرلوك هولمز ، اللفتنانت كولومبو أو دكتور هاوس. اختر بطلا لذوقك واذهب!
قيسها، قم بقياسها!
لتحليل إحصائيات الطلبات ، قمت بتثبيت PgHero . هذه طريقة ملائمة جدًا لقراءة البيانات من ملحق pg_stat_statements Postgres. انتقل إلى / الاستعلامات وانظر إلى إحصائيات جميع الاستعلامات خلال الـ 24 ساعة الماضية. فرز الاستعلامات افتراضيًا وفقًا لعمود إجمالي الوقت - نسبة إجمالي الوقت الذي تعالج فيه قاعدة البيانات الاستعلام - مصدرًا قيمًا في العثور على المشتبه بهم. متوسط الوقت - عدد الطلبات التي يتم تنفيذها في المتوسط. المكالمات - عدد الطلبات التي تم إجراؤها خلال الوقت المحدد. تعتبر PgHero أن الطلبات بطيئة إذا تم تنفيذها أكثر من 100 مرة يوميًا واستغرقت أكثر من 20 مللي ثانية في المتوسط. قائمة الاستعلامات البطيئة في الصفحة الأولى ، مباشرة بعد قائمة الفهارس المكررة.
نأخذ أول واحد في القائمة ونلقي نظرة على تفاصيل الاستعلام ، يمكنك أن تراه على الفور يشرح التحليل. إذا كان وقت التخطيط أقل بكثير من وقت التنفيذ ، فهناك خطأ ما في هذا الطلب ونركز اهتمامنا على هذا المشتبه فيه.
لدى PgHero طريقة تصوره الخاصة ، لكني أحببت استخدام expl.depesz.com أكثر ، نسخ البيانات من شرح التحليل هناك.
أحد الاستعلامات المشتبه بها هو استخدام فحص الفهرس. يظهر التصور أن هذا المؤشر غير فعال وهو نقطة ضعف - مظللة باللون الأحمر. غرامة! فحصنا مسارات المشتبه فيه ووجدنا أدلة مهمة! العدالة أمر لا مفر منه!
ارسمها!
دعونا نرسم الكثير من البيانات المستخدمة في الجزء الإشكالي من الاستعلام. سيكون من المفيد المقارنة مع البيانات التي يغطيها الفهرس.
القليل من السياق. اختبرنا إحدى الطرق للحفاظ على الجمهور في التطبيق - شيء مثل اليانصيب الذي يمكنك من خلاله الفوز ببعض العملات المحلية. أنت تضع رهانًا ، خمن رقمًا من 0 إلى 100 وتأخذ الرهان بالكامل إذا كان رقمك أقرب إلى الرقم الذي استلمه مولد الرقم العشوائي. أطلقنا عليها اسم "أرينا" وأطلقنا على المسيرات "المعارك".
تحتوي قاعدة البيانات وقت التحقيق على حوالي خمسمائة ألف سجل من المعارك. في الجزء الإشكالي من الطلب ، نبحث عن معارك لا يتجاوز المعدل فيها رصيد المستخدم وحالة المعركة في انتظار اللاعبين. نرى أن تقاطع المجموعات (المظلل باللون البرتقالي) هو عدد قليل جدًا من السجلات.
يغطي الفهرس المستخدم في الجزء المشتبه فيه من الطلب جميع المعارك التي تم إنشاؤها في الحقل create_at. يعمل الطلب من خلال 505330 سجلًا يختار منها 40 ، ويزيل 505290. تبدو مسرفة للغاية.
افترض ذلك!
طرحنا فرضية. ما الذي سيساعد قاعدة البيانات في العثور على أربعين من أصل خمسمائة ألف سجل؟ دعنا نحاول إنشاء فهرس يغطي مجال المعدل ، فقط للمعارك مع حالة "انتظار اللاعبين" - مؤشر جزئي.
add_index :arena_battles, :bet,
where: "status = 'waiting_for_players'",
name: "index_arena_battles_on_bet_partial_status"
الفهرس الجزئي - موجود فقط لتلك السجلات التي تتطابق مع الشرط: حقل الحالة يساوي "انتظار اللاعبين" ويفهرس حقل المعدل - بالضبط ما هو موجود في حالة الاستعلام. من المفيد جدًا استخدام هذا الفهرس الخاص: فهو لا يستغرق سوى 40 كيلوبايت ولا يغطي المعارك التي تم لعبها بالفعل ولا نحتاج إلى الحصول على عينة. للمقارنة ، يأخذ فهرس index_arena_battles_on_created_at ، الذي استخدمه المشتبه فيه ، حوالي 40 ميجابايت ، والجدول مع المعارك حوالي 70 ميجابايت. يمكن إزالة هذا الفهرس بأمان إذا لم تستخدمه الاستعلامات الأخرى.
افحصها!
نطرح الترحيل باستخدام الفهرس الجديد في الإنتاج ونلاحظ كيف تغيرت استجابة نقطة النهاية بالمعارك.
يوضح الرسم البياني الوقت الذي بدأنا فيه في الترحيل. في مساء يوم 6 ديسمبر ، انخفض وقت الاستجابة بحوالي 10 مرات من ~ 500 مللي ثانية إلى ~ 50 مللي ثانية. حصل المشتبه به في المحكمة على وضع السجين وهو الآن في السجن. غرامة!
الهروب من السجن
بعد بضعة أيام ، أدركنا أننا سعداء مبكرًا. يبدو أن السجين وجد متواطئين ، وطور ونفذ خطة هروب.
في صباح يوم 11 ديسمبر ، قرر جدولة استعلام postgres أن استخدام فهرس جديد ومحلل لم يعد مربحًا له وبدأ في استخدام القديم مرة أخرى.
لقد عدنا إلى مرحلة افترض أنه! تجميع التشخيص التفريقي بروح الدكتور هاوس:
- قد يكون من الضروري تحسين إعدادات postgres ؛
- ربما ترقية postgres إلى إصدار أحدث بشروط ثانوية (9.6.11 -> 9.6.15) ؛
- وربما ، مرة أخرى ، دراسة بعناية أي استفسار SQL يشكل القضبان؟
اختبرنا جميع الفرضيات الثلاث. هذا الأخير قادنا إلى درب شريك.
SELECT "arena_battles".*
FROM "arena_battles"
WHERE "arena_battles"."status" = 'waiting_for_players'
AND (arena_battles.bet <= 98.13)
AND (NOT EXISTS (
SELECT 1 FROM arena_participations
WHERE arena_battle_id = arena_battles.id
AND (arena_profile_id = 46809)
))
ORDER BY "arena_battles"."created_at" ASC
LIMIT 10 OFFSET 0دعنا نسير عبر SQL معًا. نختار جميع ساحات المعارك من طاولة المعركة التي تساوي وضعها "انتظار اللاعبين" ويكون المعدل أقل من أو يساوي رقمًا معينًا. كل شيء واضح حتى الآن. المصطلح التالي في الحالة يبدو مخيفًا.
NOT EXISTS (
SELECT 1 FROM arena_participations
WHERE arena_battle_id = arena_battles.id
AND (arena_profile_id = 46809)
)نحن نبحث عن نتيجة استعلام فرعي غير موجود. احصل على الحقل الأول من طاولة المشاركة في المعركة ، حيث يتطابق معرف المعركة مع الملف الشخصي للمشارك الذي ينتمي إليه لاعبنا. سأحاول رسم المجموعة الموضحة في الاستعلام الفرعي.
من الصعب الفهم ، ولكن في النهاية ، مع هذا الاستعلام الفرعي ، حاولنا استبعاد تلك المعارك التي يشارك فيها اللاعب بالفعل. ننظر إلى الشرح العام للاستعلام ونرى وقت التخطيط: 0.180 مللي ثانية ، وقت التنفيذ: 12.119 مللي ثانية. وجدنا شريكا!
حان الوقت لورقة الغش المفضلة لدي ، والتي كانت متاحة عبر الإنترنت منذ عام 2008. ها هو:
نعم! بمجرد أن يصادف الاستعلام شيئًا يجب أن يستبعد عددًا معينًا من السجلات استنادًا إلى بيانات من جدول آخر ، يجب أن ينبثق هذا الميم ذو اللحية والتجعيد في الذاكرة.
في الواقع ، هذا ما نحتاجه:
احفظ هذه الصورة لنفسك ، أو الأفضل من ذلك ، اطبعها وعلقها في عدة أماكن في المكتب.
نعيد كتابة الاستعلام الفرعي إلى LEFT JOIN حيث B.key IS NULL نحصل على:
SELECT "arena_battles".*
FROM "arena_battles"
LEFT JOIN arena_participations
ON arena_participations.arena_battle_id = arena_battles.id
AND (arena_participations.arena_profile_id = 46809)
WHERE "arena_battles"."status" = 'waiting_for_players'
AND (arena_battles.bet <= 98.13)
AND (arena_participations.id IS NULL)
ORDER BY "arena_battles"."created_at" ASC
LIMIT 10 OFFSET 0يعمل الاستعلام المصحح عبر جدولين في وقت واحد. أضفنا جدولًا يحتوي على سجلات مشاركة المستخدم في المعارك على "اليسار" وقمنا بإضافة شرط عدم وجود معرف المشاركة. دعنا نرى الشرح يحلل الاستعلام المستلم: وقت التخطيط: 0.185 مللي ثانية ، وقت التنفيذ: 0.337 مللي ثانية. غرامة! الآن لن يتردد مخطط الاستعلام في أنه يجب أن يستخدم الفهرس الجزئي ، ولكنه سيستخدم الخيار الأسرع. وحكم على السجين الهارب وشريكه بالسجن مدى الحياة في مؤسسة نظام صارمة. سيكون من الصعب عليهم الفرار.
الملخص موجز.
- استخدم Newrelic أو خدمة أخرى مماثلة للعثور على العملاء المحتملين. لقد أدركنا أن المشكلة هي بالضبط في استعلامات قاعدة البيانات.
- استخدم ممارسة PMDSC - وهي تعمل على أي حال وجذابة للغاية.
- استخدم PgHero للعثور على المشتبه فيهم والتحقيق في الأدلة في إحصائيات استعلام SQL.
- استخدم expl.depesz.com - من السهل قراءة شرح استفسارات التحليل هناك.
- حاول رسم الكثير من البيانات عندما لا تعرف بالضبط ما يفعله الطلب.
- فكر في الرجل القوي مع تجعيد الشعر في جميع أنحاء رأسه عندما ترى استعلامًا فرعيًا يبحث عن شيء غير موجود في جدول آخر.
- لعب المخبر ، قد تحصل حتى على شارة.