آموزش بهینه سازی عملکرد MySQL با استفاده از MySQLTuner

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

MySQL چیست؟

MySQL یک سیستم مدیریت پایگاه داده رابطه‌ای متن‌باز محبوب است. داده‌ها در MySQL در جداول متشکل از سطر و ستون ذخیره می‌شوند. که به طور کامل توسط Oracle Corporation توسعه و نگهداری می‌شود.

از نقطه نظر فنی، MySQL به زبان C و ++C نوشته شده و با چندین پلتفرم مانند Microsoft Windows ، Oracle Solaris، AIX، Symbian، Linux، MAC OS و ... سازگار است.

MySQL همچنین بخشی جدایی ناپذیر از پشته LAMP مدرن است که از یک سیستم عامل مبتنی بر لینوکس، وب سرور آپاچی، یک پایگاه داده MySQL و PHP برای پردازش تشکیل شده است.

با استفاده از دستورات SQL (Standard Query Language) می‌توان به راحتی داده‌های خود را در جداول MySQL تعریف، ویرایش، کنترل و کوئری کرد.

ویژگی‌های کلیدی MySQL

MySQL برای ذخیره و بازیابی داده‌ها در طیف گسترده‌ای از برنامه‌ها، وب سایت‌ها و سرویس‌های محبوب استفاده شده و مجموعه‌ای از ویژگی‌های چشم نواز را ارائه می‌دهد که آن را به یکی از RDMSهای با عملکرد بالا تبدیل کرده است:

• سهولت استفاده: MySQL از طیف گسترده‌ای از زبان‌های برنامه نویسی مانند PHP، PERL، C++، C و JAVA پشتیبانی می‌کند. محیط MySQL مجموعه‌ای از ابزارها را برای تسهیل وظایفی مانند مدیریت سرور، گزارش دهی و تجزیه و تحلیل داده‌ها فراهم می‌کند. برای اینکه بتوان با طیف وسیع تری از مجموعه داده‌ها کار کرد، MySQL به طور کامل از چندین ساختار داده، داده های JSON و Geospatial و انواع داده های منطقی، عددی، حروف و عددی، تاریخ و زمان پشتیبانی می‌کند.
• عملکرد درجه یک: MySQL عملکرد کوئری درجه یک را با طیف گسترده‌ای از سرورهای کلاستری تضمین می‌کند. این برنامه ابزارهای بارگذاری سریع را با کش های حافظه متمایز و Table Index Partitioning ارائه می‌دهد.د.
• متن‌باز: MySQL تحت مجوز GNU General Public License (GPL) است. به عبارت دیگر، MySQL همیشه رایگان است.  از آنجایی که این نرم افزار منبع باز است،  یک جامعه عمومی بزرگ ایجاد شده است که به طور منظم اسناد MySQL و فرهنگ پشتیبانی آنلاین را ارتقا می‌دهد.
• بومی سازی(Localization): MySQL از چندین مجموعه کاراکتر مختلف از جمله latin1 (cp1252), german, big5, ujis  و غیره پشتیبانی می کند. همچنین می توانید زبان پیام‌های خطایی را که سرور به کلاینت ارائه می‌دهد تنظیم کنید. زمان سرور MySQL را می‌توان به صورت پویا تغییر داد، بنابراین می توانید منطقه زمانی خاصی را برای هر مشتری تنظیم کنید.
• امنیت و حفاظت داده: SQL به شما امکان می‌دهد تنظیمات کنترل دسترسی به داده‌ها را پیکربندی کنید. برای تعیین اینکه چه کسی می‌تواند داده‌های MySQL را مشاهده یا استفاده کند، می‌توانید از مکانیسم‌های قدرتمندی مانند سیستم دسترسی و مدیریت حساب کاربری استفاده کنید. 

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

ابزارهایی که می توانند به بهینه سازی MySQL کمک کنند
برای اینکه بدانید  آیا دیتابیس  MySQL شما نیاز به پیکربندی مجدد دارد یا خیر، بهتر است به نحوه عملکرد منابع خود نگاه کنید. این کار را می‌توان با دستور top انجام داد. حداقل باید با مقدار مصرف RAM و CPU سرور آشنا شوید که با این دستورات قابل کشف است:
 

echo [PID]  [MEM]  [PATH] &&  ps aux | awk '{print $2, $4, $11}' | sort -k2rn | head -n 20
ps -eo pcpu,pid,user,args | sort -k 1 -r | head -20

MySQLTuner

MySQLTuner یک اسکریپت read-only است که به زبان PERL نوشته شده و نصب MySQL را تجزیه و تحلیل می‌کند و لیستی از توصیه‌ها و تنظیمات را برای بهبود عملکرد و ثبات در اختیار شما قرار می‌دهد.

متغیرهای پیکربندی فعلی و Status Data را بازیابی و لیست کاملی از آمار و پیشنهادات مرتبط با عملکرد، امنیت و... را نمایش می‌دهد.

به طور مداوم نگهداری می شود و از 300+ پیکربندی شاخص مانند  Galera Cluster, TokuDB, Performance schema, Linux OS Metrics, InnoDB, MyISAM, Aria و... پشتیبانی می‌کند.

1.    اسکریپت MySQLTuner را دانلود کنید:

 wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

یا

wget http://mysqltuner.pl/ -O mysqltuner.pl

2.    مجوزهای اسکریپت ها را تغییر دهید تا قابل اجرا باشند:

 chmod +x mysqltuner.pl

3.    اسکریپت mysqltuner.pl را اجرا کنید. از شما خواسته می شود که لاگین مدیریت MySQL و رمز عبور خود را وارد کنید:

 ./mysqltuner.pl

4.    اسکریپت نتایج مشابه خروجی زیر را برمی گرداند:

>>  MySQLTuner 2.2.8  - Major Hayden
        >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
        >>  Run with '--help' for additional options and output filtering
        Please enter your MySQL administrative login: root
        Please enter your MySQL administrative password:
        [OK] Currently running supported MySQL version 5.5.41-0+wheezy1
        [OK] Operating on 64-bit architecture        
-------- Storage Engine Statistics -------------------------------------------
        [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
        [--] Data in InnoDB tables: 1M (Tables: 11)
        [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
        [!!] Total fragmented tables: 11        
-------- Security Recommendations  -------------------------------------------
        [OK] All database users have passwords assigned        
-------- Performance Metrics -------------------------------------------------
        [--] Up for: 47s (113 q [2.404 qps], 42 conn, TX: 19K, RX: 7K)
        [--] Reads / Writes: 100% / 0%
        [--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
        [OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
        [OK] Slow queries: 0% (0/113)
        [OK] Highest usage of available connections: 0% (1/151)
        [OK] Key buffer size / total MyISAM indexes: 16.0M/99.0K
        [!!] Query cache efficiency: 0.0% (0 cached / 71 selects)
        [OK] Query cache prunes per day: 0
        [OK] Temporary tables created on disk: 25% (54 on disk / 213 total)
        [OK] Thread cache hit rate: 97% (1 created / 42 connections)
        [OK] Table cache hit rate: 24% (52 open / 215 opened)
        [OK] Open file limit used: 4% (48/1K)
        [OK] Table locks acquired immediately: 100% (62 immediate / 62 locks)
        [OK] InnoDB buffer pool / data size: 128.0M/1.2M
        [OK] InnoDB log waits: 0
        -------- Recommendations -----------------------------------------------------
        General recommendations:
            Run OPTIMIZE TABLE to defragment tables for better performance
            Enable the slow query log to troubleshoot bad queries
        Variables to adjust:
            query_cache_limit (> 1M, or use smaller result sets)

MySQLTuner  پیشنهاداتی در مورد چگونگی بهبود عملکرد پایگاه داده ارائه می‌دهد. اگر در مورد به  آپدیت دیتابیس خود محتاط هستید، پیروی از پیشنهادات MySQLTuner یکی از راه‌های امن‌تر برای بهبود عملکرد دیتابیس است.

تیونینگ  MySQL 

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

فایل پیکربندی MySQL در فایل زیر ذخیره می‌شود

/etc/mysql/my.cnf

تهیه بکاپ از تنظیمات MySQL

قبل از به‌روزرسانی تنظیمات MySQL، یک نسخه بکاپ از فایل my.cnf ایجاد کنید:

cp /etc/mysql/my.cnf ~/my.cnf.backup

بهترین روش این است که تغییرات کوچک را یکی یکی انجام دهید و بعد از هر تغییر سرور را بررسی کنید. پس از هر تغییر باید MySQL را مجددا راه اندازی کنید:

برای توزیع‌های که از systemd استفاده می‌کنند

systemctl restart mysqld

برای توزیع‌های که از init استفاده می‌کنند

service mysql restart

MySQLTuner می‌تواند برای افزایش عملکرد و پایداری، پیشنهادات مرتبط با پارامترهای زیر را به شما ارائه دهد:

key_buffer

با تغییر key_buffer، حافظه بیشتری به MySQL اختصاص می‌یابد، که می‌تواند به طور قابل ملاحظه‌ای سرعت دیتابیس را افزایش دهد، البته با این فرض که حافظه خالی دارید. اندازه key_buffer معمولا نباید بیش از 25 درصد از حافظه سیستم را هنگام استفاده از MyISAM table engine  و تا 70 درصد برای InnoDB اشغال کند. اگر مقدار خیلی بالا تنظیم شود، منابع هدر می روند.
با توجه به مستندات MySQL ، برای سرورهایی با 256MB (یا بیشتر) RAM با جداول زیاد، تنظیم 64M  توصیه می‌شود. سرورهایی با 128MB رم و جداول کمتر را می‌توان روی 16M (مقدار پیش فرض)، تنظیم کرد. وب‌سایت‌هایی که منابع و جدول‌های کمتری دارند، می‌توانند این مقدار پایین‌تر داشته باشند.

max_allowed_packet

این پارامتر امکان می‌دهد حداکثر اندازه یک بسته قابل ارسال را تنظیم کنید. یک بسته یک SQL state است، یک ردیف منفرد به یک کلاینت ارسال می‌شود، یا یک گزارش که از دیتابیس سورس  به یک replica ارسال می شود. اگر می‌دانید که سرور MySQL شما بسته‌های بزرگ را پردازش می‌کند، بهتر است آن را به اندازه بزرگ‌ترین بسته خود افزایش دهید. اگر این مقدار خیلی کوچک باشد، یک خطا در error log دریافت خواهید کرد.

thread_stack

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

thread_cache_size

اگر thread_cache_size  خاموش یا turned off باشد (روی 0 تنظیم شود)، هر اتصال جدیدی که ایجاد می‌شود نیاز به یک رشته جدید ایجاد شده برای آن دارد. هنگامی که اتصالات جدا می‌شوند، thread از بین می‌رود. در غیر این صورت، این مقدار تعداد thread ‌های استفاده نشده را تعیین می‌کند تا در حافظه پنهان (Cache) ذخیره شوند تا زمانی که برای اتصال مورد استفاده قرار گیرند. به طور کلی این تنظیم تاثیر کمی بر عملکرد دارد، مگر اینکه صدها اتصال در دقیقه دریافت کنید، در این زمان این مقدار باید افزایش یابد تا بتوان اکثر اتصالات را بر روی رشته‌های کش ایجاد کرد.

max_connections

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

table_cache

این مقدار باید بالاتر از مقدار open_tables شما نگه داشته شود. برای تعیین این مقدار از دستور زیر استفاده می‌کنیم:

SHOW STATUS LIKE 'open%';

tmp_table_size یا max_heap_table_size

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

Join_buffer_size

آن را با کم کم افزایش دهید زیرا در max_connections ضرب می‌شود.

Innodb_buffer_pool_size

اگر MySQLTuner پیشنهاد افزایش این مقدار را داد، می‌توانید آن را به اندازه‌ای بزرگ کنید که همه پایگاه‌های داده InnoDB شما را در خود جای دهد، زیرا این پارامتر به طور قابل‌توجهی بر عملکرد تاثیر می‌گذارد.

همیشه MySQLTuner را بعد از 24 ساعت از شروع MySQL برای نتایج دقیق اجرا کنید؛ برای مواردی که توصیه‌ها و پیشنهادات واضح نیست، توصیه می‌شود با DBA یا مدیر سیستم مورد اعتماد خود مشورت کنید.

نتیجه

در این مطلب، نحوه نصب، اجرا و استفاده موثر از MySQLTuner را برای بهینه سازی عملکرد MySQL بررسی کردیم.. MySQLTuner نصب MySQL شما را بررسی و لیستی از پیشنهادات را برای افزایش عملکرد و ثبات سرور ارائه می‌دهد.

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