قم بتحسين قاعدة بيانات mysql تمامًا تحت التحميل العالي على Linux

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

بعد تثبيت mysql، يجب أن تكون ملفات التكوين في الدليل /usr/local/mysql/share/mysql. هناك العديد من ملفات التكوين، بما في ذلك my-huge.cnf my-medium.cnf my-large.cnf my-small.cnf. ، فإن مواقع الويب ذات تدفقات حركة المرور المختلفة وبيئات الخادم ذات التكوينات المختلفة ستتطلب بالطبع ملفات تكوين مختلفة.

في الظروف العادية، يمكن لملف التكوين my-medium.cnf أن يلبي معظم احتياجاتنا بشكل عام، سنقوم بنسخ ملف التكوين إلى /etc/my.cnf ونحتاج فقط إلى تعديل ملف التكوين هذا، واستخدام متغيرات mysqladmin الموسعة - الحالة –u. يمكن لـ root –p رؤية المعلمات الحالية. هناك ثلاث معلمات تكوين هي الأكثر أهمية، وهي key_buffer_size وquery_cache_size وtable_cache .

key_buffer_size يعمل فقط مع جداول MyISAM،

يحدد key_buffer_size حجم المخزن المؤقت للفهرس، وهو ما يحدد سرعة معالجة الفهرس، وخاصة سرعة قراءة الفهرس. بشكل عام، قمنا بضبطه على 16M. في الواقع، هذا الرقم ليس كافيًا للمواقع الأكبر حجمًا قليلاً. من خلال التحقق من قيم الحالة Key_read_requests وKey_reads، يمكنك معرفة ما إذا كان إعداد key_buffer_size معقولًا. يجب أن تكون نسبة key_reads / key_read_requests منخفضة قدر الإمكان، على الأقل 1:100، 1:1000 أفضل (يمكن الحصول على قيمة الحالة أعلاه باستخدام SHOW STATUS LIKE 'key_read%'). أو إذا قمت بتثبيت phpmyadmin، فيمكنك رؤيته من خلال حالة تشغيل الخادم. يوصي المؤلف باستخدام phpmyadmin لإدارة mysql. قيم الحالة التالية هي تحليل الأمثلة الذي حصلت عليه من خلال phpmyadmin:

هذا الخادم يعمل منذ 20 يومًا
key_buffer_size – 128 ميجا
طلبات_قراءة_المفتاح – 650759289
قراءة المفاتيح - 79112
النسبة تقترب من 1:8000 والحالة الصحية جيدة جداً.

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

بدءًا من الإصدار 4.0.1، يوفر MySQL آلية تخزين مؤقت للاستعلام. باستخدام التخزين المؤقت للاستعلام، يقوم MySQL بتخزين عبارة SELECT ونتائج الاستعلام في المخزن المؤقت. في المستقبل، لنفس عبارة SELECT (حساسة لحالة الأحرف)، سيتم قراءة النتائج مباشرة من المخزن المؤقت. وفقًا لدليل مستخدم MySQL، فإن استخدام التخزين المؤقت للاستعلام يمكن أن يحقق كفاءة تصل إلى 238%.

من خلال ضبط المعلمات التالية، يمكنك معرفة ما إذا كان إعداد query_cache_size معقولًا أم لا.
إدراج Qcache
ضربات Qcache
Qcache Lowmem البرقوق
كتل Qcache المجانية
إجمالي كتل Qcache

إذا كانت قيمة Qcache_lowmem_prunes كبيرة جدًا، فهذا يشير إلى أن التخزين المؤقت غير كافٍ غالبًا. وفي الوقت نفسه، إذا كانت قيمة Qcache_hits كبيرة جدًا، فهذا يشير إلى أن المخزن المؤقت للاستعلام يتم استخدامه بشكل متكرر جدًا يجب زيادتها. إذا كانت قيمة Qcache_hits صغيرة، فهذا يشير إلى أن معدل تكرار الاستعلام الخاص بك مرتفع جدًا. في هذه الحالة، سيؤثر استخدام التخزين المؤقت للاستعلام على الكفاءة، لذا يمكنك التفكير في عدم استخدام التخزين المؤقت للاستعلام. بالإضافة إلى ذلك، يمكن أن تشير إضافة SQL_NO_CACHE إلى عبارة SELECT بوضوح إلى عدم استخدام المخزن المؤقت للاستعلام.
Qcache_free_blocks، إذا كانت القيمة كبيرة جدًا، فهذا يشير إلى وجود العديد من الأجزاء في المخزن المؤقت، يحدد query_cache_type ما إذا كان سيتم استخدام التخزين المؤقت للاستعلام

قمت بتعيين:
query_cache_size = 32M
query_cache_type=1

احصل على قيمة الحالة التالية:
تشير استعلامات Qcache في ذاكرة التخزين المؤقت 12737 إلى عدد العناصر المخزنة مؤقتًا حاليًا
يقوم Qcache بإدراج 20649006
يصل Qcache إلى 79060095 ويبدو أن معدل الاستعلام المتكرر مرتفع جدًا.
Qcache lowmem prunes 617913 هناك مرات عديدة تكون فيها ذاكرة التخزين المؤقت منخفضة جدًا.
Qcache غير مخبأ 189896
ذاكرة Qcache الحرة 18573912 مساحة ذاكرة التخزين المؤقت المتبقية الحالية
كتل Qcache المجانية 5328 يبدو هذا الرقم كبيرًا ومجزأًا بعض الشيء
إجمالي كتل Qcache 30953
إذا كانت الذاكرة تسمح بـ 32 ميجا، فيجب عليك إضافة المزيد.

يحدد table_cache حجم ذاكرة التخزين المؤقت للجدول. عندما يصل MySQL إلى جدول، إذا كانت هناك مساحة في المخزن المؤقت للجدول، فسيتم فتح الجدول ووضعه فيه، مما يسمح بالوصول بشكل أسرع إلى محتويات الجدول. من خلال التحقق من قيم الحالة Open_tables وOpened_tables في وقت الذروة، يمكنك تحديد ما إذا كنت بحاجة إلى زيادة قيمة table_cache. إذا وجدت أن open_tables يساوي table_cache، وأن open_tables ينمو، فأنت بحاجة إلى زيادة قيمة table_cache (يمكن الحصول على قيمة الحالة أعلاه باستخدام SHOW STATUS LIKE 'Open%tables'). لاحظ أنه لا يمكن تعيين table_cache على قيمة كبيرة بشكل أعمى. إذا تم تعيينه على مستوى عالٍ جدًا، فقد يتسبب ذلك في عدم كفاية واصفات الملف، مما يؤدي إلى أداء غير مستقر أو فشل الاتصال.

بالنسبة للأجهزة ذات الذاكرة 1 جيجا، القيمة الموصى بها هي 128-256.

إعدادات المؤلف
table_cache = 256

الحصول على الحالة التالية:
فتح الجداول256
فتح الجداول 9046
على الرغم من أن open_tables يساوي بالفعل table_cache، إلا أنه بالنسبة إلى وقت تشغيل الخادم، فهو يعمل لمدة 20 يومًا كما أن قيمة open_tables منخفضة جدًا أيضًا. ولذلك، فإن زيادة قيمة table_cache يجب أن تكون قليلة الفائدة. إذا ظهرت القيمة أعلاه بعد التشغيل لمدة 6 ساعات، فيجب عليك التفكير في زيادة table_cache.

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

يحدد log_bin ملف السجل إذا لم يتم توفير اسم الملف، فسيقوم MySQL بإنشاء اسم الملف الافتراضي نفسه. سيقوم MySQL تلقائيًا بإضافة فهرس رقمي بعد اسم الملف، وسيقوم بإعادة إنشاء ملف ثنائي جديد في كل مرة يتم فيها تشغيل الخدمة. بالإضافة إلى ذلك، استخدم log-bin-index لتحديد ملف الفهرس؛ استخدم binlog-do-db لتحديد قاعدة البيانات للتسجيل؛ استخدم binlog-ignore-db لتحديد قاعدة البيانات التي لا سيتم تسجيلها. ملاحظة: يحدد binlog-do-db وbinlog-ignore-db قاعدة بيانات واحدة فقط في كل مرة. يتطلب تحديد قواعد بيانات متعددة عبارات متعددة. علاوة على ذلك، ستقوم MySQL بتغيير جميع أسماء قواعد البيانات إلى أحرف صغيرة، ويجب عليك استخدام جميع الأسماء الصغيرة عند تحديد قاعدة البيانات، وإلا فلن تعمل.

لإيقاف هذه الوظيفة، ما عليك سوى إضافة علامة # أمامها
# سجل بن

تشغيل سجل الاستعلام البطيء (سجل الاستعلام البطيء)

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

لتمكين سجلات الاستعلام البطيئة، تحتاج إلى تعيين المعلمات log_slow_queries وlong_query_times وlog-queries-not-using-indexes.

يحدد log_slow_queries ملفات السجل إذا لم يتم توفير اسم ملف، فسيقوم MySQL بإنشاء اسم ملف افتراضي بنفسه. يحدد long_query_times حد الاستعلام البطيء، والقيمة الافتراضية هي 10 ثوانٍ. log-queries-not-using-indexes هي معلمة تم تقديمها بعد 4.1.0، مما يشير إلى أنه تم تسجيل الاستعلامات التي لا تستخدم الفهارس. يقوم المؤلف بتعيين long_query_time=10

إعدادات المؤلف:

Sort_Buffer_size = 1M
max_connections=120
انتظر_مهلة =120
back_log=100
read_buffer_size = 1M
thread_cache=32
Interactive_timeout=120
ترابط_الخيط = 4

وصف المعلمة:

back_log
عدد الاتصالات التي يتطلبها MySQL. يعمل هذا عندما يتلقى مؤشر ترابط MySQL الرئيسي الكثير من طلبات الاتصال في فترة زمنية قصيرة، ثم يستغرق مؤشر الترابط الرئيسي بعض الوقت (ولو لفترة وجيزة) للتحقق من الاتصالات وبدء موضوع جديد. تشير قيمة back_log إلى عدد الطلبات التي يمكن تخزينها في المكدس في فترة زمنية قصيرة قبل أن تتوقف MySQL مؤقتًا عن الرد على الطلبات الجديدة. فقط إذا كنت تتوقع العديد من الاتصالات في فترة زمنية قصيرة، فأنت بحاجة إلى زيادتها، بمعنى آخر، هذه القيمة هي حجم قائمة انتظار الاستماع لاتصالات TCP/IP الواردة. نظام التشغيل الخاص بك له حد خاص به على حجم قائمة الانتظار هذه. يجب أن تحتوي صفحة الدليل الخاصة باستدعاء نظام Unixاستماع(2) على مزيد من التفاصيل. تحقق من وثائق نظام التشغيل لديك لمعرفة الحد الأقصى لقيمة هذا المتغير. لن يكون لمحاولة تعيين back_log أعلى من الحد المسموح به لنظام التشغيل الخاص بك أي تأثير.
max_connections
الحد الأقصى لعدد الاتصالات المتزامنة هو 120. وإذا تجاوزت هذه القيمة، فسيتم استردادها تلقائيًا وسيتم حل المشكلات تلقائيًا.

thread_cache
لم أتمكن من العثور على أي تعليمات محددة، ولكن بعد تعيينه على 32، تم إنشاء أكثر من 400 موضوع في 20 يومًا، في حين تم إنشاء آلاف المواضيع في يوم واحد قبل ذلك، لذلك لا يزال مفيدًا.

thread_concurrency
# اضبط على رقم وحدة المعالجة المركزية الخاصة بك x2، على سبيل المثال، إذا كان هناك وحدة معالجة مركزية واحدة فقط، فإن thread_concurrency=2
# هناك 2 وحدة المعالجة المركزية، ثم thread_concurrency=4

تخطي innodb
#إزالة دعم innodb