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

سواء كان الأمر يتعلق باكتشافهم مبكرًا، أو إيقافهم في مساراتهم، أو إعداد طريقة للتعامل معهم تلقائيًا، فإن هذا الدليل يلبي احتياجاتك.

ما هو الاستعلام طويل الأمد؟

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

الأسباب الجذرية للاستعلامات الطويلة الأمد

يمكن أن تتنوع أسباب الاستعلامات الطويلة الأمد:

• عدم وجود فهرسة مناسبة – بدون الفهرسة المناسبة، يجب على MySQL فحص الجدول بأكمله لاسترداد البيانات المطلوبة. هذه العملية غير فعالة إلى حد كبير، خاصة بالنسبة للطاولات الكبيرة، لأنها تستهلك الكثير من الوقت والموارد.

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

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

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

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

تحديد الاستعلامات طويلة الأمد

لإدارة الاستعلامات طويلة الأمد بشكل فعال، تحتاج أولاً إلى تحديدها. إليك بعض الطرق:

1. استخدام عرض قائمة العمليات

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

إظهار قائمة العمليات الكاملة؛

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

2. سجل الاستعلام البطيء

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

لبدء تشغيل سجل الاستعلام البطيء، ستحتاج إلى تعديل بعض الإعدادات في ملف تكوين MySQL (إما my.cnf أو my.ini):

• Slow_query_log - اضبط هذا على 1 لتمكين السجل.
• Slow_query_log_file – حدد مسار الملف حيث تريد حفظ السجل.
• Long_query_time - قم بتعيين الحد الأدنى لوقت التنفيذ (بالثواني) الذي يؤهل تسجيل الاستعلام. على سبيل المثال، سيؤدي تعيينه إلى "2" إلى تسجيل أي استعلامات يستغرق تنفيذها أكثر من ثانيتين.

3. مخطط الأداء

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

تأكد من تمكينه في تكوين MySQL الخاص بك عن طريق إضافة السطر التالي:

[mysqld]
مخطط الأداء = تشغيل

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

حدد EVENT_ID، SQL_TEXT، TIMER_WAIT/1000000000 AS 'المدة (بالثواني)'
من Performance_schema.events_statements_history_long
حيث TIMER_WAIT> 10000000000؛

يساعدك هذا الاستعلام في العثور على أي استعلامات تم تشغيلها لأكثر من 10 ثوانٍ. فهو يوفر لك تفاصيل مثل نص SQL ومدة تشغيل كل استعلام.

قتل الاستعلامات طويلة الأمد يدويًا

عندما تحدد استعلامًا يستغرق وقتًا طويلاً ويستهلك موارد نظامك، يكون لديك خيار إنهائه يدويًا. ويتم ذلك باستخدام الأمر KILL متبوعًا بمعرف العملية المحدد للاستعلام.

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

بمجرد تحديد استعلام به مشكلات وتسجيل معرف العملية الخاص به، يمكنك إنهاؤه باستخدام أمر KILL:

قتل [معرف العملية]؛

استبدل [معرف العملية] بالرقم الفعلي من إخراج إظهار قائمة العمليات.

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

كيفية أتمتة عملية قتل الاستعلامات طويلة الأمد

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

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

1. تمكين جدولة الأحداث

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

ضبط الحدث الشامل = تشغيل؛

2. قم بإنشاء حدث لإيقاف الاستعلامات طويلة الأمد

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

`إنشاء حدث kill_long_running_queries
في الجدول الزمني كل دقيقة واحدة - يحدد عدد مرات تشغيل الحدث
يفعل
يبدأ
إعلان القيام بالخطأ الافتراضي؛
أعلن proc_id INT؛ - متغير لتخزين معرف العملية لكل استعلام
قم بتعريف المؤشر cur1 لمعرف محدد من information_schema.processlist
WHERE Command = 'Query' AND Time > 60; -- قم بتغيير "60" إلى الحد الأقصى الخاص بك في ثوانٍ
أعلن عن معالج المتابعة لعدم العثور على المجموعة تم = TRUE؛

المنحنى المفتوح 1؛

read_loop: حلقة
جلب cur1 إلى proc_id;
إذا تم ذلك،
اترك read_loop؛
نهاية إذا؛
اقتل proc_id; - يقتل العملية المحددة بواسطة proc_id
حلقة النهاية؛

إغلاق المنحنى 1؛
انتهى;`

3. قم بإعداد الحد الأقصى لوقت تنفيذ الاستعلام في MySQL

يساعد التحكم في الحد الأقصى لوقت تنفيذ الاستعلام على منع تقييد قاعدة البيانات من خلال الاستعلامات طويلة الأمد بشكل مفرط. يتم ذلك باستخدام متغير النظام max_execution_time في MySQL 5.7.8 والإصدارات الأحدث عن طريق تعيين حد زمني للتنفيذ على مستوى النظام لجميع استعلامات SELECT للقراءة فقط:

ضبط الحد الأقصى لوقت التنفيذ العالمي = 2000؛

يعين هذا الحد الأقصى على 2000 مللي ثانية (2 ثانية)

تذكر أن هذا الإعداد لا ينطبق على الإجراءات أو الوظائف أو المشغلات المخزنة ويتم إعادة تعيينه إلى الوضع الافتراضي عند إعادة تشغيل الخادم ما لم تتم إضافته إلى ملف تكوين MySQL الخاص بك:

[mysqld]
max_execution_time = 2000

4. قم بإعداد الحد الأقصى لوقت البيان في MariaDB

يقدم MariaDB، على الرغم من تشعبه من MySQL، أسلوبًا مشابهًا ولكنه متميز لإدارة أوقات تنفيذ الاستعلام. بدءًا من MariaDB 10.1.1، يمكنك استخدام متغير النظام max_statement_time لهذا الغرض:

ضبط الحد الأقصى العالمي للوقت = 2؛

يؤدي هذا إلى تحديد وقت التنفيذ بثانيتين لجميع الاستعلامات.

للتكوين المستمر من خلال إعادة تشغيل الخادم، أضف هذا السطر إلى ملف تكوين MariaDB الخاص بك:

[mysqld]
max_statement_time = 2

تحديد ومعالجة الاستعلامات طويلة الأمد باستخدام تحليلات الاستعلام من Relem

تُحدث أداة تحليلات الاستعلام الخاصة بـ Releme ثورة في كيفية مراقبة أداء قاعدة بياناتك وتحسينها. فهو يجمع تلقائيًا معلومات تفصيلية عن أهم 100 استعلام، مما يوفر مقاييس أساسية مثل متوسط ​​وقت التنفيذ والتأثير الإجمالي لكل استعلام على الكفاءة التشغيلية لقاعدة البيانات الخاصة بك.

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