Hướng dẫn cấu hình ProxySQL cho MySQL: Read/Write Splitting, Connection Pooling và Query Caching - ITFROMZERO - Share tobe shared! ProxySQL đóng vai trò cầu nối thông minh giữa ứng dụng và cụm database MySQL. Hướng dẫn chi tiết cách triển khai Read/Write Splitting trong suốt, tối ưu Connection Pooling và bật Query Caching trên RAM mà không cần can thiệp source code.

Table of Contents

Bối cảnh & Tại sao cần ProxySQL?

Khi lưu lượng truy cập tăng vọt, tầng database thường là điểm gãy đầu tiên. Giải pháp phổ biến nhất là thiết lập cụm MySQL Replication: một node Master nhận lệnh ghi và 2-3 node Slave gánh tải đọc.

Mô hình này tạo ra bài toán hóc búa cho ứng dụng. Làm sao app tự phân biệt query nào đẩy sang Master, query nào chuyển cho Slave? Nếu bắt đội ngũ developer refactor toàn bộ Repository trong source code, dự án sẽ tốn hàng tuần và đối mặt rủi ro hồi quy (regression) rất lớn.

Chưa dừng lại ở đó, áp lực kết nối cũng là điểm nghẽn nghiêm trọng. Trong mỗi đợt flash sale, hàng nghìn web worker đồng loạt kết nối tới MySQL. Mỗi connection ngốn khoảng 2MB đến 4MB RAM. Database nhanh chóng chạm trần max_connections, gây nghẽn hàng đợi (thread thrashing) và làm sập toàn bộ dịch vụ.

ProxySQL giải quyết triệt để vấn đề này. Đóng vai trò là một reverse proxy thông minh ở tầng Layer 7, ProxySQL mang lại ba giá trị then chốt:

Read/Write Splitting trong suốt: Ứng dụng chỉ kết nối tới một endpoint ProxySQL duy nhất. Hệ thống tự bóc tách cú pháp SQL: câu SELECT chuyển sang Slave, các lệnh INSERT/UPDATE/DELETE gom về Master.
Connection Pooling hiệu năng cao: ProxySQL dồn 5.000 kết nối từ client vào một pool chỉ khoảng 50-100 kết nối thực tới backend MySQL. Nhờ đó, bạn tiết kiệm hàng gigabyte RAM và triệt tiêu độ trễ handshake TCP/TLS liên tục.
Query Caching trên RAM: Kết quả của các truy vấn đọc lặp lại được lưu thẳng vào bộ nhớ của ProxySQL theo thời gian TTL tùy chỉnh. Tốc độ phản hồi đạt dưới 1ms mà không cần chạm xuống ổ đĩa MySQL.

Cần đặc biệt lưu ý vấn đề replication lag. MySQL sao chép bất đồng bộ (asynchronous). Người dùng vừa tạo đơn hàng xong tải lại trang có thể thấy đơn biến mất nếu query đọc trúng Slave bị trễ 0.5 giây. Hãy cấu hình ProxySQL theo dõi replication lag chặt chẽ hoặc giữ các transaction đọc-ghi trên Master.

Cài đặt ProxySQL

ProxySQL hỗ trợ hầu hết các bản phân phối Linux phổ biến. Dưới đây là các bước cài đặt chuẩn hóa trên Ubuntu 20.04/22.04 LTS.

# Cài đặt gói công cụ phụ trợ
sudo apt-get update && sudo apt-get install -y wget gnupg lsb-release

# Thêm GPG key và repository chính thức của ProxySQL
wget -qO - https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key | gpg --dearmor | sudo tee /usr/share/keyrings/proxysql-keyring.gpg > /dev/null
echo "deb [signed-by=/usr/share/keyrings/proxysql-keyring.gpg] https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./" | sudo tee /etc/apt/sources.list.d/proxysql.list

# Cài đặt và kích hoạt dịch vụ
sudo apt-get update && sudo apt-get install -y proxysql
sudo systemctl enable --now proxysql
sudo systemctl status proxysql

Sau khi khởi chạy thành công, ProxySQL lắng nghe trên hai port mặc định:

Port 6032: Cổng Admin Interface để quản trị bằng cú pháp SQL (tài khoản mặc định: admin/admin).
Port 6033: Cổng tiếp nhận truy vấn trực tiếp từ ứng dụng (thay thế port 3306 truyền thống).

Cấu hình chi tiết

Kiến trúc ProxySQL vận hành qua 3 tầng lưu trữ: MEMORY (chỉnh sửa runtime), RUNTIME (đang phục vụ traffic) và DISK (lưu bền vững vào file SQLite /var/lib/proxysql/proxysql.db). Quy trình chuẩn là sửa cấu hình ở MEMORY, tải sang RUNTIME rồi lưu vĩnh viễn xuống DISK.

1. Khai báo Backend Servers

Đăng nhập vào giao diện quản trị thông qua MySQL client:

mysql -u admin -padmin -h 127.0.0.1 -P 6032 --prompt='ProxySQLAdmin> '

Giả sử hạ tầng gồm Master 192.168.1.10 và Slave 192.168.1.11. Chúng ta chia thành hai nhóm: Hostgroup 10 cho Write (Master) và Hostgroup 20 cho Read (Slave).

-- Đăng ký Master vào Hostgroup 10 (giới hạn 200 connection)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight)
VALUES (10, '192.168.1.10', 3306, 200, 1000);

-- Đăng ký Slave vào Hostgroup 20 (giới hạn 500 connection)
INSERT INTO mysql_servers (hostgroup_id, hostname, port, max_connections, weight)
VALUES (20, '192.168.1.11', 3306, 500, 1000);

-- Kích hoạt cấu hình sang Runtime và lưu xuống Disk
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

2. Khai báo User kết nối

ProxySQL cần xác thực thông tin user từ client trước khi ủy quyền xuống backend MySQL. Hãy đảm bảo tài khoản app_user đã tồn tại với cùng mật khẩu trên cả hai node database.

-- Định tuyến mặc định của user này trỏ về Hostgroup 10 (Master)
INSERT INTO mysql_users (username, password, default_hostgroup, active)
VALUES ('app_user', 'MatKhauSieuKho123@', 10, 1);

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

3. Cấu hình Read/Write Splitting

Chúng ta phân luồng truy vấn bằng Regular Expression (Regex) qua bảng mysql_query_rules. Quy tắc có số thứ tự nhỏ hơn sẽ được đánh giá trước:

DELETE FROM mysql_query_rules;

-- Rule 1: Giữ câu SELECT ... FOR UPDATE trên Master để tránh khóa sai node
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (1, 1, '(?i)^SELECT.*FOR UPDATE', 10, 1);

-- Rule 2: Đẩy mọi câu SELECT thông thường sang Slave
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, apply)
VALUES (2, 1, '(?i)^SELECT', 20, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Cờ (?i) giúp regex không phân biệt chữ hoa, chữ thường. Tham số apply = 1 ra lệnh dừng kiểm tra các rule tiếp theo ngay khi tìm thấy khớp lệnh. Nhờ đó, câu lệnh FOR UPDATE được chặn lại ở Rule 1 và chuyển thẳng về Master.

4. Tối ưu Connection Pooling

Cơ chế pooling của ProxySQL giảm thiểu việc mở connection liên tục tới backend. Bạn có thể tối ưu tỷ lệ connection nhàn rỗi và vòng đời kết nối bằng hai biến toàn cục sau:

-- Duy trì tối thiểu 30% kết nối rảnh trong pool sẵn sàng phục vụ spike traffic
SET mysql-free_connections_pct = 30;

-- Tái tạo kết nối sau 30 phút (1800000ms) để tránh rò rỉ bộ nhớ backend
SET mysql-connection_max_age_ms = 1800000;

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

5. Bật Query Caching ngay tại ProxySQL

Với dữ liệu ít biến động như danh mục hay thiết lập hệ thống, cache trên RAM của ProxySQL giúp giảm đáng kể tải CPU cho Slave. Thời gian cache tính bằng mili-giây qua tham số cache_ttl:

-- Cache kết quả truy vấn categories trong 10 giây (10000ms)
INSERT INTO mysql_query_rules (rule_id, active, match_pattern, destination_hostgroup, cache_ttl, apply)
VALUES (3, 1, '(?i)^SELECT.*FROM\s+categories', 20, 10000, 1);

LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

Với các bảng danh mục đạt 2.000 QPS, thiết lập này triệt tiêu tới 95% số query xuống backend. Database Slave gần như ở trạng thái nghỉ.

Kiểm tra & Monitoring

Chuyển database host trong cấu hình ứng dụng trỏ tới port 6033 của ProxySQL. Bạn có thể kiểm tra định tuyến bằng dòng lệnh:

# Chạy SELECT để kiểm tra định tuyến sang Slave
mysql -u app_user -pMatKhauSieuKho123@ -h 127.0.0.1 -P 6033 -e "SELECT @@hostname;"

# Chạy INSERT để kiểm tra ghi vào Master
mysql -u app_user -pMatKhauSieuKho123@ -h 127.0.0.1 -P 6033 -e "INSERT INTO my_db.logs (message) VALUES ('test_proxysql');"

Theo dõi số liệu vận hành trong database stats

ProxySQL lưu trữ metric theo thời gian thực trong database stats. Ba bảng quan trọng nhất cần giám sát định kỳ gồm:

-- 1. Kiểm tra trạng thái connection pool và số query trên từng node
SELECT hostgroup, srv_host, Queries, ConnUsed, ConnFree, ConnOK 
FROM stats.stats_mysql_connection_pool;

-- 2. Kiểm tra số lần match của từng routing rule
SELECT rule_id, hits FROM stats.stats_mysql_query_rules;

-- 3. Kiểm tra hiệu quả bộ nhớ Query Cache
SELECT * FROM stats.stats_mysql_query_cache;

Nếu ConnFree luôn giữ mức an toàn và chỉ số hits của query cache tăng đều, hệ thống đã sẵn sàng chịu tải mượt mà qua các đợt cao điểm.