Công cụ, thủ thuật và kinh nghiệm tối ưu hóa MySQL trên VPS Linux

Nếu bạn sử dụng 1 VPS Linux mới cài nguyên bản LAMP lên, hẳn bạn sẽ gặp trường hợp VPS của bạn bị treo vì MySQL hoặc Apache gây quá tải RAM. Lỗi này thường được biết đến khi mà bạn chẳng tối ưu hóa các thông số kĩ thuật cho VPS. Từ quan điểm của 1 tay mơ, để VPS chạy tốt, bạn cần biết đến từng thành phần tối ưu hóa, ở đây trong bài viết này là MySQL.

CÔNG CỤ TỐI ƯU HÓA MYSQL

Ở đây chúng ta có 2 lựa chọn, chọn cái nào cũng được.

Lựa chọn 1: MySQL Performance Tuning Primer Script

Cài đặt bằng các dòng lệnh sau:

# cd /usr/local/src/
# wget http://day32.com/MySQL/tuning-primer.sh
# chmod u+x tuning-primer.sh
# ./tuning-primer.sh

Lựa chọn 2 (phổ biến): MySQLTuner

Cài đặt bằng các dòng lệnh sau:

# git clone https://github.com/major/MySQLTuner-perl.git
# cd MySQLTuner-perl
# chmod u+x mysqltuner.pl
# perl mysqltuner.pl

Dòng lệnh cuối cùng sau khi bạn chạy sẽ check thông tin MySQL của bạn và cho ra 1 số khuyến nghị (khuyến khích làm nhưng không phải là làm theo tuốt đâu nhé!). Chẳng hạn như:

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
query_cache_size (>= 8M)
thread_cache_size (start at 4)
table_cache (> 400)
innodb_buffer_pool_size (>= 365M)

TỐI ƯU HÓA THÔNG SỐ MYSQL

Sau khi bạn có 1 số thông tin cấu hình ở trên, bạn cần thực hiện các điều chỉnh. Thông thường các điều chỉnh này cần bạn sửa 1 file cấu hình của MySQL (thường nằm ở /etc/my.cnf) như sau:

Lưu ý: Bạn cần biết sử dụng editor (công cụ sửa file trên SSH). Ở đây mình thấy vi editor khá dễ sử dụng.
Cơ bản và nhanh để biết nhé:
+ Sửa file nào đó thì gõ lệnh vi <đường dẫn tới file>
+ Sửa nội dung file (sau khi vào trong file) thì ấn phím "Insert" trên bàn phím.
+ Lưu lại: ấn nút "Esc" trên bàn phím rồi gõ :wq! (gõ liền 1 cái rồi nhấn Enter thì sẽ lưu và quay trở lại command).

Bây giờ, quay trở lại, ta đang cần tối ưu hóa MySQL đúng không nào. Hãy bắt đầu bằng dòng lệnh sau:

# vi /etc/my.cnf

Nội dung file sẽ hiện ra, chẳng hạn như với 1 VPS 500MB RAM thì đây là khuyến nghị của mình:

[mysqld]
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2

Còn với 1 VPS chỉ vẻn vẹn 200MB RAM, mình thấy cần cấu hình như sau:

[mysqld]
max_connections = 75
max_user_connections = 75
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1000
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2

Sau khi bạn thử thực hiện điều chỉnh thông số cho phù hợp, bạn lưu lại và thực hiện restart MySQL bằng các dòng lệnh sau:

etc/init.d/mysqld restart hoặc service mysqld restart

Cuối cùng, bạn thử dòng lệnhtop -c để kiểm tra xem mức độ ổn định của VPS đã hơn chưa nhé.

LƯU Ý:
1, Hãy thực hiện công việc này lúc VPS ít khách nhất để tránh downtime vì MySQL có thể lỗi.
2, Nếu bạn restart MySQL mà không lên thì remove file my.cnf đi và thực hiện tunning lại, TỪNG DÒNG CẤU HÌNH MỘT.
Để xóa file my.cnf:

# rm -rf /etc/my.cnf

Để đổi tên file my.cnf:

# mv /etc/my.cnf /etc/my.cnf.bak