نوشته های من !

راجع به تجربه های شخصی از دنیای برنامه نویسی می نویسم، یا از روزمرگی هام …
بالا بردن کارآیی پایگاه داده با حذف منابع حاشیه ای (overhead) در MySQL
نویسنده:
۷ دی ۹۲

امکان داره توی  phpMyAdmin، موقعی که دارین جزئیات ( در ورژن های قدیمی Details و در ورژن های جدید Information) یک جدول رو نگاه میکنین چشمتون به عنوان overhead ( منابع حاشیه ی ) خورده باشه، که حالت ایده آل برای این عنوان صفر هست، ولی در مواقعی امکان داره این عنوان عددی رو نشون بده، تو این پست سعی دارم راجع به این بگم که اصلا این عنوان چی هست، چرا اتفاق می افته، چه ضررهایی داره و چه جوری میشه ازش جلوگیری کرد .

منابع حاشیه ای چی هستند ؟

تو یک نگاه کلی میشه گفت فضاهایی از جداول هستند که در اون ها اطلاعاتی ذخیره نشده به معنای ساده تر یعنی تعداد بیت هایی که اختصاص داده شدن، اما استفاده نمی شن . ( در منابع MySQL تعریف رسمی پیدا نکردم، ولی تعریفش همین هست، بدون منبع قبول کنین لطفا. )

چرا همچین پدیده ای اتفاق می افته ؟

منابع حاشیه ای وقتی اتفاق می افته که تغییر عمده ای توی جدول ایجاد بشه، تغییر عمده میتونه حذف ، درج و یا حتی به روز رسانی تعدادی زیاد رکورد، و یا حتی تغییراتی در ساختار جداول باشه .

با رخ دادن چنین پدیده هایی با یک فایل اطلاعات (data file) مواجه میشیم که در بین رکوردها فضاهای خالی ایجاد کرده که مورد استفاده قرار نمیگرن و باعث بالا رفتن حجم کلی فایل اطلاعات می شود. ( که این حجم اضافه همان منابع حاشیه ای یا overhead هست. )

این تداخل در فایل اطلاعات باعث کم شدن کارآیی جدل شده و در نتیجه در عملیات پایگاه داده تاخیر ایجاد می کند .

چگونه از این پدیده جلوگیری کنیم ؟

و اما جلوگیری از این پدیده اصلا کار سختی نیست. اگه به phpMyAdmin دسترسی دارین، در قسمت جزئیات ( که قبلا گفته شد ) یک لینک وجود داره به اسم Optimize table که با کیلیک بر روی این لینک و یا  با اجرای کوِئری زیر می تونیم از این پدیده جلوگیری کنیم :


OPTIMIZE TABLE [tbl_name, ...]

تو تعریف این کد گفته شده که باعث سازماندهی دوباره فایل حاوی اطلاعات (data file) و شاخص های اطلاعات (index) میشه، که این کار باعث کم شدن فضای ذخیره سازی و بالا رفتن بهره وریِ ورودی/خروجی در موقع دسترسی به جدول میشه و کاری که این کد انجام میده وابسته است به مکانیزم ذخیره سازی اطلاعات که جدول باهاش سروکار داره . ( ترجمه شده از سایت رسمی که چند باری لینک دادم بهش )

برای این که بحث کردن راجع به این موضوع بشتر قابل فهم بشه، باید بگم که MySQL برای ذخیره سازی اطلاعاتِ هر جدول از روش های مختلفی استفاده میکنه ( از قبیل : MyISAM, ARCHIVE, InnoDB, … ) و مقابله با منابع حاشیه ای در هر مکانیزم ذخیره سازی به روش خاصی انجام میشه، که می تونین لیست این روش ها رو از اینجا  مشاهده کنین .

همونطور که گفتم روش مقابله در مکانیزم های مختلف فرق داره، ولی از اونجایی که من خودم از MyISAM استفاده میکنم و فکر میکنم از بقیه روش ها بیشتر استفاده میشه، این مورد رو شرح میدم :

 بعد از حذف شدن و یا چند تغییر در یک جدول با مکانیزم MyISAM و ARCHIVE که دارای فیلدهایی با طول متغیر هستند ( از قبیل : VARCHAR, VARBINARY, BLOB و یا TEXT )، سطرهای حذف شده در یک لیست پیوندی (linked list) نگهداری میشن و در عملیات درج بعدی، از مکان ذخیره سازی سطر های حذف شده دوباره استفاده میشود . شما میتونین از Optimize table استفاده کنین تا مشکل فضاهای استفاده نشده و فایل اطلاعات نا منظم رو حل کنید.

 بعد از تغییرات وسیع در یک جدول اجرا کردن این دستور امکان داره باعث بهبود کارآیی در اجرای دستورات بشه، حتی به اندازه خیلی زیاد . ( ترجمه شده از مبعِ رسمی MySQL )

منابع استفاده شده :

سایت رسمی مای اسکیو اِل : http://dev.mysql.com/

سایت های پرسش و پاسخ : http://stackoverflow.com/

2 پیام برای بالا بردن کارآیی پایگاه داده با حذف منابع حاشیه ای (overhead) در MySQL

  1. واسه جدولهای حجیم تحت بار زیاد که overhead زیادی داشته باشن شاید این کار بتونه تاثیر قابل توجهی در پرفورمنس بذاره، اما برای جدولهای عادی و تراکنش های عادی فکر نمیکنم این قضیه در عمل هیچ اهمیتی داشته باشه. معمولا اون مقدار overhead عددی نیست امروزه روز.

    • علا عالم فلکی گفت:

      حرف شما درسته، در واقع بحث بالا بردن کارایی موقعی به وجود میاد که حجم اطلاعات بالا باشه و پردازش اونها وقت گیر، یک سیستم ساده اگر توش قوانینی هم رعایت نشه به مشکلی بر نمیخوره، به هر حال حرف شما صحیح هست و من هم توی مقاله گفتم که “موقع سروکار داشتن با تعداد زیادی رکورد” این اتفاق قابل مشاهده هست، پس مفهموم حرف ها یکی هست .
      ولی یه نکته ای هست که من تو مقاله نگفتم، این هست که اگه صبر کنین مقدار این قم به عدد قابل توجهی برسه، بعد دیگه اعمال کردن کوئری که گفته شده و بازسازی DATA FILE زمان زیادی میبره و امکان داره سایت رو DOWN کنه .

پاسخ دهید

نشانی ایمیل شما منتشر نخواهد شد. بخش‌های موردنیاز علامت‌گذاری شده‌اند *