ضبط متغيرات نظام MySQL للأداء العالي

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


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

إنها حقيقة قاسية تتطور حتى مع 6-8 سنوات تحت كفاحها الحزمي لتفسير تعقيدات محسن الاستعلام وتفضل أن تنظر إلى السماء عندما تُسأل عن ضبط قاعدة البيانات.

لماذا ا?

والمثير للدهشة أن السبب ليس الكسل (على الرغم من أنه في جزء منه).

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

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

فضولي؟ دعونا نتعمق!

لا فضول؟ حسنًا ، الغوص في أي حال ، لأن حياتك المهنية تعتمد على ذلك! ��

تحسين ذاكرة التخزين المؤقت للاستعلام MySQL

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

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

يمكنك التحقق من توفر ذاكرة التخزين المؤقت للاستعلام (ملاحظة ، متاحة ، غير ممكّنة) في قاعدة البيانات الخاصة بك عن طريق تشغيل هذا الاستعلام في وحدة تحكم قاعدة البيانات:

MariaDB [(بلا)]> إظهار المتغيرات مثل ‘have_query_cache’ ؛
+——————+——-+
| Variable_name | القيمة |
+——————+——-+
| have_query_cache | نعم |
+——————+——-+

لذا ، يمكنك أن ترى أني أقوم بتشغيل MariaDB وأن لدي التخزين المؤقت للاستعلام المتاح لتشغيله. من غير المحتمل أن تقوم بإيقاف تشغيله إذا كنت تستخدم تثبيت MySQL قياسي.

الآن دعونا نرى ما إذا كان لدي ذاكرة التخزين المؤقت للاستعلام قيد التشغيل بالفعل:

MariaDB [(بلا)]> إظهار المتغيرات مثل “query_cache_type” ؛
+——————+——-+
| Variable_name | القيمة |
+——————+——-+
| query_cache_type | ON |
+——————+——-+

أجل أقبل. ولكن في حال لم تفعل ذلك ، يمكنك تشغيله بقول:

MariaDB [(بلا)]> SET GLOBAL query_cache_type = ON ،

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

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

المتغير الآخر للاهتمام هنا هو query_cache_size ، الذي تكون وظيفته ذاتية التفسير:

MariaDB [(بلا)]> إظهار المتغيرات مثل “query_cache_size” ؛
+——————+———-+
| Variable_name | القيمة |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

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

لنفترض أنك قمت بتعيين حجم ذاكرة التخزين المؤقت للاستعلام ليكون 500 كيلوبايت:

MariaDB [(بلا)]> SET GLOBAL query_cache_size = 500000 ،

هل تفعل هذا بما فيه الكفاية؟ حسنًا ، لا ، لأن الكيفية التي سينتهي بها محرك الاستعلام في الواقع يعتمد على شيئين آخرين:

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

كيف تعرف أن ذاكرة التخزين المؤقت أصبحت مجزأة؟ تحقق من العدد الإجمالي للكتل في ذاكرة التخزين المؤقت مثل هذا:

MariaDB [(بلا)]> عرض الحالة مثل “Qcache_total_blocks” ؛
+———————+——-+
| Variable_name | القيمة |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

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

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

خيوط ، ومجموعات خيوط ، والانتظار ، والمهلة

ربما يكون هذا هو الجزء الأكثر إثارة للاهتمام في كيفية عمل MySQL والحصول عليه بشكل صحيح يعني جعل تطبيقك أسرع عدة مرات!

خيوط

MySQL هو خادم متعدد الخيوط. وهذا يعني أنه في كل مرة يكون فيها اتصال جديد بخادم MySQL ، فإنه يفتح سلسلة جديدة مع بيانات الاتصال ويمرر مقبضًا إلى العميل (فقط في حالة تساؤلك عن موضوع الخيط ، انظر هذه). يرسل العميل بعد ذلك جميع الاستعلامات حول هذا الموضوع ويتلقى النتائج. وهذا يقودنا إلى طرح سؤال طبيعي: كم عدد الخيوط التي يمكن لـ MySQL أن تدور؟ الجواب يكمن في القسم التالي.

تجمع موضوع

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

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

ariaDB [(بلا)]> عرض المتغيرات مثل “thread_pool_size” ؛
+——————+——-+
| Variable_name | القيمة |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

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

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

الانتظار والمهل

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

MariaDB [(بلا)]> إظهار المتغيرات مثل “انتظر٪” ؛
+—————+——-+
| Variable_name | القيمة |
+—————+——-+
| وقت الانتظار | 28800 |
+—————+——-+

القيمة الناتجة بالثواني. لذا نعم ، يتم ضبط MySQL افتراضيًا على الانتظار لمدة 8 ساعات أو أكثر قبل أن يقطع السلك! قد يكون هذا جيدًا إذا كان لديك استفسارات طويلة وتريد بالفعل انتظارها (ولكن حتى ذلك الحين ، ثماني ساعات سخيفة!) ولكنها رهيبة في معظم الحالات. عند تشغيل استعلام ، يتم تعيين هذه القيمة على 0 (يعني إلى الأبد) ، ولكن بشكل عام ، يجب تعيينها على قيمة منخفضة جدًا (5 ثوانٍ ، على سبيل المثال ، أو ربما أقل) لتحرير اتصال العمليات الأخرى.

ضبط الجداول المؤقتة

لنبدأ بما هي الجداول المؤقتة في MySQL.

لنفترض أن لدينا MySQL يبدو مثل هذا الهيكل: TABLE A UNION (TABLE B INNER JOIN C). أي أننا مهتمون بضم الجدولين B و C ، ثم إجراء اتحاد للنتيجة مع الجدول A. الآن ، ستبدأ MySQL أولاً في الانضمام إلى الجدولين B و C ، ولكن قبل أن تتمكن من إجراء اتحاد ، فإنها تحتاج إلى لتخزين هذه البيانات في مكان ما. هذا هو المكان الذي تأتي فيه الجداول المؤقتة – يستخدم MySQL لتخزين البيانات في المراحل المتوسطة في الاستعلامات المعقدة مؤقتًا ، وبمجرد انتهاء الاستعلام ، يتم تجاهل هذا الجدول المؤقت.

السؤال الآن هو: لماذا يجب علينا أن نهتم بكل هذا?

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

هناك متغيران يتحكمان في هذا السلوك:

MariaDB [(بلا)]> عرض المتغيرات مثل “MariaDB [(none)]> إظهار المتغيرات مثل “tmp_table_size” ؛
+—————-+———-+

| Variable_name | القيمة |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘؛
+———————+———-+
| Variable_name | القيمة |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(بلا)]> إظهار المتغيرات مثل “tmp_table_size” ؛
+—————-+———-+
| Variable_name | القيمة |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

تخبرنا الأولى ، max_heap_table_size ، عن مقدار ذاكرة الوصول العشوائي التي يمكن استخدامها بواسطة جدول MySQL (يشير “كومة الذاكرة المؤقتة” هنا إلى بنية البيانات المستخدمة في تخصيص وإدارة ذاكرة الوصول العشوائي – اقرأ المزيد هنا) ، بينما يوضح الجدول الثاني ، tmp_table_size ، الحجم الأقصى للجدول المؤقت. في حالتي ، تم ضبط كليهما على 16 ميغابايت ، على الرغم من أن النقطة التي أحاول أن أجعلها أن زيادة tmp_table_size فقط لن تعمل بشكل عام ، فإن MySQL ستظل محدودة بـ max_table_heap_size.

الآن تأتي النقطة: إذا كانت الجداول المؤقتة التي يتم إنشاؤها أكبر من الحد المسموح به بواسطة هذه المتغيرات ، فسيضطر MySQL إلى كتابتها على القرص الثابت ، مما يؤدي إلى أداء ضعيف للغاية. مهمتنا الآن بسيطة: نبذل قصارى جهدنا لتخمين حجم البيانات الأكثر دقة للجداول المؤقتة وتعديل هذه المتغيرات إلى هذا الحد. ومع ذلك ، أود أن أحذر من السخف: تعيين هذا الحد إلى 16 غيغابايت (بافتراض أن لديك هذا الحجم الكبير من ذاكرة الوصول العشوائي) عندما يكون حجم معظم طاولاتك المؤقتة أقل من 24 ميغابايت من الحماقة – أنت ببساطة تضيع ذاكرة الوصول العشوائي التي يمكن أن “ تم استخدامها بواسطة استعلامات أو أجزاء أخرى من النظام (ذاكرة التخزين المؤقت ، على سبيل المثال).

استنتاج

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

نتيجتي الأكثر جاذبية لكتابة هذا المقال هي أن تأخذ ثلاثة أشياء:

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

ما هو متغير النظام المفضل لديك للضبط؟ ��

العلامات:

  • قاعدة البيانات

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map