یکی از مشکلات رایج در عملکرد پایگاه داده، بهینه‌سازی TempDB است. چند نکته اساسی برای به دست آوردن بهترین عملکرد ممکن از TempDB شما وجود دارد، اما باید در نظر داشته باشید که هر سروری بصورت جداگانه نیاز به بهینه‌سازی دارد. گزینه‌ای در تنظیمات سرور وجود ندارد که با فعال کردن آن، عملکرد بهینه به دست آید. شما باید جزئیات خاص مثال پایگاه داده خود را بررسی کنید و سرور را به گونه‌ای تنظیم نمایید که بهترین عملکرد را از ترکیب سخت‌افزار، نرم‌افزار و بار کاربری خود به دست آورید.
پیکربندی صحیح زیرسیستم‌های ورودی/خروجی برای دستیابی به عملکرد و کارکرد بهینه SQL Server حیاتی است. برخی از رایج‌ترین و بهترین شیوه‌های توصیه شده بهتر است در زمان پیکربندی و نصب سرور انجام شوند.
درک ویژگی ها و نیازهای دسترسی به دیسک SQL Server
برای موفقیت در طراحی و استقرار ذخیره سازی برای نصب SQL Server خود، باید درک اساسی از الگو ها و ویژگی های دسترسی به دیسک SQL Server داشته باشید. Performance Monitor بهترین مکان برای جمع آوری این اطلاعات برای یک برنامه است که اجازه می دهد به اطلاعات خاصی برسید.
- نسبت خواندن در مقابل نوشتن برنامه چیست؟
- معمولاً نرخ ورودی/خروجی (عملیات ورودی/خروجی در ثانیه، مگابایت در ثانیه و اندازه عملیات ورودی/خروجی) چقدر است؟
- شمارنده‌های perfmon را نظارت کنید:
- میانگین بایت خوانده شده/نوشته شده در ثانیه
- خواندن/نوشتن در ثانیه
- بایت خوانده شده از دیسک/نوشته شده به دیسک در ثانیه
- میانگین زمان دیسک به ازای هر خواندن/نوشتن
- میانگین طول صف دیسک
- چقدر از دسترسی‌های ورودی/خروجی به صورت ترتیبی یا تصادفی هستند؟ آیا این عمدتاً یک برنامه OLTP است یا انباره داده رابطه‌ای؟
برای عملکرد بهینه، همیشه اسپیندل بیشتر(IOps بالاتر) و سریع‌تر بهتر است
- دیسک‌های اضافی با اسپیندل‌های بیشتر همیشه اجازه عملکرد بیشتر را می‌دهند و احتمال اینکه سرعت دیسک منبع گلوگاهی برای عملکرد اوج شما باشد را کاهش می‌دهند.
- مطمئن شوید تعداد اسپیندل‌های کافی برای پشتیبانی از نیازهای ورودی/خروجی شما با تاخیر قابل قبول وجود دارد.
- از فایل گروه‌ها برای نیازهای مدیریتی مانند پشتیبان گیری و بازیابی، در دسترس بودن بخشی از پایگاه داده و غیره استفاده کنید.
- از فایل‌های داده برای “نواربندی” پایگاه داده در سراسر پیکربندی ورودی/خروجی خاص شما (دیسک‌های فیزیکی، LUNها و غیره) استفاده کنید. پایگاه داده خود را روی چند فایل داده توزیع نمایید.
طراحی‌های ساده‌تر به طور کلی عملکرد خوب و انعطاف‌پذیری بیشتری نسبت به طراحی‌های پیچیده ورودی/خروجی دارند
- این به طور اساسی رویکرد “ساده نگه دارید احمق” KISS به طراحی ورودی/خروجی است.
- از تلاش برای بیش از حد بهینه‌سازی ورودی/خروجی با قرار دادن انتخابی شیءها روی اسپیندل‌های جداگانه اجتناب کنید مگر اینکه برنامه را بسیار خوب درک کنید.
- مطمئن شوید از ابتدا به استراتژی رشد فکر کنید. همان‌طور که اندازه داده‌های شما رشد می‌کند، چگونه رشد فایل‌های داده، LUNها یا گروه‌های RAID را مدیریت خواهید کرد؟ بسیار بهتر است از ابتدا برای این موضوع طراحی کنید تا اینکه در یک استقرار تولیدی بعداً فایل‌های داده یا LUN(ها) را مجدداً متوازن کنید.
پیکربندی‌ها را قبل از استقرار اعتبارسنجی و آزمایش کنید
- آزمایش عبوردهی پایه زیرسیستم ورودی/خروجی را قبل از استقرار مثال SQL Server خود انجام دهید. مطمئن شوید این آزمایش‌ها قادر به دستیابی به نیازهای ورودی/خروجی شما با تاخیر قابل قبول هستند.
- SQLIO یکی از ابزارهایی است که می‌تواند برای آزمایش عملکرد مورد استفاده قرار گیرد. یک سند با پایه‌های آزمایش یک زیرسیستم ورودی/خروجی با این ابزار ارائه شده است. این ابزار اکنون از رده خارج شده و جایگزین آن می توانید از DiskSpd استفاده کنید.
- DiskSpd یک افزوده جدید به مجموعه ابزارهای در دسترس شماست. از این ابزار می توانید برای تست عملکرد سیستم ذخیره سازی خود استفاده کنید. این برنامه ابزاری است برای تولید I/O به منظور انجام میکرو-بنچمارک. با این بازار می توانید بارکاری مد نظر خود را بصورت مصنوعی ساخته و برنامه خود را پیش از توزیع تست نمایید.
- درک کنید که هدف از اجرای این آزمایش‌ها شبیه‌سازی دقیق ویژگی‌های ورودی/خروجی SQL Server نیست، بلکه آزمایش حداکثر عبوردهی قابل دستیابی توسط زیرسیستم ورودی/خروجی برای انواع معمول ورودی/خروجی SQL Server است.
همیشه فایل‌های لاگ را روی دیسک‌های RAID 10 قرار دهید
- عملکرد اوج ارائه شده توسط RAID 10 واقعاً به عملکرد کلی لاگ کمک خواهد کرد.
- بهترین حفاظت در برابر خرابی سخت‌افزار
- عملکرد بهتر نوشتن
- به طور کلی RAID 10 عبوردهی بهتری را برای برنامه‌های با نوشتن بیشتر فراهم خواهد کرد. مقدار عملکرد به دست آمده بسته به پیاده‌سازی سازنده سخت‌افزاری RAID متفاوت خواهد بود. رایج‌ترین جایگزین RAID 10، RAID 5 است.
در سطح دیسک فیزیکی، لاگ را از داده‌ها جدا کنید
- شما نیاز دارید فایل‌های لاگ شما روی دیسک فیزیکی متفاوتی نسبت به دیسک داده باشند، نه فقط دیسک‌های منطقی متفاوت.
- این کار در محیط‌های SQL تلفیق شده، ممکن است امکان‌پذیر نباشد. پس باید ویژگی‌های ورودی/خروجی را در نظر بگیرید و فایل‌ها با ویژگی‌های ورودی/خروجی مشابه (یعنی همه لاگ‌ها)
- ترکیب بارکارهای ناهمگون (بارکارها با ویژگی‌های بسیار متفاوت دسترسی به دیسک و تاخیر) می‌تواند اثرات منفی روی عملکرد کلی داشته باشد(مثلا قرار دادن داده‌های Exchange و SQL روی همان اسپیندل‌های فیزیکی).
- شما نیاز دارید دیسک‌ها را در سطح فیزیکی درک کنید تا به بهترین پیکربندی ممکن برسید، بنابراین واقعاً نیاز دارید پیکربندی سخت‌افزار مجازی را درک کنید اگر سرور شما در یک محیط مجازی قرار دارد.
در نظر گرفتن پیکربندی پایگاه داده TempDB
- مطمئن شوید TempDB را پس از نصب SQL Server به ذخیره‌سازی با فضای کافی منتقل کرده و از پیش اندازه آن را تنظیم کنید.
- عملکرد ممکن است از قرار دادن TempDB روی RAID 10 (بسته به استفاده از TempDB ) سود ببرد.
- برای پایگاه داده TempDB، ۱ فایل داده به ازای هر CPU ایجاد کنید.
- TempDB را روی دیسک جداگانه‌ای نسبت به پایگاه‌های داده کاربر قرار دهید.
- TempDB را روی سریع‌ترین زیرسیستم دسترسی به دیسک ممکن قرار دهید.
- اندازه TempDB را متناسب تنظیم کرده و رشد خودکار آن را پیکربندی کنید. این به ویژه اگر سیستم شما نمی‌تواند کاهش عملکرد را تحمل کند، مهم است. اگر اندازه پایگاه داده را خیلی کوچک با رشد خودکار فعال تنظیم کنید، tempdb به طور خودکار بر اساس افزایش اندازه تنظیم شده توسط شما رشد خواهد کرد.
- اندازه فایل‌های داده tempdb را یکسان نگه دارید و افزایش رشد خودکار را در سراسر فایل‌های داده یکسان پیکربندی کنید.
- TempDB را روی پوشه سیستم (معمولاً درایو C: ) قرار ندهید، زیرا واسط کاربری عمومی ویندوز را خواهید دید که دسترسی به دیسک موجود را مصرف می‌کند، و TempDB می‌تواند مشکلاتی را ایجاد کند که باعث پر شدن غیرمنتظره درایو سیستم شود. این می‌تواند باعث خرابی سیستم شود.
- اگر مدیر SAN شما می‌تواند ارائه دهد، فایل‌های داده متعدد را در سراسر LUNهای متفاوت تقسیم کنید در مقابل نگه داشتن همه چیز روی یک LUN.
همراستا کردن تعداد فایل‌های داده با CPU برای بارکارهای پرتخصیص مقیاس‌پذیری بهتری دارد
- فایل‌های داده اضافی باعث بهبود عملکرد شدند
- توصیه می‌شود به ازای هر CPU روی سرور میزبان، ۱ فایل داده (در هر فایل گروه) داشته باشید.
- این به ویژه در مورد TempDB صدق می‌کند که توصیه آن ۱ فایل داده به ازای هر CPU است.
- یک CPU دو هسته‌ای به عنوان ۲ شمرده می‌شود، یک CPU چهار هسته‌ای به عنوان ۴ و غیره.
به یاد داشتن مبانی SQL Server
- شما باید از یک چک لیست ذهنی (یا نوشته شده) از چیزهایی که باید هنگام تلاش برای دستیابی به بهترین عملکرد ممکن سرور بررسی کنید، استفاده نمایید.
- SQL Server از الگوریتم پر کردن متناسب استفاده می‌کند که تخصیص‌ها را در فایل‌هایی با فضای آزاد بیشتر ترجیح می‌دهد، بنابراین فایل‌های داده باید اندازه‌های برابر داشته باشند.
- از پیش اندازه فایل‌های داده و لاگ را تنظیم کرده و به AUTOGROW تکیه نکنید. اگر امکان دارد، به جای آن رشد فایل‌های پایگاه داده را به صورت دستی مدیریت کنید. ممکن است AUTOGROW را به دلایل ایمنی روشن نگه دارید، اما باید به طور فعال رشد فایل‌های داده را مدیریت کنید.
نادیده گرفتن پایه‌های پیکربندی ذخیره‌سازی
- از جدیدترین درایورهای HBA توصیه شده توسط فروشنده ذخیره‌سازی استفاده کنید.
- از درایورهای اختصاصی فروشنده ذخیره‌سازی از وب‌سایت تولیدکننده استفاده کنید.
- تنظیمات درایور را بر اساس نیاز برای حجم‌های ورودی/خروجی خود تنظیم کنید.
- اطمینان حاصل کنید که نرم‌افزار آرایه ذخیره‌سازی تا آخرین سطح توصیه شده به روز است.
- از نرم‌افزار چندمسیری برای دستیابی به توازن در سراسر HBAها و LUNها استفاده کرده و اطمینان حاصل کنید این به درستی کار می‌کند.