تنظیم و بهینه‌سازی عملکرد MySQL 8.0
موضوعاتی که در این مقاله به آنها پرداخته خواهد شد، به شرح زیر است:
- نصب ابزارهای کمکی: mysqltuner و tuning-primer
- تیونینگ و بهینه‌سازی سرور MySQL
- نتیجه‌گیری: فراتر از ابزارها
مقدمه بهینه‌سازی عملکرد MySQL 8.0
سلام به همراهان گرامی! در این راهنما، می‌خواهیم با هم قدم به قدم یاد بگیریم چطور عملکرد پایگاه داده MySQL 8.0 را در یک سرور لینوکسی محبوب، یعنی CentOS 7، بهینه کنیم. قبل از اینکه وارد مراحل عملی شویم، مهم است که بدانیم چه عواملی روی کارایی MySQL تأثیر می‌گذارند تا بتوانیم بهینه‌سازی عملکرد MySQL 8.0 را هوشمندانه انجام دهیم. همونطور که احتمالاً در مقاله‌ی “سیستم عامل سِنت او اِس: بهترین انتخاب برای سرورهای لینوکسی” هم اشاره کرده‌ایم، CentOS پایداری و قابلیت اطمینان بالایی برای سرورها فراهم می‌کند و حالا ما می‌خواهیم MySQL را روی این بستر قوی، به بهترین شکل تنظیم کنیم.
عوامل اصلی مؤثر بر بهینه‌سازی عملکرد MySQL 8.0 عبارتند از:
- حجم داده‌هایی که قرار است بازیابی شوند.
- منابع سخت‌افزاری در دسترس سرور (CPU، حافظه RAM و سرعت دیسک).
- میزان بار (تعداد درخواست‌ها و پیچیدگی آنها) که به سرور MySQL وارد می‌شود.
اما چرا گاهی اوقات MySQL کند عمل می‌کند؟ دلایل رایج شامل موارد زیر است:
- طراحی نامناسب پایگاه داده: ساختار جداول و روابط بین آنها بهینه نیست.
- گلوگاه‌های سخت‌افزاری: منابع سرور (مثل RAM یا CPU) کافی نیست.
- کوئری‌های ضعیف: دستورات SQL به شکل ناکارآمد نوشته شده‌اند.
- ایندکس‌گذاری نامناسب: ایندکس‌ها به درستی تعریف نشده یا استفاده نمی‌شوند.
- پیکربندی ناکارآمد: تنظیمات MySQL پیش‌فرض یا نادرست باعث هدر رفتن منابع می‌شود (که ما در این مقاله روی بهبود همین بخش تمرکز داریم).
هدف ما در این راهنما، نشان دادن چگونگی تنظیم و بهینه‌سازی عملکرد MySQL 8.0 روی CentOS 7 است تا شاهد افزایش سرعت MySQL و استفاده بهینه از منابع باشیم.
پیش‌نیازها برای بهینه‌سازی عملکرد MySQL 8.0:
برای دنبال کردن این آموزش، من فرض می‌کنم شما موارد زیر را آماده دارید:
- یک سرور مجازی (VPS) یا سرور اختصاصی با سیستم‌عامل CentOS 7 نصب و راه‌اندازی شده.
- MySQL نسخه 8.0 روی سرور شما نصب شده باشد.
- یک کاربر با دسترسی sudo در سرور ایجاد کرده باشید.
نصب ابزارهای کمکی: mysqltuner و tuning-primer
برای اینکه بتوانیم تحلیل دقیقی از وضعیت MySQL داشته باشیم و پیشنهادات خوبی برای بهینه‌سازی دریافت کنیم، از دو ابزار خط فرمان بسیار مفید استفاده خواهیم کرد: mysqltuner و tuning-primer. بیایید آنها را نصب کنیم.
۱. نصب MySQLTuner برای بهینه‌سازی عملکرد MySQL 8.0
Mysqltuner یک اسکریپت Perl عالی است که وضعیت سرور MySQL شما را بررسی کرده و توصیه‌های مشخصی برای بهبود عملکرد، پایداری و کارایی ارائه می‌دهد. استفاده از خروجی mysqltuner فارسی (یعنی تحلیل خروجی آن به زبان ساده) به ما در تنظیمات MySQL کمک زیادی می‌کند.
برای نصب آن، ابتدا باید مخزن EPEL (Extra Packages for Enterprise Linux) را فعال کنیم، چون mysqltuner در این مخزن قرار دارد:
$ sudo yum install -y epel-release
حالا خود mysqltuner را نصب می‌کنیم:
$ sudo yum install -y mysqltuner
۲. نصب tuning-primer برای بهینه‌سازی عملکرد MySQL 8.0
Tuning Primer یک اسکریپت Shell است که با بررسی پارامترهای داخلی MySQL، پیشنهاداتی برای تنظیم متغیرهای سرور می‌دهد و می‌تواند مکمل خوبی برای mysqltuner باشد.
ابتدا ابزار bc (برای محاسبات) و git (برای دانلود اسکریپت) را نصب می‌کنیم (اگر git را ندارید):
sudo yum install bc git -y
سپس، سورس اسکریپت را از GitHub دانلود می‌کنیم:
git clone https://github.com/RootService/tuning-primer.git
حالا وارد پوشه‌ی دانلود شده می‌شویم و اسکریپت را قابل اجرا می‌کنیم:
cd tuning-primer
sudo chmod +x tuning-primer.sh
خب، ابزارها آماده‌اند! برویم سراغ بخش اصلی یعنی بهینه‌سازی عملکرد MySQL 8.0.
تیونینگ و بهینه‌سازی عملکرد MySQL 8.0
فرآیند اصلی تیونینگ از طریق ویرایش فایل پیکربندی اصلی MySQL انجام می‌شود که معمولاً در مسیر زیر قرار دارد:
/etc/my.cnf
(گاهی ممکن است در /etc/mysql/my.cnf یا مسیرهای مشابه باشد، بستگی به نحوه نصب دارد)
گام اول: اجرای mysqltuner و تحلیل نتایج
بیایید ابتدا از mysqltuner استفاده کنیم. دستور زیر را اجرا کنید:
sudo mysqltuner
این ابزار از شما نام کاربری و رمز عبور مدیر MySQL (معمولاً کاربر root پایگاه داده) را می‌خواهد تا بتواند به اطلاعات وضعیت سرور دسترسی پیدا کند. آنها را وارد کنید.
[linuxuser@centos7-hostadvice4 ~]$ sudo mysqltuner
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password: >> MySQLTuner 1.6.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
[--] Skipped version check for MySQLTuner script
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] Currently running unsupported MySQL version 8.0.11
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 16K (Tables: 1)
[OK] Total fragmented tables: 0
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
-------- Security Recommendations -------------------------------------------
mysql: [Warning] Using a password on the command line interface can be insecure.
[OK] There is no anonymous account in all database users
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1054 (42S22) at line 1: Unknown column 'password' in 'where clause'
[OK] All database users have passwords assigned
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(user) OR CAST(password as Binary) = PASSWORD(UPPER(user)) OR CAST(password as B' at line 1
mysql: [Warning] Using a password on the command line interface can be insecure.
[!!] There is not basic password file list !
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
Use of uninitialized value in addition (+) at /bin/mysqltuner line 1934, <>
line 2 (#1)
(W uninitialized) An undefined value was used as if it were already
defined. It was interpreted as a "" or a 0, but maybe it was a mistake.
To suppress this warning assign a defined value to your variables.
To help you figure out what was undefined, perl will try to tell you
the name of the variable (if any) that was undefined. In some cases
it cannot do this, so it also tells you what operation you used the
undefined value in. Note, however, that perl optimizes your program
anid the operation displayed in the warning may not necessarily appear
literally in your program. For example, "that $foo" is usually
optimized into "that " . $foo, and the warning will refer to the
concatenation (.) operator, even though there is no . in
your program.
Use of uninitialized value in division (/) at /bin/mysqltuner line 1934, <>
line 2 (#1)
Use of uninitialized value $mystat{"Qcache_lowmem_prunes"} in numeric eq (==)
at /bin/mysqltuner line 1949, <> line 2 (#1)
-------- Performance Metrics -------------------------------------------------
[--] Up for: 1h 37m 3s (12 q [0.002 qps], 20 conn, TX: 20K, RX: 2K)
[--] Reads / Writes: 100% / 0%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 168.0M global + 1.1M per thread (151 max threads)
[OK] Maximum reached memory usage: 169.1M (4.78% of installed RAM)
[OK] Maximum possible memory usage: 341.4M (9.65% of installed RAM)
[OK] Slow queries: 0% (0/12)
[OK] Highest usage of available connections: 0% (1/151)
[!!] Aborted connections: 40.00% (8/20)
Use of uninitialized value $myvar{"query_cache_size"} in numeric lt (<) at
/bin/mysqltuner line 2281, <> line 2 (#1)
[!!] Query cache is disabled
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 95% (1 created / 20 connections)
[OK] Table cache hit rate: 83% (118 open / 142 opened)
[OK] Open file limit used: 0% (2/10K)
[OK] Table locks acquired immediately: 100% (2 immediate / 2 locks)
[OK] Binlog cache memory access: 0% ( 0 Memory / 0 Total)
-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 18.2% (1M used / 8M cache)
[!!] Cannot calculate MyISAM index size - re-run script as root user
-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[OK] InnoDB buffer pool / data size: 128.0M/16.0K
[OK] InnoDB buffer pool instances: 1
[!!] InnoDB Used buffer: 11.08% (908 used/ 8192 total)
[OK] InnoDB Read buffer efficiency: 94.07% (12243 hits/ 13015 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 12 writes)
-------- AriaDB Metrics -----------------------------------------------------
[--] AriaDB is disabled.
-------- Replication Metrics -------------------------------------------------
[--] No replication slave(s) for this server.
[--] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
MySQL started within last 24 hours - recommendations may be inaccurate
Reduce or eliminate unclosed connections and network issues
Variables to adjust:
query_cache_size (>= 8M)
[linuxuser@centos7-hostadvice4 ~]$
تحلیل خروجی mysqltuner: گزارش طولانی‌ای تولید می‌شود. مهم‌ترین بخش آن، قسمت Recommendations در انتهاست. این قسمت به شما می‌گوید کدام متغیرها احتمالاً نیاز به تنظیم دارند (مثلاً افزایش مقدار بافرها). همچنین به هشدارهایی که با [!!] مشخص شده‌اند دقت کنید.
گام دوم: اعمال تغییرات در فایل پیکربندی برای بهینه‌سازی عملکرد MySQL 8.0
قبل از هر تغییری، حتماً یک نسخه پشتیبان از فایل پیکربندی فعلی تهیه کنید:
sudo cp /etc/my.cnf /etc/my.cnf.bak
حالا فایل اصلی را با یک ویرایشگر متن (مثل vim یا nano) باز کنید:
sudo vim /etc/my.cnf
بر اساس توصیه‌های mysqltuner، مقادیر متغیرهای پیشنهادی را پیدا کرده و آنها را تغییر دهید. یک روش خوب این است که خط حاوی مقدار پیش‌فرض را با گذاشتن علامت # در ابتدای آن غیرفعال (کامنت) کنید و خط جدیدی با مقدار توصیه‌شده یا مقداری که خودتان محاسبه کرده‌اید (مثلاً دو برابر مقدار قبلی برای شروع) اضافه کنید. به خاطر داشته باشید که افزایش سرعت MySQL ممکن است به تدریج و با آزمون و خطا حاصل شود.
گام سوم: راه‌اندازی مجدد MySQL
پس از ذخیره تغییرات در فایل my.cnf، سرویس MySQL را ری‌استارت کنید تا تنظیمات جدید اعمال شوند:
sudo systemctl restart mysql
نکته مهم:
اگر پس از ری‌استارت، سرویس MySQL اجرا نشد یا با خطا مواجه شدید، احتمالاً یکی از تغییراتی که داده‌اید نامعتبر یا بیش از حد بوده است. فایل my.cnf را دوباره باز کنید، آخرین تغییرات را به حالت قبل برگردانید (یا کامنت کنید) و سرویس را دوباره ری‌استارت کنید. سپس تغییرات را یکی‌یکی اعمال کنید و هر بار سرویس را ری‌استارت کنید تا متوجه شوید کدام تنظیمات MySQL باعث بروز مشکل شده است.
/etc/my.cnf
گام چهارم: اجرای مجدد mysqltuner (اختیاری)
اگر سرور با موفقیت ری‌استارت شد، خوب است که پس از مدتی کارکرد سرور (مثلاً یک روز)، دوباره mysqltuner را اجرا کنید تا ببینید آیا توصیه‌های جدیدی وجود دارد یا وضعیت بهبود یافته است.
گام پنجم: استفاده از tuning-primer
حالا همین فرآیند را با ابزار tuning-primer تکرار می‌کنیم. وارد پوشه tuning-primer شوید و آن را اجرا کنید:
cd tuning-primer
sudo ./tuning-primer.sh
این ابزار نیز گزارش مفصلی ارائه می‌دهد.
[linuxuser@centos7-hostadvice4 tuning-primer]$ sudo ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER 2.0.1-r1 -- - By: Matthew Montgomery - - By: Markus Kohlmeyer - MySQL Version 8.0.11 x86_64 Uptime = 0 days 0 hrs 2 min 52 sec Avg. qps = 0 Total Questions = 4 Threads Connected = 1 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 0 out of 25 that take longer than 10.000000 sec. to complete Your long_query_time may be too high, I typically set this under 5 sec. BINARY UPDATE LOG The binary update log is enabled The expire_logs_days is not set. The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually Setting expire_logs_days will allow you to remove old binary logs automatically See http://dev.mysql.com/doc/refman/8.0/en/purge-master-logs.html WORKER THREADS Current thread_cache_size = 9 Current threads_cached = 0 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 151 Current threads_connected = 1 Historic max_used_connections = 1 The number of used connections is 0% of the configured maximum. You are using less than 10% of your configured max_connections. Lowering max_connections could help to avoid an over-allocation of memory See "MEMORY USAGE" section to make sure you are not over-allocating INNODB STATUS Current InnoDB index space = 240 K Current InnoDB data space = 2 M Current InnoDB buffer pool free = 88 % Current innodb_buffer_pool_size = 128 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 153 M Configured Max Per-thread Buffers : 178 M Configured Max Global Buffers : 152 M Configured Max Memory Limit : 330 M Plus 16 M per temporary table created Physical Memory : 3.45 G Max memory limit seem to be within acceptable norms KEY BUFFER No key reads?! Seriously look into using some indexes Current MyISAM index space = 0 bytes Current key_buffer_size = 8 M Key cache miss rate is 1 : 0 Key buffer free ratio = 81 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is supported but not enabled Perhaps you should set the query_cache_size SORT OPERATIONS Current sort_buffer_size = 256 K Current read_rnd_buffer_size = 256 K No sort operations have been performed Sort buffer seems to be fine JOINS Current join_buffer_size = 260.00 K You have had 3 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open_files_limit = 10000 files The open_files_limit should typically be set to at least 2x-3x that of table_open_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 4000 tables Current table_definition_cache = 2000 tables You have a total of 136 tables You have 165 open tables. The table_open_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 16 M Of 95 temp tables, 0% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 793 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 0 : 286 Your table locking seems to be fine [linuxuser@centos7-hostadvice4 tuning-primer]$
- تحلیل خروجی tuning-primer:
به پیشنهاداتی که معمولاً با رنگ‌های قرمز یا زرد هایلایت می‌شوند و یا با کلماتی مثل Recommendation یا Suggestion مشخص شده‌اند، توجه ویژه کنید.
- اعمال تغییرات بر اساس tuning-primer:
مجدداً فایل /etc/my.cnf را (پس از تهیه پشتیبان!) ویرایش کرده و تغییرات پیشنهادی توسط tuning-primer را اعمال کنید.
راه‌اندازی مجدد MySQL: پس از اعمال تغییرات، دوباره سرویس MySQL را ری‌استارت کنید:
sudo systemctl restart mysql
مانند قبل، اگر مشکلی در ری‌استارت پیش آمد، تغییرات را یکی‌یکی بررسی کنید. همچنین می‌توانید پس از مدتی دوباره tuning-primer را اجرا کنید.
نتیجه‌گیری برای افزایش سرعت MySQL 8.0 : فراتر از ابزارها
خب، ما با هم یاد گرفتیم که چطور با استفاده از ابزارهای mysqltuner و tuning-primer، پیشنهاداتی برای بهینه‌سازی عملکرد و افزایش سرعت MySQL 8.0 در سرور CentOS 7 دریافت کنیم و تنظیمات MySQL را بر اساس آنها تغییر دهیم. اما یک نکته بسیار مهم را باید به خاطر بسپاریم: این ابزارها عصای جادویی نیستند! آنها فقط راهنماهایی هستند که به ما کمک می‌کنند نقاط ضعف احتمالی در پیکربندی را پیدا کنیم. افزایش سرعت MySQL واقعی نیازمند نگاهی جامع‌تر است.
برای دستیابی به بهترین عملکرد ممکن و افزایش سرعت MySQL 8.0، من به شما توصیه می‌کنم اقدامات زیر را نیز در نظر بگیرید:
-
- بازبینی و بهینه‌سازی کوئری‌ها: اغلب بزرگترین گلوگاه عملکرد، خودِ کوئری‌های SQL هستند. کوئری‌های کند را شناسایی کنید (مثلاً با استفاده از Slow Query Log) و آنها را بازنویسی کنید. از EXPLAIN برای تحلیل نحوه اجرای کوئری‌ها استفاده کنید.
- بررسی طراحی پایگاه داده و ایندکس‌ها: مطمئن شوید ساختار جداول شما منطقی است و ایندکس‌های مناسب روی ستون‌هایی که در WHERE, JOIN, ORDER BY استفاده می‌شوند، وجود دارد.
- ارزیابی مجدد عوامل اولیه: به عواملی که در مقدمه گفتیم (بار سرور، منابع سخت‌افزاری) دوباره نگاه کنید. شاید واقعاً نیاز به ارتقاء سخت‌افزار (RAM بیشتر، CPU قوی‌تر، دیسک SSD) باشد. انتخاب CentOS 7 به عنوان سیستم‌عامل، پایه خوبی برای این بهینه‌سازی‌ها فراهم می‌کند، اما سخت‌افزار هم نقش کلیدی دارد.
- تنظیمات متناسب با کاربرد: تنظیمات MySQL ایده‌آل به نوع کاری که پایگاه داده شما انجام می‌دهد (خواندن زیاد؟ نوشتن زیاد؟ تراکنش‌های پیچیده؟) بستگی دارد. ممکن است نیاز باشد درباره پارامترهای خاص مرتبط با برنامه خودتان بیشتر تحقیق کنید.
- اجرای منظم ابزارها: توصیه می‌شود این ابزارها (مخصوصاً mysqltuner فارسی و tuning-primer) را به طور دوره‌ای، به خصوص 24 تا 48 ساعت پس از اینکه سرور زیر بار واقعی قرار گرفت، اجرا کنید تا پیشنهاداتی بر اساس الگوی استفاده واقعی دریافت نمایید.
امیدوارم این راهنمای جامع برای بهینه‌سازی عملکرد MySQL 8.0 برای شما مفید بوده باشد. وقت گذاشتید و این مقاله را مطالعه کردید، از شما ممنونم! اگر سوالی دارید، تجربه‌ای در این زمینه کسب کرده‌اید یا نکته‌ای برای اضافه کردن دارید، حتماً در بخش نظرات با من و دیگر دوستان به اشتراک بگذارید. خوشحال می‌شویم از تجربیات شما بیاموزیم.
و فراموش نکنید، اگر در طول این فرآیند یا برای مدیریت و بهینه‌سازی‌های پیشرفته‌تر سرور لینوکس و پایگاه داده خود احساس کردید به کمک تخصصی نیاز دارید، تیم پشتیبانی پشتوار سرور با افتخار آماده است تا شما را یاری کند. کافیست با ما تماس بگیرید!