استكشاف أخطاء الاستخدام العالي لوحدة المعالجة المركزية في قاعدة بيانات Azure ل MySQL - الخادم المرن وإصلاحها
ينطبق على:
قاعدة بيانات Azure لـ MySQL - خادم فردي قاعدة بيانات Azure لـ MySQL - خادم مرن
هام
قاعدة بيانات Azure لخادم MySQL الفردي على مسار الإيقاف. نوصي بشدة بالترقية إلى قاعدة بيانات Azure لخادم MySQL المرن. لمزيد من المعلومات حول الترحيل إلى خادم Azure Database for MySQL المرن، راجع ما الذي يحدث لقاعدة بيانات Azure لخادم MySQL الفردي؟
يوفر خادم Azure Database for MySQL المرن مجموعة من المقاييس التي يمكنك استخدامها لتحديد اختناقات الموارد ومشكلات الأداء على الخادم. لتحديد ما إذا كان الخادم الخاص بك يواجه استخداما عاليا لوحدة CPU، قم بمراقبة المقاييس مثل "Host CPU percent" و"Total Connections" و"Host Memory Percent" و"IO Percent". في بعض الأحيان، سيوفر عرض مجموعة من هذه المقاييس رؤى حول ما قد يسبب زيادة استخدام وحدة المعالجة المركزية على قاعدة بيانات Azure لمثيل الخادم المرن MySQL.
على سبيل المثال، ضع في اعتبارك زيادة مفاجئة في الاتصالات التي تبدأ زيادة في استعلامات قاعدة البيانات التي تتسبب في التقاط استخدام وحدة CPU.
بالإضافة إلى التقاط المقاييس، من المهم أيضا تتبع حمل العمل لفهم ما إذا كان استعلامًا واحدا أو أكثر يتسبب في ارتفاع استخدام وحدة CPU.
أسباب عالية لوحدة المعالجة المركزية
يمكن أن تحدث طفرات وحدة المعالجة المركزية لأسباب مختلفة، ويرجع ذلك في المقام الأول إلى الارتفاعات في الاتصالات واستعلامات SQL المكتوبة بشكل سيئ، أو مزيج من كليهما:
ارتفاع في الاتصالات
يمكن أن تؤدي الزيادة في الاتصالات إلى زيادة في مؤشرات الترابط، والتي بدورها يمكن أن تسبب زيادة في استخدام وحدة المعالجة المركزية كما يجب أن تدير هذه الاتصالات جنبا إلى جنب مع استعلاماتها ومواردها. لاستكشاف أخطاء ارتفاع في الاتصالات وإصلاحها، يجب عليك التحقق من مقياس إجمالي الاتصال الاتصالات والرجوع إلى القسم التالي للحصول على مزيد من التفاصيل حول هذه الاتصالات. يمكنك استخدام performance_schema لتحديد المضيفين والمستخدمين المتصلين حاليا بالخادم باستخدام الأوامر التالية:
المضيفون المتصلون الحاليون
select HOST,CURRENT_CONNECTIONS From performance_schema.hosts
where CURRENT_CONNECTIONS > 0
and host not in ('NULL','localhost');
المستخدمون المتصلون الحاليون
select USER,CURRENT_CONNECTIONS from performance_schema.users
where CURRENT_CONNECTIONS >0
and USER not in ('NULL','azure_superuser');
استعلامات SQL المكتوبة بشكل سيئ
يمكن أن تؤدي الاستعلامات المكلفة لتنفيذ عدد كبير من الصفوف ومسحها ضوئيا دون فهرس، أو تلك التي تقوم بفرز مؤقت جنبا إلى جنب مع خطط أخرى غير فعالة، إلى ارتفاعات في وحدة المعالجة المركزية. بينما قد يتم تنفيذ بعض الاستعلامات بسرعة في جلسة عمل واحدة، يمكن أن تتسبب في ارتفاع وحدة المعالجة المركزية عند تشغيلها في جلسات متعددة. لذلك، من المهم دائما شرح استعلاماتك التي تلتقطها من قائمة عمليات العرض وضمان كفاءة خطط التنفيذ الخاصة بها. يمكن تحقيق ذلك من خلال التأكد من أنها تفحص الحد الأدنى من عدد الصفوف باستخدام عوامل التصفية/حيث العبارة، واستخدام الفهارس وتجنب استخدام فرز مؤقت كبير جنبا إلى جنب مع خطط التنفيذ السيئة الأخرى. لمزيد من المعلومات حول خطط التنفيذ، راجع شرح تنسيق الإخراج.
التقاط تفاصيل حمل العمل الحالي
يعرض الأمر SHOW (FULL) PROCESSLIST قائمة بجميع جلسات عمل المستخدم المتصلة حاليا بمثيل الخادم المرن لقاعدة بيانات Azure ل MySQL. كما يوفر تفاصيل حول الحالة والنشاط الحاليين لكل جلسة عمل.
ينتج هذا الأمر لقطة فقط لحالة جلسة العمل الحالية ولا يوفر معلومات حول نشاط جلسة العمل التاريخية.
لنلقي نظرة على عينة الإخراج من تشغيل هذا الأمر.
mysql> SHOW FULL PROCESSLIST;
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
| 1 | event_scheduler | localhost | NULL | Daemon | 13 | Waiting for next activation | NULL |
| 6 | azure_superuser | 127.0.0.1:33571 | NULL | Sleep | 115 | | NULL
|
| 24835 | adminuser | 10.1.1.4:39296 | classicmodels | Query | 7 | Sending data | select * from classicmodels.orderdetails;|
| 24837 | adminuser | 10.1.1.4:38208 | NULL | Query | 0 | starting | SHOW FULL PROCESSLIST |
+-------+------------------+--------------------+---------------+-------------+--------+-----------------------------+------------------------------------------+
5 rows in set (0.00 sec)
لاحظ أن هناك جلستين مملوكتين من قبل المستخدم المملوك للعميل "adminuser"، وكلاهما من نفس عنوان IP:
- تم تنفيذ جلسة العمل 24835 عبارة SELECT لآخر سبع ثوان.
- تنفذ جلسة العمل 24837 عبارة "show full processlist".
عند الضرورة، قد يكون مطلوبا إنهاء استعلام، مثل إعداد التقارير أو استعلام HTAP الذي تسبب في ارتفاع استخدام وحدة المعالجة المركزية لحمل العمل الإنتاجي. ومع ذلك، ضع في اعتبارك دائما العواقب المحتملة لإنهاء استعلام قبل اتخاذ الإجراء في محاولة لتقليل استخدام وحدة CPU. في أوقات أخرى في حالة وجود أي استعلامات طويلة الأمد تم تحديدها تؤدي إلى ارتفاعات فيCPU، اضبط هذه الاستعلامات بحيث يتم استخدام الموارد على النحو الأمثل.
تحليل مفصل لحمل العمل الحالي
تحتاج لاستخدام مصدرين على الأقل من المعلومات للحصول على معلومات دقيقة حول حالة جلسة العمل والمعاملة والاستعلام:
- قائمة عمليات الخادم من INFORMATION_SCHEMA. جدول PROCESSLIST، والذي يمكنك أيضا الوصول إليه عن طريق تشغيل الأمر SHOW [FULL] PROCESSLIST.
- بيانات تعريف المعاملات الخاصة ب InnoDB من INFORMATION_SCHEMA. INNODB_TRX الجدول.
بمعلومات من مصدر واحد فقط من هذه المصادر، من المستحيل وصف الاتصال وحالة المعاملة. على سبيل المثال، لا تعلمك قائمة العمليات ما إذا كانت هناك معاملة مفتوحة مقترنة بأي من جلسات العمل. من ناحية أخرى، لا تعرض بيانات تعريف المعاملة حالة الجلسة والوقت المستغرق في تلك الحالة.
استعلام المثال التالي الذي يجمع بين معلومات قائمة العمليات وبعض الأجزاء المهمة من بيانات تعريف معاملة InnoDB:
mysql> select p.id as session_id, p.user, p.host, p.db, p.command, p.time, p.state, substring(p.info, 1, 50) as info, t.trx_started, unix_timestamp(now()) - unix_timestamp(t.trx_started) as trx_age_seconds, t.trx_rows_modified, t.trx_isolation_level from information_schema.processlist p left join information_schema.innodb_trx t on p.id = t.trx_mysql_thread_id \G
يوضح المثال التالي الإخراج من هذا الاستعلام:
*************************** 1. row ***************************
session_id: 11
user: adminuser
host: 172.31.19.159:53624
db: NULL
command: Sleep
time: 636
state: cleaned up
info: NULL
trx_started: 2019-08-01 15:25:07
trx_age_seconds: 2908
trx_rows_modified: 17825792
trx_isolation_level: REPEATABLE READ
*************************** 2. row ***************************
session_id: 12
user: adminuser
host: 172.31.19.159:53622
db: NULL
command: Query
time: 15
state: executing
info: select * from classicmodels.orders
trx_started: NULL
trx_age_seconds: NULL
trx_rows_modified: NULL
trx_isolation_level: NULL
يتم سرد تحليل هذه المعلومات، حسب جلسة العمل، في الجدول التالي.
| المساحة | التحليل |
|---|---|
| جلسة 11 | جلسة العمل هذه بطيئة حاليا (خاملة) مع عدم وجود استعلامات قيد التشغيل، وقد كانت لمدة 636 ثانية. ضمن جلسة العمل، قامت معاملة مفتوحة لمدة 2908 ثوان بتعديل 17825792 صفا، وتستخدم عزل READ قابل للتكرار. |
| جلسة 12 | تقوم جلسة العمل حاليا بتنفيذ عبارة SELECT، والتي تم تشغيلها لمدة 15 ثانية. لا يوجد استعلام قيد التشغيل داخل جلسة العمل، كما هو موضح في قيم NULL trx_started trx_age_seconds. ستستمر الجلسة في الاحتفاظ بحد مجموعة البيانات المهملة طالما أنها تعمل ما لم تكن تستخدم عزل READ COMMITTED الأكثر استرخاء. |
لاحظ أنه إذا تم الإبلاغ عن جلسة عمل ك بطيئة، فلن يتم تنفيذ أي عبارات. في هذه المرحلة، تكمل الجلسة أي عمل سابق وتنتظر عبارات جديدة من العميل. ومع ذلك، لا تزال جلسات العمل الخاملة مسؤولة عن بعض استهلاك وحدة CPU واستخدام الذاكرة.
سرد المعاملات المفتوحة
يوفر الإخراج من الاستعلام التالي قائمة بجميع المعاملات التي تعمل حاليا مقابل خادم قاعدة البيانات بترتيب وقت بدء المعاملة بحيث يمكنك بسهولة تحديد ما إذا كان هناك أي عمليات تشغيل طويلة وحظر تجاوز وقت التشغيل المتوقع.
SELECT trx_id, trx_mysql_thread_id, trx_state, Unix_timestamp() - ( To_seconds(trx_started) - To_seconds('1970-01-01 00:00:00') ) AS trx_age_seconds, trx_weight, trx_query, trx_tables_in_use, trx_tables_locked, trx_lock_structs, trx_rows_locked, trx_rows_modified, trx_isolation_level, trx_unique_checks, trx_is_read_only FROM information_schema.innodb_trx ORDER BY trx_started ASC;
فهم حالات مؤشر الترابط
يمكن أن يكون للمعاملات التي تسهم في زيادة استخدام وحدة CPU أثناء التنفيذ مؤشرات ترابط في حالات مختلفة، كما هو موضح في الأقسام التالية. استخدم هذه المعلومات لفهم دورة حياة الاستعلام وحالات مؤشرات الترابط المختلفة بشكل أفضل.
التحقق من الأذونات/فتح الجداول
تعني هذه الحالة عادة أن عملية الجدول المفتوح تستغرق وقتا طويلا. يمكنك عادةً زيادة حجم ذاكرة التخزين المؤقت للجدول لتحسين المشكلة. ومع ذلك، يمكن أن تكون الجداول التي تفتح ببطء أيضا مؤشرا على مشكلات أخرى، مثل وجود عدد كبير جدًا من الجداول ضمن نفس قاعدة البيانات.
إرسال البيانات
في حين أن هذه الحالة يمكن أن تعني أن مؤشر الترابط يرسل البيانات عبر الشبكة، فإنه يمكن أن يشير أيضا إلى أن الاستعلام يقرأ البيانات من القرص أو الذاكرة. يمكن أن ينتج هذه الحالة عن فحص جدول متسلسل. يجب التحقق من قيم innodb_buffer_pool_reads innodb_buffer_pool_read_requests لتحديد ما إن كان يتم تقديم عدد كبير من الصفحات من القرص إلى الذاكرة. لمزيد من المعلومات، راجع استكشاف مشكلات الذاكرة المنخفضة وإصلاحها في خادم Azure Database for MySQL المرن.
Updating
تعني هذه الحالة عادة أن مؤشر الترابط ينفذ عملية كتابة. تحقق من المقياس المتعلق بالقياس في مراقبة الأداء للحصول على فهم أفضل لما تفعله الجلسات الحالية.
في انتظار <تأمين lock_type>
تشير هذه الحالة إلى أن مؤشر الترابط ينتظر تأمينا ثانيا. في معظم الحالات، قد يكون تأمين بيانات التعريف. يجب عليك مراجعة جميع مؤشرات الترابط الأخرى ومعرفة من يأخذ التأمين.
فهم وتحليل أحداث الانتظار
من المهم فهم أحداث الانتظار الأساسية في محرك MySQL، لأن الانتظار الطويل أو عدد كبير من الانتظارات في قاعدة البيانات يمكن أن يؤدي إلى زيادة استخدام وحدة CPU. يوضح المثال التالي الأمر المناسب وإخراج العينة.
SELECT event_name AS wait_event,
count_star AS all_occurrences,
Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_wait_time,
Concat(Round(avg_timer_wait / 1000000000, 2), ' ms') AS
avg_wait_time
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE count_star > 0 AND event_name <> 'idle'
ORDER BY sum_timer_wait DESC LIMIT 10;
+--------------------------------------+-----------------+-----------------+---------------+
| wait_event | all_occurrences | total_wait_time | avg_wait_time |
+--------------------------------------+-----------------+-----------------+---------------+
| wait/io/file/sql/binlog | 7090 | 255.54 s | 36.04 ms |
| wait/io/file/innodb/innodb_log_file | 17798 | 55.43 s | 3.11 ms |
| wait/io/file/innodb/innodb_data_file | 260227 | 39.67 s | 0.15 ms |
| wait/io/table/sql/handler | 5548985 | 11.73 s | 0.00 ms |
| wait/io/file/sql/FRM | 1237 | 7.61 s | 6.15 ms |
| wait/io/file/sql/dbopt | 28 | 1.89 s | 67.38 ms |
| wait/io/file/myisam/kfile | 92 | 0.76 s | 8.30 ms |
| wait/io/file/myisam/dfile | 271 | 0.53 s | 1.95 ms |
| wait/io/file/sql/file_parser | 18 | 0.32 s | 17.75 ms |
| wait/io/file/sql/slow_log | 2 | 0.05 s | 25.79 ms |
+--------------------------------------+-----------------+-----------------+---------------+
10 rows in set (0.00 sec)
تقييد وقت تنفيذ عبارات SELECT
إذا كنت لا تعرف تكلفة التنفيذ ووقت التنفيذ لعمليات قاعدة البيانات التي تتضمن استعلامات SELECT، فإن أي من استعلامات SELECTs طويلة الأمد يمكن أن تؤدي إلى عدم القدرة على التنبؤ أو التقلب في خادم قاعدة البيانات. يستمر حجم البيانات والمعاملات، بالإضافة إلى استخدام الموارد المرتبطة به، في النمو اعتمادًا على نمو مجموعة البيانات الأساسية. وبسبب هذا النمو غير المرتبط، تستغرق بيانات المستخدم النهائي والمعاملات وقتا أطول وأطول، وتستهلك المزيد من الموارد بشكل متزايد حتى تطغى على خادم قاعدة البيانات. عند استخدام استعلامات SELECT غير المحدودة، يوصى بتكوين المعلمة max_execution_time بحيث يتم إجهاض أي استعلامات تتجاوز هذه المدة.
التوصيات
- تأكد من أن قاعدة البيانات الخاصة بك لديها موارد كافية مُخصصة لتشغيل الاستعلامات الخاصة بك. في بعض الأحيان، قد تحتاج إلى توسيع حجم المثيل للحصول على المزيد من الذاكرات الأساسية لوحدة المعالجة المركزية لاستيعاب حمل العمل الخاص بك.
- تجنب المعاملات الكبيرة أو طويلة الأمد عن طريق تقسيمها إلى مُعاملات أصغر.
- شغّل عبارات SELECT على خوادم النسخ المتماثلة عند الإمكان.
- استخدم التنبيهات على "Host CPU Percent" بحيث تحصل على إعلامات إذا تجاوز النظام أي من الحدود المحددة.
- استخدم أداء الاستعلام نتيجة التحليلات أو Azure Workbooks لتحديد أي استعلامات إشكالية أو تعمل ببطء، ثم قم بتحسينها.
- بالنسبة لخوادم قاعدة بيانات الإنتاج، اجمع التشخيصات على فترات مُنتظمة للتأكد من أن كل شيء يعمل بسلاسة. إذا لم يكن الأمر كما هو، قم باستكشاف الأخطاء وإصلاحها وحل أي مشكلات تحددها.
الخطوات التالية
للعثور على إجابات النظراء لأسئلتك الأكثر أهمية أو لنشر سؤال أو الإجابة عليه، تفضل بزيارة Stack Overflow.