۱۰ نکته ضروری دیگر برای تنظیم عملکرد MySQL

طراحی طرحواره، نمایه ها، پرس و جوها، تنظیمات، ورودی/خروجی… چه چیزی ممکن است اشتباه باشد؟ این ۱۰ نکته ضروری را برای حفظ صدای سرورهای MySQL خود دنبال کنید.

• نکته شماره ۱ عملکرد MySQL: طراحی طرحواره به اندازه هر MySQL دیگری مهم است تنظیمات
• نکته شماره ۲ عملکرد MySQL: کلیدهای ثانویه دشمن شما نیستند
• نکته شماره ۳ عملکرد MySQL: ردیف‌ها را می‌توان از نمایه‌ها ارائه کرد
• نکته شماره ۴ عملکرد MySQL: بررسی پرس و جو، بررسی پرس و جو، بررسی پرس و جو
• نکته شماره ۵ عملکرد MySQL: قابلیت مشاهده اهمیت دارد
• نکته شماره ۶ عملکرد MySQL: مراقب ابزارهای تنظیم باشید< /li>
• نکته شماره ۷ عملکرد MySQL: عملیات I/O هنوز پرهزینه است
• نکته شماره ۸ عملکرد MySQL: از عبارات جدول رایج استفاده کنید
• نکته شماره ۹ عملکرد MySQL: از فضای ابری آگاه باشید< /li>
• نکته شماره ۱۰ عملکرد MySQL: کپی ها را به روز نگه دارید

MySQL پرکاربردترین پایگاه داده منبع باز جهان است و در رتبه دوم محبوبیت قرار دارد در بین پایگاه های داده به طور کلی. این یک سیستم مدیریت پایگاه داده رابطه ای موثر است که سال ها در قلب برنامه های کاربردی محبوب قرار داشته است. با این حال، استفاده از آن می تواند چالش برانگیز باشد و فرصت های زیادی برای بهبود عملکرد وجود دارد.

در چند سال گذشته پیشرفت‌های مهم جدیدی برای MySQL نیز وجود داشته است. این مقاله مجموعه قبلی نکات تنظیم عملکرد MySQL ارائه شده توسط بارون شوارتز را به روز می کند. اگرچه مقاله قبلی هنوز مرتبط است، اما مراحل دیگری وجود دارد که می توانید برای دستیابی به بهترین عملکرد برای استقرار MySQL خود بردارید. در اینجا ۱۰ نکته دیگر برای تنظیم عملکرد MySQL وجود دارد که باید به لیست خود اضافه کنید.

نکته شماره ۱ عملکرد MySQL: طراحی طرحواره به اندازه سایر تنظیمات MySQL مهم است

طراحی طرحواره یکی از مهمترین کارهایی است که در پایگاه داده خود انجام خواهید داد. این یک اصل فناوری پایگاه داده متقابل است، زیرا فرم های معمولی در دهه ۱۹۷۰ معرفی شدند. از آنجایی که MySQL به InnoDB به عنوان موتور ذخیره سازی پیش فرض در نسخه ۵.۶ منتقل شد، طراحی طرحواره اهمیت بیشتری پیدا می کند.

چرا این است؟ در InnoDB، همه چیز یک کلید اصلی است! این به نحوه سازماندهی داده ها توسط InnoDB مربوط می شود. در InnoDB، کلید اولیه خوشه بندی شده است و هر کلید ثانویه یک اشاره گر ورودی به کلید اصلی اضافه می کند. اگر این را در طراحی طرحواره خود در نظر نگیرید، عملکرد شما تأثیر منفی خواهد داشت.

داده‌ها همچنین با استفاده از شاخص‌های B-tree ذخیره می‌شوند، بنابراین درج داده‌ها به صورت مرتب (یعنی با استفاده از مقادیر شبه ترتیبی) از تکه تکه شدن کلید اولیه جلوگیری می‌کند و بنابراین عملیات ورودی/خروجی مورد نیاز برای یافتن گره‌های برگ را کاهش می‌دهد.

موردهایی وجود دارد که کلیدهای اصلی متوالی انتخاب درستی نیستند – یک مثال خوب در اینجا شناسه منحصر به فرد جهانی یا UUID است. می‌توانید به مشکلات پیرامون UUID و کلیدهای اصلی نگاه کنید اینجا. با این حال، به طور کلی، توصیه می کنیم از کلیدهای اصلی متوالی برای بیشتر موارد استفاده استفاده کنید.

نکته شماره ۲ عملکرد MySQL: کلیدهای ثانویه دشمن شما نیستند

کلیدهای ثانویه با فرآیند پس زمینه به روز می شوند. در نتیجه، تأثیر عملکرد آن‌قدر که انتظار دارید جدی نیست. در عوض، مشکل مربوط به ردپای دیسک است زیرا افزودن کلیدهای ثانویه نیازهای ذخیره سازی را افزایش می دهد.

فیلتر کردن روی فیلدی که نمایه ندارد می‌تواند منجر به اسکن کامل جدول در هر بار اجرا شود. البته این می تواند تاثیر زیادی بر عملکرد داشته باشد. بنابراین بهتر است یک کلید ثانویه داشته باشید تا اینکه یکی را از دست بدهید.

همانطور که گفته شد، نباید پایگاه داده های خود را بیش از حد ایندکس کنید، زیرا اجرای بسیاری از ایندکس ها ممکن است بهبود عملکردی را که می خواهید به دست آورید، ارائه ندهد. در عین حال، این فهرست‌های اضافی ممکن است هزینه‌های ذخیره‌سازی شما را افزایش دهند و InnoDB مجبور است بسیاری از عملیات پس‌زمینه را انجام دهد تا آنها را به‌روز نگه دارد.

نکته شماره ۳ عملکرد MySQL: ردیف‌ها را می‌توان از نمایه‌ها ارائه کرد

InnoDB می‌تواند ردیف‌ها را مستقیماً از نمایه‌ها پیدا کند و در واقع آن‌ها را ارائه کند، در حالی که یک کلید ثانویه به کلید اصلی اشاره می‌کند و کلید اصلی شامل خود ردیف است. اگر InnoDB Buffer Pool به اندازه کافی بزرگ باشد، می تواند بیشتر داده ها را نیز در حافظه نگه دارد. حتی می‌توانید از کلیدهای ترکیبی استفاده کنید که معمولاً برای پرس‌و‌جوها مؤثرتر از کلیدهای تک ستونی هستند. MySQL می‌تواند از یک فهرست برای دسترسی به جدول استفاده کند، بنابراین اگر پرس‌و‌جوهایی را با عبارتی مانند WHERE x=1 و y=2 اجرا می‌کنید، پس داشتن فهرستی بیش از x,y است. بهتر از داشتن نمایه های جداگانه روی هر ستون.

علاوه بر این، یک نمایه ترکیبی بیش از x,y همچنین می‌تواند عملکرد جستار زیر را بهبود بخشد:

SELECT y FROM table WHERE x=1

MySQL از نمایه پوششی استفاده می‌کند و y را از فهرستی که در حافظه است، ارائه می‌کند.

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

SELECT a,b,c FROM table WHERE a=1 and b=2

سپس نمایه‌ای روی a,b به درخواست کمک می‌کند. اما اگر پرس و جو در این قالب باشد:

SELECT a,b,c FROM table WHERE b=2

سپس ایندکس بی فایده خواهد بود و باعث اسکن کامل جدول می شود. ایده همیشه خواندن نمایه ها از سمت چپ در برخی موارد دیگر نیز صدق می کند. به عنوان مثال، با توجه به پرس و جو زیر:

SELECT a,b,c FROM table WHERE a=1 and c=2

سپس نمایه‌ای روی a,b,c فقط ستون اول را می‌خواند زیرا هیچ بند WHERE برای فیلتر کردن ستون b وجود ندارد. بنابراین در این مورد MySQL می تواند تا حدی فهرست را بخواند، که بهتر از اسکن جدول کامل است، اما هنوز به اندازه کافی خوب نیست تا بهترین عملکرد پرس و جو را به دست آورد.

یک عنصر دیگر مربوط به طراحی پرس و جو، سمت چپ ترین رویکرد شاخص است، زیرا این یک بهینه سازی رایج در MySQL است. به عنوان مثال، یک نمایه در a,b,c پرس و جوی مانند انتخاب a,c در جایی که c=x را پوشش نمی دهد زیرا پرس و جو نمی تواند قسمت اول نمایه را رد کند. ، که a,b است. همین امر برای پرس و جوی مانند انتخاب c,count(c) که در آن a=x با c گروه می شود، صدق می کند. این پرس و جو نمی تواند از فهرست a,b,c برای گروه استفاده کند زیرا نمی تواند از نمایه b رد شود. با این حال، اگر درخواستی مانند دارید، c,count(c) را انتخاب کنید که در آن a=x و b=y گروه c را انتخاب کنید، که در a,b فیلتر می‌کند و یک را انجام می‌دهد. گروه‌بندی بر اساس در c، سپس یک فهرست در a,b,c می‌تواند در فیلتر کردن و گروه بر اساس کمک کند. کد>.

نکته شماره ۴ عملکرد MySQL: بررسی پرس و جو، بررسی پرس و جو، بررسی پرس و جو

فقط داشتن یک ماشین فرمول یک برنده مسابقه نیست. نه اگر یک راننده بی تجربه را پشت فرمان بنشینید و در همان پیچ اول تصادف کند. به طور مشابه، ممکن است بهترین سرور MySQL روی زمین را داشته باشید، اما اگر سؤالات بدی داشته باشید پایگاه داده شما کندتر از آنچه باید باشد.

باید به طور منظم طرح پرس و جو خود را در طول زمان مرور کنید زیرا برنامه شما با ویژگی های جدید و رفع اشکال تغییر می کند. مجموعه داده‌ها و الگوهای استفاده از برنامه نیز احتمالاً در طول زمان تغییر می‌کنند، که همگی می‌توانند بر عملکرد جستجو تأثیر بگذارند.

تنظیم زمان برای بررسی پرس و جو و نظارت بر زمان اجرای پرس و جو بسیار مهم است. برای این کار می‌توانید از یک گزارش جستجوی کند یا طرح‌واره عملکرد استفاده کنید، اما پیاده‌سازی یک ابزار نظارت به شما کمک می‌کند تا داده‌های بهتری دریافت کنید.

به خاطر داشته باشید که همیشه کندترین پرس و جو مهم ترین مورد برای رفع کردن نیست. به عنوان مثال، ممکن است درخواستی داشته باشید که ۳۰ ثانیه طول می کشد اما دو بار در روز در کنار درخواستی که یک ثانیه طول می کشد و ۱۰۰ بار در دقیقه اجرا می شود. برای یک برد بزرگ، باید بهینه سازی پرس و جو دوم را شروع کنید، زیرا بهبود می یابد که می توان در طولانی مدت زمان و منابع زیادی را ذخیره کرد.

نکته شماره ۵ عملکرد MySQL: قابلیت مشاهده اهمیت دارد

نظارت یکی از عناصر کلیدی تنظیم عملکرد است. بدون دانستن حجم کار و الگوهای فعلی، ارائه هیچ توصیه خاصی دشوار است. در سال‌های اخیر، MySQL سطح پایین معیارهای MySQL/InnoDB را بهبود بخشیده است، که می‌تواند به درک حجم کار کمک کند.

به عنوان مثال، در نسخه های قبلی، طرحواره عملکرد یک گلوگاه بود و تأثیر قابل توجهی داشت، به خصوص اگر جدول های زیادی داشته باشید. در نسخه های اخیر MySQL، بسیاری از تغییرات مانند دیکشنری داده جدید عملکرد را بهبود بخشیده است و اکنون می توانید جداول زیادی را بدون تأثیر قابل توجه داشته باشید.

بیشتر ابزارهای نظارتی مدرن به نوعی از طرحواره عملکرد استفاده می کنند، بنابراین توصیه خوب این است که این ابزارها را بررسی کنید و بهترین گزینه را انتخاب کنید که با نیازهای شما مطابقت دارد. این قابل مشاهده بودن داده های عملکرد می تواند دارایی بزرگی در تحقیقات شما باشد.

نکته شماره ۶ عملکرد MySQL: مراقب ابزارهای تنظیم باشید

برخی توصیه‌های کلی که توسط ابزارهای تنظیم ارائه می‌شوند در بیشتر موارد استفاده می‌شوند. با این حال، هر حجم کاری و هر طرح واره ای متفاوت است. در برخی موارد توصیه های کلی ابزارهای تنظیم کار نمی کند و عاقلانه است که هنگام اعتماد به این توصیه ها مراقب باشید. حتی innodb_dedicated_server ، که ابزار خود اوراکل است و در MySQL موجود است، می تواند تغییرات مشکوکی در پیکربندی ایجاد کند.

به عنوان مثال، تنظیم innodb_buffer_pool_size روی ۷۵٪ از کل RAM یک قانون کلی خوب است. با این حال، امروزه می توانید سرورهایی با صدها گیگابایت رم داشته باشید. اگر ۵۱۲ گیگابایت رم دارید، ۱۲۸ گیگابایت رایگان باقی می‌ماند و به استخر بافر اختصاص داده نمی‌شود، که اتلاف زیادی است.

innodb_log_file_size و innodb_log_files_in_group نیز بر اساس مقدار RAM تعریف شده‌اند. در سرورهایی با بیش از ۱۲۸ گیگابایت رم، این تنظیم منطقی نیست زیرا ۶۴ فایل ثبت مجدد (بله، ۶۴!) هر کدام ۲ گیگابایت را ایجاد می کند. این باعث می‌شود ۱۲۸ گیگابایت گزارش مجدد روی دیسک ذخیره شود. در بیشتر موارد، حتی در شلوغ ترین محیط ها، نیازی به چنین فایل های ثبت مجدد بزرگی نیست. بنابراین این توصیه خوبی نیست.

innodb_flushing_method تنها مقداری است که در صورت فعال بودن پیکربندی خودکار به درستی پیکربندی شده است. این متغیر روش فلاشینگ را روی O_DIRECT_NO_FSYNC تنظیم می کند، که روش توصیه شده هنگام استفاده از سیستم های فایل Ext4 یا XFS است، زیرا از بافر مضاعف داده ها جلوگیری می کند.

یک توصیه خوب این است که innodb_buffer_pool_size را روی ۷۵٪ یا ۸۰٪ در سرورهای اختصاصی تنظیم کنید. در سرورهایی با مقادیر زیاد رم، یعنی بیش از ۱۲۸ گیگابایت، با پروفایل مناسب مصرف حافظه، این میزان را به ۹۰ درصد یا حتی بیشتر افزایش دهید. به طور مشابه، برای اکثر موارد با innodb_log_file_size و innodb_log_files_in_group، با دو فایل ۲ گیگابایتی شروع کنید و عملیات ثبت گزارش را کنترل کنید. معمولاً توصیه می‌شود هنگام اندازه‌گیری مجدد گزارش‌ها، تقریباً یک ساعت از نوشتن را پوشش دهید.

در رابطه با innodb_flush_method، این گزینه باید روی O_DIRECT یا O_DIRECT_NO_FSYNC برای سیستم‌های فایل لینوکس مدرن مانند Ext4 یا XFS تنظیم شود.

نکته شماره ۷ عملکرد MySQL: عملیات I/O هنوز پرهزینه است

MySQL و InnoDB سعی می کنند تعداد عملیات I/O را که انجام می دهند به حداقل برسانند زیرا دسترسی به لایه ذخیره سازی از نظر عملکرد برنامه پرهزینه است. تنظیمات کمی وجود دارد که می تواند بر تعداد عملیات I/O که InnoDB انجام می دهد تأثیر بگذارد. دو مورد از این تنظیمات اغلب اشتباه درک می شوند و تغییر آنها اغلب باعث مشکلات عملکرد می شود.

innodb_io_capacity و innodb_io_capacity_max متغیرهایی هستند که به تعداد عملیات I/O برای فلاشینگ در پس‌زمینه مربوط می‌شوند. بسیاری از مشتریان مقادیر این تنظیمات را افزایش می دهند تا از SSD های مدرن بهره ببرند که می توانند ظرفیت ورودی/خروجی بسیار بالایی را در تأخیر نسبتاً کم فراهم کنند. در حالی که این ایده منطقی به نظر می رسد، افزایش تنظیمات ظرفیت ورودی/خروجی می تواند منجر به چند مشکل شود.

نخستین مشکل، کاهش عملکرد است که باعث می‌شود InnoDB صفحات کثیف را خیلی سریع شسته شود، بنابراین فرصت اصلاح یک صفحه قبل از شستشوی بیش از یک بار کاهش می‌یابد. نگه داشتن صفحات کثیف در حافظه می تواند به طور قابل توجهی عملیات ورودی/خروجی مورد نیاز برای نوشتن داده ها در حافظه را کاهش دهد.

ثانیاً، SSDها قبل از کاهش عملکرد، تعداد مورد انتظاری از نوشتن دارند. بنابراین افزایش میزان عملیات نوشتن می‌تواند بر طول عمر SSD شما تأثیر بگذارد، حتی اگر از درایوهای پیشرفته استفاده می‌کنید.

این روزها میزبانی ابری رایج است و اجرای نمونه سرویس MySQL در فضای ابری می تواند بسیار مفید باشد. با این حال، سرورهای موجود در فضای ابری اغلب محدودیت‌های ورودی/خروجی دارند یا برای استفاده بیشتر از ورودی/خروجی، هزینه بیشتری دریافت می‌کنند. با آگاهی از این محدودیت‌ها، می‌توانید این پارامترها را با دقت پیکربندی کنید تا مطمئن شوید به این محدودیت‌ها نرسیده‌اند و عملیات ورودی/خروجی به حداقل رسیده است.

ذکر innodb_lru_scan_depth نیز حائز اهمیت است زیرا این تنظیم کنترل می‌کند که تا چه اندازه در فهرست صفحه LRU حوضچه بافر، رشته تمیزکننده صفحه برای تمیز کردن صفحات کثیف اسکن می‌کند. اگر حجم کاری سنگینی برای نوشتن با یک مخزن بافر بزرگ و نمونه‌های بافر استخر زیادی دارید، می‌توانید این متغیر را کاهش دهید تا از عملیات ورودی/خروجی کمتری استفاده کنید.

یک توصیه خوب برای دنبال کردن این است که پیش‌فرض‌ها را حفظ کنید مگر اینکه بدانید باید آنها را تغییر دهید.

همچنین شایان ذکر است که جدیدترین SSD ها به طور خاص برای پایگاه های داده تراکنش بهینه شده اند. یک مثال وسترن دیجیتال است که به دنبال کمک متخصص برای کمک به آنها برای برآوردن الزامات موج جدید برنامه های کاربردی در حال ایجاد است.

نکته شماره ۸ عملکرد MySQL: از عبارات جدول رایج استفاده کنید

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

با توجه به اینکه CTEها فقط یک بار تحقق می‌یابند، در تراکنش‌های پیچیده‌ای که چندین پرس‌وجو را اجرا می‌کنند، سریع‌تر عمل می‌کنند. به علاوه، بازگشت CTE پشتیبانی می شود، بنابراین می توانید به راحتی ساختارهای پیچیده ای مانند مدل ها و سری های سلسله مراتبی در زبان SQL ایجاد کنید. اگر جزئیات بیشتری در مورد CTE می‌خواهید، یک مقدمه اینجا.

نکته شماره ۹ عملکرد MySQL: از ابر آگاه باشید

گزینه‌های ابری مختلف برای استقرار MySQL وجود دارد، از پیاده‌سازی یک نمونه سرور MySQL در ماشین مجازی که مدیریت می‌کنید، تا استفاده از پایگاه داده به‌عنوان راه‌حل سرویس (DBaaS). گستره گزینه ها بسیار زیاد است.

بسیاری از این سرویس‌ها قول می‌دهند که عملکرد قابل توجهی را افزایش دهند و همه مشکلات شما را برطرف کنند. در برخی موارد استفاده ساده که ممکن است درست باشد. با این حال، حتی در فضای ابری، باید اصول اولیه پایگاه های داده را بدانید و درک کنید، در غیر این صورت هزینه های شما به میزان قابل توجهی افزایش می یابد. این افزایش هزینه اغلب به این دلیل اتفاق می‌افتد که شما اساساً مشکلات را با پرتاب سخت‌افزار بیشتر به مشکل حل می‌کنید تا اینکه طرح را اصلاح کنید.

• MySQL
• پایگاه داده
• پایگاه‌های داده رابطه‌ای< /a>
• توسعه نرم افزار< /a>