کانفیگ 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 است. حداقل اندازه بافر که برای اسکن های شاخص ساده ، اسکن های شاخص دامنه و اتصالات استفاده می شود که از شاخص ها استفاده نمی کنند و بنابراین اسکن های جدول کامل را انجام می دهند.

بیشتر بخوانید:  NginX چیست معرفی دومین وب سرور محبوب جهان

همچنین این مقدار توسط بهینه سازی 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 را رد کنید.

بیشتر بخوانید:  پهنای باند هاست (Bandwidth) دقیقا چیه؟

 

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 بالا نیز مقیاس خوبی ندارد ، اما درصورتی که درصد صفحات کثیف بیش از این مقدار باشد ، در هر ثانیه برافروختگی بافر کثیف را در هر ثانیه دو برابر می کند.

بیشتر بخوانید:  VPN چیست !؟ همه چیز در مورد VPN

متا دیتای InnoDB

innodb_io_capacity

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

 

innodb_write_io_threads

تعداد رشته هایی را که در حال نوشتن بر روی دیسک