ربما لا تكون هذه مقالة ، ولكنها ملاحظة قصيرة حول بعض ميزات العمل مع الجداول الكبيرة في MySQL.
سبب الكتابة هو إضافة عمود جديد يوميًا إلى الجدول. ولكن تبين أن كل شيء ليس بهذه البساطة كما هو متوقع.
لذلك ، في إحدى الأمسيات ، حتى لا نزعج عملائنا الأعزاء ، نحتاج إلى إضافة عمود إلى الجدول.
لجعله أكثر وضوحًا ، خصائص الجدول والقاعدة:
- حجم الجدول 110GB
- عدد الصفوف: 7.5 مليون
- محرك التخزين: InnoDB
- يوجد خادمتان sql متصلتان وفقًا لنظام السيد والعبد ، بينما يوجد السيد على SSD ، والعبد موجود على القرص الصلب
يبدو أنه حل واضح لإضافة عمود - تغيير الجدول.
alter table table_name add source varchar(32)
استخدمناها (نعم ، فهمنا أنها كانت سيئة ، ولكن في هذه الحالة بالذات كانت المخاطر ضئيلة).
وكانت النتائج غير سارة للغاية:
- في المعالج ، استغرقت عملية إضافة عمود حوالي ساعة (!)
- على العبد ، بدأت بعد انتهاء العملية على السيد واستمرت حوالي 8 ساعات (!!)
- أثناء جدول التبديل ، توقف نسخ البيانات (!!!) تمامًا عن العبد
ولكن هناك بطانة فضية: كانت المكافأة الصغيرة أنه بعد إضافة عمود ، انخفض حجم الجدول بنسبة 10 ٪.
في الرسوم البيانية أدناه هو واضح للعيان.
تحميل وحدة المعالجة المركزية الرسم البياني على المعالج.
تحميل وحدة المعالجة المركزية الرسم البياني على الرقيق.
تأخر النسخ المتماثل.ما هي المشاكل التي تنتظر أولئك الذين يفعلون ذلك على طاولات المعارك؟
أولاً ، خلال مدة جدول Alter ، لا يمكنك كتابة البيانات إلى الجدول (ولكن يمكنك قراءتها). في الواقع ، يعتمد الأمر على إصدار MySQL ، في الأخير لا ، ولكنك تحتاج إلى فهم ما يمكن أن يفعله إصدارك بالضبط لتجنب المشاكل.
وفقًا لذلك ،
إذا كان الجدول كبيرًا ، فسيكون وقت عدم التوافر كبيرًا (كما هو الحال معنا ، عند استخدام SSD استغرق الأمر ساعة ، وعلى قرص عادي - 8 ساعات) ، وهو ما من غير المرجح أن يتوقعه عملاؤك.
ثانياً ، كما في حالتنا ، أثناء تنفيذ Alter Table ،
توقف تزامن جميع الجداول ، وليس فقط
الجدول الذي قمنا بتغييره ،
تمامًا على العبد. لذلك ، إذا كانت بياناتك على الخادم الثاني مهمة ويجب أن تكون جديدة - فإنك تخاطر بالترك بدون تحديثات مع كل العواقب المترتبة عليها.
هناك نقطة أخرى غير واضحة واجهناها عند إضافة عمود (ولكن هذا كان وقتًا آخر) -
هناك حاجة إلى مساحة إضافية على القرص .
الحقيقة هي أن بعض التغييرات في الجداول تعيد إنشاء الجدول من البداية ، لذلك لا تحتاج إلى مساحة أقل من الجدول الموجود. بالنسبة للطاولات الكبيرة ، على التوالي ، هناك حاجة إلى مساحة كبيرة ، بعبارة ملطفة. وفقًا للوثائق ، يتم إنشاء جدول مؤقت في نفس الدليل الأصلي.
بالإضافة إلى ذلك ، أثناء تنفيذ جميع أنواع Alter Table ، تتم كتابة جميع التغييرات في ملف السجل ، بحيث بعد التغييرات ، يمكن نقل البيانات خلال الوقت الذي تم فيه تنفيذ العملية. وهنا أيضًا ، قد تنتظر مفاجأة غير سارة: إذا تغير الجدول لفترة طويلة ، وكان حجم العمليات كبيرًا ، فقد لا تنتهي مساحة القرص فقط ، ولكن قد يتم أيضًا تجاوز حد حجم الملف المحدد في إعدادات SQL. في أي حال ، "تفشل عملية DDL عبر الإنترنت ، ويتم التراجع عن عمليات DML المتزامنة غير الملتزمة".
لقد واجهنا حقيقة أن دليل الملفات المؤقتة كان صغيراً ، كنتيجة لذلك كان علينا إعادة تعريف
innodb_tmpdir .
لمعرفة أين يشير المتغير حاليًا ، يمكنك القيام بذلك:
select @@GLOBAL.innodb_tmpdir;
ضع في اعتبارك أن حجم الدليل المؤقت قد يحتاج أيضًا إلى حجم فهرس + جدول. بشكل عام ، تخزين على الفضاء.
حتى لا تتكرر الوثائق ، اقرأ بمزيد من التفاصيل على
https://dev.mysql.com/doc/refman/5.7/ar/innodb-online-ddl-space-requirements.htmlولكن كيف نفعل ذلك؟ في الواقع ، لا توجد وصفة واحدة لجميع المناسبات.
أحد الخيارات الممكنة ، كما نفعل للجداول التي ليست مهمة للتحديث:
- إنشاء جدول جديد مع الهيكل المطلوب
- املأ الحقول من الجدول القديم
- حذف أو إعادة تسمية الجدول القديم
- إعادة تسمية الجديد
أكرر أن هذا يعمل لجداول التحديث غير الضرورية. وفي الوقت نفسه يتجنب حظر النسخ المتماثل. يجب أن يؤخذ في الاعتبار أن ملء جدول جديد يجب أن يتم بطريقة تتيح متابعة النسخ المتماثل ، ولأنه يعمل بشكل متتابع ، لا يمكنك القيام بتعبير sql واحد ، يجب تقسيمه إلى عدة استعلامات صغيرة بين إجراء النسخ المتماثل للبيانات الأخرى. في حالات أخرى ، تكون الخيارات الأخرى ممكنة ، وربما يشارك شخص ما في التعليقات.
UPD. اقترح
Syavadee استخدام تغيير المخطط عبر الإنترنت. في الواقع ، تطبق الخوارزمية الموضحة أعلاه مع الأشياء الجيدة الإضافية.
UPD. يوصي
Arheops بتمكين النسخ المتماثل المتماثل / gtid لحل مشكلات النسخ المتماثل.
حسنًا ، بالمناسبة ، في بعض الأحيان ، لفهم حجم الطاولة وعدد الصفوف الموجودة فيها ، عليك أن تفعل طريقة التدريس
select count(*) from table_name
ولكن على الطاولات الكبيرة والمحملة ، فإن هذا ليس هو الأسرع في التشغيل ، خاصةً عندما يكون لديك نصف مليون صف أو أكثر.
لذلك ، لتقدير تقريبي لوحدة التخزين ، يمكنك استخدام الطريقة التالية:
SHOW TABLE STATUS FROM express where name='table_name'
لسوء الحظ ، في محرك InnoDB ، قد يختلف الحجم الناتج بنسبة 50 بالمائة (في حالتنا ، مع الجدول أعلاه ، يبلغ العدد الفعلي للسجلات حوالي 7.5 مليون ، وهذه الطريقة أظهرت 5 ملايين فقط) ، لكن هذا مناسب تمامًا لتقدير إرشادي.
هذا كل شيء ، آمل أن تساعد هذه الملاحظة شخصًا على تجنب مشكلات كبيرة مع أوامر SQL غير المفترضة الضارة.