تحسين استعلامات قاعدة البيانات باستخدام مثال خدمة B2B للمنشئين

كيف يمكن زيادة عدد الاستعلامات إلى قاعدة البيانات بمقدار 10 أضعاف دون الانتقال إلى خادم أكثر إنتاجية والحفاظ على وظائف النظام؟ سأخبرك كيف تعاملنا مع الانخفاض في أداء قاعدة البيانات الخاصة بنا، وكيف قمنا بتحسين استعلامات SQL لخدمة أكبر عدد ممكن من المستخدمين وعدم زيادة تكلفة موارد الحوسبة.

أقدم خدمة لإدارة العمليات التجارية في شركات المقاولات. حوالي 3 آلاف شركة تعمل معنا. يعمل أكثر من 10 آلاف شخص مع نظامنا يوميًا لمدة 4-10 ساعات. إنه يحل مشاكل التخطيط المختلفة والإخطار والتحذير والتحقق من الصحة... نحن نستخدم PostgreSQL 9.6. لدينا حوالي 300 جدول في قاعدة البيانات ويتم تلقي ما يصل إلى 200 مليون استفسار (10 آلاف استفسار مختلف) يوميًا. في المتوسط، لدينا 3-4 آلاف طلب في الثانية، وفي اللحظات الأكثر نشاطًا أكثر من 10 آلاف طلب في الثانية. معظم الاستعلامات هي OLAP. هناك عدد أقل بكثير من الإضافات والتعديلات والحذف، مما يعني أن حمل OLTP خفيف نسبيًا. لقد قدمت كل هذه الأرقام حتى تتمكن من تقييم حجم مشروعنا وفهم مدى فائدة تجربتنا بالنسبة لك.

صورة واحدة. غنائي

عندما بدأنا التطوير، لم نفكر حقًا في نوع الحمل الذي سيقع على قاعدة البيانات وماذا سنفعل إذا توقف الخادم عن السحب. عند تصميم قاعدة البيانات، اتبعنا التوصيات العامة وحاولنا ألا نطلق النار على أنفسنا، بل تجاوزنا النصائح العامة مثل "لا تستخدم النمط" قيم سمات الكيان لم ندخل لقد صممنا بناءً على مبادئ التطبيع، وتجنب تكرار البيانات ولم نهتم بتسريع استعلامات معينة. بمجرد وصول المستخدمين الأوائل، واجهنا مشكلة في الأداء. وكالعادة، لم نكن مستعدين على الإطلاق لهذا الأمر. تبين أن المشاكل الأولى بسيطة. كقاعدة عامة، تم حل كل شيء عن طريق إضافة فهرس جديد. ولكن جاء وقت توقفت فيه التصحيحات البسيطة عن العمل. بعد أن أدركنا أننا نفتقر إلى الخبرة وأنه أصبح من الصعب علينا بشكل متزايد فهم سبب المشاكل، قمنا بتعيين متخصصين ساعدونا في إعداد الخادم بشكل صحيح، وربط المراقبة، وأظهروا لنا المكان الذي يجب أن نبحث عنه الإحصاء.

الصورة الثانية. إحصائية

لذلك لدينا حوالي 10 آلاف استعلام مختلف يتم تنفيذه على قاعدة بياناتنا يوميًا. من بين هذه الـ 10 آلاف، هناك وحوش يتم تنفيذها 2-3 مليون مرة بمتوسط ​​وقت تنفيذ يبلغ 0.1-0.3 مللي ثانية، وهناك استعلامات بمتوسط ​​وقت تنفيذ يبلغ 30 ثانية يتم استدعاؤها 100 مرة في اليوم.

لم يكن من الممكن تحسين جميع الاستعلامات البالغ عددها 10 آلاف، لذلك قررنا معرفة أين نوجه جهودنا لتحسين أداء قاعدة البيانات بشكل صحيح. بعد عدة تكرارات، بدأنا بتقسيم الطلبات إلى أنواع.

أعلى الطلبات

هذه هي الاستعلامات الأثقل التي تستغرق معظم الوقت (الوقت الإجمالي). هذه هي الاستعلامات التي يتم استدعاؤها كثيرًا أو الاستعلامات التي يستغرق تنفيذها وقتًا طويلاً جدًا (تم تحسين الاستعلامات الطويلة والمتكررة في التكرارات الأولى للكفاح من أجل السرعة). ونتيجة لذلك، يقضي الخادم معظم الوقت في تنفيذها. علاوة على ذلك، من المهم فصل الطلبات العليا حسب إجمالي وقت التنفيذ وبشكل منفصل حسب وقت الإدخال/الإخراج. تختلف طرق تحسين مثل هذه الاستعلامات قليلاً.

الممارسة المعتادة لجميع الشركات هي العمل مع طلبات TOP. هناك عدد قليل منها، ويمكن أن يؤدي تحسين استعلام واحد إلى تحرير ما بين 5 إلى 10% من الموارد. ومع ذلك، مع نضوج المشروع، يصبح تحسين الاستعلامات TOP مهمة غير تافهة على نحو متزايد. لقد تم بالفعل تطوير جميع الأساليب البسيطة، ويستغرق الطلب "الثقيل" 3-5% فقط من الموارد. إذا كانت استعلامات TOP في المجموع تستغرق أقل من 30-40% من الوقت، فمن المرجح أنك بذلت بالفعل جهودًا لجعلها تعمل بسرعة وحان الوقت للانتقال إلى تحسين الاستعلامات من المجموعة التالية.
يبقى الإجابة على سؤال حول عدد أهم الاستعلامات التي يجب تضمينها في هذه المجموعة. عادة ما آخذ ما لا يقل عن 10، ولكن ليس أكثر من 20. أحاول التأكد من أن وقت الأول والأخير في مجموعة TOP يختلف بما لا يزيد عن 10 مرات. وهذا يعني أنه إذا انخفض وقت تنفيذ الاستعلام بشكل حاد من المركز الأول إلى المركز العاشر، فأنا آخذ TOP-1، وإذا كان الانخفاض أكثر تدريجيًا، أقوم بزيادة حجم المجموعة إلى 10 أو 10.

فلاحون وسط

هذه كلها طلبات تأتي مباشرة بعد TOP، باستثناء آخر 5-10٪. عادة، في تحسين هذه الاستعلامات تكمن الفرصة لزيادة أداء الخادم بشكل كبير. يمكن أن يصل وزن هذه الطلبات إلى 80%. ولكن حتى لو تجاوزت حصتهم 50%، فقد حان الوقت للنظر إليهم بعناية أكبر.

ذيل

كما ذكرنا سابقًا، تأتي هذه الاستعلامات في النهاية وتستغرق ما بين 5 إلى 10% من الوقت. لا يمكنك نسيانها إلا إذا كنت لا تستخدم أدوات تحليل الاستعلامات التلقائية، فقد يكون تحسينها رخيصًا أيضًا.

كيفية تقييم كل مجموعة؟

أستخدم استعلام SQL الذي يساعد في إجراء مثل هذا التقييم لـ PostgreSQL (أنا متأكد من إمكانية كتابة استعلام مماثل للعديد من أنظمة إدارة قواعد البيانات الأخرى)

استعلام SQL لتقدير حجم مجموعات TOP-MEDIUM-TAIL

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

نتيجة الاستعلام هي ثلاثة أعمدة، يحتوي كل منها على النسبة المئوية للوقت المستغرق لمعالجة الاستعلامات من هذه المجموعة. يوجد داخل الطلب رقمان (في حالتي 20 و 800) يفصلان الطلبات من مجموعة عن أخرى.

هذه هي الطريقة التي تتم بها مقارنة حصص الطلبات تقريبًا في الوقت الذي بدأ فيه عمل التحسين والآن.

يوضح الرسم البياني أن حصة طلبات TOP قد انخفضت بشكل حاد، لكن "الفلاحين المتوسطين" زادوا.
في البداية، تضمنت طلبات TOP أخطاء فادحة. بمرور الوقت، اختفت أمراض الطفولة، وانخفضت حصة طلبات TOP، وكان لا بد من بذل المزيد والمزيد من الجهود لتسريع الطلبات الصعبة.

للحصول على نص الطلبات نستخدم الطلب التالي

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

فيما يلي قائمة بالأساليب الأكثر استخدامًا والتي ساعدتنا في تسريع أهم الاستعلامات:

• إعادة تصميم النظام، على سبيل المثال، إعادة صياغة منطق الإشعارات باستخدام وسيط الرسائل بدلاً من الاستعلامات الدورية لقاعدة البيانات
• إضافة أو تغيير الفهارس
• إعادة كتابة استعلامات ORM إلى SQL خالص
• إعادة كتابة منطق تحميل البيانات البطيء
• التخزين المؤقت من خلال إلغاء تطبيع البيانات. على سبيل المثال، لدينا جدول توصيل التسليم -> الفاتورة -> الطلب -> التطبيق. أي أن كل تسليم يرتبط بتطبيق من خلال جداول أخرى. لكي لا يتم ربط جميع الجداول في كل طلب، قمنا بتكرار رابط الطلب في جدول التسليم.
• تخزين الجداول الثابتة مؤقتًا باستخدام الكتب المرجعية ونادرًا ما يتم تغيير الجداول في ذاكرة البرنامج.

في بعض الأحيان كانت التغييرات بمثابة إعادة تصميم مثيرة للإعجاب، لكنها وفرت 5-10٪ من حمل النظام وكان لها ما يبررها. مع مرور الوقت، أصبح العادم أصغر وأصغر، وكان مطلوبا إعادة تصميم أكثر وأكثر جدية.

ثم وجهنا انتباهنا إلى المجموعة الثانية من الطلبات - مجموعة الفلاحين المتوسطين. هناك العديد من الاستفسارات فيه ويبدو أن تحليل المجموعة بأكملها سيستغرق الكثير من الوقت. ومع ذلك، تبين أن معظم الاستعلامات سهلة التحسين للغاية، وتم تكرار العديد من المشكلات عشرات المرات في أشكال مختلفة. فيما يلي أمثلة لبعض التحسينات النموذجية التي قمنا بتطبيقها على العشرات من الاستعلامات المشابهة وكل مجموعة من الاستعلامات المحسنة قامت بتفريغ قاعدة البيانات بنسبة 3-5%.

• بدلاً من التحقق من وجود السجلات باستخدام COUNT ومسح كامل للجدول، بدأ استخدام EXISTS
• تخلصت من DISTINCT (لا توجد وصفة عامة، لكن في بعض الأحيان يمكنك التخلص منها بسهولة عن طريق تسريع الطلب بمقدار 10-100 مرة).

  على سبيل المثال، بدلاً من الاستعلام لتحديد كافة برامج التشغيل من جدول كبير للتسليم (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  قدم استعلامًا على جدول صغير نسبيًا PERSON

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  يبدو أننا استخدمنا استعلامًا فرعيًا مرتبطًا، لكنه يعطي سرعة تزيد عن 10 مرات.

• في كثير من الحالات، تم التخلي عن COUNT تمامًا و
  استبدالها بحساب القيمة التقريبية
• بدلا من

  UPPER(s) LIKE JOHN%’ 
  

  استخدم

  s ILIKE “John%”
  

تم أحيانًا تسريع كل طلب محدد بمقدار 3 إلى 1000 مرة. على الرغم من الأداء المثير للإعجاب، بدا لنا في البداية أنه لا فائدة من تحسين استعلام يستغرق إكماله 10 مللي ثانية، وهو أحد أثقل مائة استعلام، ويستغرق أجزاء من المئات من المائة من إجمالي وقت تحميل قاعدة البيانات. ولكن من خلال تطبيق نفس الوصفة على مجموعة من الاستعلامات من نفس النوع، فقد استردنا نسبة مئوية قليلة. لكي لا نضيع الوقت في مراجعة مئات الاستعلامات يدويًا، قمنا بكتابة عدة نصوص برمجية بسيطة تستخدم التعبيرات العادية للعثور على استعلامات من نفس النوع. ونتيجة لذلك، أتاح لنا البحث التلقائي في مجموعات الاستعلامات إمكانية تحسين أدائنا بجهد متواضع.

ونتيجة لذلك، فإننا نعمل على نفس الجهاز لمدة ثلاث سنوات حتى الآن. يبلغ متوسط ​​\u30b\u70bالحمل اليومي حوالي 10٪، وفي الذروة يصل إلى XNUMX٪. وقد زاد عدد الطلبات وكذلك عدد المستخدمين بنحو XNUMX مرات. وكل هذا بفضل المراقبة المستمرة لهذه المجموعات نفسها من طلبات TOP-MEDIUM. بمجرد ظهور طلب جديد في المجموعة TOP، نقوم بتحليله على الفور ومحاولة تسريعه. نقوم بمراجعة المجموعة المتوسطة مرة واحدة في الأسبوع باستخدام البرامج النصية لتحليل الاستعلام. إذا صادفنا استعلامات جديدة نعرف بالفعل كيفية تحسينها، فإننا نغيرها بسرعة. في بعض الأحيان نجد طرق تحسين جديدة يمكن تطبيقها على عدة استعلامات في وقت واحد.

وفقا لتوقعاتنا، سيتحمل الخادم الحالي زيادة في عدد المستخدمين بمقدار 3-5 مرات أخرى. صحيح، لدينا ورقة أخرى في جعبتنا - ما زلنا لم ننقل استعلامات SELECT إلى المرآة، كما هو موصى به. لكننا لا نفعل ذلك بوعي، لأننا نريد أولا أن نستنفد تماما إمكانيات التحسين "الذكي" قبل تشغيل "المدفعية الثقيلة".
قد تقترح نظرة نقدية على العمل المنجز استخدام القياس الرأسي. قم بشراء خادم أكثر قوة بدلاً من إضاعة وقت المتخصصين. قد لا يكلف الخادم هذا القدر من التكلفة، خاصة أننا لم نستنفد بعد حدود القياس الرأسي. ومع ذلك، زاد عدد الطلبات 10 مرات فقط. على مدار عدة سنوات، زادت وظائف النظام وأصبح هناك الآن المزيد من أنواع الطلبات. بفضل التخزين المؤقت، يتم تنفيذ الوظيفة الموجودة في طلبات أقل وطلبات أكثر كفاءة. هذا يعني أنه يمكنك الضرب بأمان في 5 أخرى للحصول على معامل التسارع الحقيقي. لذلك، وفقا للتقديرات الأكثر تحفظا، يمكننا القول أن التسارع كان 50 مرة أو أكثر. سيكلف التأرجح العمودي للخادم 50 مرة أكثر. لا سيما أنه بمجرد إجراء التحسين، فإنه يعمل طوال الوقت، وتأتي فاتورة الخادم المستأجر كل شهر.

المصدر: www.habr.com