Tăng tốc độ truy vấn dữ liệu SQL với replication [Phần 2]

Đặt vấn đề

Có một vấn đề cần lưu ý khi thiết kế hệ thống, đó là việc truy vấn vào database luôn chiếm một phần chi phí rất lớn trong tổng thời gian xử lý của một request. Vậy tăng tốc độ truy vấn database cũng đồng nghĩa với việc tối ưu hiệu suất của hệ thống. Một ứng dụng có hiệu suất tốt là một ứng dụng mà có khả năng truy xuất dữ liệu một cách nhanh chóng để giảm độ trễ tương tác giữa người dùng và ứng dụng. Có hai hướng chính để để tăng tốc độ truy xuất dữ liệu:

• Tối ưu câu query.
• Tăng phần cứng: scale database theo chiều dọc hoặc theo chiều ngang.

Ở bài trước mình đã hướng dẫn các bạn cài đặt mô hình database replication với MySQL là một phương pháp scale theo chiều ngang. Về cơ bản chúng ta đã có một hệ thống Replication bao gồm một master và một số replica đồng bộ dữ liệu với nhau. Từ đó người dùng có thể chủ động điều hướng request đọc ghi đến các database thích hợp (Ghi ở master, đọc ở slave) để tăng performance so với mô hình single database truyền thống.

Nhưng cách làm này vẫn chưa được thuận tiện trong việc sử dụng. Người dùng cần phải cấu hình ứng dụng, các function đọc ghi tới nhiều database. Điều này khá là phức tạp và có thể gây ảnh hưởng đến các tính năng đang chạy hoặc gặp khó khăn khi mở rộng. Trong bài viết này chúng ta sẽ mở rộng solution lần trước với việc sử dụng ProxySQL để khắc phục nhược điểm vừa nêu.

Nguyên lý thiết kế

ProxySQL hoạt động chủ yếu ở tầng SQL, nó kết hợp với MySQL client để quản lý lưu lượng truy cập từ ứng dụng đến các MySQL server backends. Cụ thể trong bài viết này mình sẽ dùng ProxySQL để chia các MySQL server backends thành các nhóm đọc/ghi dữ liệu, đồng thời định nghĩa các rule đọc/ghi dữ liệu cho các nhóm đó. Từ những rule được gán cho từng nhóm server backends thì MySQL client sẽ điều hướng các câu query được gửi từ ứng dụng đển một trong số các MySQL server backends tương ứng. Tổng quan kiến trúc hệ thống sẽ như sau:

Chuẩn bị trước khi cài đặt:

• Cài đặt sẵn một database MySQL với 2 replica. Phần này các bạn có tự cài theo theo hướng dẫn hoặc có thể sử dụng dịch vụ database của Sunteco để tạo database với replica một cách nhanh chóng. Ở đây mình đã tạo sẵn database MySQL với hai replica bằng dịch vụ của Sunteco và sử dụng IP public của database để cấu hình. Trong thực tế, để đảm bảo hiệu suất và các vấn đề về bảo mật, chúng ta có thể tạo MySQL và MySQL replica cùng giải network với VM cài ProxySQL, lúc đó chúng ta sẽ dùng IP private thay thế.

• Tạo một máy ảo VM với hệ điều hành Ubuntu để cài đặt ProxySQL. Sunteco cũng cung cấp sẵn dịch vụ VM nên mình sẽ tạo máy ảo như dưới đây:

Các bước cài đặt và cấu hình ProxySQL

Bước 1: Cài đặt ProxySQL và MySQL client

Truy cập vào máy chủ VM, thực hiện các câu lệnh sau để cài đặt ProxySQL:

sudo apt-get install -y –no-install-recommends lsb-release wget apt-transport-https ca-certificates gnupg
sudo wget -O – ‘https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/repo_pub_key’ | sudo apt-key add –
sudo echo deb https://repo.proxysql.com/ProxySQL/proxysql-2.5.x/$(lsb_release -sc)/ ./ | sudo tee /etc/apt/sources.list.d/proxysql.list
sudo apt-get update
sudo apt-get install proxysql

Sau khi chạy các câu lệnh cài đặt ProxySQL, bạn cần enable và start service ProxySQL đồng thời kiểm tra lại trạng thái hoạt động:

sudo systemctl enable proxysql
sudo systemctl start proxysql
sudo systemctl status proxysql

Tiếp theo thực hiện các câu lệnh sau để cài đặt MySQL client:

sudo apt-get update
sudo apt-get install mysql-client

Bước 2: Cấu hình MySQL server backends

Đầu tiên tại máy ảo vm chúng ta kết nối tới giao diện ProxySQL admin với câu lệnh:

mysql -u admin -padmin -h 127.0.0.1 -P6032 –prompt ‘ProxySQL Admin> ‘

Trong đó :

• -u là username dùng để kêt nối, mặc định khởi tạo ban đầu là: admin.
• -p là password dùng để kết nối, mặc định ban đầu cũng là: admin.
• -h là địa chỉ IP của ProxySQL instance, ở đây chúng ta thực hiện kết nối trực tiếp tại máy chủ VM nên sẽ là 127.0.0.1 (localhost).
• -P là port kết nối tới ProxySQL admin.

Giao diện terminal ProxySQL administration như sau:

Tiếp đó, chúng ta sẽ thêm các MySQL server backends thông qua các câu lệnh sau:

INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,’10.254.216.153′,3306); INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,’10.254.216.224′,3306);
INSERT INTO mysql_servers(hostgroup_id,hostname,port) VALUES (1,’10.254.216.73′,3306);

Trong đó:

• hostgroup_id dùng để nhóm các mysql node có cùng chức năng logic, ví dụ các MySQL replica sẽ được cấu hình chỉ đọc dữ liệu (readonly=1) thì sẽ được nhóm vào chung với nhau, MySQL master có thể vừa đọc vừa ghi dữ liệu hoặc chỉ ghi dữ liệu ( readonly=0) sẽ được tách thành nhóm riêng. Ở đây đang để mặc định là 1, giá trị này sẽ được ProxySQL cập nhật bằng cách giám sát giá trị readonly của từng MySQL server backend.
• hostname là địa chỉ ip của các MySQL server backends.
• port là cổng để kết nối tới MySQL server backend.

Để kiểm tra lại các MySQL server backends vừa được thêm, thực hiện câu lệnh sau:

SELECT * FROM mysql_servers;

Kết quả:

Bước 3: Cấu hình giám sát cho các MySQL server backends

Việc cấu hình giám sát nhằm để ProxySQL sẽ phân chia các MySQL server backends thành các groups phục vụ cho việc đọc ghi dữ liệu. Đồng thời giám sát các thông số về connection, thời gian query, healthcheck,… và điều hướng các câu query đến các các MySQL servers backend.

Đầu tiên chúng ta cần tạo một tài khoản dùng để thực hiện cho việc giám sát. Truy cập vào MySQL master thông qua câu lệnh sau:

mysql -h 10.254.216.153 -u root -p

Nhập mật khẩu để truy cập vào màn hình termial MySQL master:

Thực hiện câu lệnh sau để tạo và phân quyền cho tài khoản giám sát:

CREATE USER ‘monitor’@’%’ IDENTIFIED BY ‘monitor’;
GRANT USAGE, REPLICATION CLIENT ON *.* TO ‘monitor’@’%’;

Tiếp đó, chúng ta quay lại màn hình terminal ProxySQL administration và update tài khoản giám sát cho ProxySQL:

UPDATE global_variables SET variable_value=’monitor’ WHERE variable_name=’mysql-monitor_username’;
UPDATE global_variables SET variable_value=’monitor’ WHERE variable_name=’mysql-monitor_password’;

Có thể chỉnh sửa thêm một vài thông số về MySQL monitor:

UPDATE global_variables SET variable_value=’2000′ WHERE variable_name IN (‘mysql-monitor_connect_interval’,’mysql-monitor_ping_interval’,’mysql-monitor_read_only_interval’);

Các thông số giám sát sẽ được lưu trong các bảng:

SHOW TABLES FROM monitor;

Để các thống số về giám sát chúng ta vừa cấu hình trên ProxySQL được áp dụng và lưu lại trạng thái ngay cả khi ProxySQL bị khởi động lại, thực hiện các câu lệnh sau:

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

Kiểm tra một vài thông số mà ProxySQL giám sát được:

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 3;

Kết quả:

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 3;

Kết quả:

Sau khi kiểm tra các MySQL server backends được giám sát chính xác và hoạt động tốt. Chúng ta thực hiện câu lệnh sau để áp dụng cấu hình vừa cài đặt cho MySQL server backends:

LOAD MYSQL SERVERS TO RUNTIME;

Kiểm tra lại bằng câu lệnh:

SELECT * FROM mysql_servers;

Kết quả:

Bước 4: Tạo các hostgroups cho các MySQL server backends

ProxySQL sử dụng khái niêm hostgroups để nhóm các máy chủ có cùng chức năng logic. Trong phần cài đặt này chúng ta sẽ tạo 2 hostgroups:

hostgroup 1 dành cho MySQL master sử dụng để ghi dữ liệu vào.

hostgroup 2 dành cho các MySQL slaves sử dụng để đọc dữ liệu ra.

INSERT INTO mysql_replication_hostgroups (writer_hostgroup,reader_hostgroup,comment) VALUES (1,2,’cluster1′);
LOAD MYSQL SERVERS TO RUNTIME;

Sau khi tạo hostgroups, ProxySQL sẽ giám sát giá trị readonly của các MySQL server backends vào đưa vào các hostgroups tương ứng:

readonly = 0 sẽ được nhóm vào writer_hostgroup

readonly = 1 sẽ được nhóm vào reader_hostgroup

Thực hiện các câu lện sau để kiểm tra:

SELECT * FROM monitor.mysql_server_read_only_log ORDER BY time_start_us DESC LIMIT 3;

Kết quả:

SELECT hostgroup_id, hostname, status FROM runtime_mysql_servers;

Kết quả:

Sau kiểm tra các MySQL server backends được nhóm vào các nhóm chính xác, chúng ta cần lưu lại cấu hình để dùng cho các lần khởi động lại tiếp theo:

SAVE MYSQL SERVERS TO DISK;
SAVE MYSQL VARIABLES TO DISK;

Bước 5: Cấu hình MySQL users và query rule

Mục đích để ProxySQL là điều hướng các câu query đến các MySQL server backends. Để làm được điều này chúng ta cần tạo 1 user đóng vai trò làm người điều hướng.

Đầu tiên chúng ta cần truy cập vào MySQL master thực hiện câu lênh sau để tạo và phần quyền cho user:

CREATE USER ‘stnduser’@’%’ IDENTIFIED BY ‘stnduser’;
GRANT ALL PRIVILEGES ON *.* TO ‘stnduser’@’%’;

Đồng bộ user được tạo trên MySQL server về ProxySQL. Truy cập vào terminal ProxySQL admin thực hiện câu lệnh sau:

INSERT INTO mysql_users(username,password,default_hostgroup) VALUES (‘stnduser’,’stnduser’,1);
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

Chú ý: default_hostgroup bằng 1 tức là nếu các câu query không thuộc môt trong các rule được cấu hình thì câu query đó sẽ được điều hướng đến writer_hostgroup.

Tiếp đến chúng ta sẽ cấu hình query rule để điều hướng các câu query đến các hostgoups mong muốn.

Các query rule sẽ được cấu hình trong bảng mysql_query_rules bằng các câu lệnh sau:

INSERT INTO mysql_query_rules (active, match_digest, destination_hostgroup, apply) VALUES (1, ‘^SELECT.*’, 2, 1);
LOAD MYSQL QUERY RULES TO RUNTIME;

Kiểm tra lại các rule được cấu hình:

SELECT rule_id, match_digest,destination_hostgroup hg_id, apply FROM mysql_query_rules WHERE active=1;

Kết quả:

Như cấu hình trên, ProxySQL sẽ kiểm tra các câu query phù hợp với match_digest bắt đầu bằng ‘SELECT’ sẽ được điều hướng đến các MySQL server backends thuộc hostgroups 2 (các MySQL slaves).

Các câu query không phù hợp với match_digest sẽ được điều hướng đến MySQL server backends thuộc default_hostgroup bằng 1 được cấu hình ở trên (MySQL master).

ProxySQL được dùng trên 2 port là 6032 dành cho admin và 6033 là dành cho client.

Để kiểm tra các câu query có được điều hướng chính xác với các rule đã được cài đặt không cũng như các thông số về thời gian query, số lượng query , …các bạn có thể cài sysbench (https://github.com/akopytov/sysbench).

Trong phạm vi bài viết này mình sẽ kiểm tra đơn giản bằng một câu lệnh query ‘SELECT’.

Thoát khỏi giao diện ProxySQL admin và thực hiện câu lệnh sau:

mysql -u stnduser -pstnduser -h 127.0.0.1 -P6033 -e “SELECT @@server_id”

Kết quả sau 3 lần thử:

Chúng ta có thể thấy cùng với một câu query là ‘SELECT @@server_id’ ProxySQL đã điều hướng nó tới các MySQL slaves. Chúng ta có thể kiểm tra server id của các MySQL slaves bằng cách truy cập vào các MySQL salves và thực hiện câu query ‘SELECT @@server_id’ tương tự.

Kết

Vậy là chúng ta đã cài đặt và cấu hình xong ProxySQL như một load balancer điều hướng các câu query đến các MySQL server backends mong muốn. Các bạn có vấn đề gì thắc mắc có thể comment xuống dưới để chúng ta có thể bàn luận thêm.