استكشاف أخطاء أداء الاستعلام وإصلاحها في قاعدة بيانات Azure ل MySQL - الخادم المرن

ينطبق على: قاعدة بيانات Azure ل MySQL - خادم واحد قاعدة بيانات Azure ل MySQL - خادم مرن

هام

قاعدة بيانات Azure لخادم MySQL الفردي على مسار الإيقاف. نوصي بشدة بالترقية إلى قاعدة بيانات Azure لخادم MySQL المرن. لمزيد من المعلومات حول الترحيل إلى خادم Azure Database for MySQL المرن، راجع ما الذي يحدث لقاعدة بيانات Azure لخادم MySQL الفردي؟

يمكن أن يتأثر أداء الاستعلام بعوامل متعددة، لذلك من المهم أولا النظر إلى نطاق الأعراض التي تواجهها في مثيل خادم Azure Database for MySQL المرن. على سبيل المثال، أداء الاستعلام بطيء ل:

• جميع الاستعلامات التي تعمل على مثيل الخادم المرن لقاعدة بيانات Azure ل MySQL؟
• مجموعة معينة من الاستعلامات؟
• استعلام محدد؟

ضع في اعتبارك أيضاً أن أي تغييرات حديثة في البنية أو البيانات الأساسية للجداول التي تستعلم عنها يمكن أن تؤثر على الأداء.

تمكين وظائف التسجيل

قبل تحليل الاستعلامات الفردية، تحتاج إلى تحديد معايير الاستعلام. باستخدام هذه المعلومات، يمكنك تنفيذ وظيفة التسجيل على خادم قاعدة البيانات لتتبع الاستعلامات التي تتجاوز الحد الذي تحدده استناداً إلى احتياجات التطبيق.

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

قبل أن تتمكن من البدء في تتبع الاستعلامات طويلة الأمد، تحتاج إلى تمكين slow_query_log المعلمة باستخدام مدخل Microsoft Azure أو Azure CLI. مع تمكين هذه المعلمة، يجب عليك أيضاً تكوين قيمة long_query_time المعلمة لتحديد عدد الثواني التي يمكن تشغيل الاستعلامات قبل تعريفها على أنها استعلامات "تشغيل بطيء". القيمة الافتراضية للمعلمة هي 10 ثوان، ولكن يمكنك ضبط القيمة لتلبية احتياجات اتفاقية مستوى الخدمة للتطبيق الخاص بك.

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

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

استرداد المعلومات من السجلات

تتوفر السجلات لمدة تصل إلى سبعة أيام من إنشائها. يمكنك سرد سجلات الاستعلام البطيئة وتنزيلها عبر مدخل Microsoft Azure أو Azure CLI. في مدخل Microsoft Azure، انتقل إلى الخادم الخاص بك، ضمن Monitoring، حدد Server logs، ثم حدد السهم لأسفل بجوار إدخال لتنزيل السجلات المقترنة بالتاريخ والوقت الذي تحقق فيه.

بالإضافة إلى ذلك، إذا تم دمج سجلات الاستعلام البطيئة مع سجلات Azure Monitor من خلال سجلات التشخيص، يمكنك تشغيل الاستعلامات في محرر لتحليلها بشكل أكبر:

AzureDiagnostics
| where Resource == '<your server name>'
| where Category == 'MySqlSlowLogs'
| project TimeGenerated, Resource , event_class_s, start_time_t , query_time_d, sql_text_s
| where query_time_d > 10

إشعار

لمزيد من الأمثلة لتبدأ في تشخيص سجلات الاستعلام البطيئة عبر سجلات التشخيص، راجع تحليل السجلات في سجلات Azure Monitor.

تصور اللقطة التالية عينة استعلام بطيء.

# Time: 2021-11-13T10:07:52.610719Z
# User@Host: root[root] @  [172.30.209.6]  Id: 735026
# Query_time: 25.314811  Lock_time: 0.000000 Rows_sent: 126  Rows_examined: 443308
use employees;
SET timestamp=1596448847;
select * from titles where DATE(from_date) > DATE('1994-04-05') AND title like '%senior%';;

لاحظ أن الاستعلام تم تشغيله في 26 ثانية، وفحص أكثر من 443 ألف صف، وأرجع 126 صفاً من النتائج.

عادة، يجب التركيز على الاستعلامات ذات القيم العالية Query_time و Rows_examined. ومع ذلك، إذا لاحظت استعلامات ذات Query_time عالية ولكن عدد قليل فقط من Rows_examined، فهذا غالباً ما يشير إلى وجود ازدحام مورد. بالنسبة لهذه الحالات، يجب عليك التحقق مما إذا كان هناك أي تقييد IO أو استخدام وحدة المعالجة المركزية.

جمع معلومات استعلام

بعد تحديد استعلام تشغيل بطيء محدد، يمكنك استخدام الأمر EXPLAIN وجمع المعلومات لجمع مزيد من التفاصيل.

للتحقق من خطة الاستعلام، قم بتشغيل الأمر التالي:

EXPLAIN <QUERY>

إشعار

لمزيد من المعلومات حول استخدام عبارات EXPLAIN، راجع كيفية استخدام EXPLAIN لملف تعريف أداء الاستعلام في قاعدة بيانات Azure لخادم MySQL المرن.

بالإضافة إلى إنشاء خطة EXPLAIN لاستعلام، يمكنك استخدام الأمر SHOW PROFILE، والذي يسمح لك بتشخيص تنفيذ العبارات التي تم تشغيلها داخل جلسة العمل الحالية.

لتمكين جمع معلومات ملف تعريف استعلام معين في جلسة عمل، قم بتشغيل المجموعة التالية من الأوامر:

SET profiling = 1;
<QUERY>;
SHOW PROFILES;
SHOW PROFILE FOR QUERY <X>;

إشعار

لا يتوفر جمع معلومات الاستعلامات الفردية إلا في جلسة عمل ولا يمكن تعريف العبارات التاريخية.

دعونا نلقي نظرة فاحصة على استخدام هذه الأوامر لملفات تعريف استعلام. أولاً، تمكين جمع المعلومات لجلسة العمل الحالية، قم بتشغيل SET PROFILING = 1 الأمر:

mysql> SET PROFILING = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

بعد ذلك، قم بتنفيذ استعلام دون المستوى الأمثل الذي يقوم بإجراء فحص كامل للجدول:

mysql> select * from sbtest8 where c like '%99098187165%';
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| id | k       | c                                                                                                                       | pad                                                         |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
| 10 | 5035785 | 81674956652-89815953173-84507133182-62502329576-99098187165-62672357237-37910808188-52047270287-89115790749-78840418590 | 91637025586-81807791530-84338237594-90990131533-07427691758 |
+----+---------+-------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------+
1 row in set (27.60 sec)

بعد ذلك، اعرض قائمة بجميع ملفات تعريف الاستعلام المتوفرة عن طريق تشغيل SHOW PROFILES الأمر:

mysql> SHOW PROFILES;
+----------+-------------+----------------------------------------------------+
| Query_ID | Duration    | Query                                              |
+----------+-------------+----------------------------------------------------+
|        1 | 27.59450000 | select * from sbtest8 where c like '%99098187165%' |
+----------+-------------+----------------------------------------------------+
1 row in set, 1 warning (0.00 sec)

وأخيراً، لعرض ملف التعريف للاستعلام 1، قم بتشغيل SHOW PROFILE FOR QUERY 1 الأمر.

mysql> SHOW PROFILE FOR QUERY 1;
+----------------------+-----------+
| Status               | Duration  |
+----------------------+-----------+
| starting             |  0.000102 |
| checking permissions |  0.000028 |
| Opening tables       |  0.000033 |
| init                 |  0.000035 |
| System lock          |  0.000018 |
| optimizing           |  0.000017 |
| statistics           |  0.000025 |
| preparing            |  0.000019 |
| executing            |  0.000011 |
| Sending data         | 27.594038 |
| end                  |  0.000041 |
| query end            |  0.000014 |
| closing tables       |  0.000013 |
| freeing items        |  0.000088 |
| cleaning up          |  0.000020 |
+----------------------+-----------+
15 rows in set, 1 warning (0.00 sec)

سرد الاستعلامات الأكثر استخداماً على خادم قاعدة البيانات

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

لسرد أهم 10 استعلامات تم تنفيذها مقابل مثيل خادم Azure Database for MySQL المرن، قم بتشغيل الاستعلام التالي:

SELECT digest_text AS normalized_query,
 count_star AS all_occurrences,
 Concat(Round(sum_timer_wait / 1000000000000, 3), ' s') AS total_time,
 Concat(Round(min_timer_wait / 1000000000000, 3), ' s') AS min_time,
 Concat(Round(max_timer_wait / 1000000000000, 3), ' s') AS max_time,
 Concat(Round(avg_timer_wait / 1000000000000, 3), ' s') AS avg_time,
 Concat(Round(sum_lock_time / 1000000000000, 3), ' s') AS total_locktime,
 sum_rows_affected AS sum_rows_changed,
 sum_rows_sent AS sum_rows_selected,
 sum_rows_examined AS sum_rows_scanned,
 sum_created_tmp_tables,
 sum_select_scan,
 sum_no_index_used,
 sum_no_good_index_used
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 10;

إشعار

استخدم هذا الاستعلام لقياس أهم الاستعلامات المنفذة في خادم قاعدة البيانات وتحديد ما إذا كان هناك تغيير في أهم الاستعلامات أو ما إذا كانت أي استعلامات موجودة في المعيار الأولي قد زادت في مدة التشغيل.

سرد الاستعلامات العشرة الأكثر تكلفة حسب إجمالي وقت التنفيذ

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

SELECT REPLACE(event_name, 'statement/sql/', '') AS statement, 
 count_star AS all_occurrences , 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency, 
 Concat(Round(avg_timer_wait / 1000000000000, 2), ' s') AS avg_latency, 
 Concat(Round(sum_lock_time / 1000000000000, 2), ' s') AS total_lock_time  , 
 sum_rows_affected AS sum_rows_changed, 
 sum_rows_sent AS  sum_rows_selected, 
 sum_rows_examined AS  sum_rows_scanned, 
 sum_created_tmp_tables,  sum_created_tmp_disk_tables, 
 IF(sum_created_tmp_tables = 0, 0, Concat( Truncate(sum_created_tmp_disk_tables / 
 sum_created_tmp_tables * 100, 0))) AS 
 tmp_disk_tables_percent, 
 sum_select_scan, 
 sum_no_index_used, 
 sum_no_good_index_used 
FROM performance_schema.events_statements_summary_global_by_event_name 
WHERE event_name LIKE 'statement/sql/%' 
 AND count_star > 0 
ORDER BY sum_timer_wait DESC 
LIMIT 10;

مراقبة مجموعة البيانات المهملة InnoDB

عند حظر مجموعة البيانات المهملة InnoDB أو تأخيرها، يمكن لقاعدة البيانات تطوير تأخر إزالة كبير يمكن أن يؤثر سلباً على استخدام التخزين وأداء الاستعلام.

يقيس طول قائمة محفوظات مقطع التراجع InnoDB (HLL) عدد سجلات التغيير المخزنة في سجل التراجع. تشير قيمة HLL المتزايدة إلى أن مؤشرات ترابط تجميع البيانات المهملة في InnoDB (مؤشرات ترابط المسح) لا تواكب حمل عمل الكتابة أو أن عملية الإزالة محظورة بواسطة استعلام أو معاملة طويلة الأمد.

يمكن أن يكون للتأخير المفرط في جمع البيانات المهملة عواقب وخيمة وسلبية:

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

ونتيجة لذلك، من المهم مراقبة قيم HLL وأنماطها واتجاهاتها.

البحث عن قيم HLL

يمكنك العثور على قيمة HLL عن طريق تشغيل الأمر show engine innodb status. سيتم سرد القيمة في الإخراج، ضمن عنوان TRANSACTIONS:

mysql> show engine innodb status\G 
*************************** 1. row *************************** 
 
(...) 
 
------------ 
TRANSACTIONS 
------------ 
Trx id counter 52685768 
Purge done for trx's n:o < 52680802 undo n:o < 0 state: running but idle 
History list length 2964300 
 
(...) 

يمكنك أيضاً تحديد قيمة HLL عن طريق الاستعلام عن جدول information_schema.innodb_metrics:

mysql> select count from information_schema.innodb_metrics  
    -> where name = 'trx_rseg_history_len'; 
+---------+ 
|  count  | 
+---------+ 
| 2964300 | 
+---------+ 
1 row in set (0.00 sec)

تفسير قيم HLL

عند تفسير قيم HLL، ضع في اعتبارك الإرشادات المدرجة في الجدول التالي:

القيمة	ملاحظات
أقل من ~10,000	القيم العادية، مما يشير إلى أن جمع البيانات المهملة لا يتخلف عن الركب.
بين ~10,000 و ~1,000,000	تشير هذه القيم إلى تأخر بسيط في جمع البيانات المهملة. قد تكون هذه القيم مقبولة إذا ظلت ثابتة ولا تزيد.
أكبر من ~1,000,000	يجب التحقق من هذه القيم وقد تتطلب إجراءات تصحيحية

معالجة قيم HLL الزائدة

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

للحصول على معلومات المعاملة من information_schema.innodb_trx الجدول، على سبيل المثال، قم بتشغيل الأوامر التالية:

select * from information_schema.innodb_trx  
order by trx_started asc\G

ستساعدك التفاصيل الموجودة في trx_started العمود على حساب عمر المعاملة.

mysql> select * from information_schema.innodb_trx  
    -> order by trx_started asc\G 
*************************** 1. row *************************** 
                    trx_id: 8150550 
                 trx_state: RUNNING 
               trx_started: 2021-11-13 20:50:11 
     trx_requested_lock_id: NULL 
          trx_wait_started: NULL 
                trx_weight: 0 
       trx_mysql_thread_id: 19 
                 trx_query: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
(…) 

للحصول على معلومات حول جلسات عمل قاعدة البيانات الحالية، بما في ذلك الوقت المستغرق في الحالة الحالية لجلسة العمل، تحقق من information_schema.processlist الجدول. يظهر الإخراج التالي، على سبيل المثال، جلسة عمل تقوم بتنفيذ استعلام بنشاط لآخر 1462 ثانية:

mysql> select user, host, db, command, time, info  
    -> from information_schema.processlist  
    -> order by time desc\G 
*************************** 1. row *************************** 
   user: test 
   host: 172.31.19.159:38004 
     db: employees 
command: Query 
   time: 1462 
   info: select * from employees where DATE(hire_date) > DATE('1998-04-05') AND first_name like '%geo%';
 
(...) 

التوصيات

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

• تجنب المعاملات الكبيرة أو طويلة الأمد عن طريق تقسيمها إلى مُعاملات أصغر.

• تكوين innodb_purge_threads وفقاً لحمل العمل الخاص بك لتحسين كفاءة عمليات إزالة الخلفية.

  إشعار

  اختبر أي تغييرات على متغير الخادم هذا لكل بيئة لقياس التغيير في سلوك المحرك.

• استخدم التنبيهات على "Host CPU Percent" و"Host Memory Percent" و"Total Connections" بحيث تحصل على إعلامات إذا تجاوز النظام أي من الحدود المحددة.

• استخدم أداء الاستعلام نتيجة التحليلات أو Azure Workbooks لتحديد أي استعلامات إشكالية أو تعمل ببطء، ثم قم بتحسينها.

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

الخطوات التالية

للعثور على إجابات النظراء لأسئلتك الأكثر أهمية أو لنشر سؤال أو الإجابة عليه، تفضل بزيارة Stack Overflow.