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

کانفیگ 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 را فشار دهید.

نصب MySQL

اسکریپت 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 به طور پیش فرض، مقدار فعلی تنظیم شده این متغیر 256 KiB است. بر این اساس فقط درصورتی که کوئری هایی داشته باشید که به شدت از انواع استفاده می کنند یا تماس می گیرند، میتوانید مجددا در کانفیگ 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 صدق نمی کند، به خصوص هنگام استفاده از MRR. MariaDB از mrr_buffer_size در حالی که MySQL از read_buffer_size read_rnd_buffer_size استفاده می کند.

پارامتر اندازه بافر

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)، مقدار پیش فرض فعلی 64 MiB است. اگر ردیف های بزرگ BLOB دارید که باید بیرون کشیده شوند (یا بخوانید) ، ممکن است این تنظیم را انجام دهید، در غیر این صورت می توانید این تنظیمات پیش فرض را با 8.0 بگذارید اما در نسخه های قدیمی ، پیش فرض 4 MiB است بنابراین در صورت وجود می توانید از آن مراقبت کنید با خطای 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

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

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 باشد ، نمایش داده ها از سیستم ثبت نمی شود.

MyISAM

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 را می تونا به عنوان یکی از کارهای پیچیده معرفی نمود. در صورتی که شما نصب این پایگاه داده را با تنظیمات پیشفرض انجام دهید کار زیاید در پیش ندارید. اما شما نمیتوانید یک پایگاه داده بهینه داشته باشید. برای بهینه سازی این پایگاه داده ها باید کانفیگ MySQL را به صورت دستی انجام دهید. در این مقاله پارامتر های ضروری را با هم بررسی کردیم و دیدیم که برای اینکار لازم است چه مواردی را تغییر دهید.
واکنش شما چیست؟
+1
+1
+1
+1
+1
+1
2
+1
1
برچسب‌ها:, , ,

نظر دهید

آدرس ایمیل شما منتشر نخواهد‌شد.