بهینه سازی دیتابیس MariaDB
بهینه سازی دیتابیس MariaDB
MariaDB که توسط توسعه دهندگان اصلی MySQL راه اندازی شده است، یکی از RDMS های پرکاربرد است. همانطور که استفاده از MariaDB به سطح گسترده ای رسید، مدیریت عملکرد و بهینه سازیش بسیار مهمتر شد. MariaDB شامل پارامترهای زیادی است که پایگاه های داده به طور مستقیم به آنها وابسته هستند. تغییر این پارامترها می تواند عملکرد پایگاه داده و در نهایت ثبات محصول شما را بهبود بخشد. اول از همه ما در مورد دو موتور پایگاه داده mariadb بحث خواهیم کرد. در این مقاله میتوانید آموزش نصب MariaDB را مشاهده کنید.
انواع موتورهای (Database engine) پایگاه داده
دو نوع موتور پایگاه داده MyISAM و InnoDB وجود دارد. هر دو دارای امتیازات مثبت و منفی خود هستند و بسته به نیاز محصول شما قابل استفاده هستند. تفاوت اصلی و مهم بین آنها این است که MyISAM از قفل کردن سطح جدول پشتیبانی می کند در حالی که InnoDB از قفل شدن سطح ردیف پشتیبانی می کند اما با این مزیت InnoDB یک اشکال وجود دارد که کندتر از MyISAM است. تغییر نام جدول InnoDB در مقایسه با جدول MyISAM زمان بسیار بیشتری خواهد برد. بعد از خرید VPS از هر یک از لوکیشن های خرید سرور مجازی ایران یا خرید سرور مجازی آلمان میتونین این بهینه سازی هارو انجام بدین و باعث عملکرد سایتتون بشید.
در زیر پارامترهایی ذکر شده است که باید بهینه شوند تا از تنظیمات MariaDB شما حداکثر استفاده را ببرند:
InnoDB file-per-table InnoDB Buffer Pool Size Disable Swapiness In MySQL Max Connections Thread Cache Size Disable MySQL DNS Lookups Query Cache Size Tmp Table Size & Max Heap Table Size Slow Query Logs Idle Connections
InnoDB file-per-table
InnoDB موتور پیش فرض mariaDB است و پس از تنظیم این پارامترها ، همه جداول دارای پرونده .idb مخصوص خود در سرور هستند. حال این سوال مطرح می شود که چگونه MariaDB را کارآمدتر می کند؟ از آنجا که تمام عملیات انجام شده بر روی این جدول از I / O آن فایل .idb استفاده می کند و اگر آن جدول را کوتاه کنید می توانید آن فضا را بازیابی کنید زیرا فایل مقابل آن جدول حذف خواهد شد. همچنین می توانید جداول را بر روی دستگاه های ذخیره سازی مختلف ذخیره کنید زیرا فایل های .idb می توانند در سرور دیگر موجود باشند. برای فعال کردن این پارامتر باید پارامتر زیر را در پرونده /etc/my.cnf تنظیم کنید.
innodb_file_per_table=1
InnoDB Buffer Pool Size
innodb_buffer_pool_size = 1G
Disable Swap In MySQL
Swapping زمانی اتفاق می افتد که حافظه فیزیکی سیستم های شما تمام شود و از دیسک مخصوص معروف به swap استفاده کنید. هنگامی که حافظه RAM شما اشغال می شود، شروع به تعویض می کند و از ورودی و خروجی دیسک شما استفاده می شود که باعث کند شدن سرور می شود و در صورت عدم برداشتن مراحل مناسب، سیستم می تواند خراب شود. برای جلوگیری از swappiness ، دستور زیر را در خط فرمان خود اجرا کنید.
مقدار swappiness پیش فرض 60 است که به 0 تغییر می کند که نشان می دهد اکنون غیرفعال است.
sysctl -w vm.swappiness=0
Max Connections
Max connection پارامتر حداکثر اتصال در MySQL نشان می دهد که چند اتصال همزمان می تواند در سرور MariaDB شما ایجاد شود. وب سایت های کوچک 300 تا 500 اتصال به طور معمول مصرف می کنند در حالی که وب سایت های عظیم 1000 تا 1500 اتصال مصرف می کنند. در اینجا باید به یک نکته مهم توجه داشت که باز و بسته شدن اتصالات کاملاً به توسعه دهنده بستگی دارد و اگر اتصالات به درستی مدیریت نشوند ، وب سایت شما شروع به نمایش “اتصالات بسیار زیاد” می کند. برای تنظیم پارامتر، خط زیر را به my.cnf اضافه کنید.
Max connections = 300
Thread Cache Size
Thread cache size همچنین عملکرد سرور پایگاه داده شما را بهینه می کند زیرا تعداد رشته ها را در حافظه پنهان ذخیره می کند اما به طور تصادفی تنظیم نمی شود و شما باید با استفاده از فرمول ساده زیر مقدار بهینه آن را محاسبه کنید.
100 –((Threads_Created / Connections) *100)
حال ما این متغیرها را محاسبه خواهیم کرد. اول از همه با ایجاد دستور زیر می توانید موضوعات ایجاد شده را پیدا کنید.
mysql> show status like 'Threads_created';
خروجی نمونه:
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | Threads_created | 14515 | +-----------------+-------+ 1 row in set (0.00 sec)
اتصالات را می توان به صورت زیر محاسبه کرد:
mysql> show status like 'Connections';
+---------------+----------+ | Variable_name | Value | +---------------+----------+ | Connections | 36372200 | +---------------+----------+ 1 row in set (0.01 sec)
اکنون قرار دادن این مقادیر در فرمول نتیجه زیر را به ما می دهد
100-((14515/36372200)*100) = 96
این نتیجه خوب است اما اگر مانند زیر عدد کمی دریافت کنید می توانید پارامتر را با اضافه کردن خط زیر در my.cnf تنظیم کنید
thread_cache_size = 16;
Disable MySQL DNS Lookups
هر بار که اتصال باز می شود، IP آن resolved by DNS lookup می شود. که مقداری زمان می برد. برای ذخیره این کار اضافی که توسط سرور انجام شده است، می توانیم جستجوی DNS معکوس را با اضافه کردن خط زیر به پرونده my.cnf غیرفعال کنیم.
skip-name-resolve
Query Cache Size
اندازه حافظه کش Query نیز یک پارامتر مهم است زیرا تمام جستجوگرهایی را که تکرار می شوند با داده های مشابه ذخیره می کند. برای وب سایت های کوچک مقدار آن نباید بیش از 64 مگابایت باشد. افزایش این مقدار به GB باعث کاهش عملکرد به جای کارآمد کردن آن می شود. برای تنظیم مقدار مورد نیاز، خط زیر را به my.cnf اضافه کنید.
query_cache_size = 64M
Tmp Table Size & Max Heap Table Size
این پارامترها برای جلوگیری از نوشتن دیسک در سرورهای شما تنظیم شده اند. برای عملکرد کارآمد، هر دو این مقادیر باید یکسان باشند. برای تنظیم این پارامترهای ذکر شده خطوط زیر را به my.cnf اضافه کنید.
tmp_table_size= 64M
max_heap_table_size= 64M
Slow Query Logs
این یکی از بهترین ویژگی های MariaDB است که به توسعه دهندگان پایگاه داده اجازه می دهد تا با بررسی queries هایی که برای اجرای آنها زمان زیادی لازم است، عملکرد را بهبود بخشند. برای فعال کردن گزارش های کند جستجو ، خطوط زیر را اضافه کنید.
slow-query-log = 1 slow-query-log-file = /var/lib/mysql/mysql-slow.log long_query_time = 1
پارامتر اول لاگ کوئری های کند درخواست را فعال می کند. دومی اجازه می دهد تا سرور پایگاه داده نام فایلی را که در آن درخواست های کند صادر می شود، بداند و آخرین پارامتر به سرور می گوید که حداکثر آستانه برای مدت زمان طولانی کوئری چیست.
Idle Connections
مدیریت اتصال در MariaDB همیشه مهم است زیرا منابع سیستم ها را اشغال می کند. اگر اتصالات در کد به درستی بسته نشوند، باید زمان آنها تمام شود و به طور خودکار بسته شوند و این کار را می توان با تنظیم متغیر timeout انجام داد.
wait_timeout=60
پارامتر فوق باعث قطع ارتباط در حالت sleep بعد از 60 ثانیه می شود.
نتیجه :
همانطور که در این مقاله نشان داده شده است، این پارامترها را می توان به منظور افزایش حداکثر عملکرد پایگاه داده تغییر داد، اما مقادیر باید پس از محاسبه مناسب پیش بینی ترافیک و مشخصات سیستم سرور پایگاه داده تنظیم شوند. همیشه توصیه می شود در مورد مقادیر بهینه چنین پارامترهایی از توسعه دهندگان خود کمک و مشاوره بگیرید.