سلام دوستان عزیز،
در پست امروز قصد دارم Buffer Pool Extensions که در SQL Server 2014 منتشر شد صحبت کنم. همانطوری که می دانید Buffer Pool یکی از مصرف کننده های اصلی حافظه در SQL Server است. وقتی دیتا را رسانه ذخیره سازی می خوانید، دیتا در Buffer Pool ذخیره می شود. هر چه حافظه اصلی بیشتری داشته باشید، Buffer Pool بزرگتری هم خواهید داشت (که از طریق تنظیمات Max Server Memory ست می شود).
بیشترین مشتریان SQL Server با مشکل محدودیت حافظه اصلی در database server ها مواجه هستند: وقتی تمام slot های حافظه اصلی اشغال شده اند، چطور می شود حافظه اضافی را در سرور نصب کرد؟ البته که می توانید به سرور بزرگتری مهاجرت کنید، اما آن هم یک داستان دیگر است… راه حل این مشکل بخصوص انتشار Buffer Pool Extensions در SQL Server 2014 است. با کمک Buffer Pool Extensions، SQL Server لایه دیگری از سلسله مراتب حافظه را بوجود می آورد. بیایید به
تصویر زیر نگاهی بیندازیم:
همانطوری که می بینید شما در بالا خود Buffer Pool را دارید (که خیلی سریع است) و در پایین رسانه ذخیره سازی سنتی را می بینید، که خیلی کند است. و Buffer Pool Extensions هم بین این دو قرار گرفته است. Buffer Pool Extensions خودش شامل یک فایل ساده است (که اصطلاحاً Extension File نامیده می شود) که باید روی رسانه خیلی سریع قرار بگیرد – مثل یک درایو SSD. یک Extension File درست مثل page file در سیستم عامل Windows است. به جای اینکه حافظه فیزیکی اضافی به Database server اضافه کنیم، یک Extension File روی یک درایو SSD پیکربندی کنید – فقط همین!
قبل از اینکه بخواهم در مورد پیکربندی و روش استفاده از Buffer Pool Extensions صحبت کنم، می خواهم کمی در مورد معماری و طراحی پشت صحنه Buffer Pool Extensions صحبت کنم. Buffer Pool سنتی در SQL Server همیشه بین Page های تمیز و کثیف (تمیز به معنی عاری از تغییر از زمان قرار گرفتن در حافظه اصلی) تفاوت قائل می شود. یک page کثیف در حافظه تغییر کرده اما هنوز در رسانه ذخیره سازی ثبت نشده است. فرآیندی به نام CHECKPOINT ظرف حدود یک دقیقه صفحات کثیف را روی رسانه ذخیره سازی ثبت می کند، و این به این معنی است که صفحات کثیف ما تمیز می شوند!
Buffer Pool Extensions خودش فقط وقتی استفاده می شود که Buffer Pool در SQL Server تحت فشار کمبود حافظه باشد. فشار حافظه به این معنی است که SQL Server نیاز به حافظه بیشتری نسبت به چیزی که در اختیار دارد است. در این شرایط Buffer برخی Page ها را که نسبت به سایرین کمتر به آنها رجوع شده از Buffer Pool خارج می کند. SQL Server در اینجا از یک سیاستی به نام Least Recently Used Policy (LRU) استفاده می کند. حالا اگر شما یک Extension File پیکربندی کرده باشید، SQL Server این page های اخراجی را در آنها می نویسد، به جای اینکه این کار را در رسانه ذخیره سازی که در پایین ترین سطح هرم بالا قرار دارد انجام دهد. اگر page یک page کثیف باشد، این page همزمان در رسانه فیزیکی هم نوشته می شود (از طریق یک عمل asynchronous I/O) .
بنابراین شما هیچ دیتایی را مادامی که با Buffer Pool Extensions کار می کنید از دست نمی دهید. در نقطه ای از زمان Extension File شما کاملاً پر می شود. در چنین شرایطی SQL Server باید صفحات قدیمی را از Extension File خارج کند (باز هم از طریق سیاست LRU) و در نهایت آنها را در رسانه فیزیکی بنویسد. Extension File به عنوان یک لایه اضافی بین Buffer Pool و رسانه ذخیره سازی عمل می کند.
حالا اجازه دهید ببینیم چطور Buffer Pool Extensions در SQL Server 2014 تنظیم می شود. SQL Server در اینجا دستور ALTER SERVER CONFIGURATION SET BUFFER POOL EXTENSION را ارائه می کند.
[sql] ALTER SERVER CONFIGURATION |
اولین محدودیتی که شما با آن مواجه هستید این است که Extension File حداقل باید هم اندازه خود Buffer Pool باشد. اگر شما اندازه فایل کوچکتری را تعیین می کنید، یک پیام خطای دوست داشتنی از SQL Server دریافت می کنید:
Buffer pool extension size must be larger than the current memory allocation threshold 1596 MB. Buffer pool extension is not enabled.
و محدودیت بعدی که شما قطعاً با آن مواجهید این است که شما نمی توانید اندازه Extension File را در طول زمان اجرای SQL Server تغییر دهید. مثلاً وقتی می خواهید Extension File را به اندازه ای بزرگتر تغییر دهید، باید Buffer Pool Extensions را غیر فعال کنید و دوباره آن را فعال کنید. و شما در طول زمان انجام این کار با افت Performance مواجه خواهید شد، چون شما لایه مهمی از Caching را در SQL Server غیر فعال می کنید!
و علاوه بر این شما نخواهید توانست اندازه Extension File را کاهش دهید، اندازه فعلی همیشه باید از اندازه قبلی بزرگتر باشد. در غیر این صورت با خطای زیر مواجه می شوید:
Buffer pool extension size must be larger than the current memory allocation threshold 4096 MB. Buffer pool extension is not enabled.
Config فعلی Buffer Pool Extensions می تواند از طریق یک dmv به نام sys.dm_os_buffer_pool_extension_configuration مشاهده شود.
شما چه زمانی باید از Buffer Pool Extensions استفاده کنید؟ توصیه مایکروسافت بر این است که workload شما باید write-heavy باشد، مانند OLTP workload. و وقتی شما در مورد خود Extension File صحبت می کنید باید یک SSD خیلی سریع خرجش کنید و نه یک هارد دیسک!
منبع: آموزش SQL Server نیک آموز
فوریه 2016، تورج عزیزی
20 Comments
Hamid J. Fard
مسعود طاهری
سلام حمید جان
سید سیاوش گلچوبیان
سلام
البته موضوع افت Performance درخصوص عملیات Delete بر روی SSD ها رو میشه با استفاده از قابلیت SSD TRIM به شدت بهبود داد، تا افت Performance ای که در حالت overwrite برای SSD ها پیش میاد (به همون دلیلی که ذکر کردید) جبران شه. البته پیش نیازش اینه که :
Hamid J. Fard
خب در آخر بازهم IO اضافی هستش که به سیستم تحمیل می شود و اینکه کلا اگر هم بهترین SSD استفاده شود باز هم سرعت بالایی نسبت به RAM درایو ها ندارد. و البته SSD طول عمر محدودی دارد و اگر احیانا Block خراب باشد هیچ موقع اخطار IO را به Sql server منتقل نمی کند.
مسعود طاهری
سلام
Hamid J. Fard
بار IO منظورم Pending IO , Queue IO در SQL Server و سیستم عامل برای هر پردازش است که زمان زیادی را تلف می کند.
سید سیاوش گلچوبیان
درضمن فراموش کردم به این موضوع اشاه کنم که خوشبختانه این قابلیت (SSD Trim) در سیستم عامل های Win2008R2, Win7, Win8.1, Win10 ساپورت میشه، البته Win7 یکسری محدودیت در این خصوص دارد.
حمیدقلیپور
با سلام و احترام
مهدی ربانی ذبیحی
با سلام و خسته نباشید
سیدمحمد حسینی
سلام
سیدمحمد حسینی
باز هم ببخشید
مسعود طاهری
–buffer_pool_extension غیر فعال کردن
سید سیاوش گلچوبیان
البته موضوع، سرعت بالای RAM Drive نسبت به SSD نبود، مسئله نحوه قلبه کردن به محدودیت RAM با حفظ حداکثری Performance بود.
قطعا RAM Drive راه حل بسیار پر سرعتی است ولی باید مسئله قیمت آن را نیز مدنظر قرار داد (که البته برای سازمانها اصلا عدد زیاد و مهمی نیست)، در عین حال محدودیت های RAM Drive ها را نیز باید در نظر داشت (البته من در مورد RAM Drive های سخت افزاری صحبت میکنم، زیرا RAM Drive های نرم افزاری برای بحث ما هیچ ارزشی ایجاد نمیکنند) مانند:
– محدودیت برخی از آنها در پشتیبانی از تکنولوژی های جدید SATA (عمدتا SATA 1 هستند، مانند i-Ram)
– پشتیبانی آنها از RAM های نسل DDR 2 (مانند i-Ram, ACARD,DDRDrive)
– محدودیت اسلات های حافظه آنها (حداکثر 8 اسلات 8 گیگ ارائه میدهند مانند ACARD)
– محدودیت زیاد آنها در مقدار حافظه قابل پشتیبانی (تا جایی که من اطلاع دارم بیشترین ظرفیت را ACARD ارائه میدهد با حجم حداکثر 64GB)
البته که اگر هیچ یک از این محدودیت ها برای ما مصداق نداشته باشد و با آنها مشکلی نداشته باشیم، RAM DRIVE راه حل بسیار پر سرعت و مناسبی میباشد (البته RAM Drive داریم تا RAM Drive، همونطور که SSD داریم تا SSD ، مثلا IOPS محصول i-Ram نسبت به خیلی از SSD ها مقدار کمتری است که اصلا جالب نیست، صد البته تنها به واسطه این شاخص نمیتوان قضاوت کرد و گفت i-Ram بد است زیرا همین i-Ram از Response time بسیار بهتری نسبت به همان SSD ها برخوردار است)
Hamid J. Fard
Z-Drive R2 تا ۲ ترابایت فضا قبول می کنه.
سید سیاوش گلچوبیان
Z-Drive R2 جزء RAM Drive ها نیست و از خانواده SSD با تکنولوژی NAND Flash-MLC هست و مانند دیگر SSD ها با محدودیت تعداد Write مواجه است در حالیکه RAM درایورها، از خانواده SDRAM هستند و با همچون محدودیتی مواجه نیستند، علاوه بر این سرعت های وحشتناکی ارائه میکنند (مثلا انواع DDR3 1066 Dual Channelها میتوانند تا 17GB/s خروجی/Transfer rate داشته باشند) که متاسفانه اکثرا بدلیل استفاده از پورت SATA در RAM Drive ها، نرخ انتقال آنها به شدت محدود میشود (SATA 3 خروجی 1.9GB/s ارائه میدهد و یا Thunderbolt 3 نرخ 4.8GB/s را و بالاترین سرعت ها را میتوان از PCI Express 3,4 16 lane گرفت که حدود 15GB/s و 31.5GB/s است و ظاهرا در حال حاضر فقط این دو هستند که گلوگاه استفاده از سرعت بالای SDRAM ها نیستند).
متاسفانه در بازار، من RAM Drive های مبتنی بر DDR3,4 ندیدم و فقط DDR2 موجود بوده، که اکثرا یا با SATA1 (با نرخ 150MB/s) کار میکرده و یا در برخی برندها نیز با PCI Express (خوشبختانه) ولی در هر صورت حجم آنها به 64GB محدود بود.
Hamid J. Fard
اره یادم نبود.
غلامحسین عبادی
با سلام و عرض ادب
ممنون از زحمات شما دوست عزیز . اگر راجع به موضوع Buffer Pool Extensions لینک سایتی را میدانید لطفا اطلاع دهید تا مطالعه بیشتری راجع به این موضوع داشته باشم . سپاسگزارم .
تورج عزیزی
سلام دوست عزیز
این مقاله از سایت sqlpassion.at انتخاب شده میتونید در سایت sqlskills.com و سایت های معروف دیگر هم حساب کنید.
حسن ضرابی
با سلام و خسته نباشید خدمت شما
از مقاله بسیار عالیتون واقعا ممنونم
من یک سئوال داشتم اگر ما BUFFER POOL EXTENSION را غیرفعال کنیم و دوباره از ram های server بخواهیم استفاده بکنیم آن page هایی که در BUFFER POOL EXTENSION قرار دارند آیا در دیسک به صورت فیزیکی نوشته می شوند مثلا ما 10 gb اطلاعات درون BUFFER POOL EXTENSION داریم حالا اگر بخواهیم غیرفعال کنیم این قابلیت رو آیا 10 gb در دیسک نوشته می شود منظورم در فایلهای mdf یا ndf هستند؟
با تشکر از مقاله بسیار عالی شما
تورج عزیزی
سلام
از اونجایی که فقط clean page ها یعنی پیج هایی که عاری از تغییر هستند به bpe منتقل میشوند نگرانی از این بابت وجود ندارد چون قبلا این پیج ها توسط checkpoint روی دیسک نوشته شده اند.
اطلاعات بیشتر:
Buffer Pool Extension Details
SSD storage is used as an extension to the memory subsystem rather than the disk storage subsystem. That is, the buffer pool extension file allows the buffer pool manager to use both DRAM and NAND-Flash memory to maintain a much larger buffer pool of lukewarm pages in nonvolatile random access memory backed by SSDs. This creates a multilevel caching hierarchy with level 1 (L1) as the DRAM and level 2 (L2) as the buffer pool extension file on the SSD. Only clean pages are written to the L2 cache, which helps maintain data safety. The buffer manager handles the movement of clean pages between the L1 and L2 caches.
The following illustration provides a high-level architectural overview of the buffer pool relative to other SQL Server components.
SSD Buffer Pool Extension Architecture
When enabled, the buffer pool extension specifies the size and file path of the buffer pool caching file on the SSD. This file is a contiguous extent of storage on the SSD and is statically configured during startup of the instance of SQL Server. Alterations to the file configuration parameters can only be done when the buffer pool extension feature is disabled. When the buffer pool extension is disabled, all related configuration settings are removed from the registry. The buffer pool extension file is deleted upon shutdown of the instance of SQL Server.