ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기

MySQL, MariaDB

ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기

SungWookKang 2023. 7. 23. 22:38

ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기

l Version : ProxySQL, MySQL 8.X

ProxySQL은 MySQL 및 포크된 데이터베이스(Percona Server 및 MariaDB)를 위한 고성능, 고가용성을 제공하는 프록시이다.

l ProxySQL이란 무엇인가 : https://sungwookkang.com/1528

ProxySQL을 구성할 때 MySQL 서버 환경이 복제 구성이 되어 있으면 Write, Read를 효과적으로 분산하여, ProxySQL장점을 조금 더 효율적으로 활용할 수 있다. MySQL설치, 복제구성, ProxySQL 설치는 아래 링크를 참고한다.

l ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

이번 포스트에서는 ProxySQL + MySQL Replication 환경에서, 쿼리 타입(INSET UPDATE, DELET, SELECT)에 따라 Write, Read를 분기하여 호출하는 방법에 대해서 알아본다. 실습에 사용된 서버 및 환경은 아래와 같다.

Server Name	IP	OS	Service Version
proxy-sql	172.30.1.49	Ubuntu 22.04.2 LTS	ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master	172.30.1.97	Ubuntu 22.04.2 LTS	mysql Ver 8.0.33
mysql-slave1	172.30.1.10	Ubuntu 22.04.2 LTS	mysql Ver 8.0.33
mysql-slave2	172.30.1.13	Ubuntu 22.04.2 LTS	mysql Ver 8.0.33

이전의 글 (https://sungwookkang.com/1529)에서 ProxySQL을 구성할 때 서버를 등록하는 과정에서, 모든 서버를 동일한 그룹으로 지정하였다. 쿼리 타입에 따라 각기 다른 서버로 호출하기 위해서는 서버 특성에 따라 그룹 할당이 필요 하다. 이번 포스트에서는 1대의 Write서버와 2대의 Read서버로 구성하는 그룹으로 구성한다. proxy-sql 서버에서 아래 스크립트를 사용하여 데이터베이스에 접속한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

proxy-sql 데이터베이스에 접근이 되었으면, 아래 스크립트를 사용하여, write및 read 서버를 등록한다. 이때 필요한 그룹을 지정할 수 있도록 한다.

l Write : hostgroup_id = 1

l Read : hostgroup_id = 2

--기존 서버 목록 삭제
DELETE FROM mysql_servers;

--master (write) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.97', 3306);

--slave (read) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.13', 3306);

--추가된 서버 확인
select * from mysql_servers;

-- 변경사항 반영
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

사용자 기본 호스트 그룹을 지정한다. hostgroup_id = 1을 기본으로 지정하였다.

-- hostgroup_id를 기본 1로 지정
UPDATE mysql_users SET default_hostgroup = 1;

-- 변경사항 반영
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

쿼리 타입에 따라 각 그룹으로 분기할 룰을 지정한다. SELECT쿼리는 hostgroup_id = 2의 서버로 호출하고, 나머지 쿼리는 hostgroup_id = 1 서버로 호출한다. 이때 동일 호스트 그룹에 2대 이상의 서버가 있으면 ProxySQL에서 라운드로빈으로 로드밸런싱을 한다.

-- 기존 쿼리 룰 삭제
DELETE FROM mysql_query_rules;

-- SELECT로 시작하는 쿼리는 hostgroup=2(slave)로 라우팅
INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);

-- 변경사항 반영
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

쿼리 룰 설정이 완료되었으면, 쿼리를 실행하고 정상적으로 분기되는지 확인이 필요하다. 그 전에 각 서버에 general_log를 활성화하여, 서버로 호출되는 쿼리를 기록할 수 있도록 한다. 아래 스크립트는 로그를 테이블 및 파일에 동시에 기록한다.

mysql-master	mysql-slave1	mysql-slave2
set global log_output = 'TABLE,FILE'; set global general_log = on; show variables like '%general%'; show variables LIKE '%log_output%';

모든 설정이 완료되었으면, 클라이언트 도구(ex : MySQL Workbench)를 사용하여, proxy-sql 서버에 접속한 다음, INSERT 및 SELECT 쿼리를 호출 한다. 여러번 호출하여 라운드로빈이 발생하였을 때 각 서버로 유입 될 수 있도록 한다.

proxy-sql

#proxy-sql
select * from testdb.tbl_a;

insert into testdb.tbl_a (name) values ('insert test for proxy5');

select * from testdb.tbl_a;

mysql-master 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-master

#mysql-mster
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

mysql-slave1 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-slave1

#mysql-slave1
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

mysql-slave2 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-slave2

#mysql-slave2
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

지금까지 ProxySQL에서 쿼리 타입에 따른 룰을 지정하여, Write 및 Read를 분기하는 방법에 대해서 알아보았다. 실제 현업에 사용할 때에는 로그 기록에 따른 오버헤드가 발생할 수 있으므로 사용자 환경에 따라 제네럴로그 기록을 OFF 또는 적절히 조절할 수 있도록 한다. 대용량 트래픽 환경에서 Write 및 Read 쿼리를 분기 및 분산함으로써 DB 서버의 오버헤드를 분산하여 유용하게 활용할 수 있으리라 생각한다.

2023-07-23 / Sungwook Kang / http://sungwookkang.com

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치, 쿼리룰설정

저작자표시 비영리 변경금지