Monitoring و Performance Tuning

۱۴.۱ مقدمه: قانون «اول اندازه بگیر»

قبل از هر تغییر تنظیمات، باید بدانید مشکل کجاست. تنظیمات کور (مثل بالا بردن max_connections بدون دلیل) معمولاً باعث مشکلات جدیدتر می‌شوند.

در این فصل ابزارهای اندازه‌گیری و تنظیم را با هم می‌بینیم.

۱۴.۲ Performance Schema

یک storage engine ویژه برای جمع‌آوری متریک‌های runtime. از MySQL 5.7 پیش‌فرض فعال است.

SQL📋 کپی
-- بررسی فعال بودن
SHOW VARIABLES LIKE 'performance_schema';

-- لیست instrument‌ها
SELECT * FROM performance_schema.setup_instruments LIMIT 10;

-- فعال/غیرفعال instrument خاص
UPDATE performance_schema.setup_instruments
SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'wait/io/file%';

کوئری‌های مفید Performance Schema

SQL📋 کپی
-- پرتکرارترین Query‌ها (Top 10)
SELECT
    DIGEST_TEXT,
    COUNT_STAR AS execs,
    ROUND(AVG_TIMER_WAIT/1000000000, 2) AS avg_ms,
    ROUND(SUM_TIMER_WAIT/1000000000, 2) AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;

-- Query‌هایی که Full Scan می‌کنند
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    SUM_NO_INDEX_USED,
    SUM_ROWS_EXAMINED
FROM performance_schema.events_statements_summary_by_digest
WHERE SUM_NO_INDEX_USED > 0
ORDER BY SUM_NO_INDEX_USED DESC
LIMIT 10;

-- جدول‌های با بیشترین I/O
SELECT
    OBJECT_SCHEMA, OBJECT_NAME,
    COUNT_READ, COUNT_WRITE,
    SUM_TIMER_WAIT/1000000000 AS total_ms
FROM performance_schema.table_io_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC LIMIT 10;

۱۴.۳ sys schema – رابط آسان‌تر

sys schema روی Performance Schema ساخته شده و View‌های آماده دارد:

SQL📋 کپی
-- جدول‌هایی که full scan می‌خورند
SELECT * FROM sys.statements_with_full_table_scans LIMIT 10;

-- Query‌های کند
SELECT * FROM sys.statements_with_runtimes_in_95th_percentile LIMIT 10;

-- ایندکس‌های بلااستفاده
SELECT * FROM sys.schema_unused_indexes;

-- آمار ایندکس‌ها
SELECT * FROM sys.schema_index_statistics
WHERE table_schema = 'shop_db'
ORDER BY rows_selected DESC;

-- جدول‌های بدون PK
SELECT * FROM sys.schema_tables_with_full_table_scans;

-- اتصالات per-user
SELECT * FROM sys.user_summary;

-- وضعیت حافظه
SELECT * FROM sys.memory_global_total;
SELECT * FROM sys.memory_by_user_by_current_bytes LIMIT 10;

۱۴.۴ Status Variables – متریک‌های لحظه‌ای

SQL📋 کپی
-- اتصالات
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW STATUS LIKE 'Max_used_connections';
SHOW STATUS LIKE 'Aborted_connects';

-- Buffer Pool
SHOW STATUS LIKE 'Innodb_buffer_pool%';
-- مهم‌ترین:
-- Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads = hit ratio
-- نزدیک ۱۰۰٪ خوب، زیر ۹۹٪ نگران‌کننده

-- Query rate
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Com_select';
SHOW GLOBAL STATUS LIKE 'Com_insert';
SHOW GLOBAL STATUS LIKE 'Com_update';
SHOW GLOBAL STATUS LIKE 'Com_delete';

-- Sort و Tmp
SHOW STATUS LIKE 'Sort_merge_passes';   -- اگر بالا، sort_buffer_size کم است
SHOW STATUS LIKE 'Created_tmp_disk_tables';  -- اگر بالا، tmp_table_size کم
SHOW STATUS LIKE 'Created_tmp_tables';

-- Slow Query
SHOW STATUS LIKE 'Slow_queries';

-- Replication (روی replica)
SHOW STATUS LIKE 'Slave_running';

۱۴.۵ MySQLTuner – تحلیلگر خودکار

یک اسکریپت Perl که وضعیت MySQL را بررسی و توصیه می‌دهد:

Bash📋 کپی
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl
chmod +x mysqltuner.pl
./mysqltuner.pl

# یا از package
sudo apt install mysqltuner
mysqltuner

# نمونه خروجی:
# [OK] Maximum reached memory usage: 850.0M (10.4% of installed RAM)
# [!!] InnoDB buffer pool / data size: 1.0G/3.5G
# [!!] Ratio InnoDB buffer pool reads = 92.3% (should be > 95%)
#
# Recommendations:
#   Increase innodb_buffer_pool_size to 4G
#   Adjust your join queries to always use indexes

۱۴.۶ Percona Toolkit

مجموعه‌ای از CLI tool‌های قدرتمند برای DBAها:

Bash📋 کپی
sudo apt install percona-toolkit -y

# pt-summary: خلاصه سیستم
pt-summary

# pt-mysql-summary: خلاصه MySQL
pt-mysql-summary

# pt-query-digest: تحلیل slow log
pt-query-digest /var/log/mysql/slow.log

# pt-online-schema-change: ALTER بدون lock
pt-online-schema-change 
    --alter "ADD COLUMN phone VARCHAR(15)" 
    D=shop_db,t=users 
    --execute

# pt-archiver: حذف داده قدیمی به‌صورت batch
pt-archiver 
    --source D=shop_db,t=logs 
    --where "created_at < NOW() - INTERVAL 90 DAY" 
    --purge --limit=1000 --commit-each

# pt-stalk: ضبط متریک هنگام مشکل
pt-stalk --threshold 50 --variable Threads_running

# pt-deadlock-logger: مانیتور deadlock‌ها
pt-deadlock-logger h=localhost --create-dest-table --dest D=monitor,t=deadlocks

۱۴.۷ Prometheus + Grafana

برای monitoring بصری و alerting، استک Prometheus + Grafana استاندارد است:

MySQL Server
    │
    ├── mysqld_exporter ───┐
    │                       ▼
    │                  Prometheus ──► Grafana ──► Dashboard
    │                       │
    └── node_exporter ──────┘
                            │
                            └──► Alertmanager ──► Email/Telegram
    

Bash📋 کپی
# نصب mysqld_exporter
wget https://github.com/prometheus/mysqld_exporter/releases/download/v0.15.0/mysqld_exporter-0.15.0.linux-amd64.tar.gz
tar xvf mysqld_exporter-*.tar.gz
sudo mv mysqld_exporter /usr/local/bin/

# ساخت کاربر MySQL برای exporter
mysql -e "
CREATE USER 'exporter'@'localhost' IDENTIFIED BY 'StrongPass!' WITH MAX_USER_CONNECTIONS 3;
GRANT PROCESS, REPLICATION CLIENT, SELECT ON *.* TO 'exporter'@'localhost';
"

# /etc/.exporter.cnf
[client]
user=exporter
password=StrongPass!

# اجرا
mysqld_exporter --config.my-cnf=/etc/.exporter.cnf

# Prometheus job
- job_name: mysql
  static_configs:
    - targets: ['localhost:9104']

Dashboards آماده

در grafana.com/dashboards این dashboard‌های آماده برای MySQL پیشنهاد می‌شوند:

• MySQL Overview (#7362)
• MySQL InnoDB Metrics (#12630)
• MySQL Replication (#7371)

۱۴.۸ تنظیم Buffer Pool - مهم‌ترین parameter

SQL📋 کپی
-- اندازه فعلی
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

-- محاسبه hit ratio
SELECT
    ROUND(
        (1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
              WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads')
           / (SELECT VARIABLE_VALUE FROM performance_schema.global_status
              WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests')
        ) * 100, 2
    ) AS buffer_pool_hit_ratio;
-- هدف: > 99.0%

-- چقدر buffer pool استفاده شده
SELECT
    ROUND((1 - (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_free')
            / (SELECT VARIABLE_VALUE FROM performance_schema.global_status
                WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total')
          ) * 100, 2) AS pct_used;

اندازه‌گیری مناسب

• اگر pct_used = 100% و hit ratio < 99%: buffer pool کم است → بزرگ‌تر کنید
• اگر pct_used < 50%: شاید بیش از حد است (RAM اضافه)
• افزایش از MySQL 5.7+ بدون restart امکان دارد:

  SET GLOBAL innodb_buffer_pool_size = 8589934592;  -- 8G

۱۴.۹ تنظیمات کلیدی my.cnf برای production

my.cnf - Production tuned📋 کپی
[mysqld]
# === InnoDB Memory ===
innodb_buffer_pool_size = 12G                # 60-75% of RAM
innodb_buffer_pool_instances = 8             # 1 per GB of buffer pool
innodb_log_file_size = 2G                    # بزرگ = sync کمتر
innodb_log_buffer_size = 64M

# === InnoDB I/O ===
innodb_flush_log_at_trx_commit = 1           # 1=ACID, 2=performance
innodb_flush_method = O_DIRECT               # دور زدن OS cache
innodb_io_capacity = 2000                    # for SSD
innodb_io_capacity_max = 4000
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# === Connections ===
max_connections = 500
thread_cache_size = 50
table_open_cache = 8000
table_definition_cache = 4000

# === Query Cache (در MySQL 8 حذف شده) ===
# اگر MariaDB:
query_cache_type = 0
query_cache_size = 0

# === Sort & Join Buffers (per-connection) ===
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
tmp_table_size = 64M
max_heap_table_size = 64M

# === Slow Log ===
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1.0
log_queries_not_using_indexes = 0  # فقط در debug

# === Logging ===
log_error = /var/log/mysql/error.log
log_error_verbosity = 2

# === Performance Schema (مهم برای monitoring) ===
performance_schema = ON

۱۴.۱۰ Alert‌های ضروری

۱۴.۱۱ خلاصه فصل

• قبل از تنظیم، اندازه بگیرید (Performance Schema، sys schema)
• MySQLTuner و Percona Toolkit ابزارهای حرفه‌ای DBA
• Buffer Pool مهم‌ترین parameter است؛ hit ratio بالای ۹۹٪
• Slow Query Log + pt-query-digest = طلا
• Prometheus + Grafana = استاندارد monitoring
• Alert روی متریک‌های کلیدی = جلوگیری از مشکل قبل از مراجعه کاربر