تنظیمات متغیرهای سیستم MySQL برای کارایی بالا

برای اکثر برنامه نویسان برنامه ، این پایگاه داده محراب خدایان شیطان است که به بهترین نحو دست نخورده مانده اند. اما لازم نیست به این ترتیب باشد!


مساوات دیگر ، سطح راحتی یک توسعه دهنده با پایگاه داده زیر ، سطح ارشد آنها را تعیین می کند. بانک اطلاعاتی کمی و تجربه کد نویسی کمی = توسعه دهنده جوان؛ بانک اطلاعاتی کمی و تجربه برنامه نویسی خوب = توسعه دهنده سطح متوسط؛ پایگاه داده خوب و تجربه برنامه نویسی خوب = توسعه دهنده ارشد.

این یک واقعیت خشن است که حتی با 6-8 سال در زیر کمربند خود تلاش می کند تا پیچیدگی های بهینه کننده پرس و جو را توضیح دهد و ترجیح می دهد وقتی از شما سؤال شود بهشت ​​نگاه کنید تنظیم پایگاه داده.

چرا?

با کمال تعجب ، دلیل این تنبلی نیست (هرچند که در بخشی از آن است).

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

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

کنجکاو؟ بیایید وارد شوید!

کنجکاو نیستید؟ خوب ، به هر حال شیرجه بزنید ، زیرا شغل شما به آن بستگی دارد! ��

حافظه پنهان درخواست MySQL را بهینه کنید

تقریباً تمام بهینه سازی ها در زمینه رایانه ها به ذخیره سازی کاهش می یابد. از یک طرف ، CPU چندین سطح حافظه نهان را برای سرعت بخشیدن به محاسبات خود حفظ می کند ، و از طرف دیگر برنامه های وب با استفاده تهاجمی از راه حل های حافظه پنهانی مانند Redis به سرور نتایج پیشگیرانه را در اختیار کاربران قرار می دهند تا اینکه هر بار به پایگاه داده ضربه بزنند.

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

با اجرای این پرس و جو در کنسول پایگاه داده می توانید بررسی کنید که آیا حافظه پنهان در دسترس است (توجه داشته باشید ، در دسترس نیست ، فعال نیست):

MariaDB [(هیچ)]> SHOW VARIABLES LIKE ‘have_query_cache’؛
+——————+——-+
| متغیر_نام | ارزش |
+——————+——-+
| have_query_cache | بله |
+——————+——-+

بنابراین ، می بینید که من MariaDB را اجرا می کنم و حافظه پنهان در دسترس است تا روشن شود. اگر از یک نصب استاندارد MySQL استفاده می کنید ، بعید است که آن را خاموش کنید.

حالا ببینیم من واقعاً حافظه نهان جستجو را روشن کرده ایم:

MariaDB [(هیچ)]> نشان می دهد VARIABLES LIKE “query_cache_type”؛
+——————+——-+
| متغیر_نام | ارزش |
+——————+——-+
| query_cache_type | در |
+——————+——-+

بله ، من اما اگر این کار را نکنید ، می توانید با گفتن این گزینه را روشن کنید:

MariaDB [(هیچ)]> تنظیم GLOBAL query_cache_type = روشن؛

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

با این کار ، شما در حال ذخیره سازی پرس و جو هستید و اولین قدم را برای راه اندازی محکم تر MySQL برداشته اید! من اولین قدم را می گویم زیرا در حالی که روشن کردن آن یک پیشرفت اساسی است ، ما باید حافظه پنهان را برای تنظیم تنظیمات خود تنظیم کنیم. بنابراین بیاموزیم این کار را انجام دهیم.

متغیر دیگر مورد علاقه در اینجا query_cache_size است که عملکرد آن توضیحی است:

MariaDB [(هیچ)]> نشان می دهد VARIABLES LIKE “query_cache_size”؛
+——————+———-+
| متغیر_نام | ارزش |
+——————+———-+
| query_cache_size | 16777216 |
+——————+———-+

بنابراین ، یک حافظه پنهان پرس و جو در اندازه 16 مگابایت دارم. توجه داشته باشید که حتی اگر حافظه پنهان نمایش داده شود اما این اندازه صفر است ، حافظه پنهان به طور موثر خاموش است. به همین دلیل بررسی فقط یک متغیر کافی نیست. حال باید اندازه کش پرس و جو را تعیین کنید ، اما چقدر باید باشد؟ اول از همه ، لطفاً توجه داشته باشید که ویژگی حافظه پنهان حافظه برای ذخیره ابرداده خود به 4 KB نیاز دارد ، بنابراین هر آنچه را انتخاب کردید باید بالاتر از آن باشد.

بیایید بگوییم که اندازه حافظه پنهان پرس و جو را 500 KB تنظیم کرده اید:

MariaDB [(هیچ)]> تنظیم GLOBAL query_cache_size = 500000؛

آیا انجام این کار به اندازه کافی کافی است؟ خوب ، نه ، زیرا نحوه عملکرد موتور کوئری در واقع چگونه به کار خود بستگی دارد:

  • اول از همه ، متغیر query_cache_size باید به اندازه کافی بزرگ باشد تا نتیجه سؤالات شما را نگه دارد. اگر خیلی کوچک باشد ، چیزی ذخیره نمی شود.
  • در مرحله دوم ، اگر query_cache_size روی یک عدد خیلی زیاد تنظیم شده باشد ، دو نوع مشکل ایجاد خواهد شد: 1) موتور مجبور است کار اضافی را برای ذخیره سازی و یافتن نتایج پرس و جو در این منطقه حافظه عظیم انجام دهد. 2) اگر بیشتر نمایش داده شد به اندازه های بسیار کوچکتر ، حافظه پنهان تکه تکه می شود و مزایای استفاده از حافظه پنهان از بین می رود.

چگونه می دانید حافظه پنهان در حال تکه تکه شدن است؟ تعداد کل بلوک موجود در حافظه پنهان مانند این را بررسی کنید:

MariaDB [(هیچ)]> نمایش وضعیت مانند “Qcache_total_blocks”؛
+———————+——-+
| متغیر_نام | ارزش |
+———————+——-+
| Qcache_total_blocks | 33 |
+———————+——-+

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

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

موضوع ، استخرهای نخ ، انتظار و زمانهای طولانی

این احتمالاً جالب ترین بخش نحوه کار MySQL و درست کردن آن به معنای سریع تر کردن برنامه شماست!

موضوع

MySQL یک سرور چند رشته ای است. این بدان معناست که ، هر بار اتصال جدیدی به سرور MySQL وجود داشته باشد ، موضوع جدیدی را با داده های اتصال باز می کند و یک دسته از آن را به مشتری منتقل می کند (فقط در صورتی که نمی دانید موضوع چیست؟ این) مشتری سپس تمام نمایش داده شدگان را از طریق این موضوع ارسال می کند و نتایج را دریافت می کند. این باعث می شود که یک سؤال طبیعی بپرسیم: MySQL چند تا چرخش دارد؟ پاسخ در بخش بعدی قرار دارد.

استخر موضوع

هیچ برنامه ای در یک سیستم رایانه ای نمی تواند موضوعات مورد نظر خود را باز کند. دلیل این دو مورد است: 1) حافظه هزینه موضوعات (RAM) است ، و سیستم عامل فقط به شما اجازه نمی دهد تا به راحتی جواب دهید و تمام آن را بخورید. 2) مدیریت ، به عنوان مثال ، یک میلیون موضوع به خودی خود یک کار عظیم است و اگر سرور MySQL بتواند موضوعات بسیاری را ایجاد کند ، در تلاش برای مقابله با سربار می میرد..

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

ariaDB [(هیچ)]> متغیرهایی مانند ‘thread_pool_size’ را نشان دهید؛
+——————+——-+
| متغیر_نام | ارزش |
+——————+——-+
| thread_pool_size | 4 |
+——————+——-+

بنابراین ، دستگاه من حداکثر چهار اتصال را همزمان امکان پذیر می کند. جالب است بدانید که شماره 4 از این واقعیت ناشی می شود که من یک پردازنده چهار هسته ای دارم ، این بدان معنی است که رایانه من می تواند فقط 4 کار موازی را همزمان انجام دهد (من در اینجا در مورد کارهای واقعاً موازی صحبت می کنم ، نه همزمان). در حالت ایده آل ، این حد است که باید مقدار thread_pool_size را هدایت کند ، اما در ماشین های بزرگتر که باعث افزایش آن می شوند ، تا حدی سود دارند. اگر نمی خواهید همه اتصالات جدید منتظر بمانید و خوب است که برخی از عملکردها را برداشت (دوباره ، این منطقه است که می توانید بر اساس عملکرد برنامه تحت بارگیری خود بهترین قضاوت کنید) ، استفاده از آن تا 8 ممکن است ایده خوبی باشد.

با این حال ، تنظیم آن فراتر از 16 ایده وحشتناکی است مگر اینکه شما یک ماشین 32 هسته ای داشته باشید زیرا عملکرد به شدت کاهش می یابد. سوراخ خرگوش استخرهای نخ در MySQL عمیق است ، اما اگر علاقه دارید, اینجا بحث مفصل تر.

منتظر و پایان وقت

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

MariaDB [(هیچ)]> متغیرهایی مانند “انتظار٪” را نشان دهید؛
+—————+——-+
| متغیر_نام | ارزش |
+—————+——-+
| منتظر_تمام | 28800 |
+—————+——-+

مقدار حاصل در ثانیه است. بنابراین بله ، به طور پیش فرض MySQL قرار است 8 ساعت قبل صبر کند تا سیم را قطع کند! این ممکن است خوب باشد اگر پرس و جوهای طولانی دارید و در واقع می خواهید منتظر آنها باشید (اما حتی در این صورت ، هشت ساعت نیز پوچ است!) اما در بیشتر موارد وحشتناک است. هنگامی که یک پرس و جو اجرا شد ، این مقدار بر روی 0 (به معنای برای همیشه) تنظیم می شود ، اما به طور کلی ، این مقدار باید برای یک مقدار بسیار کم (مثلاً 5 ثانیه یا حتی شاید کمتر) تنظیم شود تا اتصال برای سایر فرایندها آزاد شود..

تنظیم جداول موقت

بیایید با آنچه که جدول های موقت در MySQL است شروع کنیم.

فرض کنید ما یک MySQL داریم که از لحاظ ساختاری به این شکل به نظر می رسد: TABLE A UNION (TABLE B INNER JO C). یعنی ما علاقه مندیم به جداول B و C بپیوندیم و سپس اتحادیه نتیجه را با جدول A. انجام دهیم. اکنون MySQL ابتدا به جدولهای B و C می پیوندد ، اما قبل از اینکه بتواند اتحادیه را انجام دهد ، نیاز دارد. برای ذخیره این داده ها در جایی اینجاست که جداول موقت وارد می شوند – MySQL از آنها برای ذخیره داده ها در مراحل میانی در نمایش داده های پیچیده به طور موقت استفاده می کند ، و پس از اتمام پرس و جو ، این جدول موقتی حذف می شود..

حال سوال اینجاست: چرا باید با این همه زحمت بکشیم?

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

دو متغیر وجود دارد که این رفتار را کنترل می کند:

MariaDB [(هیچ)]> نمایش متغیرهایی مانند ‘MariaDB [(هیچ)]> متغیرهایی مانند ‘tmp_table_size’ را نشان دهید؛
+—————-+———-+

| متغیر_نام | ارزش |

+—————-+———-+

| tmp_table_size | 16777216 |

+—————-+———-+
‘؛
+———————+———-+
| متغیر_نام | ارزش |
+———————+———-+
| max_heap_table_size | 16777216 |
+———————+———-+

MariaDB [(هیچ)]> متغیرهایی مانند ‘tmp_table_size’ را نشان دهید؛
+—————-+———-+
| متغیر_نام | ارزش |
+—————-+———-+
| tmp_table_size | 16777216 |
+—————-+———-+

مورد اول ، max_heap_table_size ، به ما می گوید که چه مقدار RAM را می توان با استفاده از جدول MySQL مورد استفاده قرار داد (“heap” در اینجا به ساختار داده مورد استفاده در تخصیص و مدیریت RAM اشاره دارد – ادامه مطلب اینجا) ، در حالی که مورد دوم ، tmp_table_size ، حداکثر اندازه جدول موقتی را نشان می دهد. در مورد من ، هر دو روی 16 مگابایت تنظیم شده اند ، اگرچه نکته ای که من سعی می کنم آن را افزایش دهم که فقط افزایش tmp_table_size به طور کلی کار نمی کند ، MySQL همچنان توسط max_table_heap_size محدود خواهد شد.

حال این نکته پیش می آید: اگر جداول موقت ایجاد شده بزرگتر از حد مجاز این متغیرها باشد ، MySQL مجبور خواهد شد تا آنها را روی دیسک سخت بنویسد ، و در نتیجه عملکرد بسیار ضعیفی خواهد داشت. اکنون کار ما ساده است: تمام تلاش خود را بکنید تا دقیق ترین اندازه داده ها برای جداول موقتی حدس بزنید و این متغیرها را در آن حد قرار دهید. با این حال ، من می خواهم نسبت به پوچ بودن احتیاط کنم: این حد را به 16 گیگابایت تنظیم کنید (با فرض اینکه این رم زیاد داشته باشید) وقتی بیشتر میزهای موقت شما از اندازه 24 مگابایت کمتر باشد ، حماقت است – شما به راحتی هدر می دهید RAM را که می توانستید “. توسط سایر نمایش داده ها یا بخش هایی از سیستم استفاده شده است (برای مثال حافظه پنهان).

نتیجه

پوشاندن همه متغیرهای سیستم در یک مقاله یا حتی همه موارد مهم در یک مقاله امکان پذیر نیست وقتی اسناد MySQL شامل چندین هزار کلمه باشد. در حالی که ما اینجا متغیرهای جهانی را پوشش می دهیم ، من شما را تشویق می کنم که به متغیرهای سیستم برای موتور مورد استفاده خود توجه کنید (InnoDB یا مییسام).

مطلوب ترین نتیجه من برای نوشتن این مقاله برای شما سه چیز است:

  1. MySQL یک نرم افزار معمولی است که در محدوده تعیین شده توسط سیستم عامل کار می کند. این یک برنامه مرموز نیست که خدا را می داند – چه چیزی و کراهت غیر ممکن است. همچنین ، خوشبختانه ، درک چگونگی تنظیم و کنترل آن توسط متغیرهای سیستم آن چندان دشوار نیست.
  2.  هیچ تنظیمات واحدی وجود ندارد که MySQL را نصب شما بزرگنمایی کند. شما چاره ای ندارید که به سیستم های در حال اجرا خود نگاه کنید (به یاد داشته باشید که بهینه سازی بعد از تولید برنامه انجام می شود ، نه قبل از آن) ، بهترین حدس ها و اندازه گیری ها را بگیرید و با این واقعیت زندگی کنید که هرگز کامل نخواهد بود.
  3. تنظیم متغیرها تنها راه بهینه سازی نمایش داده های MySQL نیست – کار بزرگ دیگری است ، اما این چیزی است که من در مقاله دیگری به آن خواهم پرداخت. اما نکته این است که ، حتی اگر شما یک تحلیل الهی را انجام داده اید و این پارامترها را به بهترین وجه تنظیم کرده اید ، هنوز هم ممکن است برای همه شما متوقف شود.

متغیر سیستم مورد علاقه شما برای تنظیم چیست؟ ��

برچسب ها:

  • بانک اطلاعات

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map