Skip to main content

🚀 Tối Ưu MySQL với MySQLTuner

📋 Giới Thiệu​

Việc tối ưu database là một công việc phức tạp và đòi hỏi nhiều kinh nghiệm. Tuy nhiên, với MySQLTuner - một script được viết bằng Perl, chúng ta có thể dễ dàng phân tích và nhận được các đề xuất để cải thiện hiệu suất MySQL một cách an toàn.

MySQLTuner sẽ:

  • Phân tích cài đặt MySQL hiện tại
  • Đánh giá hiệu suất và độ ổn định
  • Đưa ra các đề xuất tối ưu cụ thể
  • Cảnh báo các vấn đề tiềm ẩn

Ví dụ về output của MySQLTuner:

>> MySQLTuner 1.4.0 - Major Hayden <[email protected]>
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering

-------- 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] 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)
tip
  • [OK]: Cấu hình đã tốt
  • [!!]: Cần chú ý và xem xét tối ưu
  • [--]: Thông tin tham khảo

💻 Cài Đặt MySQLTuner​

1. Tải Script​

# Tải MySQLTuner
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl

# Tải file password cơ bản (tùy chọn)
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt

# Tải file CVE (tùy chọn)
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv

2. Phân Quyền​

# Cấp quyền thực thi
chmod +x mysqltuner.pl

# Di chuyển vào thư mục bin (tùy chọn)
sudo mv mysqltuner.pl /usr/local/bin/mysqltuner

3. Cấp Quyền MySQL​

-- Tạo user riêng cho MySQLTuner
CREATE USER 'mysqltuner'@'localhost' IDENTIFIED BY 'strong_password';

-- Cấp quyền cần thiết
GRANT SELECT, PROCESS, EXECUTE, REPLICATION CLIENT, SHOW DATABASES, SHOW VIEW
ON *.* TO 'mysqltuner'@'localhost';

FLUSH PRIVILEGES;

🛠️ Sử Dụng MySQLTuner​

1. Chạy Phân Tích Cơ Bản​

# Sử dụng local socket
perl mysqltuner.pl

# Hoặc chỉ định thông tin đăng nhập
perl mysqltuner.pl --host localhost --user mysqltuner --pass strong_password

2. Phân Tích Chi Tiết​

# Hiển thị thông tin chi tiết
perl mysqltuner.pl --verbose

# Phân tích toàn diện
perl mysqltuner.pl --buffers --dbstat --idxstat --sysstat --pfstat --tbstat

3. Xuất Báo Cáo​

# Xuất ra file
perl mysqltuner.pl --outputfile /tmp/mysql_tuner_results.txt

# Chạy silent mode
perl mysqltuner.pl --silent --outputfile /tmp/mysql_tuner_results.txt

📊 Các Thông Số Quan Trọng​

1. Buffer và Cache​

# File: /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
# InnoDB Buffer Pool (70-80% RAM cho dedicated server)
innodb_buffer_pool_size = 4G

# Số instance buffer pool (buffer_pool_size/1G)
innodb_buffer_pool_instances = 4

# Query Cache (vô hiệu hóa trên MySQL 8+)
query_cache_type = 0
query_cache_size = 0

2. Connection và Thread​

# Giới hạn kết nối
max_connections = 500

# Thread cache
thread_cache_size = 128
thread_stack = 256K

3. InnoDB Settings​

# File per table
innodb_file_per_table = 1

# I/O Settings
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1

# Log file size
innodb_log_file_size = 512M
innodb_log_buffer_size = 16M

📊 Phân Tích Kết Quả MySQLTuner​

Sau khi chạy MySQLTuner, bạn sẽ nhận được báo cáo chi tiết về các khía cạnh sau:

1. Thông Tin Hệ Thống​

  • Phiên bản MySQL
  • Thời gian uptime
  • Tổng số threads và connections
  • Tổng dung lượng RAM và Swap đang sử dụng

2. Các Vấn Đề Bảo Mật​

# Kiểm tra các user không có mật khẩu
SELECT user,host FROM mysql.user WHERE authentication_string='';

# Kiểm tra user có quyền quá rộng
SELECT user,host FROM mysql.user WHERE super_priv='Y';

3. Storage Engine​

-- Kiểm tra storage engine đang sử dụng
SELECT table_schema, table_name, engine 
FROM information_schema.tables 
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema');

4. Phân Tích Buffer và Cache​

# Điều chỉnh InnoDB Buffer Pool
innodb_buffer_pool_size = RAM * 0.7  # 70% của RAM

# Điều chỉnh số instance
innodb_buffer_pool_instances = {buffer_pool_size_in_gb}

5. Kiểm Tra Slow Queries​

-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Phân tích slow queries
SELECT * FROM mysql.slow_log ORDER BY start_time DESC LIMIT 10;

🛠️ Thực Hiện Các Khuyến Nghị​

1. Backup Trước Khi Thay Đổi​

# Backup cấu hình hiện tại
cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup

# Backup databases
mysqldump --all-databases > full_backup_$(date +%F).sql

2. Phân Loại Khuyến Nghị​

a. Vấn Đề Nghiêm Trọng (Ưu Tiên Cao)​

  • Security recommendations
  • Performance issues affecting stability
  • Memory allocation problems

b. Vấn Đề Hiệu Suất (Ưu Tiên Trung Bình)​

  • Buffer/cache configurations
  • Index optimizations
  • Query optimizations

c. Vấn Đề Tối Ưu (Ưu Tiên Thấp)​

  • Minor configuration adjustments
  • Optional features
  • Nice-to-have optimizations

3. Quy Trình Thực Hiện​

  1. Kiểm Tra Môi Trường
# Kiểm tra resource usage
vmstat 1 5
iostat -x 1 5
free -m
  1. Thay Đổi Từng Phần
# Ví dụ: Tăng buffer pool size
mysql -e "SET GLOBAL innodb_buffer_pool_size = 4294967296;"

# Kiểm tra thay đổi
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"
  1. Theo Dõi Sau Thay Đổi
# Kiểm tra error log
tail -f /var/log/mysql/error.log

# Kiểm tra performance
mysqladmin extended-status -i 10

4. Monitoring Dài Hạn​

# Tạo script monitoring đơn giản
cat > mysql_monitor.sh << 'EOF'
#!/bin/bash
while true; do
  mysql -e "SHOW GLOBAL STATUS LIKE '%Questions%';"
  mysql -e "SHOW GLOBAL STATUS LIKE '%Threads_connected%';"
  mysql -e "SHOW GLOBAL STATUS LIKE '%Innodb_buffer_pool_read_requests%';"
  sleep 60
done
EOF
chmod +x mysql_monitor.sh

⚠️ Các Lưu Ý Quan Trọng​

  1. Không Thay Đổi Đồng Thời
  • Thực hiện từng thay đổi một
  • Theo dõi ảnh hưởng ít nhất 24h
  • Ghi chép lại mọi thay đổi
  1. Kiểm Tra Tương Thích
  • Phiên bản MySQL
  • OS và resource limits
  • Application requirements
  1. Plan Rollback
  • Lưu cấu hình cũ
  • Chuẩn bị lệnh rollback
  • Test rollback procedure

📚 Tài Liệu Tham Khảo​