كيف تنمو 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
هذه هي أصعب الاستعلامات التي تستغرق معظم الوقت (الوقت الإجمالي). هذه هي الاستعلامات التي يتم استدعاؤها في كثير من الأحيان أو الاستعلامات التي تستغرق وقتًا طويلاً للغاية (تم تحسين الاستعلامات الطويلة والمتكررة حتى في التكرارات الأولى للنضال من أجل السرعة). نتيجة لذلك ، يقضي الخادم معظم الوقت في التنفيذ. علاوة على ذلك ، من المهم فصل الطلبات العليا حسب إجمالي وقت التنفيذ وبشكل منفصل بوقت الإدخال / الإخراج. تختلف طرق تحسين مثل هذه الاستعلامات قليلاً.
الممارسة المعتادة لجميع الشركات هي العمل مع طلبات TOP. هناك القليل منها ، يمكن أن يؤدي تحسين طلب واحد حتى إلى توفير 5-10٪ من الموارد. ومع ذلك ، كلما كبر المشروع ، يصبح تحسين استعلامات TOP مهمة غير تافهة بشكل متزايد. لقد تم بالفعل وضع جميع الأساليب البسيطة ، وحتى الطلب الأكثر صعوبة "يسلب" فقط "3-5٪ من الموارد. إذا استغرق إجمالي طلبات البحث TOP أقل من 30-40٪ من الوقت ، فمن الأرجح أنك بذلت بالفعل جهودًا لجعلها تعمل بسرعة وحان وقت الانتقال إلى تحسين الاستعلامات من المجموعة التالية.
يبقى أن نجيب على السؤال عن عدد أهم طلبات البحث التي يجب تضمينها في هذه المجموعة. عادةً ما يستغرق ما لا يقل عن 10 ، ولكن لا يزيد عن 20 عامًا. أحاول التأكد من أن وقت الأول والأخير في مجموعة TOP لا يختلف عن 10 مرات. أي إذا انخفض وقت تنفيذ الاستعلام بشكل حاد من مكان واحد إلى 10 ، فأنا أحصل على TOP-10 ، إذا كان الانخفاض أكثر سلاسة ، فعندئذ أزيد حجم المجموعة إلى 15 أو 20.
الفلاحون الأوسطون (متوسط)
هذه هي جميع الطلبات التي تذهب مباشرة بعد TOP ، باستثناء آخر 5-10 ٪. عادة ، في تحسين هذه الطلبات المحددة تكمن القدرة على زيادة أداء الخادم بشكل كبير. يمكن لهذه الاستعلامات أن "تزن" حتى 80٪. لكن حتى لو تجاوزت حصتها 50٪ ، فقد حان الوقت للنظر إليها عن كثب.
الذيل (ذيل)
كما قيل ، هذه الطلبات تذهب في النهاية وتستغرق 5-10 ٪ من الوقت. لا يمكنك نسيانها إلا إذا كنت لا تستخدم أدوات تحليل الاستعلام التلقائي ، ثم يمكن أن يكون تحسينها رخيصًا أيضًا.
كيف تقيم كل مجموعة؟
يمكنني استخدام استعلام SQL يساعد في إجراء مثل هذا التقييم لـ PostgreSQL (أنا متأكد من أنه بالنسبة للعديد من قواعد بيانات قواعد البيانات (DBMS) الأخرى ، يمكنك كتابة استعلام مماثل)
استعلام SQL لتقدير حجم مجموعات TOP-MEDIUM-TAILSELECT 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:
- إعادة تصميم الأنظمة ، على سبيل المثال ، معالجة منطق الإشعار على وسيط الرسائل بدلاً من استعلامات قاعدة البيانات الدورية
- إضافة أو تعديل الفهارس
- أعد كتابة استعلامات ORM في SQL خالص
- أعد كتابة منطق تحميل البيانات البطيئة
- التخزين المؤقت من خلال إزالة البيانات. على سبيل المثال ، لدينا رابط بين الجداول التسليم -> الفاتورة -> طلب -> طلب. وهذا يعني أن كل التسليم يرتبط بالتطبيق من خلال الجداول الأخرى. لكي لا نربط جميع الجداول في كل طلب ، قمنا بتكرار الرابط إلى التطبيق في جدول التسليم.
- التخزين المؤقت الجداول الثابتة مع الدلائل ونادراً ما تغيير الجداول في ذاكرة البرنامج.
في بعض الأحيان كانت التغييرات تجر على إعادة تصميم مثيرة للإعجاب ، لكنها أعطت 5-10 ٪ من تفريغ النظام وكانت مبررة. مع مرور الوقت ، أصبح العادم أقل وأقل ، وإعادة التصميم بحاجة إلى أكثر وأكثر خطورة.
ثم لفتنا الانتباه إلى المجموعة الثانية من الطلبات - مجموعة الفلاحين الأوسطين. لديها الكثير من الطلبات ويبدو أن الأمر سيستغرق الكثير من الوقت لتحليل المجموعة بأكملها. ومع ذلك ، اتضح أن معظم الاستعلامات كانت بسيطة للغاية للتحسين ، وتكررت العديد من المشكلات عشرات المرات في أشكال مختلفة. فيما يلي أمثلة لبعض التحسينات النموذجية التي طبقناها على عشرات الاستعلامات المماثلة ، وكل مجموعة من الاستعلامات المحسّنة قامت بإلغاء تحميل قاعدة البيانات بنسبة 3-5٪.
- بدلاً من التحقق من وجود سجلات باستخدام COUNT ومسح كامل للجدول ، بدأ استخدام EXISTS.
- لقد تخلصنا من DISTINCT (لا توجد وصفة عامة ، لكن في بعض الأحيان يمكنك التخلص منها بسهولة عن طريق تسريع الطلب 10-100 مرة).
على سبيل المثال ، بدلاً من الاستعلام لتحديد كافة برامج التشغيل على جدول تسليم كبير (التسليم)
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 مللي ثانية ، والذي تم تضمينه في المائة من أكثر الاستعلامات أثقلًا وفي إجمالي وقت تحميل قاعدة البيانات استغرق الأمر مئات المئات من المئة. لكن بتطبيق نفس الوصفة على مجموعة من الطلبات المماثلة ، فقد عدنا إلى الوراء عدة بالمائة. حتى لا نضيع الوقت في عرض مئات الاستعلامات يدويًا ، كتبنا العديد من البرامج النصية البسيطة التي ، باستخدام تعبيرات منتظمة ، وجدت استعلامات مشابهة. نتيجة لذلك ، أتاح لنا البحث التلقائي عن مجموعات الاستعلام تحسين أدائنا من خلال بذل جهود متواضعة.
نتيجة لذلك ، نحن نعمل على نفس الجهاز لمدة ثلاث سنوات حتى الآن. يبلغ متوسط الحمل اليومي حوالي 30٪ ، حيث يصل إلى 70٪. زاد عدد الطلبات وكذلك عدد المستخدمين بنحو 10 مرات. وكل هذا بفضل المراقبة المستمرة لهذه المجموعات ذاتها من استعلامات TOP-MEDIUM. بمجرد ظهور طلب جديد في المجموعة TOP ، نحللها على الفور ونحاول تسريعها. نراجع مجموعة MEDIUM مرة واحدة في الأسبوع باستخدام نصوص تحليل الاستعلام. إذا صادفت طلبات جديدة عرفناها بالفعل كيفية تحسينها ، فسنغيرها بسرعة. في بعض الأحيان ، نجد طرق تحسين جديدة يمكن تطبيقها على العديد من الاستعلامات دفعة واحدة.
وفقًا لتوقعاتنا ، سيتحمل الخادم الحالي زيادة في عدد المستخدمين بمقدار 3-5 مرات. صحيح ، لدينا بطاقة رابحة واحدة في الغلاف ؛ ما زلنا لم نترجم استفسارات SELECT إلى المرآة ، على النحو الموصى به. لكننا لا نفعل ذلك بوعي ، لأننا نريد أولاً استنفاد إمكانيات التحسين "الذكي" بالكامل قبل تشغيل "المدفعية الثقيلة".
إلقاء نظرة فاحصة على العمل المنجز قد يوحي باستخدام القياس الرأسي. اشترِ خادمًا أقوى ، بدلاً من إضاعة وقت المتخصصين. قد لا يكلف الخادم الكثير ، خاصة وأن حدود القياس الرأسي لم تستنفد بعد. ومع ذلك ، زاد عدد الطلبات فقط 10 مرات. لعدة سنوات ، زادت وظائف النظام والآن هناك المزيد من أنواع الطلبات. يتم تنفيذ الوظيفة التي كانت بسبب التخزين المؤقت عن طريق عدد أقل من الطلبات ، علاوة على ذلك ، طلبات أكثر كفاءة. حتى تتمكن من مضاعفة 5 بأمان للحصول على معامل التسارع الحقيقي. لذلك ، وفقًا للتقديرات الأكثر تحفظًا ، يمكننا القول أن التسارع كان 50 مرة أو أكثر. هز الخادم عموديا 50 مرة سيكلف أكثر. لا سيما بالنظر إلى أنه بمجرد تنفيذ التحسين طوال الوقت ، يتم إصدار فاتورة لخادم مستأجر كل شهر.