اندازه‌گیری کارایی کوئری‌های MySQL با mysqlslap

مقدمه

mysqlslap یک ابزار تست بار (Load Testing) و بنچمارک است که همراه MySQL عرضه می‌شود. این ابزار با اجرای مکرر بارهای کاری SQL و گزارش‌گیری از زمان‌بندی تجمعی، عملکرد دیتابیس شما را تحت بار شبیه‌سازی‌شده کلاینت اندازه‌گیری می‌کند: میانگین، حداقل و حداکثر ثانیه برای تکمیل بار کاری، به‌علاوه تعداد کلاینت‌های هم‌زمان. درک این متریک‌ها به شما کمک می‌کند خطوط پایه (Baseline) تعیین کنید، نقاط اشباع (Saturation) را پیدا کنید و تغییرات (مانند ایندکس‌ها یا پیکربندی‌های جدید) را قبل از تأثیرگذاری روی پروداکشن اعتبارسنجی کنید.

mysqlslap همراه MySQL 8.0 عرضه می‌شود و نیازی به نصب جداگانه ندارد. این آموزش، تست‌های بارِ خودکار برای خطوط پایه سخت‌افزاری، کوئری‌های سفارشی برای بنچمارک‌های اختصاصی اپلیکیشن، شبیه‌سازی هم‌زمانی برای یافتن محدودیت‌های مقیاس‌پذیری، بنچمارک اختصاصی موتورهای ذخیره‌سازی (InnoDB در مقابل MyISAM) و تست در برابر دیتابیس‌های MySQL ریموت یا مدیریت‌شده مانند MySQL مدیریت‌شده پارمین کلود را پوشش می‌دهد.

هشدار: بنچمارک را روی دیتابیس پروداکشن اجرا نکنید. از یک سرور تست اختصاصی یا یک instance دیتابیس مدیریت‌شده ایزوله از ترافیک پروداکشن استفاده کنید.

پیش‌نیازها

• یک سرور اوبونتو (هر نسخه LTS پشتیبانی‌شده)
• MySQL 8.0 یا جدیدتر نصب‌شده، یا دسترسی به یک instance دیتابیس مدیریت‌شده پارمین کلود
• یک کاربر غیر root در MySQL با دسترسی‌های کافی برای دیتابیس‌هایی که تست می‌کنید
• حداقل ۲ گیگابایت رم برای تمرین‌های دیتابیس نمونه در این آموزش توصیه می‌شود

نکات کلیدی

• mysqlslap همراه MySQL 8.0 عرضه می‌شود و نیازی به نصب جداگانه ندارد.
• برای تست‌های خط پایه سخت‌افزاری از --auto-generate-sql استفاده کنید؛ برای بنچمارک‌های اختصاصی اپلیکیشن از --query با یک فایل .sql استفاده کنید.
• --concurrency کلاینت‌های موازی شبیه‌سازی‌شده را کنترل می‌کند؛ --iterations تکرار تست را برای قابلیت اطمینان آماری کنترل می‌کند.
• همیشه بنچمارک را روی یک نسخه غیرپروداکشن دیتابیس‌تان اجرا کنید.
• کش کوئری در MySQL 8.0 حذف شده است؛ کش باقی‌مانده از Buffer Pool اینnoDB و کش صفحه سیستم‌عامل می‌آید؛ پس انتظار اجراهای سریع‌تر «کش گرم» را داشته باشید و در صورت نیاز، برای اندازه‌گیری عملکرد کش-سرد، Buffer Pool را فلش کنید یا MySQL را بین اجراها ری‌استارت کنید.
• بعد از شناسایی کوئری‌های کند، از EXPLAIN ANALYZE برای تشخیص پلن اجرا استفاده و تغییرات ایندکس را با یک اجرای بعدی mysqlslap اعتبارسنجی کنید.
• mysqlslap از دیتابیس‌های MySQL ریموت و مدیریت‌شده از طریق فلگ‌های --host، --port و --ssl-ca پشتیبانی می‌کند.
• برای شبیه‌سازی کامل بار کاری OLTP، mysqlslap را با sysbench جفت کنید.

نحوه کار mysqlslap

mysqlslap از مدل شبیه‌سازی کلاینت استفاده می‌کند: تعدادی قابل‌تنظیم از تردهای موازی کلاینت راه می‌اندازد که هر کدام همان بار کاری SQL را (چه خودکار-تولیدشده و چه تأمین‌شده توسط شما) اجرا می‌کنند. بار کاری را یک بار یا بیشتر (Iteration) اجرا و سپس زمان‌بندی تجمعی همه کلاینت‌ها و اجراها را گزارش می‌دهد. این به شما اجازه می‌دهد ببینید چگونه تأخیرِ میانگین و بدترین‌حالت با افزایش هم‌زمانی یا تغییر ترکیب کوئری تغییر می‌کند.

درک اینکه mysqlslap حین اجرای تست با دیتابیس‌های‌تان چه می‌کند، قبل از اجرای هر دستوری مهم است. وقتی از --auto-generate-sql استفاده می‌کنید، mysqlslap دیتابیس موقت خودش به نام mysqlslap را می‌سازد، یک جدول ساده داخلش ایجاد، بار کاری را اجرا و در پایان تست کل دیتابیس را حذف می‌کند. به هیچ‌چیز در دیتابیس‌های موجود شما دست نمی‌زند. وقتی از --create-schema با کوئری‌های خودتان استفاده می‌کنید، mysqlslap به آن دیتابیس موجود وصل و کوئری‌های شما را مثل یک کلاینت معمولی در برابرش اجرا می‌کند. دیتابیس را نمی‌سازد یا حذف نمی‌کند و اسکیما یا داده را تغییر نمی‌دهد مگر اینکه کوئری شما این کار را بکند. این تمایز تعیین می‌کند کدام ترکیب فلگ برای هر سناریوی تست مناسب است.

گزینه‌های مرجع:

فلگ	هدف	مقدار نمونه
--concurrency	تعداد اتصالات هم‌زمان کلاینت	50
--iterations	تعداد دفعات تکرار تست کامل	10
--auto-generate-sql	استفاده از کوئری‌های خودکار-تولیدشده	(فقط فلگ)
--query	رشته SQL درون‌خطی یا مسیر فایل .sql	"SELECT * FROM t;" یا /path/to/file.sql
--create-schema	دیتابیس هدف اجرای کوئری‌ها	employees
--delimiter	جداکننده برای دستورات متعدد	;
--engine	موتور ذخیره‌سازی برای تست	InnoDB
--number-int-cols	ستون‌های int در جدول خودکار-تولیدشده	5
--number-char-cols	ستون‌های varchar در جدول خودکار-تولیدشده	20
--number-of-queries	کل کوئری‌های توزیع‌شده بین کلاینت‌ها	1000
--debug-info	چاپ مصرف CPU و حافظه بعد از تست	(فقط فلگ)
--verbose	نمایش آمار هر اجرا	(فقط فلگ)

گام ۱ — تأیید موجود بودن mysqlslap روی سیستم

تأیید کنید که بعد از نصب استاندارد MySQL 8.0، mysqlslap موجود است:

mysqlslap --version

فرمت خروجی مورد انتظار:

mysqlslap  Ver 8.0.xx Distrib 8.0.xx, for Linux (x86_64)

اگر mysqlslap موجود نیست، توسط پکیج mysql-client ارائه می‌شود. آن را نصب کنید:

sudo apt update
sudo apt install mysql-client

دوباره mysqlslap --version را اجرا کنید تا تأیید شود.

نکته: در MySQL 8.0، mysqlslap به‌طور پیش‌فرض با پلاگین احراز هویت caching_sha2_password وصل می‌شود. اگر سرور MySQL شما TLS لازم دارد (رایج برای سرویس‌های مدیریت‌شده)، با --ssl-mode=REQUIRED و در صورت نیاز --ssl-ca=/path/to/ca.pem وصل شوید. اگر خطاهای احراز هویت دیدید، مطمئن شوید از کلاینتی استفاده می‌کنید که caching_sha2_password را پشتیبانی می‌کند یا فقط به‌عنوان آخرین راه‌حل، کاربر MySQL را به mysql_native_password تغییر دهید. برای اتصالات غیر-TLS با caching_sha2_password، ممکن است لازم باشد --get-server-public-key را هم اضافه کنید تا کلاینت بتواند کلید عمومی سرور را دریافت کند.

گام ۲ — نصب MySQL و بارگذاری دیتابیس نمونه

MySQL Server را روی اوبونتو نصب، فعال و اسکریپت امنیتی را اجرا کنید:

sudo apt update
sudo apt install mysql-server
sudo systemctl enable --now mysql
sudo mysql_secure_installation

به‌عنوان کاربر root به MySQL وصل شوید:

sudo mysql

پرامپت MySQL را می‌بینید:

Welcome to the MySQL monitor.  Commands end with ; or \g.
mysql>

یک کاربر تست غیر root با سینتکس MySQL 8.0 بسازید. your_password را با یک رمز قوی جایگزین کنید. در پرامپت MySQL اجرا کنید:

CREATE USER 'benchuser'@'localhost' IDENTIFIED BY 'your_password';

-- اختیاری: اسکیمای اختصاصی برای تست‌های خودکار mysqlslap
CREATE DATABASE IF NOT EXISTS mysqlslap_benchmark;

-- دسترسی فقط-خواندنی به اسکیماهای نمونه استفاده‌شده در این آموزش
GRANT SELECT ON employees.* TO 'benchuser'@'localhost';
GRANT SELECT ON employees_backup.* TO 'benchuser'@'localhost';

-- دسترسی‌های گسترده‌تر فقط روی اسکیمای اختصاصی بنچمارک
GRANT CREATE, DROP, SELECT, INSERT, UPDATE, DELETE, INDEX, ALTER
    ON mysqlslap_benchmark.* TO 'benchuser'@'localhost';
FLUSH PRIVILEGES;
EXIT;

حالا می‌توانید از benchuser برای همه دستورات بعدی این آموزش استفاده کنید.

دیتابیس نمونه employees را دانلود و بارگذاری کنید:

mkdir ~/mysqlslap_tutorial && cd ~/mysqlslap_tutorial
wget https://github.com/datacharmer/test_db/archive/refs/heads/master.zip
sudo apt install unzip
unzip master.zip
cd test_db-master
mysql -u benchuser -p < employees.sql

هنگام درخواست، رمز benchuser را وارد کنید. ایمپورت را تأیید کنید:

SHOW DATABASES;
USE employees;
SHOW TABLES;
SELECT COUNT(*) FROM employees;

خروجی مورد انتظار برای COUNT(*):

+----------+
| count(*) |
+----------+
|   300024 |
+----------+

یک دیتابیس بکاپ برای بنچمارک امن بسازید:

mysqldump -u benchuser -p employees > ~/mysqlslap_tutorial/employees_backup.sql
mysql -u benchuser -p -e "CREATE DATABASE employees_backup;"
mysql -u benchuser -p employees_backup < ~/mysqlslap_tutorial/employees_backup.sql

رمز را هنگام درخواست برای هر دستور وارد کنید. حالا employees و employees_backup را برای تست‌ها در دسترس دارید.

گام ۳ — اجرای اولین بنچمارک با کوئری‌های خودکار-تولیدشده

گزینه --auto-generate-sql دیتابیس موقتی به نام mysqlslap می‌سازد، عملیات INSERT و SELECT را در برابر جدول خودکار-تولیدشده اجرا و سپس دیتابیس را حذف می‌کند. از آن برای تست‌های خط پایه سطح-سخت‌افزار استفاده کنید، نه برای تست کوئری‌های خاص اپلیکیشن‌تان. این نقطه شروع درست قبل از تست کوئری‌های خودتان است. اگر سرورتان نتواند بار خودکار-تولیدشده را در سطح هم‌زمانی مشخصی مدیریت کند، کوئری‌های واقعی اپلیکیشن را هم بهتر مدیریت نخواهد کرد. اول این خط پایه را برقرار کنید، سپس به تست کوئری سفارشی بروید.

یک خط پایه تک-کلاینت اجرا کنید:

mysqlslap --user=benchuser --password --host=localhost \
  --auto-generate-sql --verbose

رمز را هنگام درخواست وارد کنید. خروجی مورد انتظار:

Benchmark
        Average number of seconds to run all queries: 0.009 seconds
        Minimum number of seconds to run all queries: 0.009 seconds
        Maximum number of seconds to run all queries: 0.009 seconds
        Number of clients running queries: 1
        Average number of queries per client: 0

هم‌زمانی را به ۵۰ کلاینت و ۱۰ iteration افزایش دهید:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql --verbose

خروجی مورد انتظار:

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

دقت کنید که زمان میانگین از ۰.۰۰۹ ثانیه (تک کلاینت) به ۰.۱۹۷ ثانیه زیر ۵۰ کلاینت هم‌زمان افزایش یافته. جهش حداکثر به ۰.۳۹۹ ثانیه نشان می‌دهد رقابت (Contention) حتی روی این بار کاری سبکِ خودکار-تولیدشده شروع به ظاهر شدن کرده است.

جدول شبیه‌سازی‌شده را به ۵ ستون عدد صحیح و ۲۰ ستون varchar با ۱۰۰ iteration گسترش دهید:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=100 \
  --number-int-cols=5 --number-char-cols=20 \
  --auto-generate-sql --verbose

خروجی مورد انتظار:

Benchmark
        Average number of seconds to run all queries: 0.521 seconds
        Minimum number of seconds to run all queries: 0.389 seconds
        Maximum number of seconds to run all queries: 1.642 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

اسکیمای جدولِ گسترده‌تر، زمان میانگین را بیشتر افزایش و شکاف بین حداقل و حداکثر را بازتر می‌کند؛ که هزینه I/O بالاترِ خواندن ستون‌های بیشتر به ازای هر ردیف زیر بار هم‌زمان را منعکس می‌کند.

دیتابیس mysqlslap در شروع تست ساخته و در پایان حذف می‌شود. از یک نشست MySQL جداگانه می‌توانید حین اجرای تست SHOW DATABASES; را اجرا کنید تا دیتابیس موقت را ببینید.

تفسیر خروجی:

متریک	چه چیزی به شما می‌گوید
ثانیه میانگین	توان عملیاتی معمول کوئری زیر این بار
ثانیه حداقل	عملکرد بهترین‌حالت (کمترین رقابت)
ثانیه حداکثر	جهش بدترین‌حالت، اغلب به‌دلیل رقابت قفل یا انتظار I/O
کلاینت‌های در حال اجرا	اتصالات هم‌زمانِ با موفقیت سرو‌شده

گام ۴ — تست با کوئری‌های SQL سفارشی

از کوئری‌های سفارشی استفاده کنید وقتی می‌خواهید بارهای کاری واقعی اپلیکیشن را بنچمارک کنید، نه فقط محدودیت‌های خام سخت‌افزار. این تمایز مهم است؛ چون کوئری‌های خودکار-تولیدشده در برابر جدولی دوستونه بدون JOIN اجرا می‌شوند. کوئری‌های اپلیکیشن شما احتمالاً شامل جداول متعدد، JOINهای پیچیده و بندهای ORDER BY هستند. سروری که در تست‌های خودکار خوب نمره می‌گیرد همچنان می‌تواند روی الگوهای کوئری خاص ضعیف عمل کند. مثال‌های درون‌خطی تک-کوئری و چند-کوئری، به‌علاوه یک روند کار فایل SQL در زیر نشان داده شده‌اند.

کوئری منفرد درون‌خطی:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --create-schema=employees \
  --query="SELECT * FROM dept_emp;" \
  --verbose

خروجی مورد انتظار:

Benchmark
        Average number of seconds to run all queries: 18.486 seconds
        Minimum number of seconds to run all queries: 15.590 seconds
        Maximum number of seconds to run all queries: 28.381 seconds
        Number of clients running queries: 50
        Average number of queries per client: 1

جدول dept_emp بیش از ۳۰۰,۰۰۰ ردیف دارد. با ۵۰ کلاینت هم‌زمان که هر کدام یک اسکن کامل جدول بدون WHERE اجرا می‌کنند، میانگین ۱۸ ثانیه روی یک instance با ۲ گیگابایت انتظار می‌رود. جهش به ۲۸ ثانیه در بدترین اجرا، رقابت منابع (فشار CPU/I/O و Buffer Pool) و صف‌بندی افزایش‌یافته زیر بار را هنگام رقابت کلاینت‌ها برای اسکن هم‌زمان جدول منعکس می‌کند.

کوئری‌های درون‌خطی متعدد با --delimiter:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=20 --iterations=10 \
  --create-schema=employees \
  --query="SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;" \
  --delimiter=";" --verbose

خروجی مورد انتظار:

Benchmark
        Average number of seconds to run all queries: 23.800 seconds
        Minimum number of seconds to run all queries: 22.751 seconds
        Maximum number of seconds to run all queries: 26.788 seconds
        Number of clients running queries: 20
        Average number of queries per client: 3

دقت کنید که «Average number of queries per client» حالا ۳ است؛ یکی برای هر دستور در رشته کوئری. زمان میانگین از ۱۸.۴۸۶ ثانیه (تک کوئری، ۵۰ کلاینت) به ۲۳.۸۰۰ ثانیه افزایش یافته؛ با وجود استفاده از کلاینت‌های کمتر؛ چون هر کلاینت حالا به‌جای یک اسکن، سه اسکن ترتیبی جدول در هر iteration اجرا می‌کند.

فایل SQL (ترجیح داده می‌شود برای کوئری‌های پیچیده):

فایل را بسازید:

cat > ~/mysqlslap_tutorial/select_query.sql << 'EOF'
SELECT * FROM employees;SELECT * FROM titles;SELECT * FROM dept_emp;SELECT * FROM dept_manager;SELECT * FROM departments
EOF

بنچمارک را با فایل اجرا و ۱۰۰۰ کوئری کل را بین ۲۰ کلاینت توزیع کنید (۵۰ به ازای هر کلاینت):

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=20 --number-of-queries=1000 \
  --create-schema=employees \
  --query="$HOME/mysqlslap_tutorial/select_query.sql" \
  --delimiter=";" --verbose --iterations=2 --debug-info

گزینه --number-of-queries تعداد ۱۰۰۰ اجرای کل کوئری را بین ۲۰ کلاینت توزیع می‌کند؛ یعنی هر کلاینت تقریباً ۵۰ کوئری اجرا می‌کند. خروجی نمونه شامل بلوک --debug-info:

Benchmark
        Average number of seconds to run all queries: 217.151 seconds
        Minimum number of seconds to run all queries: 213.368 seconds
        Maximum number of seconds to run all queries: 220.934 seconds
        Number of clients running queries: 20
        Average number of queries per client: 50

User time 58.16, System time 18.31
Maximum resident set size 909008, Integral resident set size 0
Non-physical pagefaults 2353672, Physical pagefaults 0, Swaps 0
Blocks in 0 out 0, Messages in 0 out 0, Signals 0
Voluntary context switches 102785, Involuntary context switches 43

هر فیلد دیباگ را صریحاً تفسیر کنید:

• User time / System time: زمان CPU مصرف‌شده به‌ترتیب در فضای کاربر و کرنل. System time بالا نسبت به User time نشان می‌دهد بار کاری بیشتر I/O-محور است تا محاسباتی.
• Maximum resident set size: حداکثر مصرف حافظه به کیلوبایت. اگر به رم موجود شما نزدیک شود، مجموعه کاری (Working Set) در حافظه جا نمی‌شود و I/O دیسک احتمالاً زمان‌های کوئری شما را متورم می‌کند.
• Non-physical pagefaults: صفحات حافظه‌ای که باید از کش صفحه (نه دیسک) واکشی می‌شدند. تعداد بزرگ اینجا یعنی MySQL داده‌ای را می‌کشد که از قبل در Buffer Pool اینnoDB نبوده.
• Physical pagefaults: صفحات واکشی‌شده از دیسک. هر مقدار غیرصفر اینجا یعنی Buffer Pool برای این بار کاری کوچک است.
• Involuntary context switches: سیستم‌عامل پروسه را پیش‌empt کرده چون پروسه دیگری به زمان CPU نیاز داشته. تعداد بالا اینجا نشان‌دهنده رقابت CPU است؛ چه از پروسه‌های دیگر روی سرور چه از عبور بار کاری از ظرفیت CPU.

نکته: کش کوئری MySQL در 5.7 منسوخ و در 8.0 کاملاً حذف شد. گزینه‌هایی مثل SQL_NO_CACHE فقط روی کش کوئری قدیمی اثر داشتند و جلوی سرو شدن داده از Buffer Pool اینnoDB یا کش صفحه سیستم‌عامل را در MySQL 8.0+ نمی‌گیرند. به‌طور مشابه، innodb_buffer_pool_dump_now و innodb_buffer_pool_load_now برای ذخیره و بازیابی Buffer Pool گرم در طول ری‌استارت‌ها طراحی شده‌اند، نه برای پاک کردنش بین اجراهای بنچمارک. برای بنچمارک‌های واقع‌بینانه حالت-پایدار، معمولاً بهتر است عملکرد را با Buffer Pool گرم‌شده (بعد از چند بار اجرای بار کاری) اندازه‌گیری کنید. اگر واقعاً اندازه‌گیری کش-سرد لازم دارید، باید MySQL را بین اجراها ری‌استارت کنید (مثلاً با sudo systemctl restart mysql) و در صورت تناسب و ایمن بودن برای محیط‌تان، حذف کش صفحه سیستم‌عامل را هم در نظر بگیرید. هر دو رویکرد عملیاتی مخل‌اند و فقط باید روی سیستم‌های غیرپروداکشن استفاده شوند.

گام ۵ — شبیه‌سازی اتصالات هم‌زمان

چند سطح هم‌زمانی را به‌ترتیب تست کنید تا ببینید تأخیر کجا به‌شدت رشد می‌کند (نقطه اشباع). هدف پیدا کردن عددی نیست که سرورتان می‌تواند مدیریت کند؛ هدف پیدا کردن عددی است که عملکرد به‌صورت غیرخطی تنزل می‌یابد. آن آستانه، محدودیت هم‌زمانی عملی شما برای آن کوئری زیر اسکیما و پیکربندی فعلی است. هر کاری که بعد از این نقطه می‌کنید (افزودن ایندکس، تنظیم Buffer Pool، بازنویسی کوئری) باید آن آستانه را بالاتر ببرد. اجرا کنید:

for CONCURRENCY in 10 25 50 100; do
  echo "--- Concurrency: $CONCURRENCY ---"
  mysqlslap --user=benchuser --password --host=localhost \
    --concurrency=$CONCURRENCY --iterations=5 \
    --create-schema=employees \
    --query="SELECT e.first_name, e.last_name, d.dept_name FROM employees e INNER JOIN dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no ORDER BY e.last_name" \
    --verbose 2>&1 | grep -E "Average|Minimum|Maximum|clients"
done

خروجی مقایسه‌ای نمونه (اعداد شما متفاوت خواهند بود):

هم‌زمانی	میانگین (ثانیه)	حداقل (ثانیه)	حداکثر (ثانیه)
10	4.2	3.9	4.8
25	9.1	8.7	10.3
50	22.4	20.1	28.6
100	55.8	50.2	78.1

وقتی زمان میانگین تقریباً خطی با هم‌زمانی رشد می‌کند، مقیاس‌پذیری سازگار است. وقتی در سطح هم‌زمانی مشخصی جهش می‌کند، احتمالاً به گلوگاه خورده‌اید: رقابت قفل، اشباع I/O یا محدودیت‌های Pool اتصال.

گام ۶ — بنچمارک موتورهای ذخیره‌سازی خاص

فلگ --engine به mysqlslap می‌گوید از کدام موتور ذخیره‌سازی برای جدول تست خودکار-تولیدشده استفاده کند. این وقتی مفید است که در حال ارزیابی این هستید که تغییر موتورها برای نوع بار کاری خاصی عملکرد را بهبود می‌دهد یا نه.

اول تست InnoDB را اجرا کنید:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql \
  --engine=InnoDB \
  --verbose

خروجی نمونه:

Benchmark
        Average number of seconds to run all queries: 0.197 seconds
        Minimum number of seconds to run all queries: 0.168 seconds
        Maximum number of seconds to run all queries: 0.399 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

تست MyISAM را با همان پارامترها اجرا کنید:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=50 --iterations=10 \
  --auto-generate-sql \
  --engine=MyISAM \
  --verbose

خروجی نمونه:

Benchmark
        Average number of seconds to run all queries: 0.163 seconds
        Minimum number of seconds to run all queries: 0.130 seconds
        Maximum number of seconds to run all queries: 0.289 seconds
        Number of clients running queries: 50
        Average number of queries per client: 0

MyISAM ممکن است روی بارهای کاری SELECT خالص زمان‌های پایین‌تری از InnoDB نشان دهد؛ چون لاگ‌های تراکنش را نگه نمی‌دارد یا سربار قفل سطح-ردیف را ندارد. اما این تحت نوشتن‌های هم‌زمان به‌طور چشمگیری تغییر می‌کند. MyISAM از قفل سطح-جدول استفاده می‌کند: وقتی یک کلاینت می‌نویسد، همه کلاینت‌های دیگر که همان جدول را کوئری می‌کنند باید منتظر بمانند. InnoDB از قفل سطح-ردیف استفاده می‌کند؛ پس نوشتن‌های هم‌زمان به ردیف‌های مختلف همدیگر را بلاک نمی‌کنند.

نکته: برای بارهای کاری پرخواندن بدون نوشتن هم‌زمان، تفاوت بین موتورها ممکن است کوچک باشد. برای هر بار کاری که خواندن و نوشتن را مخلوط می‌کند، InnoDB در سطوح هم‌زمانی بالاتر به‌طور ثابت بهتر از MyISAM عمل خواهد کرد. MyISAM هنوز در MySQL 8.0 موجود است اما برای اپلیکیشن‌های جدید توصیه نمی‌شود.

گام ۷ — ثبت کوئری‌های زنده و اجرای بنچمارک واقع‌بینانه

این بخش یک روند کار امن-پروداکشن را مرور می‌کند: کوئری‌های واقعی را از سرور پروداکشن ثبت کنید، سپس آن‌ها را در برابر یک نسخه تست دیتابیس بازپخش کنید.

نمای کلی روند کار:

1. از دیتابیس پروداکشن بکاپ بگیرید.
2. بکاپ را به محیط تست بازیابی کنید.
3. لاگ عمومی کوئری را روی سرور پروداکشن فعال کنید.
4. بار کاری موردنظر تست را تریگر کنید.
5. لاگ‌گیری کوئری را غیرفعال کنید.
6. کوئری‌های هدف را از لاگ استخراج کنید.
7. mysqlslap را در برابر دیتابیس تست با کوئری‌های استخراج‌شده اجرا کنید.
8. نتایج را تحلیل و بهینه‌سازی‌ها را اعمال کنید.
9. mysqlslap را دوباره اجرا کنید تا بهبودها تأیید شوند.

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

mysqldump -u benchuser -p employees > ~/mysqlslap_tutorial/employees_backup.sql

دیتابیس تست را بسازید و بکاپ را داخلش بازیابی کنید:

mysql -u benchuser -p -e "CREATE DATABASE employees_backup;"
mysql -u benchuser -p employees_backup < ~/mysqlslap_tutorial/employees_backup.sql

تکمیل بازیابی را تأیید کنید:

USE employees_backup;
SHOW TABLES;
SELECT COUNT(*) FROM employees;

خروجی مورد انتظار:

+----------+
| count(*) |
+----------+
|   300024 |
+----------+

همه دستورات بنچمارک بعدی این بخش در برابر employees_backup اجرا می‌شوند، نه دیتابیس اصلی employees.

روی سرور پروداکشن، لاگ عمومی کوئری را فقط برای پنجره کوتاه ثبت فعال کنید:

SET GLOBAL general_log = 1;
SET GLOBAL general_log_file = '/var/lib/mysql/capture_queries.log';

بار کاری‌ای که می‌خواهید ثبت کنید را اجرا کنید. کوئری پیچیده نمونه:

USE employees;
SELECT SQL_NO_CACHE e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date
FROM employees e
INNER JOIN dept_emp de ON e.emp_no = de.emp_no
INNER JOIN departments d ON de.dept_no = d.dept_no
INNER JOIN titles t ON e.emp_no = t.emp_no
ORDER BY e.first_name, e.last_name, d.dept_name, t.from_date;

لاگ‌گیری را به‌محض پایان بار کاری غیرفعال کنید:

SET GLOBAL general_log = 0;

کوئری را از لاگ استخراج کنید:

sudo grep "SELECT" /var/lib/mysql/capture_queries.log | tail -5

کوئری را در یک فایل .sql ذخیره کنید (بدون سمی‌کالن در انتها، بدون شکستگی خط در کوئری):

cat > ~/mysqlslap_tutorial/captured_query.sql << 'EOF'
SELECT SQL_NO_CACHE e.first_name, e.last_name, d.dept_name, t.title, t.from_date, t.to_date FROM employees e INNER JOIN dept_emp de ON e.emp_no=de.emp_no INNER JOIN departments d ON de.dept_no=d.dept_no INNER JOIN titles t ON e.emp_no=t.emp_no ORDER BY e.first_name, e.last_name, d.dept_name, t.from_date
EOF

بنچمارک را در برابر دیتابیس بکاپ اجرا کنید:

mysqlslap --user=benchuser --password --host=localhost \
  --concurrency=10 --iterations=2 \
  --create-schema=employees_backup \
  --query="$HOME/mysqlslap_tutorial/captured_query.sql" \
  --verbose

خروجی خط پایه نمونه:

Benchmark
        Average number of seconds to run all queries: 68.692 seconds
        Minimum number of seconds to run all queries: 59.301 seconds
        Maximum number of seconds to run all queries: 78.084 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

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

USE employees_backup;
CREATE INDEX employees_empno ON employees(emp_no);
CREATE INDEX dept_emp_empno ON dept_emp(emp_no);
CREATE INDEX titles_empno ON titles(emp_no);

همان دستور mysqlslap را دوباره اجرا کنید. خروجی نمونه بعد از افزودن ایندکس‌ها:

Benchmark
        Average number of seconds to run all queries: 55.869 seconds
        Minimum number of seconds to run all queries: 55.706 seconds
        Maximum number of seconds to run all queries: 56.033 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

کاهش تقریباً ۱۹٪ در زمان میانگین کوئری (از ۶۸.۷ به ۵۵.۹ ثانیه) ارزش ایندکس‌گذاری ستون‌های JOIN با کاردینالیتی بالا را نشان می‌دهد. بهبودها را روی دیتابیس تست قبل از اعمال روی پروداکشن تأیید کنید. افزودن‌های ایندکس بده‌بستان‌های سربار نوشتن دارند که باید زیر بارهای کاری مخلوط خواندن/نوشتن اندازه‌گیری شوند.

گام ۸ — تست در برابر MySQL مدیریت‌شده پارمین کلود

mysqlslap می‌تواند دیتابیس‌های MySQL ریموت و مدیریت‌شده را بنچمارک کند. از --host، --port و برای دیتابیس‌های مدیریت‌شده که TLS لازم دارند، --ssl-ca و --ssl-mode استفاده کنید.

هاست، پورت، کاربر و مسیر گواهی SSL CA را از پنل کنترل پارمین کلود برای دیتابیس مدیریت‌شده‌تان دریافت کنید.

برای دانلود گواهی CA، وارد پنل کنترل پارمین کلود شوید، به بخش دیتابیس‌ها بروید، کلاستر MySQL خود را انتخاب و روی تب «جزئیات اتصال» کلیک کنید. زیر «دانلود گواهی CA»، روی «دانلود گواهی CA» کلیک کنید. فایل را روی سرورتان ذخیره کنید؛ مثلاً در /etc/ssl/certs/do-mysql-ca.crt:

scp ~/Downloads/ca-certificate.crt your_server_user@your_server_ip:/etc/ssl/certs/do-mysql-ca.crt

یا اگر از قبل وارد سرور هستید، محتوای گواهی را مستقیماً روی سرور کپی کنید:

sudo nano /etc/ssl/certs/do-mysql-ca.crt

محتوای گواهی را پیست کنید، ذخیره و خارج شوید. سپس از مسیر کامل در فلگ --ssl-ca استفاده کنید.

سپس اجرا کنید:

mysqlslap --user=doadmin --password \
  --host=your-managed-db-host.db.ondigitalocean.com \
  --port=25060 \
  --ssl-ca=/etc/ssl/certs/do-mysql-ca.crt \
  --ssl-mode=REQUIRED \
  --concurrency=25 --iterations=5 \
  --auto-generate-sql --verbose

your-managed-db-host و مقادیر پورت و کاربر را با جزئیات اتصال واقعی خودتان جایگزین کنید. مسیر --ssl-ca باید با جایی که گواهی را در مراحل بالا ذخیره کردید مطابقت داشته باشد. MySQL مدیریت‌شده پارمین کلود SSL لازم دارد: همیشه --ssl-mode=REQUIRED و یک مسیر --ssl-ca معتبر بگنجانید.

نکته: هنگام بنچمارک دیتابیس‌های مدیریت‌شده، تأخیر شبکه بین کلاینت و instance مدیریت‌شده در زمان‌بندی‌های mysqlslap شامل می‌شود. برای نتایجی که شرایط اپلیکیشن را منعکس می‌کنند، mysqlslap را از یک سرور در همان منطقه و VPC دیتابیس مدیریت‌شده اجرا کنید. اگر اعداد ایزوله-از-تأخیر می‌خواهید، از خارج از شبکه پارمین کلود بنچمارک اجرا نکنید.

تفسیر نتایج mysqlslap و اقدام

از جدول زیر به‌عنوان مرجع سریع برای تفسیر خروجی و تصمیم‌گیری درباره اقدام بعدی استفاده کنید:

فیلد خروجی	معنی	تریگر اقدام
میانگین بالا، شکاف حداکثر بالا	رقابت قفل یا جهش‌های I/O زیر بار	SHOW ENGINE INNODB STATUS را چک کنید، لاگ کوئری کند را مرور کنید
میانگین با هم‌زمانی خطی مقیاس می‌شود	رفتار مورد انتظار، هنوز گلوگاهی نیست	این را به‌عنوان خط پایه‌تان برقرار کنید
میانگین در آستانه هم‌زمانی جهش می‌کند	اشباع Pool اتصال یا CPU	max_connections را تنظیم کنید، Connection Pooling (مثلاً ProxySQL) را در نظر بگیرید
--debug-info خطاهای صفحه بالا نشان می‌دهد (پروسه کلاینت mysqlslap)	کلاینت بنچمارک در حال Page است؛ مستقیماً مصرف Buffer Pool سرور MySQL را منعکس نمی‌کند	متریک‌های سمت سرور Buffer Pool (درخواست‌های خواندن Buffer Pool در مقابل خواندن‌ها، SHOW ENGINE INNODB STATUS) و حافظه OS را چک کنید؛ فقط بعد innodb_buffer_pool_size را تنظیم یا رم اضافه کنید
--debug-info سوییچ‌های زمینه اجباری بالایی نشان می‌دهد (پروسه کلاینت)	کلاینت بنچمارک فشار زمان‌بند را تجربه می‌کند؛ سیگنال فقط سمت کلاینت	از متریک‌های سرور (مصرف CPU، رویدادهای انتظار Performance Schema) و EXPLAIN ANALYZE برای تأیید کوئری‌های CPU-محور قبل از بازنویسی کوئری یا تغییر اسکیما/ایندکس استفاده کنید

مثال کاربردی

فرض کنید حلقه هم‌زمانی گام ۵ را اجرا کردید و این نتایج را گرفتید:

هم‌زمانی	میانگین (ثانیه)	حداقل (ثانیه)	حداکثر (ثانیه)
10	4.2	3.9	4.8
25	9.1	8.7	10.3
50	22.4	20.1	28.6
100	55.8	50.2	78.1

از ۱۰ به ۲۵ کلاینت، زمان میانگین تقریباً دو برابر می‌شود (از ۴.۲ به ۹.۱). این با مقیاس‌پذیری خطی سازگار است: هر کلاینت اضافی بار متناسبی اضافه می‌کند بدون رقابت قابل توجه. از ۲۵ به ۵۰ کلاینت، زمان میانگین به جای ۲، با ضریب ۲.۵ افزایش می‌یابد. این شروع رشد غیرخطی است. از ۵۰ به ۱۰۰ کلاینت، زمان میانگین دوباره با ضریب ۲.۵ جهش و حداکثر به ۷۸.۱ ثانیه در مقابل میانگین ۵۵.۸ می‌رسد؛ شکافی بیش از ۲۲ ثانیه. آن شکاف بین میانگین و حداکثر زیر هم‌زمانی بالا، مهم‌ترین سیگنال است: نشان می‌دهد برخی کلاینت‌ها به‌طور قابل توجهی بیشتر از بقیه منتظر می‌مانند؛ که مشخصه رقابت قفل یا انباشت صف اتصال است.

اقدام اینجا فوراً افزایش سخت‌افزار نیست. اول اجرا کنید:

SHOW ENGINE INNODB STATUS\G

به دنبال بخش TRANSACTIONS بگردید. اگر تراکنش‌هایی در حالت LOCK WAIT می‌بینید، رقابت قفل گلوگاه است. بعد max_connections را چک کنید:

SHOW VARIABLES LIKE 'max_connections';

اگر سقف هم‌زمانی‌تان نزدیک max_connections است، اتصالات در صف هستند یا رد می‌شوند. سپس:

• EXPLAIN ANALYZE را روی کوئری کند اجرا کنید تا به دنبال اسکن کامل جدول بگردید (type: ALL).
• روی ستون‌های بندهای JOIN، WHERE و ORDER BY ایندکس اضافه کنید.
• حلقه mysqlslap را دوباره اجرا کنید تا بهبود تأیید شود.

برای پروفایل عمیق‌تر، راهنماهای «استفاده از پروفایلینگ کوئری MySQL» و «بهینه‌سازی کوئری‌ها و جداول در MySQL و MariaDB» را در پارمین کلود ببینید.

mysqlslap در مقابل ابزارهای بنچمارک دیگر MySQL

ابزار	همراه MySQL	مناسب برای	محدودیت‌ها
mysqlslap	بله	بنچمارک‌های سریع سطح-کوئری، تست هم‌زمانی	بدون بارهای کاری نوشتن-مخلوط، گزارش محدود
sysbench	خیر (نصب جداگانه)	CPU، حافظه، I/O و بارهای کاری کامل OLTP	نیازمند راه‌اندازی؛ منحنی یادابی تندتر
Apache JMeter	خیر	تست بار سطح-اپلیکیشن، چند-پروتکلی	مخصوص MySQL نیست؛ سربار بالاتر
Percona Toolkit (pt-query-digest)	خیر	تحلیل لاگ کوئری کند و اثر انگشت کوئری	ابزار تحلیل، نه مولد بار

از mysqlslap برای بنچمارک‌های سریع و قابل‌اسکریپت سطح-کوئری که در روندهای کاری MySQL موجود جا می‌شوند استفاده کنید. از sysbench وقتی نیاز به شبیه‌سازی کامل OLTP دارید (خواندن، نوشتن و تراکنش‌های مخلوط در مقیاس بزرگ). در CI/CD، CLI ساده mysqlslap ساخت تست‌های رگرسیون خط پایه را ساده می‌کند: مثلاً اگر زمان میانگین کوئری از آستانه‌ای عبور کرد، بیلد را شکست بده.

عیب‌یابی

خروجی نیست یا «Lost connection to MySQL server during query»

سرور ممکن است بیش‌ازحد بارگذاری شده باشد. --concurrency و --iterations را کم کنید و دوباره امتحان کنید. اگر همچنان شکست خورد، از instance بزرگ‌تری استفاده یا حجم بار کاری را کاهش دهید.

خطای 1044: Access denied

کاربر MySQL دسترسی‌های لازم را ندارد. ALL PRIVILEGES را روی اسکیمای تست یا حداقل SELECT، INSERT، CREATE و DROP اعطا کنید.

خطای 2003: Can’t connect

--host و --port را چک کنید. تأیید کنید MySQL در حال شنود است با sudo ss -tlnp | grep 3306.

خطاهای پلاگین احراز هویت MySQL 8.0

تأیید کنید کاربر برای کدام پلاگین احراز هویت پیکربندی شده:

SELECT user, plugin FROM mysql.user WHERE user = 'benchuser';

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

+-----------+-----------------------+
| user      | plugin                |
+-----------+-----------------------+
| benchuser | caching_sha2_password |
+-----------+-----------------------+

اگر ستون plugin مقدار متفاوتی نشان داد، به‌روزرسانی‌اش کنید:

ALTER USER 'benchuser'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'your_password';
FLUSH PRIVILEGES;

برای کلاینت‌های قدیمی که caching_sha2_password را پشتیبانی نمی‌کنند، mysql_native_password هنوز موجود است اما در MySQL 8.4 منسوخ شده و نباید برای راه‌اندازی‌های جدید استفاده شود.

RESET QUERY CACHE شکست می‌خورد

کش کوئری در MySQL 8.0 حذف شد. دستوراتی مانند RESET QUERY CACHE و Hintهایی مانند SQL_NO_CACHE دیگر کاربردی ندارند و اشکال دیگر کشینگ (Buffer Pool اینnoDB یا کش فایل OS) را غیرفعال نمی‌کنند. برای بنچمارک با mysqlslap روی MySQL 8.x:

• برای عملکرد کش-گرم، همان تست را چند بار اجرا و از نتایج اجراهای بعدی وقتی زمان‌ها پایدار شدند استفاده کنید.
• برای عملکرد کش-سرد، MySQL را ری‌استارت کنید (و در صورت نیاز، هاست) یا صبر کنید تا Buffer Pool تخلیه شده، سپس تست را بعد از ری‌استارت یک بار اجرا کنید.
• هنگام مقایسه نتایج، همیشه یادداشت کنید کش گرم بوده یا سرد تا تفاوت‌ها را درست تفسیر کنید.

خطاهای SSL در برابر instanceهای مدیریت‌شده

تأیید کنید --ssl-ca به فایل CA درست اشاره می‌کند و --ssl-mode=REQUIRED تنظیم شده.

سوالات متداول

س: mysqlslap چیست و چه چیزی اندازه‌گیری می‌کند؟

mysqlslap یک ابزار تست بار است که همراه MySQL عرضه می‌شود. بارهای کاری SQL را زیر تعداد قابل‌تنظیمی از کلاینت‌های شبیه‌سازی‌شده اجرا و زمان اجرای میانگین، حداقل و حداکثر و تعداد کلاینت‌ها را گزارش می‌کند. از آن برای اندازه‌گیری توان عملیاتی کوئری و تغییر عملکرد با هم‌زمانی استفاده می‌کنید.

س: آیا mysqlslap در MySQL 8.0 و 8.2 شامل است؟

بله. mysqlslap بخشی از ابزارهای کلاینت MySQL در 8.0 و 8.2 است. روی اوبونتو همراه mysql-server یا mysql-client نصب می‌شود؛ هیچ پکیج جداگانه‌ای لازم نیست.

س: چطور چند سطح هم‌زمانی را با mysqlslap تست کنم؟

حلقه‌ای روی مقادیر مختلف --concurrency بزنید و mysqlslap را برای هر کدام اجرا کنید (گام ۵ را ببینید). ثانیه‌های میانگین، حداقل و حداکثر را بین اجراها مقایسه کنید تا ببینید تأخیر کجا به‌شدت افزایش می‌یابد.

س: تفاوت --iterations و --concurrency در mysqlslap چیست؟

--concurrency تعداد کلاینت‌های شبیه‌سازی‌شده است که هم‌زمان بار کاری را اجرا می‌کنند. --iterations تعداد دفعات تکرار تست کامل (همه کلاینت‌ها در حال اجرای بار کاری) است. Iterationهای بالاتر، قابلیت اطمینان آماری زمان‌های گزارش‌شده را بهبود می‌بخشند.

س: آیا mysqlslap می‌تواند در برابر دیتابیس MySQL ریموت یا مدیریت‌شده تست کند؟

بله. از --host و --port استفاده کنید. برای MySQL مدیریت‌شده (مثلاً پارمین کلود) که SSL لازم دارد، --ssl-ca و --ssl-mode=REQUIRED را اضافه کنید.

س: چطور از فایل کوئری SQL سفارشی با mysqlslap استفاده کنم؟

مسیر فایل را به --query بدهید. برای دستورات متعدد در یک فایل، --delimiter را تنظیم کنید (مثلاً ;). مثال: --query=/path/to/file.sql --delimiter=";".

س: اگر نتایج mysqlslap زمان میانگین کوئری بالای زیر هم‌زمانی نشان بدهند چه کنم؟

EXPLAIN ANALYZE را روی کوئری کند اجرا کنید، به دنبال اسکن کامل جدول و ایندکس‌های مفقود بگردید و روی ستون‌های JOIN/WHERE/ORDER BY ایندکس اضافه کنید. mysqlslap را دوباره اجرا کنید تا تأیید شود. SHOW ENGINE INNODB STATUS و لاگ کوئری کند را برای مسائل قفل یا I/O چک کنید.

س: mysqlslap در مقایسه با sysbench برای تست کارایی MySQL چطور است؟

mysqlslap در MySQL داخلی است و برای تست‌های سریع سطح-کوئری و هم‌زمانی بهترین است. sysbench نصب جداگانه است و برای بارهای کاری کامل OLTP (خواندن و نوشتن مخلوط، تراکنش‌ها) بهتر است. از mysqlslap برای تکرار سریع استفاده کنید؛ از sysbench وقتی شبیه‌سازی واقع‌بینانه OLTP لازم دارید.

س: چرا RESET QUERY CACHE در MySQL 8.0 حذف شد و چه تأثیری روی بنچمارک دارد؟

کش کوئری در MySQL 8.0 حذف شد چون اغلب باعث رقابت و عملکرد غیرقابل پیش‌بینی می‌شد. در 8.0 هیچ کش کوئری‌ای برای پاک کردن وجود ندارد؛ پس RESET QUERY CACHE جایگزینی ندارد و استفاده از SELECT SQL_NO_CACHE دیگر رفتار کشینگ را به‌طور معناداری تغییر نمی‌دهد یا روی Buffer Pool اینnoDB اثری ندارد. واریانس بنچمارک حالا عمدتاً به «گرمی» Buffer Pool اینnoDB و کش فایل‌سیستم OS، به‌علاوه اثرات هم‌زمانی وابسته است. برای نتایج تکرارپذیرتر، یا قبل از زمان‌بندی iterationهای گرم‌کردن اجرا کنید یا صریحاً سرور را ری‌استارت یا کش‌های OS را حذف کنید اگر واقعاً تست کش-سرد لازم دارید.

نتیجه‌گیری

mysqlslap راهی سریع و داخلی برای اندازه‌گیری توان عملیاتی کوئری MySQL و رفتار زیر بار هم‌زمان به شما می‌دهد. از SQL خودکار-تولیدشده برای خطوط پایه سخت‌افزاری و از کوئری‌های سفارشی (یا کوئری‌های ثبت‌شده پروداکشن) برای تنظیم اختصاصی اپلیکیشن استفاده کنید. همیشه بنچمارک را روی یک نسخه غیرپروداکشن داده‌های‌تان اجرا کنید.