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

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

هام

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

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

لاحظ أن خادم Azure Database for MySQL المرن يستهلك الذاكرة لتحقيق أكبر قدر ممكن من ذاكرة التخزين المؤقت. ونتيجة لذلك، يمكن أن يحوم استخدام الذاكرة غالبا بين 80- 90٪ من الذاكرة الفعلية المتوفرة للمثيل. ما لم تكن هناك مشكلة في تقدم حِمل عمل الاستعلام، فإنها ليست مصدر قلق. ومع ذلك، يمكن أن تواجه مشكلات في الذاكرة لأسباب مثل ما يلي:

• تم تكوين مَخازن مؤقتة كبيرة جدا.
• الاستعلامات المُثلى الفرعية قيد التشغيل.
• الاستعلامات التي تقوم بإجراء الصِلات وفرز مجموعات البيانات الكبيرة.
• تعيين الحد الأقصى للاتصالات على خادم قاعدة بيانات مُرتفع جدا.

يتم استخدام معظم ذاكرة الخادم بواسطة المخازن المؤقتة العمومية وذاكرة التخزين المؤقت في InnoDB، والتي تتضمن مكونات مثل innodb_buffer_pool_sizeinnodb_log_buffer_sizekey_buffer_sizequery_cache_size.

تُحدد قيمة المعلمة innodb_buffer_pool_size مساحة الذاكرة التي يقوم InnoDB بتخزين جداول قاعدة البيانات والبيانات المتعلقة بالفهرس مؤقتا. يُحاول MySQL استيعاب أكبر قدر ممكن من البيانات المتعلقة بالجدول والفهرس في تجمع المخزن المؤقت. يتطلب تجمع المخزن المؤقت الأكبر عددا أقل من عمليات الإدخال/الإخراج التي تحول إلى القرص.

مُراقبة استخدام الذاكرة

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

إذا لاحظت أن استخدام الذاكرة قد زاد فجأة وأن الذاكرة المتوفرة تنخفض بسرعة، راقب المقاييس الأخرى، مثل Host CPU Percent و Total Connections و IO Percent، لتحديد ما إذا كان الارتفاع المفاجئ في حمل العمل هو مصدر المشكلة.

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

أسباب الاستِخدام العالي للذاكرة

لننظر في بعض الأسباب أكثر من استخدام الذاكرة العالية في MySQL. تَعتمد هذه الأسباب على خصائص حمل العمل.

زيادة في الجَداول المؤقتة

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

باستخدام MySQL، يتم تحديد حَجم الجدول المؤقت من خلال قيم معلمتين، كما هو موضح في الجدول التالي.

المعلمة‬	الوصف
tmp_table_size	تَحديد الحد الأقصى لحجم الجداول المؤقتة الداخلية في الذاكرة.
max_heap_table_size	تَحديد الحد الأقصى للحجم الذي يمكن أن تنمو إليه جداول الذاكرة التي أنشأها المستخدم.

إشعار

عند تَحديد الحد الأقصى لحجم جدول مؤقت داخلي في الذاكرة، يعتبر MySQL أقل من القيم التي تم تعيينها للمعلمات tmp_table_size max_heap_table_size.

التوصيات

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

• قبل زيادة قيمة tmp_table_size، تحقق من فهرسة قاعدة البيانات بشكل صحيح، خاصة للأعمدة المشاركة في الصلات وتجميعها حَسب العمليات. يؤدي استخدام الفهارس المُناسبة في الجداول الأساسية إلى الحد من عدد الجداول المؤقتة التي تم إنشاؤها. يُمكن أن تسمح زيادة قيمة هذه المعلمة والمعلمة max_heap_table_size دون التحقق من الفهارس الخاصة بك بتشغيل الاستعلامات غير الفعالة دون فهارس وإنشاء جداول مؤقتة أكثر مما هو ضروري.
• ضَبط قيم المعلمات max_heap_table_size tmp_table_size لتلبية احتياجات حمل العمل الخاص بك.
• إذا كانت القيم التي قمت بتعيينها للمعلمات max_heap_table_size tmp_table_size منخفضة جدا، فقد تمتد الجداول المؤقتة بانتظام إلى التخزين، مِما يضيف زمن انتقال إلى الاستعلامات. يُمكنك تعقب الجداول المؤقتة المتسربة إلى القرص باستخدام عداد الحالة العمومية created_tmp_disk_tables. من خلال مقارنة قيم متغيرات created_tmp_disk_tables created_tmp_tables، يُمكنك عرض عدد الجداول المؤقتة الداخلية على القرص التي تم إنشاؤها إلى العدد الإجمالي للجداول المؤقتة الداخلية التي تم إنشاؤها.

ذاكرة التَخزين المؤقت للجداول

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

التوصيات

لاستكشاف مُشكلات الذاكرة المنخفضة المتعلقة بالجداول المؤقتة وإصلاحها، ضَع في اعتبارك التوصيات التالية.

• تُحدد المعلمة table_open_cache عدد الجداول المفتوحة لكافة مؤشرات الترابط. تؤدي زيادة هذه القيمة إلى زيادة عدد واصفات المَلفات التي يتطلبها mysqld. يُمكنك التحقق مما إذا كنت بحاجة إلى زيادة ذاكرة التخزين المؤقت للجدول عن طريق التحقق من متغير الحالة opened_tables في عرض عداد الحالة العمومية. زود قيمة هذه المعلمة بزيادات لاستيعاب حمل العمل الخاص بك.
• قد يؤدي تعيين table_open_cache منخفض جدا إلى أن يقضي خادم Azure Database for MySQL المرن مزيدا من الوقت في فتح الجداول المطلوبة لمعالجة الاستعلام وإغلاقها.
• قد يؤدي تَعيين هذه القيمة عالية جدا إلى استخدام المزيد من الذاكرة وتشغيل نظام التشغيل لواصفات الملفات مما يؤدي إلى اتصالات مرفوضة أو فشل في معالجة الاستعلامات.

المَخازن المؤقتة الأخرى وذاكرة التخزين المؤقت للاستعلام

عند استِكشاف المشكلات المتعلقة بضعف الذاكرة وإصلاحها، يمكنك العمل مع عدد قليل من المخازن المؤقتة وذاكرة التخزين المؤقت للمساعدة في الحل.

المخزن المؤقت الصَافي (net_buffer_length)

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

ضمّ المخزن المؤقت (join_buffer_size)

يَتم تخصيص المخزن المؤقت للضم لتخزين صفوف الجدول مؤقتا عندما يتعذر على الصلة استخدام فهرس. إذا كانت قاعدة البيانات تحتوي على العديد من الصلات التي تم تنفيذها بدون فهارس، فكر في إضافة فهارس لوصلات أسرع. إذا لم تتمكن من إضافة فهارس، فكر في زيادة قيمة المعلمة join_buffer_size، والتي تحدد مقدار الذاكرة المخصصة لكل اتصال.

فَرز المخزن المؤقت (sort_buffer_size)

يتم استِخدام المخزن المؤقت للفرز لإجراء عمليات فرز لبعض استعلامات ORDER BY وGROUP BY. إذا رأيت العديد من Sort_merge_passes في الثانية في إخراج SHOW GLOBAL STATUS، ففكر في زيادة قيمة sort_buffer_size لتسريع عمليات ORDER BY أو GROUP BY التي لا يُمكن تحسينها باستخدام تحسين الاستعلام أو الفهرسة بشكل أفضل.

تجنب زيادة قيمة sort_buffer_size بشكل عشوائي ما لم تَكن لديك معلومات ذات صلة تشير إلى خلاف ذلك. يتم تَعيين الذاكرة لهذا المخزن المؤقت لكل اتصال. في وثائق MySQL، تستدعي مقالة Server System Variables أنه على Linux، هناك حدان، 256 كيلوبايت و2 ميغابايت، وأن استخدام قيم أكبر يُمكن أن يؤدي إلى إبطاء تخصيص الذاكرة بشكل كبير. ونتيجة لذلك، تجنب زيادة قيمة sort_buffer_size إلى ما بعد 2M، حيث إن عقوبة الأداء سوف تفوق أي فوائد.

ذاكرة التَخزين المؤقت للاستعلام (query_cache_size)

ذاكرة التخزين المؤقت للاستعلام هي منطقة ذاكرة تُستخدم للتخزين المؤقت لمجموعات نتائج الاستعلام. تُحدد المعلمة query_cache_size مقدار الذاكرة المخصصة للتخزين المؤقت لنتائج الاستعلام. بشكل افتراضي، يتم تعطيل ذاكرة التخزين المؤقت للاستعلام. بالإضافة إلى ذلك، يَتم إهمال ذاكرة التخزين المؤقت للاستعلام في الإصدار 5.7.20 من MySQL وإزالتها في الإصدار 8.0 من MySQL. إذا تم تمكين ذاكرة التخزين المؤقت للاستعلام حاليا في الحل الخاص بك، قبل تعطيله، تحقق من عدم وجود أي استعلامات تعتمد عليه.

حِساب نسبة مرات الوصول إلى ذاكرة التخزين المؤقت المؤقتة

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

لحساب نسبة الوصول إلى تجمع المخزن المؤقت InnoDB لطلبات القراءة، يُمكنك تشغيل SHOW GLOBAL STATUS لاسترداد العدادين "Innodb_buffer_pool_read_requests" و"Innodb_buffer_pool_reads" ثم حساب القيمة باستخدام الصيغة الموضحة أدناه.

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

لنأخذ في الاعتبار المثال التالي.

mysql> show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Innodb_buffer_pool_reads | 197   |
+--------------------------+-------+
1 row in set (0.00 sec)

mysql> show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name                    | Value    |
+----------------------------------+----------+
| Innodb_buffer_pool_read_requests | 22479167 |
+----------------------------------+----------+
1 row in set (0.00 sec)

باستخدام القيم المذكورة أعلاه، تؤدي حَوسبة نسبة الوصول إلى تجمع المخزن المؤقت InnoDB لطلبات القراءة إلى النتيجة التالية:

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100 

Buffer hit ratio = 99.99%

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

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

التوصيات

• تأكد من أن قاعدة البيانات الخاصة بك لديها موارد كافية مُخصصة لتشغيل الاستعلامات الخاصة بك. في بعض الأحيان، قد تحتاج إلى توسيع حجم المثيل للحصول على المزيد من الذاكرة الفعلية بحيث يتم تخزين المخازن المؤقتة وذاكرة التخزين المؤقت لاستيعاب حِمل العمل الخاص بك.
• تجنب المعاملات الكبيرة أو طويلة الأمد عن طريق تقسيمها إلى مُعاملات أصغر.
• استخدم التنبيهات على "Host CPU Percent" بحيث تحصل على إعلامات إذا تجاوز النظام أي من الحدود المُحددة.
• استخدم أداء الاستعلام نتيجة التحليلات أو Azure Workbooks لتحديد أي استعلامات إشكالية أو تعمل ببطء، ثم قم بتحسينها.
• بالنسبة لخوادم قاعدة بيانات الإنتاج، اجمع التشخيصات على فترات مُنتظمة للتأكد من أن كل شيء يعمل بسلاسة. إذا لم يكن الأمر كما هو، قم باستكشاف الأخطاء وإصلاحها وحل أي مشكلات تحددها.

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

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