کانفیگ MySQL و معرفی پارامترها

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

معرفی MySQL

MySQL یک برنامه‌ی مدیریت پایگاه داده رابطه‌ای منبع باز محبوب است. این برنامه بخشی از LAMP Stack که متشکل از برنامه‌های Linux ،Apache ،MySQL و PHP است می‌باشد، که می‌تواند روی هاست لینوکس و یا سرور مجازی پیاده‌سازی شود. این مجموعه برنامه‌ها یک پشته‌ی نرم‌افزاری هستند که مسئولیت تامین برنامه‌های موردنیاز برای پاسخ به درخواست‌های وارد شده و خارج شده از سایت را بر عهده دارند، و در واقع قرار است که یک وب سرور را برای شما تشکیل دهند.

MySQL بر اساس زبان جستجوی ساختاریافته (SQL) می‌تواند در اکثر سیستم عامل‌ها اجرا شود و عمدتا برای برنامه‌های تحت وب استفاده می‌شود. این پایگاه داده به زبان C و ++C نوشته شده است. پایگاه داده MySQL گسترده است و زمینه‌های زیادی برای بهینه‌سازی دارد و همچنین تغییر عملکرد مناسبی دارد. برخی از تغییرات را می‌توان به صورت پویا انجام داد، و برخی دیگر نیاز به راه‌اندازی مجدد سرور دارند. نصب و کانفیگ MySQL با پیکربندی پیش‌فرض بسیار ساده است.

کانفیگ MySQL و ایمن‌سازی

نصب جدید MySQL شما با یک اسکریپت امنیتی برای سهولت در تنظیمات امنیتی فراهم شده است. اسکریپت با دستور ترمینال زیر راه‌اندازی می‌شود :

sudo mysql_secure_installation

این سیستم از شما درخواست می‌کند که رمز عبور پیش‌فرض root را وارد کنید. رمز عبوری را که قبلاً بازیابی کرده‌اید وارد کنید. بعد، سیستم به شما می‌گوید که گذرواژه منقضی شده است و از شما می‌خواهد رمز جدیدی وارد کنید. یک رمز ورود جدید وارد کنید، آن را یادداشت کنید، سپس Enter را فشار دهید. سیستم قدرت رمز عبور شما را ارزیابی می‌کند و از شما می‌پرسد آیا می‌خواهید رمز عبور جدید و قوی‌تری وارد کنید. اگر از قدرت رمز عبور خود راضی هستید، کلید Space را فشار دهید. برای تجدیدنظر در گذرواژه خود، Y را فشار دهید.

اسکریپت Secure Installation ادامه خواهد یافت، و شما می‌توانید با خیال راحت Y را به بقیه اعلان‌ها پاسخ دهید، که شامل موارد زیر است:

• کاربران ناشناس را حذف کنید
• ورود به سیستم از راه دور را از بین ببرید
• حذف پایگاه داده آزمون
• جداول امتیاز را بارگیری مجدد کنید

متغیرهای سیستم (System Variables)

کانفیگ MySQL متغیرهای زیادی دارد که می‌توانید تغییر دهید. برخی از متغیرها پویا هستند. به این معنی که می‌توان آنها را با استفاده از دستور SET تنظیم کرد. بعد از اینکه سرور در پرونده کانفیگ MySQL تنظیم شد (بعنوان مثال /etc/my.cnf و غیره / mysql / my.cnf)، دیگر به سرور نیاز دارید. با این حال ما موارد عمومی را که کاملاً معمول است تنظیم می‌کنیم تا بهینه‌سازی سرور را انجام دهیم.

مرتب کردن اندازه‌ی بافر (sort buffer size)

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

توجه داشته باشید که این متغیر به صورت per-query (به ازای هر اتصال) پردازش می‌شود، به این معنی که وقتی مقدار این متغیر را بالاتر قرار می‌دهید و اگر چندین اتصال داشته باشید حافظه اصطلاحا گرسنه (hungry) خواهد بود.

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

در کانفیگ MySQL ورژن 8.0 به طور پیش‌فرض، مقدار فعلی تنظیم شده این متغیر 256KIB است. بر این اساس فقط در صورتی که کوئری‌های زیادی داشته باشید، می‌توانید مجددا در کانفیگ MySQL مقدار این متغیر را بالا ببرید.

read_buffer_size

در راهنمای ارائه شده برای کانفیگ MySQL ذکر شده است که برای هر درخواست که اسکن متوالی یک جدول را انجام می‌دهد، یک بافر خواندن اختصاص می‌دهد. متغیر سیستم read_buffer_size اندازه بافر را تعیین می‌کند که برای MyISAM نیز مفید است، اما این متغیر بر روی تمام موتورهای ذخیره‌سازی نیز تأثیر می‌گذارد. و همچنین برای جداول MEMORY و تعیین اندازه بلوک حافظه استفاده می‌شود.

اساساً هر موضوعی که اسکن متوالی جدول MyISAM را انجام می‌دهد، برای هر جدولی که اسکن می‌کند یک بافر به این اندازه (در بایت) اختصاص می‌دهد. این برای همه‌ی موتورهای ذخیره‌سازی (که شامل InnoDB است) نیز اعمال می‌شود.

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

read_rnd_buffer_size

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

این مستندات می‌گوید هنگام خواندن ردیف‌ها به ترتیب دلخواه یا از جدول MyISAM مرتب شده پس از عمل مرتب‌سازی کلید، ردیف‌ها از طریق این بافر خوانده می‌شوند (و از طریق این اندازه بافر تعیین می‌شوند) تا از جستجوی دیسک جلوگیری شود. تنظیم متغیر روی مقدار زیاد می‌تواند عملکرد ORDER BY را تا حد زیادی بهبود بخشد. با این حال، این یک بافر اختصاص یافته برای هر مشتری است.

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

join_buffer_size

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

همچنین این مقدار توسط بهینه‌سازی BKA (که به طور پیش‌فرض غیرفعال است) استفاده می‌شود. شما می‌توانید ارزش آن را افزایش دهید تا در صورت عدم امکان اضافه کردن فهرست‌ها، پیوستن کامل سریع‌تر داشته باشید. اگر مقدار این متغیر را خیلی زیاد تنظیم کنید، ممکن است کانفیگ MySQL شما دچار مشکلاتی مانند Caveat شود. به یاد داشته باشید که برای هر اتصال کامل بین دو جدول یک بافر پیوستن اختصاص داده شده است. برای پیوستن پیچیده بین چندین جدول که از نمایه‌ها برای آنها استفاده نشده است، بافرهای اتصال چندگانه وجود دارند.

max_heap_table_size

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

این متغیر همچنین با tmp_table_size در ارتباط است تا اندازه جداول داخلی در حافظه را محدود کند (این تفاوت با جداول ایجاد شده به طور صریح موتور = MEMORY دارد زیرا فقط حداکثر max_heap_table_size را اعمال می‌کند)، هر کدام از کوچکترها بین این دو اعمال می‌شود.

tmp_table_size

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

اگر یک جدول موقتی در حافظه بیش از حد مجاز باشد، MySQL آن را به طور خودکار به یک جدول موقتی روی دیسک تبدیل می‌کند. اگر تعداد زیادی پرس‌و‌جوی پیشرفته GROUP BY انجام می‌دهید و فضای حافظه زیادی دارید، مقدار tmp_table_size (و max_heap_table_size در صورت لزوم) را افزایش دهید.

شما می‌توانید با مقایسه مقادیر متغیرهای Created_tmp_disk_table و Created_tmp_table تعداد جداول موقتی داخلی روی دیسک ایجاد شده را با تعداد جداول موقتی داخلی مقایسه کنید.

table_open_cache

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

اگر چه افزایش این مقدار در زمان کانفیگ MySQL تعداد توصیف کننده‌های فایل مورد نیاز mysqld را افزایش می‌دهد، بنابراین شما همچنین می‌توانید مقدار open_files_limit خود را بررسی کنید که SOFT و HARD در سیستم عامل * nix شما چقدر بزرگ است.

شما می‌توانید با بررسی متغیر وضعیت Opened_tables این مورد را افزایش دهید یا نیازی به افزایش حافظه پنهان جدول نیست. اگر مقدار Open_tableها زیاد است و شما اغلب از FLUSH TABLES استفاده نمی‌کنید (که فقط مجبور به بسته شدن و بازگشایی جداول است)، باید مقدار متغیر table_open_cache را افزایش دهید.

همین‌طور اگر در کانفیگ MySQL مقدار کمی را برای table_open_cache در نظر گرفته باشید و تعداد زیادی از جداول مرتباً قابل دسترسی باشند، این می‌تواند بر عملکرد سرور شما تأثیر بگذارد. اگر ورودی‌های زیادی را در لیست فرآیند MySQL با وضعیت «جداول باز کردن» یا «جدول‌های بسته شده» مشاهده کردید، وقت آن است که مقدار این متغیر را تنظیم کنید. در ClusterControl می‌توانید این مورد را در بخش Dashboards -> Table Open Cache Status یا Dashboards -> Open Tables بررسی کنید. برای اطلاعات بیشتر می‌توانید آن را اینجا بررسی کنید.

table_open_cache_instances

تنظیم این متغیر در کانفیگ MySQL به بهبود مقیاس‌‌پذیری و البته عملکردی کمک می‌کند که اختلاف بین جلسات را کاهش می‌دهد. مقداری که در کانفیگ MySQL تنظیم کرده‌اید تعداد نمونه‌های حافظه نهان جداول باز را محدود می‌کند. حافظه پنهان جداول باز را می‌توان به چندین نمونه حافظه پنهان کوچکتر از اندازه size_open / cache / table_open_cache_pandition کرد.

برای دسترسی به عبارات DML یک جلسه باید فقط یک نمونه را قفل کند. این دسترسی، حافظه‌ی پنهان را در میان نمونه‌ها تقسیم می‌کند و عملکردهای بالاتر را برای عملیاتی که از حافظه پنهان استفاده می‌کنند، درصورتی که بسیاری از جلسات به جداول دسترسی دارن، امکان‌پذیر می‌کند. (دستورات DDL هنوز به قفل کردن حافظه پنهان کامل احتیاج دارند، اما تکرار چنین عباراتی بسیار کمتر از دستورات DML است.) مقدار 8 یا 16 در سیستم‌هایی که به طور معمول از 16 هسته یا بیشتر استفاده می‌کنند ، توصیه می‌شود.

table_definition_cache

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

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

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

از این رو نمونه‌های جدول والدین و فرزند با روابط خارجی با کلید در لیست LRU قرار ندارند و می‌توانند بالاتر از حد تعریف شده توسط cache table_definition_ را تحمیل کنند و در حین LRU در معرض تخلیه حافظه نیستند. علاوه بر این، table_definition_cache برای تعداد InnoDB فایل‌های جداول که می‌تواند هم‌زمان باز شود اندازه محدودی را تعیین می‌کند که همچنین توسط innodb_open_files کنترل می‌شود و در واقع اگر هر دو تنظیم شده باشند از بالاترین تنظیمات استفاده می‌شود .

اگر هیچ یک از متغیرها تنظیم نشده باشد، از table_definition_cache که مقدار پیش فرض بالاتری دارد استفاده می‌شود. اگر تعداد دستگیره‌های پرونده‌های باز شده از حد مجاز بیش از حد تعریف شده توسط table_definition_cache یا innodb_open_files باشد، مکانیزم LRU لیست LRU پرونده tablespace را برای پرونده‌هایی جستجو می‌کند که کاملاً فلاش شده‌اند و در حال حاضر تمدید نمی‌شوند.

max_allowed_packet

این متغیر کانفیگ MySQL نشان دهنده حداکثر اندازه اتصال هر پرسش یا ردیف SQL است. این مقدار آخرین بار در MySQL 5.6 افزایش یافته است. اما در MySQL 8.0 (حداقل در تاریخ 8.0.3)، مقدار پیش‌فرض فعلی 64MiB است. اگر ردیف‌های بزرگ BLOB دارید که باید بیرون کشیده شوند (یا بخوانید)، ممکن است این تنظیم را انجام دهید، در غیر این صورت می‌توانید این تنظیمات پیش‌فرض را با 8.0 بگذارید اما در نسخه‌های قدیمی، پیش‌فرض 4MiB است بنابراین در صورت وجود می‌توانید از آن مراقبت کنید با خطای ER_NET_PACKET_TOO_LARGE روبرو شوید. بزرگترین بسته ممکن که به سرور یا سرویس‌گیرنده MySQL 8.0 یا از طریق آن قابل انتقال است 1 گیگابایت است.

skip_name_resolve

با استفاده از این متغیر کانفیگ MySQL، سرور MySQL ارتباطات ورودی را با وضوح نام میزبان مدیریت می‌کند. به طور پیش‌فرض، MySQL هیچ رزولوشن نام میزبان را غیرفعال نمی‌کند به این معنی که جستجوی DNS را انجام می‌دهد و به طور تصادفی اگر DNS کند باشد می‌تواند دلیل عملکرد افتضاح پایگاه داده شما باشد. اگر نیازی به وضوح DNS ندارید، این مورد را در زمان کانفیگ MySQL روشن کنید و از غیرفعال کردن عملکرد MySQL خود در غیرفعال شدن استفاده کنید. توجه داشته باشید که این متغیر پویا نیست، بنابراین اگر این مورد را در پرونده پیکربندی MySQL خود تنظیم کنید، راه‌اندازی مجدد سرور لازم است. شما می‌توانید به صورت اختیاری mysqld daemon را راه‌اندازی کنید، برای فعال کردن این گزینه –skip-name -olution را رد کنید.

max_connections

این متغیر کانفیگ MySQL، تعداد اتصالات مجاز برای سرور MySQL شما است. اگر در MySQL «اتصالات بیش از حد» متوجه خطا شوید ممکن است تنظیم آن را بالاتر انجام دهید. به طور پیش‌فرض مقدار 151 به‌ویژه در یک پایگاه داده تولید کافی نیست و با توجه به اینکه منابع سرور بیشتری دارید (منابع سرور خود را هدر ندهید، مخصوصاً اگر یک سرور اختصاصی MySQL باشد). با این حال شما باید توصیف‌کننده پرونده به اندازه کافی داشته باشید در غیر این صورت تعداد آنها تمام می‌شود. در این صورت، تنظیم SOFT و HARD محدودیت سیستم عامل * nix خود را در نظر بگیرید و مقدار open_files_limit را در MySQL بالاتر قرار دهید (5000 حد پیش‌فرض است). در نظر داشته باشید که بسیار شایع است که برنامه ارتباطات خود را به درستی به پایگاه داده نمی‌بندد، و تنظیم حداکثر ارتباطات می‌تواند باعث عدم پاسخگویی یا بار زیاد سرور شما شود. استفاده از یک اتصال در سطح برنامه می‌تواند به حل مسئله در اینجا کمک کند.

thread_cache_size

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

اگر اتصال جدید زیادی داشته باشید می‌توانید این متغیر را برای بهبود عملکرد افزایش دهید. به طور معمول، اگر پیاده‌سازی موضوعی خوبی داشته باشید، این بهبود عملکرد قابل توجهی ندارد. با این حال اگر سرور شما صدها اتصال در ثانیه مشاهده می‌کند باید مقدار thread_cache_size را در کانفیگ MySQL به اندازه کافی زیاد تنظیم کنید تا در بیشتر اتصالات جدید از نخ‌های ذخیره شده استفاده شود. با بررسی تفاوت بین متغیرهای وضعیت اتصال و Threads_created، می‌توانید حافظه پنهان موضوع را کارآمد ببینید. با استفاده از فرمول ذکر شده در اسناد، 8+ (حداکثر اتصالات / 100) به اندازه کافی خوب است.

query_cache_size

برای برخی از تنظیمات این متغیر قابل تنظیم در کانفیگ MySQL بدترین دشمن آنها خواهد بود. برای برخی از سیستم‌هایی که بار زیادی را تجربه می‌کنند و مشغول خواندن زیاد هستند، این متغیر سیستم شما را خسته می‌کند. مواردی وجود داشته که توسط Percona به خوبی مورد آزمایش قرار گرفته است. برای غیرفعال کردن این متغیر باید 0 به همراه query_cache_type = 0 تنظیم شود. خبر خوب در کانفیگ MySQL ورژن 8.0 این است که تیم MySQL پشتیبانی از این امر را متوقف کرده است زیرا این متغیر می‌تواند باعث مشکلات عملکردی شود.

اگر شما در استفاده از حافظه پنهان پرس‌و‌جو کار می‌کنید، پیشنهاد می‌کنیم برای کانفیگ MySQL از Redis یا ProxySQL استفاده کنید.

موتور ذخیره‌سازی – InnoDB

InnoDB یک موتور ذخیره‌سازی سازگار با ACID است که دارای ویژگی‌های مختلفی به همراه پشتیبانی از کلید خارجی است (Declarative Referential Integrity). این گزینه چیزهای زیادی برای گفتن دارد اما متغیرهای خاصی را باید برای تنظیم در نظر بگیرید:

اندازه innodb_buffer_pool

این متغیر کانفیگ MySQL مانند یک بافر اصلی MyISAM عمل می‌کند اما چیزهای زیادی برای ارائه دارد. از آنجا که InnoDB بسیار به استخر بافر متکی است، شما می‌خواهید این مقدار را معمولاً روی 70٪ -80٪ ​​حافظه سرور خود تنظیم کنید. همچنین مطلوب است که شما فضای حافظه بیشتری نسبت به مجموعه داده خود داشته باشید و مقدار بیشتری را برای بافر خود تنظیم کنید اما نه خیلی زیاد. در ClusterControl می‌توان با استفاده از داشبورد – – نمودارهای InnoDB -> نمودار صفحات استخر بافر InnoDB این را کنترل کرد. همچنین می‌توانید با استفاده از متغیرهای Innodb_buffer_pool_pages * این وضعیت را با SHOW GLOBAL STATUS کنترل کنید.

innodb_buffer_pool_intiles

برای بار کاری هم‌زمانی شما، تنظیم این متغیر می‌تواند هم‌زمان‌سازی را بهبود بخشد و از اختلاف نظر به‌عنوان رشته‌های مختلف خواندن / نوشتن در صفحات ذخیره شده کاسته شود. حداقل مقدار innodb_buffer_pool باید بین 1 (حداقل) و 64 (حداکثر) باشد.

هر صفحه‌ای که در استخر بافر ذخیره یا خوانده می‌شود، با استفاده از یک تابع hash کردن به طور تصادفی به یکی از موارد بافر اختصاص‌ می‌یابد. هر استخر بافر لیست‌های رایگان، لیست‌‌های فلاش، LRU و سایر ساختارهای داده متصل به یک بافر را مدیریت می‌کند و توسط mutex استخر بافر خود محافظت می‌شود. توجه داشته باشید که این گزینه فقط زمانی اعمال می‌شود که innodb_buffer_pool_size> = 1GiB و اندازه آن بین نمونه‌های بافر تقسیم شود.

innodb_log_file_size

این متغیر پرونده ورود به سیستم در یک گروه ورود به سیستم است. اندازه ترکیبی پرونده‌های ورود به سیستم نمی‌تواند از حداکثر مقدار کمی کمتر از 512 گیگابایت باشد. اندازه پرونده بزرگتر برای عملکرد بهتر است‌، اما دارای یک اشکال (قابل توجه) است که باید نگران آن باشید:

زمان بازیابی پس از خرابی شما باید در شرایط نادر بهبودی خرابی در مقابل حداکثر توان عملیاتی در طی عملیات اوج، زمان بازیابی را متعادل کنید. این محدودیت می‌تواند در فرایند بازیابی خرابی 20 برابر طولانی‌تر ترجمه شود!

برای توضیح بیشتر، مقدار بزرگتر برای ثبت معاملات InnoDB مفید است و برای عملکرد نوشتن خوب و پایدار بسیار مهم است.

با این حال‌، هنگامی که پایگاه داده شما غیر عادی خاموش شد (کرش کرد یا حذف شد یا عمدی یا تصادفی)، روند بازیابی بسیار کند است. در حالت ایده‌آل شما می‌توانید 1-2 گیگابایت تولید داشته باشید. اما مطمئناً می‌توانید این میزان را تنظیم کنید. محک زدن این تغییرات می‌تواند یک مزیت بزرگ برای دیدن عملکرد آن به‌ویژه در هنگام کرش کردن باشد.

innodb_log_buffer_size

برای ذخیره I/O دیسک، InnoDB’s داده تغییر را در بافر ورود به سیستم lt می‌نویسد و از مقدار innodb_log_buffer_size با مقدار پیش فرض 8MiB استفاده می‌کند. این امر خصوصاً برای معاملات بزرگ مفید است زیرا نیازی به نوشتن گزارش تغییرات روی دیسک قبل از انجام معامله نیست. اگر ترافیک نوشتن شما خیلی زیاد است (درج، حذف، به‌روزرسانی) بافر باعث بزرگتر شدن ورودی/خروجی دیسک می‌شود.

innodb_flush_log_at_trx_commit

وقتی innodb_flush_log_at_trx_commit روی 1 تنظیم شود بافر ورود به سیستم در هر تراکنش متعهد به پرونده ورود به سیستم بر روی دیسک حذف می‌شود و حداکثر یکپارچگی داده را فراهم می‌کند، اما تأثیر عملکردی نیز دارد. تنظیم آن بر روی 2 به این معنی است که بافر ورود به سیستم حافظه نهان پرونده سیستم عامل بر روی هر تعهد معامله قرار دارد. اگر بتوانید نیازهای ACID خود را کاهش دهید، مفهوم 2 بهینه است و عملکرد را بهبود می‌بخشد و در صورت خرابی سیستم عامل قادر به از دست دادن معاملات برای یکی دو ثانیه آخر هستید.

innodb_thread_concurrency

با بهبود موتور InnoDB توصیه می‌شود به موتور اجازه دهید تا هم‌زمانی را با نگه داشتن آن در مقدار پیش‌فرض (که صفر است) کنترل کند. اگر مشکلات هم‌زمانی را مشاهده کردید می‌توانید این متغیر را تنظیم کنید. مقدار توصیه شده 2 برابر تعداد پردازنده‌ها به‌علاوه تعداد دیسک‌ها است. این متغیر پویا است یعنی می‌تواند بدون راه‌اندازی مجدد سرور MySQL تنظیم شود.

innodb_flush_method

این متغیر باید بارها آزمایش شود که بر اساس کدام سخت‌افزار بیشتر مناسب شما است. اگر از RAID با حافظه پنهان پشتیبان باتری استفاده می‌کنید، DIRECT_IO به شما در کاهش فشار ورودی‌/خروجی کمک می‌کند. ورودی I/O مستقیم حافظه پنهان نیست بنابراین از ایجاد بافر مضاعف با بافر و حافظه پنهان سیستم فایل جلوگیری می‌کند. اگر دیسک شما در SAN ذخیره شده باشد ممکن است O_DSYNC برای بار سنگین خوانده شده با عبارات بیشتر SELECT سریع‌تر باشد.

innodb_file_per_table

innodb_file_per_table به طور پیش‌فرض از MySQL 5.6 فعال است. این معمولاً توصیه می‌شود زیرا از داشتن یک قاشق غذاخوری مشترک بزرگ جلوگیری می‌کند و به شما امکان می‌دهد هنگام جدا کردن یا برش دادن میز، فضای خود را پس بگیرید. Spaceaceace جداگانه همچنین برای طرح پشتیبان‌گیری جزئی Xtrabackup مزایایی دارد.

innodb_stats_on_metadata

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

innodb_io_capacity

این تنظیم‌، علی‌رغم همه امیدهای بزرگ ما که به Innodb امکان استفاده بهتر از IO در همه عملیات را می‌دهد، به سادگی میزان شستشوی صفحه‌ی کثیف در ثانیه (و سایر وظایف پس‌زمینه مانند پیش خواندن) را کنترل می‌کند. این را بزرگتر کنید هر ثانیه بیشتر می شود. این سازگار نیست اگر بافرهای کثیف برای شستشو وجود داشته باشد به‌راحتی بسیاری از ثانیه‌ها را در هر ثانیه انجام می‌دهد. اگر حجم کاری کافی برای نوشتن داشته باشید بهینه‌سازی ادغام IO را از بین می‌برد (یعنی صفحات کثیف تقریباً بلافاصله سرخ می‌شوند، در این صورت بدون ثبت تراکنش بهتر خواهیم بود). همچنین اگر این مورد را خیلی زیاد قرار دهید به‌سرعت می‌تواند داده‌ها را بخواند و در سیستم ثبت معاملات بنویسد.

innodb_write_io_threads

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

innodb_adaptive_flushing

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

innodb_dedicated_server

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

هنگامی که این متغیر فعال باشد، InnoDB یک پیکربندی خودکار برای مقدار حافظه شناسایی شده برای متغیرهایinnodb_buffer_pool_size ، innodb_log_file_size ،innodb_flush_method انجام می‌دهد.

تنها نکته منفی این است که شما نمی‌توانید این امکان را داشته باشید که مقادیر موردنظر خود را روی متغیرهای شناسایی شده اعمال کنید.

MyISAM

MyISAM موتور ذخیره‌سازی پیش‌فرض نسخه‌های سیستم مدیریت پایگاه داده رابطه‌ای MySQL قبل از نسخه 5.5 بود که در دسامبر 2009 منتشر شد.

key_buffer_size

InnoDB موتور ذخیره‌سازی پیش‌فرض اکنون MySQL است. پیش‌فرض key_buffer_size احتمالاً می‌تواند کاهش یابد مگر اینکه از MyISAM به عنوان بخشی از برنامه خود به طور مولد استفاده کنید (اما چه کسی اکنون از MyISAM در تولید استفاده می‌کند؟).

ما در اینجا پیشنهاد می‌کنیم اگر حافظه بزرگتری دارید 1٪ RAM یا 256 MiB را در ابتدا تنظیم کنید و حافظه باقیمانده را برای حافظه نهانگاه سیستم عامل و بافر InnoDB اختصاص دهید.

سایر مقررات عملکرد کانفیگ MySQL

slow_query_log

البته این متغیر به تقویت سرور MySQL شما کمک نمی‌کند. با این حال این متغیر می‌تواند به شما در تحلیل سوالات به کندی سرعت کمک کند. برای غیرفعال کردن ورود به سیستم می‌توان مقدار را روی 0 یا OFF تنظیم کرد.

برای فعال کردن آن روی 1 یا ON تنظیم کنید. مقدار پیش‌فرض بستگی به این دارد که گزینه –slow_query_log داده شود. مقصد خروجی ورود به سیستم توسط متغیر سیستم log_output کنترل می‌شود. اگر این مقدار NONE باشد، حتی اگر log فعال باشد هیچ ورودی وارد نمی‌شود. با تنظیم متغیر slow_query_log_file می‌توانید نام پرونده یا مقصد پرونده ورود به سیستم را تنظیم کنید.

long_query_time

اگر پرس‌وجو بیش از این چند ثانیه طول بکشد سرور متغیر وضعیت Slow_queries را افزایش می‌دهد. اگر ثبت کند پرس‌وجو فعال باشد، درخواست در پرونده ورود به سیستم کند پرس‌وجو وارد می‌شود.

این مقدار در زمان واقعی اندازه‌گیری می‌شود نه زمان پردازنده، بنابراین ممکن است سوالی که در یک سیستم کم بارگیری در زیر آستانه است در بالای آستانه یک سیستم بارگیری شده زیاد باشد.

حداقل و مقدار پیش‌فرض long_query_time به ترتیب 0 و 10 است. همچنین توجه داشته باشید که اگر متغیر min_examined_row_limit> 0 تنظیم شود، اگر تعداد ردیف‌های برگشتی کمتر از مقدار تعیین شده در min_examined_row_limit باشد، نمایش داده‌ها از سیستم ثبت نمی‌شود.

sync_binlog

این متغیر کانفیگ MySQL را کنترل می‌کند که MySQL هر چند وقت یکبار Binlogها را بر روی دیسک همگام‌سازی می‌کند. به طور پیش‌فرض (> = 5.7.7)، این مقدار 1 تنظیم شده است که به این معنی است که قبل از انجام معاملات با دیسک همگام‌سازی می‌شود. با این حال این متغیر به دلیل افزایش تعداد نوشتن، تأثیر منفی بر عملکرد دارد.

اما این امن ترین تنظیم است اگر شما می خواهید ACID سازگاری بالا با slaves های شما داشته باشد. متناوباً ، اگر می خواهید همگام سازی دیسک را غیرفعال کنید و فقط به سیستم عامل اعتماد کنید تا گاه به گاه ورود باینری را به دیسک بکشید ، می توانید مقدار این متغیر را روی 0 تنظیم کنید. تنظیم آن بالاتر از 1 به این معنی است که binlog پس از جمع آوری N گروه های باینری log bin ، که N> 1 است ، با دیسک همگام می شود.

استخر بافر را ریخته و بازیابی کنید

این یک چیز معمول است که پایگاه داده تولید شما باید از یک شروع یا راه‌اندازی مجدد به اصطلاح سردوگرم شود. با ریختن حوضچه بافر فعلی قبل از شروع مجدد، محتویات موجود در استخر بافر را ذخیره می‌کند و پس از بالا آمدن محتویات را مجدداً در استخر بافر بارگیری می‌کند. بنابراین این امر از گرم شدن مجدد پایگاه داده شما جلوگیری می‌کند. توجه داشته باشید که این نسخه از سال 5.6 معرفی شده است اما Percona Server 5.5 آن را از قبل در دسترس داشت.

برای فعال کردن این ویژگی هر دو متغیر را تنظیم کنید :

innodb_buffer_pool_dump_at_shutdown = ON و innodb_buffer_pool_load_at_startup = ON

سخت‌افزار

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

برای CPU، پردازنده‌های سریع‌تر با چند هسته برای MySQL حداقل در نسخه‌های جدیدتر از 5.6 بهینه شدند. پردازنده‌های Xeon / Itanium اینتل می‌توانند گران قیمت باشند. اما برای سیستم عامل‌های محاسباتی مقیاس‌پذیر و قابل اعتماد آزمایش شده‌اند.

معیارهایی وجود دارد که سالها پیش ساخته شده است. CPU مدرن می‌تواند فرکانس‌های خود را بر اساس سیاست‌های صرفه‌جویی در مصرف دما، بار و سیستم عامل، بالا و پایین کند.

با این حال این احتمال وجود دارد که تنظیمات CPU شما در سیستم عامل لینوکس روی یک تنظیم‌کننده دیگر تنظیم شود.

با انجام موارد زیر می‌توانید این مورد را بررسی کنید یا آن را با فرمان‌دهنده «عملکرد» ​​تنظیم کنید:

echo performance | sudo tee /sys/devices/system/cpu/cpu[0-9]*/cpufreq/scaling_governor>

نتیجه‌گیری

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