Tăng performance của SQL Database 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> ‘

• -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.

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);

SELECT * FROM mysql_servers;

mysql -h 10.254.216.153 -u root -p

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

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’;

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

SHOW TABLES FROM monitor;

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

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

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

LOAD MYSQL SERVERS TO RUNTIME;

SELECT * FROM mysql_servers;

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ệnh 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ả là:

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.