فضای Buffer Pool در MySQL InnoDB – بخش دوم

buffer pool در MySQL InnoDB فضایی از حافظه اصلی است که برای Cache کردن داده ها (جداول و ایندکس ها) از دیسک به درون حافظه اصلی استفاده می شود تا سریعتر بتوان به داده ها دسترسی داشت. در پایگاه داده ها سرعت پایین عملیات های I/O مربوط به دیسک، یکی از اصلی ترین مسائل است، حتی اگر از دیسک های SSD برای ذخیره سازی داده ها استفاده شود.

MySQL فضای Buffer Pool را به صفحه هایی ۱۶ کیلوبایتی تقسیم می کند و هر صفحه می تواند شامل یک یا چند صفحه باشد. زمانی که کوئری به داده ها نیاز داشته باشد، داده ها از درون دیسک بر روی buffer pool و در این صفحه ها Cache می شوند. در پایگاه داده MySQL درون اسکمای INFORMATION_SCHEMA جدولی به نام innodb_buffer_page وجود دارد که می توان اطلاعاتی از آن بدست آورد.

فضای Buffer Pool در MySQL InnoDB – بخش نخست

جدول innodb_buffer_page به ازای هر صفحه در buffer pool یک سطر را ذخیره می کند. بنابراین برای پیدا کردن تعداد کل صفحه های buffer pool از کوئری زیر استفاده کنید.

بنابراین اندازه buffer pool تقریبا برابر است با تعداد صفحه ها ضربدر ۱۶ کلیوبایت که اندازه هر صفحه است.

Pages in our buffer pool × 16 KB per page

جدول innodb_buffer_page

انواع صفحه ها در MySQL InnoDB Buffer Pool

  • ALLOCATED -> Freshly allocated page
  • INDEX -> B-TREE Index
  • UNDO_LOG -> Undo log page
  • INODE -> Index Node
  • IBUF_FREE_LIST -> Insert buffer free list
  • IBUF_BITMAP -> Insert buffer bitmap
  • SYSTEM -> System page
  • TRX_SYSTEM -> Transaction System Data
  • FILE_SPACE_HEADER -> File space header
  • EXTENT_DESCRIPTOR -> Extent descriptor page
  • BLOB -> Uncompressed BLOB page
  • COMPRESSED_BLOB -> First compressed BLOB page
  • COMPRESSED_BLOB2 -> Subsequent comp BLOB page
  • IBUF_INDEX -> Insert buffer index
  • UNKNOWN -> unknown

از کوئری زیر نیز می توان برای پیدا کردن تعداد هر یک از انواع صفحه ها در فضای buffer pool استفاده کنید.

جدول innodb_buffer_page برای نظارت بر کارایی MySQL در سطوح بالا مفید خواهد بود. همانطور که در بخش نخست گفته شد، تا زمانی که جدول، سطرهای جدول، پارتیشن و ایندکس ها حذف نشوند، صفحه های اختصاص داده شده به هر یک در تا زمانی که فضای کافی وجود نداشته باشد، در فضای buffer pool باقی می مانند. همچنین الگوریتم مدیریت کَش LRU برای مدیریت فضای buffer pool استفاده می شود.

مدیریت Cache و الگوریتم های مدیریت Cache

اندازه buffer pool به طور مستقیم به اندازه سطرهای جداول و ایندکس ها وابسته است. کوئری زیر نیز جزییات بیشتری از جدول innodb_buffer_page را نشان می دهد.

شکل ۱ خروجی کوئری بالا را نشان می دهد. (جدول 18-21 را مشاهده کنید.)

innodb_buffer_page_query_1-300x187

ذخیره سازی (Dumpping) و بارگذاری مجدد (Reloading) محتوای Buffer Pool

زمانی که سرور MySQL را Stop (متوقف) و Restart (راه اندازی) مجدد می کنیم، به دلیل آنکه buffer pool در حافظه اصلی (RAM) قرار دارد، محتوای آن از بین خواهد رفت (داده های کَش شده از دست می روند). از MySQL 5.6 به بعد یک ویژگی جدید اضافه شده است که اجازه می دهد محتوای buffer pool را پیش از shutdown کردن فرایند mysqld ذخیره (dump) کنیم. پس از راه اندازی فرایند mysqld می توانیم محتوای ذخیره شده را مجدد بارگذاری کنیم. می توانیم هر زمان به منظور بارگذاری مجدد، محتوای buffer pool را ذخیره کنیم.

از دستور زیر برای نمایش وضعیت فعلی Buffer Pool استفاده می شود. شکل ۲ نمونه خروجی از دستور زیر را نشان می دهد.

buffer_pool_status_command-300x134

از شکل ۲ مشخص است که تعداد ۴۸۹ صفحه database (یا database page) وجود دارد. زمانی که محتوای buffer pool را بر روی دیسک ذخیره (Dump) می کنیم، تنها database page بر روی دیسک ذخیره خواهند شد و زمانی که سرویس mysqld راه اندازی مجدد می شود، مجدد درون حافظه اصلی بارگذاری خواهند شد. از دستور زیر برای ذخیره محتوای buffer pool بر روی دیسک استفاده می شود.

با اجرای دستور بالا فایلی به نام ib_buffer_pool در زیر دایرکتوری پیشفرض MySQL ایجاد خواهد شد. ib_buffer_pool فایلی متنی است که می توانید با ویرایشگرهای متنی آنرا باز و مشاهده کنید. دستور زیر نیز وضعیت Dump را نشان می دهد.

محل پیشفرض فایل های پایگاه داده MySQL/Maria DB

همچنین اگر می خواهید در زمان shutdown بودن MySQL از دستور زیر برای ذخیره کردن استفاده کنید.

برای آنکه محتوای فایل dump در زمان راه اندازی مجدد بارگذاری شود، عبارت زیر به دستور mysqld اضافه می شود.

و یا از دستور زیر برای بارگذاری زمانی که سرور mysql در حال اجرا است، استفاده می شود.