Buffer Pool در دیتابیس‌های رابطه ای

۱۴۰۳/۰۶/۱۰

Bufferpool یا Cache چیست؟

بافر پول بخشی از حافظه اصلی سیستم است که دیتابیس در اختیار (Allocate) می گیرد. یک دیتابیس برای افزایش کارایی برای بخش‌های مختلف بخشی از حافظه را در اختیار می‌گیرد. اینجا قصد داریم به یک مورد از آن که برای کارایی دیتابیس بسیار مهم است اشاره کنیم. دیتابیس پس از دریافت دستور Select ابتدا داده را در بافر پول جستجو می‌کند. چنانچه داده مورد نظر در بافر پول موجود باشد داده به کاربر ارسال می‌شود. در صورتی که در بافرپول نباشد درخواست خواندن از دیسک (IO) می‌کند و در بافر پول قرار می‌دهد با به اصطلاح load می‌کند و بعد از آن به کاربر ارسال می‌کند. چنانچه دستورات از نوع نوشتن باشد باز هم ابتدا صفحات (Pages) به حافظه منتقل می‌شوند و تغییرات در صفحات رخ می‌دهد اما لزوما جهت افزایش کارایی در همان لحظه در فایلی که روی دیسک قرار دارد نوشته نمی‌شوند.

استفاده از بافر چه مزیتی دارد؟

با ارسال دستور به دیتابیس، در صورتی که داده مورد نظر در بافر باشد کاربر با سرعت بیشتر و زمان کمتری داده را دریافت می‌کند و دیگر منتظر خواندن از روی دیسک(IO) نمی‌ماند. در واقع سرعت پاسخ (Response time) کاهش می‌یابد و دفعات IO کاهش می‌یابد. به عبارتی هرچه داده‌های مفید و پرکاربرد در حافظه بافر پول قرار بگیرند کاربران پاسخ خود را سریع‌تر دریافت می‌کنند و عملا سرعت ذخیره و بازیابی افزایش می‌یابد.

مفهوم صفحه (Page) چیست؟

در دیتابیس‌های رابطه‌ای مانند SQL Server، My SQL ، Oracle، DB2 و ... داده‌ها اغلب بصورت سطری(ROW) در کنار هم در غالب صفحه (Page) ذخیره می‌شوند. داده‌ها می‌توانند بصورت ستونی ذخیره شوند که کاربرد خود را دارند و الان این موضوع مد نظر نیست. در صورتی که داده بصورت سطری در صفحه نوشته شوند در واقع بصورت فیزیکی هم داده‌ها روی دیسک به همین ترتیب نوشته می‌شوند و در زمان خواندن اطلاعات بجای خواندن یک یا چند سطر صفحات حاوی رکوردها خوانده می‌شود و به Buffet Pool منتقل می شوند. بنابراین حتی در صورت خواندن یک فیلد از یک رکورد یا یک سطر از یک جدول در عمل یک صفحه خوانده می‌شود. صفحات معمولا در غالب 4K ، 8K و 16K می‌باشند و انتخاب اندازه صفحه به طول و تعداد رکورد و طراحی و سلیقه راهبر مربوط می‌شود.

چند اصطلاح :

Bufferpool size : اندازه بافر پول توسط ادمین دیتابیس تعیین می‌شود. معمولا مقادیر حداقل، حداکثر و پیش فرض دارند. مقدار حداقل مقداریست که در صورتی که دیتابیس در اختیار بگیرد آن را آزاد نمی‌کند. مقدار حداکثر مقداری است که حافظه بافر پول تا آن مقدار قابل تخصیص است و در صورت عدم استفاده فضای آن آزاد و در اختیار سیستم عامل قرار می‌گیرد.

Dirty Pages, Updated Pages : صفحاتی که تغییر می‌کنند اصطلاحا صفحات کثیف یا تغییر یافته می‌نامند. این صفحات در زمان مناسب به دیسک منتقل خواهند شد.

Clean pages, Available pages: زمانی که داده‌های صفحه تغییر یافته روی دیسک نوشته می‌شوند و می‌توانند بازنویسی شوند و یا پاک شوند.

Hit Ratio : نسبت صفحات پیدا شده در بافر به کل صفحات خوانده شده را HR می نامند. این شاخص برای ارزیابی بافر پول استفاده می‌شود. هرچه عدد به سمت 100 نزدیک باشد نشان از استفاده بهینه از بافر پول است. بدین معنی که اطلاعات جداول و ایندکس بیشتر از حافظه خوانده شده و بار کمتری روی دیسک‌ها بوده و در نتیجه عملیات IO کمتر انجام شده است. هرچه HR کمتر باشد نشان از کندی دیتابیس، زمان پاسخ بالا به کاربران و اجرای دستورهای بد و نتایج پرحجم می‌باشد.

Hit Ratio = (total get pages – total pages read) / total get pages

Offload, Page Write : در زمان‌هایی دیتابیس تصمیم می‌گیرد که صفحات تغییر یافته را روی دیسک بنویسد. بعضی دیتابیس‌ها پارامترهایی نظیر تعداد کامیت (Commit) در دقیقه، میزان پر بودن بافر پول یا زمانبندی خاصی دارند. در هر صورت وقتی زمان آن برسد یا پارامترهایی به حد آستانه خود برسند دیتابیس صفحات تغییر یافته را روی دیسک می‌نویسد و صفحات آماده استفاده مجدد (Rewrite) می شوند.

Page Stealing: زمانیکه بافرپول پر باشد و برای خواندن صفحات جدید نیاز به صفحات خالی در بافر پول باشد. چنانچه امکان تخصیص بیشتر حافظه نباشد، دیتابیس صفحاتی را برای پاک کردن کاندید می‌کند. برای این منظور معمولا از الگوریتم LRU که صفحاتی کمتر استفاده شدن استفاده می‌کند و یا FIFO به صفحاتی که اول آمده‌اند را استفاده می‌کند.

آزادسازی بافر پول چطور انجام می شود؟

در بخش Stealing Page اشاره شد. معمولا حافظه اصلی نسبت به حجم جداول و ایندکس‌ها کمتر است. بنابراین اختصاص ظرفیت بالا برای بافر پول‌ها امکان پذیر نیست. بنابراین زمانیکه دیتابیس نیاز به فضای خالی در بافر پول می‌کند اقدام به آزاد سازی صفحات می‌کند. یا حتی زمانیکه صفحات استفاده نمی‌شوند آن‌ها را پاک می‌کند و به این ترتیب حافظه اصلی را تا رسیدن به اندازه حداقلی آزاد می‌شوند.

تنظیم اندازه Bufferpool

مقداردهی اولیه و تعیین میزان بافرپول در کارایی دیتابیس تاثیر زیادی دارد. مقدار کم باعث افزایش زمان پاسخ و افزایش دفعات IO می شود. تعیین مقدار زیاد آن هم باعث مصرف حافظه اصلی می‌شود و عملا سیستم‌ها و برنامه های دیگر از کمبود حافظه رنج می‌برند. تنظیم اندازه بافرپول و تخصیص آن‌ها به جداول و ایندکس‌ها نیازمند تجربه بالایی است و بهتر است بافرپول‌های یک دیتابیس مانیتور شوند و مورد ارزیابی قرار بگیرند.

توصیه به برنامه‌نویسان و DBA

در سیستم‌های OLTP از اجرای کوئری هایی که پیمایش یا اسکن داده حجیم می‌کنند خودداری کنید. اسکن داده حجیم باعث پاک شدن یا خارج شدن صفحات مفید می‌شود و در صورت نیاز بایستی مجدد در حافظه Load شوند.
طراحی و ساخت ایندکس مناسب از خواندن جدول‌های بزرگ جلوگیری می‌کند. افزایش ایندکس سربار زیادی در نوشتن جدول دارد اما ایندکس مناسب برای کوئری که حاوی Where می‌باشد نتایج بهتری دارد.
داده‌های تکراری و پایه را در کلاینت نگهداری (cache) کنید. مقیم کردن داده‌های ثابت مثل لیست شهرها، شعبه‌ها و ... باعث کاهش ترافیک شبکه و کمتر شدن بار دیتابیس می‌شوند و بافرپول بیشتری برای داده‌های مهم استفاده می‌شوند.
بصورت مستمر بافرپول را مانیتور کنید. در زمان‌های پیک سیستم از اجرای کوئری هایی که باعث کم شدن کارایی بافر پول می شوند خودداری کنید.
کوئری‌ها را قبل از انتقال به محیط عملیاتی Explain کنید. در صورت مشاهده اسکن حجیم اقدام به بهینه‌سازی کنید.
در برنامه‌های کاربردی، تاجایی که ممکن است از پذیرش ویژگی‌هایی که نیاز به پیمایش یا تغییرات عمده در داده‌ها می‌باشد خودداری نمایید.
در سیستم‌های OLTP از قبول تهیه گزارشات پر حجم و کوئری‌های محاسباتی سنگین خودداری نمایید.
در صورتی که دیتابیس این امکان را دارد، جداول پایه و ثابت در بافرپول بصورت Fix تنظیم نمایید که هیچ وقت داده‌های آن از بافر پول خارج نشوند.
بهتر است برای هر نوع دیتابیسی که استفاده می‌کنید اطلاعات فنی، زیر ساخت و توصیه های آن بطور دقیق مطالعه شود.

مانیتورینگ Bufferpool

در این بخش بهتر است کمی با شاخص‌های مهم که با پلتفرم مانیتورینگ معین می‌شود مانیتور کرد اشاره کنیم. در SQL Server می‌توانیم موارد زیر را مانیتور کنیم :