ProxySQL 를 이용한 failover 예상 아키텍쳐
전체 아키텍쳐
HAProxy 를 이용한 read / write split 및 failover
proxysql 아키텍쳐
3개의 layer 는 모두 동일한 설정을 가진다. 상호 데이터를 이동할 수 있으며 쿼리 형태로 제공된다.
- RUNTIME
- 운영중에 사용되는 설정들 proxysql이 기동중일때 사용되는 layer
- MEMORY
- 기동시 disk 로 부터 초기화되는 layer 로 운영 중 변경 내용이 저장되는 layer 다
- runtime 으로 옮겨야 운영에 적용되며 disk 에 저장해야 영구적용된다.
- DISK
- persistent 를 위해 저장용도로만 사용된다.
- CONFIG
- config 파일에 설정을 기록하여 proxysql 기동시에 적용할 수 있다.
proxysql threads
main thread
core 및 기타 modure 을 시작하는 역할
admin thread
- admin interface
- config 파일 및 데이터베이스 파일 초기 로딩
- http server handle
- cluster module
- start admin listener
mysql workers
mysql client 로 부터의 traffic 에 대한 worker thread
mysql auxiliray threads
- idle thread
- client 가 매우 많지만 idle 상태인 경우가 많다면 해당 client 의 connection 을 관리하는 thread
http server
- bug 로 firefox 에서만 접속 가능
- status
- statistics
cluster threads
- decentralized proxy
- 수백대의 proxysql 서버를 간단하게 재설정할 수 있다.
- 기능보다는 관리적 측면
- application 에 근접하게 설치할 것
- 아직 미완성
- 아래 4가지 component 에 대해서 monitoring, re-configuration 기능을 제공
- mysql_query_rules
- mysql_servers
- mysql_users
- proxysql_servers
- 앞으로 지속적인 업데이트 예정
query cache purge thread
query cache garbage collector
clickhouse server thread
ClickHouse → Open source OLAP 데이터베이스
sqlite3 server thread
sqlite3 support
monitor threads
- a main thread responsible to start a thread for each monitor category (5 in total)
- a thread scheduling connection checks
- a thread scheduling ping checks
- a thread scheduling read-only checks
- a thread scheduling replication lag checks
- a thread scheduling group replication monitoring
- a threads pool (initially twice the size of mysql-threads)
Admin> select * from global_variables where variable_name like '%monitor_threads%';
+-------------------------------------+----------------+
| variable_name | variable_value |
+-------------------------------------+----------------+
| mysql-monitor_threads_min | 8 |
| mysql-monitor_threads_max | 128 |
| mysql-monitor_threads_queue_maxsize | 128 |
+-------------------------------------+----------------+
3 rows in set (0.01 sec)
Admin>ProxySQL 특징(주요기능)
Query Cache
querey cache 를 위해서는 DB 의 자체 cache 혹은 별도의 application 이 필요하지만, 변경없이 proxysql 을 통해 쿼리 캐싱을 할 수 있다.
다음은 sysbench 로 테스트 한 내용
$ sysbench --num-threads=16 --max-requests=0 --max-time=60 --test=oltp
--mysql-user=msandbox --mysql-password=msandbox --mysql-db=sbtest --mysql-host=127.0.0.1 --mysql-port=6033
--oltp-table-size=10000 --oltp-read-only=on --db-ps-mode=disable --oltp-skip-trx=on
--oltp-point-selects=100 --oltp-simple-ranges=1 --oltp-sum-ranges=1 --oltp-order-ranges=1
--oltp-distinct-ranges=1 run
# 결과
read/write requests: 380952 (6341.71 per sec.)
# 캐싱을 등록
Admin> INSERT INTO mysql_query_rules (rule_id,active,digest,cache_ttl,apply)
VALUES (5,1,'0xE8930CB2CC9E68D7',2000,1);
Query OK, 1 row affected (0.00 sec)
# 다시 실행 했을때의 결과
read/write requests: 1613248 (26873.58 per sec.)https://proxysql.com/documentation/Query-Cache/
Query Logging
- proxysql 을 통해 실행되는 쿼리를 로깅할 수 있다.
- 사용자별
- 쿼리 패턴별
- start, end, duration time, rows_affected, rows_sent
- json format
Mirroring
- pattern 기반의 쿼리를 mirroring 해서 쿼리는 두번 실행한다.
- hostgroup 단위로 두번 실행(original group, mirror group)
- 같은 쿼리를 실행할 수 있다.
- pattern 을 기반으로 쿼리를 rewrite 할 수 있다.
- pattern 을 기반으로 쿼리를 discard 할 수 있다.
Scheduler
- cron 과 유사한 형태
- milisecond 단위
- 관련 테이블
- scheduler ( in MEMORY )
- runtime_scheduler ( in RUNTIME )
- 테이블 구조
Admin> SHOW CREATE TABLE scheduler\G
*************************** 1. row ***************************
table: scheduler
Create Table: CREATE TABLE scheduler (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
active INT CHECK (active IN (0,1)) NOT NULL DEFAULT 1,
interval_ms INTEGER CHECK (interval_ms>=100 AND interval_ms<=100000000) NOT NULL,
filename VARCHAR NOT NULL,
arg1 VARCHAR,
arg2 VARCHAR,
arg3 VARCHAR,
arg4 VARCHAR,
arg5 VARCHAR,
comment VARCHAR NOT NULL DEFAULT '')
1 row in set (0.00 sec)ProxySQL Cluster
상기 proxysql threads → cluster threads 설명과 동일
HTTP Web UI Statistic Graphs
상기 proxysql threads → http server 설명과 동일
REST API
restapi endpoint 기능을 제공한다.
-- 설정
Admin> select * from runtime_global_variables where variable_name like '%rest%';
+-----------------------+----------------+
| variable_name | variable_value |
+-----------------------+----------------+
| admin-restapi_enabled | true |
| admin-restapi_port | 6070 |
+-----------------------+----------------+
2 rows in set (0.01 sec)
Admin>python 기반의 스크립트가 있다고 가정
-- rest api 를 등록한다.
-- ./sciprts/export_user.py 가 python 스크립트
insert into restapi_routes (active, interval_ms, method, uri, script, comment)
values (1,1000,'POST','export_users','./scripts/export_users.py','comm');호출했을때의 결과
-- rest api 를 등록한다.
-- ./sciprts/export_user.py 가 python 스크립트
insert into restapi_routes (active, interval_ms, method, uri, script, comment)
values (1,1000,'POST','export_users','./scripts/export_users.py','comm');ProxySQL 를 이용한 Failover
Failover 과정
- master 노드 장애 발생
- 장애 발생시 수초~수분까지 시간이 소요될 수 있다.
- 새로운 master 선정
- mha 에 의해 master 가 lag 이 가장 적은 slave 를 master 로 선정한다.
- 새로운 master 를 기준으로 데이터 sync
- 구 master 노드는 복구 후 slave 로 편입
- dba 에 의해 read_only 가 선행되고 새로운 master 를 통해 데이터를 sync 한다.
- 단 proxysql 에서는 sync 가 되지 않아도 접속이 가능하면 read hostgroup 에 등록 되므로 일관성이 위배된다.
- (slave 편입 이전에 mysql_servers 에서 해당 서버를 삭제하고 sync 이후에 편입해야 한다. )
ProxySQL 에서 제공하는 옵션
- mysql_servers
- proxysql 에 mysql 서버를 등록할 수 있다.
- hostgroup 개념이 있으며 read/write 로 호스트그룹을 분류하여 등록
-- 모든 mysql 서버는 read / write hostgroup 에 등록된다.
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('10.1.5.11',600,3306,1000,0);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('10.1.5.11',601,3306,1000,10);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('10.1.5.12',600,3306,1000,0);
INSERT INTO mysql_servers (hostname,hostgroup_id,port,weight,max_replication_lag) VALUES ('10.1.5.12',601,3306,1000,10);- mysql_replication_hostgroups
- read hostgrroup, write hostgroup 을 지정
- read / write 구분 방법을 지전한다. ( read_only, super read_only, innodb read_only )
-- 600 : write hostgroup
-- 601 : read hostgroup
INSERT INTO mysql_replication_hostgroups VALUES (600,601,'read_only','test comment');- mysql_users
- 사용자 관리
-- user : test
-- pass : test
-- default_schema : test
-- default_hostgroup : 600
-- transaction_persistent : 1 (yes)
INSERT INTO mysql_users (username,password,active,default_hostgroup,default_schema,transaction_persistent) VALUES ('test','test',1,600,'test',1);- mysql_query_rules
- 쿼리의 패턴을 이용해 read / write 그룹에 분배할 규칙을 정한다.
-- dml, ddl 등은 기본적으로 write hostgroup 으로 전송
-- "^SELECT.*FOR UPDATE" : select for update clause 는 write hostgroup 에 전달한다.
-- "^SELECT" : select caluse 는 read hostgroup 에 전달 한다.
insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('test',600,1,3,'^SELECT.*FOR UPDATE');
insert into mysql_query_rules (username,destination_hostgroup,active,retries,match_digest) values('test',601,1,3,'^SELECT');Master 와 slave 정상노드 조건
조건
| master | global variable read_only = off |
| slave | global variable read_ony = on
slave 의 thread 상태 확인 안함 |
slave 의 정상노드 조건에서 thread 상태가 빠져 있는 부분에 대해 max_replication_lag 로 갈음 할 수 있다.
- io thread
- master 로그로 부터 relay 로그를 쌓는 thread
- sql trehad
- relay 로그에서 테이블로 반영하는 thread
두 thread 가 정상 동작하지 않으면 max_replication_lag 값이 증가 하므로 max_replication_lag 만으로도
정상상태를 판단 할 수 있다.
환경
Client
- 호스트 정보
- 10.1.5.13
- 테스트 스크립트
- 1초에 주기로 haproxy 를 통해 mysql server_id 를 조회하여 master 의 상태를 조회한다.
#!/bin/bash
while true
do
mysql --port=6033 -e "start transaction;insert into t1(i2) values( 1 ); select now(6), max(i1) , @@server_id from t1;commit;"
sleep 1
donePerconaServer
mysql> show create table t1;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t1 | CREATE TABLE `t1` (
`i1` int NOT NULL AUTO_INCREMENT,
`i2` int DEFAULT NULL,
PRIMARY KEY (`i1`)
) ENGINE=InnoDB AUTO_INCREMENT=5230 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ProxySQL
- 호스트 정보
- 10.1.5.13
- version
- ProxySQL version 2.0.10-27-g5b31997, codename Truls
- conf
- 상기 "ProxySQL 에서 제공하는 옵션" 참고
수행 및 검증
MASTER DB 강제종료
실행
[root@tdb01 ~]# systemctl stop mysqld확인
master db
[root@tdb01 ~]# ps -ef | grep mysqld
root 10489 10054 0 14:00 pts/0 00:00:00 grep --color=auto mysqldprosysql log
# 서버 다운 감지
2020-06-01 13:59:36 MySQL_HostGroups_Manager.cpp:2887:replication_lag_action(): [WARNING] Shunning server 10.1.5.12:3306 from HG 601 with replication lag of 60 second
# 재연결 실패
2020-06-01 13:59:37 MySQL_Monitor.cpp:982:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 10.1.5.11:3306 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on '10.1.5.11' (115).
# read_only 체크 실패
2020-06-01 13:59:37 MySQL_Monitor.cpp:1138:monitor_read_only_thread(): [ERROR] Server 10.1.5.11:3306 missed 3 read_only checks. Assuming read_only=1
# 재연결 실패
2020-06-01 13:59:38 MySQL_Monitor.cpp:982:monitor_read_only_thread(): [ERROR] Timeout on read_only check for 10.1.5.11:3306 after 0ms. Unable to create a connection. If the server is overload, increase mysql-monitor_connect_timeout. Error: timeout on creating new connection: Can't connect to MySQL server on '10.1.5.11' (115).
# slave 로 간주
2020-06-01 13:59:38 MySQL_Monitor.cpp:1138:monitor_read_only_thread(): [ERROR] Server 10.1.5.11:3306 missed 3 read_only checks. Assuming read_only=1prosysql table
-- master 셧다운 전
-- write 그룹(600)에 11번호스트가 등록되어 있다.
Admin> select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 601 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 600 | 10.1.5.11 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.11 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)
-- master 셧다운 후
-- write 그룹(600)에 11번호스트가 제거되어 있다.
Admin> select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 601 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.11 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
2 rows in set (0.00 sec)test.sh
+----------------------------+---------+-------------+
| now(6) | max(i1) | @@server_id |
+----------------------------+---------+-------------+
| 2020-06-01 14:10:45.031846 | 128 | 1 |
+----------------------------+---------+-------------+
+----------------------------+---------+-------------+
| now(6) | max(i1) | @@server_id |
+----------------------------+---------+-------------+
| 2020-06-01 14:10:46.045739 | 129 | 1 |
+----------------------------+---------+-------------+
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 600 after 10000ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 600 after 10000ms
ERROR 9001 (HY000) at line 1: Max connect timeout reached while reaching hostgroup 600 after 10000msslave db
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Reconnecting after a failed master event read
Master_Host: 10.1.5.11
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 4683
Relay_Log_File: tdb02-relay-bin.000007
Relay_Log_Pos: 4857
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Connecting
Slave_SQL_Running: Yes
... ...
Seconds_Behind_Master: NULL
... ...새로운 MASTER 선정
실행
slave db
-- 새로운 master 로 승격
ysql> set global read_only =off;
Query OK, 0 rows affected (0.00 sec)
-- slave 기능 중지
mysql> stop slave;
Query OK, 0 rows affected (0.01 sec)
-- slave 역할 삭제
mysql> stop slave;reset slave all;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.02 sec)확인
slave db
mysql> show variables like 'read_only';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only | OFF |
+---------------+-------+
1 row in set (0.01 sec)
mysql> show slave status \G;
Empty set (0.00 sec)
ERROR:
No query specified
mysql>proxysql log
2020-06-01 14:13:05 [INFO] Changing status for server 601:10.1.5.12:3306 (10.1.5.12:3306) from 4 (4) to 0
2020-06-01 14:13:05 [INFO] Changing status for server 601:10.1.5.11:3306 (10.1.5.11:3306) from 1 (1) to 0
2020-06-01 14:13:05 [INFO] Creating new server in HG 600 : 10.1.5.12:3306 , gtid_port=0, weight=1000, status=0
2020-06-01 14:13:05 [INFO] New mysql_replication_hostgroups table
writer_hostgroup: 600 , reader_hostgroup: 601, check_type read_only, comment: test comment
2020-06-01 14:13:05 [INFO] New mysql_group_replication_hostgroups table
2020-06-01 14:13:05 [INFO] New mysql_galera_hostgroups table
2020-06-01 14:13:05 [INFO] New mysql_aws_aurora_hostgroups table
2020-06-01 14:13:05 [INFO] Dumping current MySQL Servers structures for hostgroup ALL
HID: 601 , address: 10.1.5.12 , port: 3306 , gtid_port: 0 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 601 , address: 10.1.5.11 , port: 3306 , gtid_port: 0 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment:
HID: 600 , address: 10.1.5.12 , port: 3306 , gtid_port: 0 , weight: 1000 , status: ONLINE , max_connections: 1000 , max_replication_lag: 10 , use_ssl: 0 , max_latency_ms: 0 , comment:
2020-06-01 14:13:05 [INFO] Dumping mysql_servers: ALL
+-----+-----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| hid | hostname | port | gtid | weight | status | cmp | max_conns | max_lag | ssl | max_lat | comment | mem_pointer |
+-----+-----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+
| 601 | 10.1.5.12 | 3306 | 0 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | | 140141630268576 |
| 600 | 10.1.5.12 | 3306 | 0 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | | 140141576410208 |
| 601 | 10.1.5.11 | 3306 | 0 | 1000 | 0 | 0 | 1000 | 10 | 0 | 0 | | 140141630268256 |
+-----+-----------+------+------+--------+--------+-----+-----------+---------+-----+---------+---------+-----------------+proxysql table
-- write 그룹(600) 에 12번서버가 등록됨
Admin> select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 600 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.11 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)test.sh
+----------------------------+---------+-------------+
| now(6) | max(i1) | @@server_id |
+----------------------------+---------+-------------+
| 2020-06-01 14:13:05.801875 | 130 | 2 |
+----------------------------+---------+-------------+
+----------------------------+---------+-------------+
| now(6) | max(i1) | @@server_id |
+----------------------------+---------+-------------+
| 2020-06-01 14:13:16.212596 | 131 | 2 |
+----------------------------+---------+-------------+구 MASTER DB SLAVE 로 편입
실행
구 master db
mysql> CHANGE MASTER TO
-> MASTER_HOST = '10.1.5.12',
-> MASTER_PORT = 3306,
-> MASTER_USER = 'test',
-> MASTER_PASSWORD = 'test',
-> MASTER_AUTO_POSITION = 1;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)확인
구 master db
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.1.5.12
Master_User: test
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 148602
Relay_Log_File: tdb01-relay-bin.000002
Relay_Log_Pos: 111338
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
... ...
Seconds_Behind_Master: 0
... ...proxysql log
2020-06-01 14:22:56 MySQL_HostGroups_Manager.cpp:2898:replication_lag_action(): [WARNING] Re-enabling server 10.1.5.11:3306 from HG 601 with replication lag of 0 secondproxysql table
Admin> select * from mysql_servers;
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| hostgroup_id | hostname | port | gtid_port | status | weight | compression | max_connections | max_replication_lag | use_ssl | max_latency_ms | comment |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
| 600 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.11 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
| 601 | 10.1.5.12 | 3306 | 0 | ONLINE | 1000 | 0 | 1000 | 10 | 0 | 0 | |
+--------------+-----------+------+-----------+--------+--------+-------------+-----------------+---------------------+---------+----------------+---------+
3 rows in set (0.00 sec)Reference
'Database > MySQL' 카테고리의 다른 글
| SYSBENCH 를 이용하여 RDS MySQL, RDS Aurora, GCP Cloud SQL MySQL OLTP 테스트 2차 (0) | 2019.07.12 |
|---|---|
| SYSBENCH 를 이용하여 RDS MySQL, RDS Aurora, GCP Cloud SQL MySQL OLTP 테스트 (0) | 2019.07.12 |
| keepalived를 이용한 mysql failover (0) | 2019.07.08 |
| NDB DataNode Directory Files (0) | 2019.07.08 |
| MySQL NDB Cluster 설치 (0) | 2019.07.08 |
