Tinh chỉnh (tune) một MySQL server thế nào: buffer pool, slow query log và connection?

Question

IT Interview Practice · Accepted Answer

Tinh chỉnh server theo một vòng lặp: **đo, tìm nút thắt, sửa một thứ, đo lại.** Ba khu vực đòn bẩy cao nhất là InnoDB buffer pool, slow query log, và xử lý connection.

## InnoDB buffer pool — núm số 1

Buffer pool cache các page dữ liệu và index trong RAM; khi working set vừa với nó, đọc đạt tốc độ bộ nhớ và disk I/O gần như biến mất. Đặt kích thước khoảng ~50–75% RAM trên server DB chuyên dụng.

```sql
SET GLOBAL innodb_buffer_pool_size = 12 * 1024 * 1024 * 1024;  -- 12 GB

-- Kiểm tra sức khỏe: hit rate nên ~99%+
SHOW ENGINE INNODB STATUS\G
--   Buffer pool hit rate 999 / 1000     <- tốt; gần 1000/1000 = chủ yếu từ RAM
```

## Slow query log — tìm cái cần sửa

```sql
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 0.5;   -- log mọi truy vấn quá 500 ms
-- Rồi tổng hợp bằng pt-query-digest / mysqldumpslow để xếp hạng truy vấn tệ nhất,
-- và EXPLAIN từng cái. Phần lớn cải thiện là do thiếu index, không phải một setting server.
```

## Connection

Mỗi connection tốn bộ nhớ và thread. Đừng đặt `max_connections` cao ngất để che một chỗ rò — một cơn lũ hàng nghìn connection sẽ vắt kiệt server. Hãy dùng **connection pool** trong app (hoặc một proxy như ProxySQL) để giữ một tập nhỏ được tái dùng, và theo dõi `Threads_connected` so với `Threads_running`.

```sql
SHOW GLOBAL STATUS LIKE 'Threads_running';   -- bao nhiêu thread đang thực sự làm việc
SHOW GLOBAL STATUS LIKE 'Max_used_connections';
```

## Thứ tự tấn công

Sửa các truy vấn chậm hàng đầu (index) trước — điều đó thường thắng mọi thay đổi config. Rồi chỉnh đúng kích thước buffer pool. Chỉ sau đó mới tinh chỉnh `max_connections`, `innodb_log_file_size`, và các setting flush. Đổi một biến mỗi lần rồi đo lại.

## Tại sao điều này quan trọng

Phần lớn sự cố "MySQL chậm" là một nhúm truy vấn thiếu index hoặc một buffer pool quá nhỏ để chứa working set — không phải tinh chỉnh cao siêu gì. Một kỹ sư senior với tay tới slow log và hit rate của buffer pool trước tiên, sửa đúng nút thắt thực sự thay vì vặn config một cách mù quáng.

InnoDB buffer pool — núm số 1

Slow query log — tìm cái cần sửa

Connection

Thứ tự tấn công

Tại sao điều này quan trọng

Câu hỏi liên quan