بهینه سازی عملکرد MYSQL با استفاده از MYSQLTuner

MySQLTuner چیست؟

MYSQLTuner یک اسکریپت است که نصب MYSQL را ارزیابی می‌کند و سپس پیشنهاداتی را برای افزایش عملکرد و ثبات سرور تان ارائه می‌دهد.

بهینه سازی عملکرد MYSQL با استفاده از MYSQLTuner:

• اسکریپت MYSQLTuner را دانلود و اجرا کنید

curl -L http://mysqltuner.pl/ | perl

• نتایج خروجی به‌صورت زیر است

>>  MySQLTuner 1.4.0 – Major Hayden <major@mhtx.net>

 >>  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)

اسکریپت MSQLTuner پیشنهاداتی را جهت بهبود عملکرد دیتابیس ارائه می‌دهد. چنان‌چه می‌خواهید خودتان دیتابیس را به‌روز رسانی کنید، پیروی از پیشنهادات MYSQLTuner یکی از ایمن‌ترین روش‌ها برای بهبود عملکرد دیتابیس است.

تنظیم MYSQL

هنگام تغییر در پیکربندی MYSQL، خیلی باید احتیاط کرد چرا که هرگونه تغییری مستقیماً بر روی دیتابیس اثر خواهد گذاشت. حتی زمانی که از ساختار برنامه‌هایی مانند MYSQLTuner استفاده می‌کنید، بهتر است تا از نحوه‌ی انجام فرایند مطلع باشید.

فایلی که در حال انجام تغییرات بر روی آن هستید، در /etc/mysql/my.cnf قرار دارد.

توجه:

پیش از شروع به‌روز رسانی پیکربندی MYSQL، از فایل my.cnf بکاپ بگیرید:

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

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

برای سیستم‌های بدون systemd:

systemctl restart mysqld

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

service mysql restart

Key_buffer

تغییر Key_buffer باعث می‌شود تا حافظه بیشتری به MYSQL اختصاص یابد و در نتیجه منجر به افزایش سرعت دیتابیس شما خواهد شد. اندازه key_buffer معمولاً نباید بیشتر از ۲۵ درصد حافظه سیستم هنگام استفاده از موتور MyISAM باشد. همچنین این اندازه برای InnoDB باید حداکثر ۷۰ درصد باشد. اگر اندازه بیش از حد بالا باشد، باعث هدر رفتن منابع خواهد شد.

مطابق با مستندات MYSQL، برای سرورهایی که ۲۵۶ مگابایت حافظه دارند، مقداری Key_buffer باید برابر با ۶۴M باشد. سرورهایی که دارای حافظه‌ی ۱۲۸ مگابایتی یا کمتر از آن هستند، این مقدار ۱۶M پیشنهاد می‌شود.

max_allowed_packet

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

thread_stack

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

thread_cache_size

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

 max_connections

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

table_cache

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

SHOW STATUS LIKE ‘open%’;

با رعایت مواردی که در بالا گفته شد، بهینه سازی عملکرد MYSQL با استفاده از MYSQLTuner را می‌توانید به‌سادگی انجام دهید.