MySQL/MariaDB Proxy Software Test

MySQL/MariaDB Proxy Software Test

• 1.
  2016. 9. 30 MySQL/MariaDBProxy Software Test
• 2.
  1 1. 목표 1) Proxy소프트웨어를 사용해도 트랜잭션이 잘 유지되는가? 2) Proxy 소프트웨어를 사용하면 Proxy 소프트웨어를 사용하지 않았을 때와 비교해서 얼마나 성능이 하락하는가? 3) 일부 DB에 장애가 발생해도 서비스가 유지될 수 있는가?
• 3.
  2 2. Proxy 소프트웨어의Read/Write 분산 기능 비교 구분 MaxScale ProxySQL Proxy • Router 기능을 통한 자동 Read/Write 분리 • Replication 상태 파악을 통해 Master/Slave 노드 구분 • 규칙 기반 Query 분리 (규칙은 자유로운 설정 가능) • Replication에 관계없이 hostgroup으로 노드 구분 - Hostgroup으로 서버 등록하고, 용도에 따라 Read/Write 구분 장점 • 기술지원 가능 • 하나의 노드만 있어도 최소 서비스 가능 • 풍부한 모니터링 정보 : 운영중인 환경 정보 확인이 수월 단점 • Master 노드 및 Slave 노드 모두 각각 1개 이상 필요 • 모니터링 정보 부족 - 현재 운영중인 환경에 대한 정확한 정보가 부족함 • 기술지원 없음 특이사항 • 일부 패턴 쿼리는 전체 노드에 Broadcasting 됨 - Client Session 처리 관련 SQL (예 use <db name>, SET autocommit=0, COM_STMT_PREPARE 등) - system/user defined variable을 사용할 때 • 일부 패턴 쿼리는 무조건 Master로 전달됨 - Prepared Statement를 사용할 때 - stored procedure나 User Defined Function Call을 포함할 때 - autocommit=off 설정된 이후, 해당 세션에서 실행되는 모든 쿼리 • start transaction을 이용한 트랜잭션 설정만 가능 • mysql_user의 transaction_persistent을 1로 설정해야만 트랜잭션이 지원됨 • start transaction을 이용한 트랜잭션 설정만 가능 ■ Read/Write 분산 기능 비교
• 4.
  3  MaxScale 1.4.3GA ProxySQL 1.2.3 beta 2. 테스트 환경 ■ 비교대상 ■ 테스트 환경  JDBC Driver를 이용한 Connection Pool 환경에서 테스트하기 위해 sysbench의 oltp 성능측정 모델을 jmeter로 porting하여 테 스트함  일반적인 OLTP 시스템의 조회 비중이 높은 점을 고려하여 Insert/Update/Delete 쿼리 4개, SELECT 쿼리 12개로 설정하여 조회 쿼리 비율을 높여서 수행함 (jmeter의 Random Controller 사용)  Proxy 소프트웨어가 없을 떄와 비교하기 위해서 Write용 데이터소스와 Read용 데이터소스 두가지를 사용함  Proxy 소프트웨어 테스트에서는 Write/Read 데이터소스를 모두 Proxy 서버로 설정함  CPU: OpenStack 4 vCore  Memory: 7823M  CentOS 6.8 , CentOS 7.2  MariaDB 10.0.26  JDBC Driver: MariaDB Connector/J 1.5.2  jmeter 3.0 ■ 테스트 모델
• 5.
  4 3. 테스트 아키텍처 STLOpenStack 환경 하에서 테스트를 위한 인스턴스 구성은 CentOS 6 기반의 DB 4대, 부하발생기(jmeter) 1대, ProxySQL 1대 및 CentOS 7 기반의 MaxScale 1대 서버로 구성하여 테스트를 진행하였다. DB #1 - Master (mhanode1) MariaDB10.0.20 CentOS6 ServerIP : 10.5.0.35 MHANode0.57 DB #2 – CandidateMaster (mhanode2) MariaDB10.0.20 CentOS6 ServerIP : 10.5.0.36 MHANode0.57 DB #3 - Slave (mhanode3) MariaDB10.0.20 CentOS6 ServerIP : 10.5.0.37 MHANode0.57 DB #4 -Slave (mhanode4) MariaDB10.0.20 CentOS6 ServerIP : 10.5.0.38 MHANode0.57 CentOS6 MHAJMET #1 ServerIP : 10.5.0.103 Java 1.7.0 JMeterClient 3.0 MaxScale MAXScale1.4.3 CentOS7 MHAManager0.57 PythonNova SDK Python2.7 ServerIP: 10.5.0.34 ProxySQL ProxySQl1.2.3 CentOS6 ServerIP: 10.5.0.104
• 6.
  5 4. 트랜잭션 테스트와부하 테스트 ■ 트랜잭션 테스트 결과 테스트번호 Proxy 솔루션 테스트 내용 트랜잭션 지원 비고 T-1 MaxScale autocommit=off 설정 후 트랜잭션의 유지 여부 △ 트랜잭션이 시작되면 트랜잭션 종료와 관계없이 이후 모든 쿼리는 Master노드로 전달됨 T-2 MaxScale start transaction 사용시 트랜잭션의 유지 여부 O 트랜잭션이 시작되면 트랜잭션이 종료될 때까지 조회를 포함한 모든 쿼리는 Master노드로 전달됨 T-3 ProxySQL autocommit=off 설정 후 트랜잭션의 유지 여부 X 트랜잭션을 시작시키는 모든 쿼리 요청시 설정과 관계 없이 autocommit=1 로 자동 변경함 - Insert, Update, Delete - use 등 T-4 ProxySQL start transaction 사용시 트랜잭션의 유지 여부 O 트랜잭션이 시작되면 트랜잭션이 종료될 때까지 조회를 포함한 모든 쿼리는 Master노드로 전달됨 ■ 부하 테스트 결과 테스트번호 Proxy 솔루션 QPS Response Time (ms) Baseline 대비 성능 RW-1 - 8,087 1.2 Baseline RW-2 MaxScale 7,061 1.4 87.32% (약 13% 하락) RW-3 ProxySQL 7,063 1.4 87.34%(약 13% 하락) 1.10 1.15 1.20 1.25 1.30 1.35 1.40 6500 7000 7500 8000 8500 JDBC MAXSCALE PROXYSQL QPS elapsed(ms)
• 7.
  6 • autocommit=off 로설정하면 이후 모든 쿼리는 트랜잭션 종료 여부와 관계없이 모두 Master 노드로만 전달됨 • start transaction을 사용했을 때만 트랜잭션이 종료된 후에 조회 쿼리가 Slave 노드로 전달됨 4. 트랜잭션 테스트 결과 상세 - MaxScale 시간 Client에서 수행한 쿼리 Master 노드에서 처리된 내용 Slave 노드에서 처리된 내용 set autocommit=off; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; select * from sysbench.testa; set autocommit=off; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; select * from sysbench.testa; set autocommit=off; start transaction; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; select * from sysbench.testa; start transaction; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; set autocommit=off; select * from sysbench.testa;
• 8.
  7 4. 트랜잭션 테스트결과 상세 - ProxySQL 시간 Client에서 수행한 쿼리 Master 노드에서 처리된 내용 Slave 노드에서 처리된 내용 set autocommit=off; update sysbench.testa set a = ‘09’; select * from sysbench.testa; set autocommit=off; SET autocommit=1; update sysbench.testa set a = ‘09’; select * from sysbench.testa; start transaction; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; select * from sysbench.testa; start transaction; update sysbench.testa set a = ‘09’; select * from sysbench.testa; commit; set autocommit=off; select * from sysbench.testa; • autocommit=off 로 설정해도 ProxySQL은 자동으로 autocommit=on으로 설정을 바꿔버림 • start transaction을 사용하면 트랜잭션은 Master에서 처리되고, 트랜잭션이 종료되고 나면 조회 쿼리는 Slave 노드로 전달됨
• 9.
  8 0.0 10.0 20.0 30.0 40.0 50.0 22:52:09 22:52:19 22:52:29 22:52:39 22:52:49 22:52:59 22:53:09 22:53:19 22:53:29 22:53:39 22:53:49 22:53:59 22:54:09 22:54:19 22:54:29 22:54:39 22:54:49 22:54:59 22:55:09 22:55:19 22:55:29 22:55:39 22:55:49 22:55:59 22:56:09 22:56:19 22:56:29 22:56:39 22:56:49 Master Candiate Slave 1 Slave 2 0.00 0.20 0.40 0.60 0.80 1.00 1.20 1.40 1.60 1.80 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 10000 22:51:59 22:52:13 22:52:27 22:52:41 22:52:55 22:53:09 22:53:23 22:53:37 22:53:51 22:54:05 22:54:19 22:54:33 22:54:47 22:55:01 22:55:15 22:55:29 22:55:43 22:55:57 22:56:11 22:56:25 22:56:39 22:56:53 QPS:8,087, elapsed(ms): 1.22 QPS elapsed(ms) 4. 부하 테스트 결과 상세 – No Proxy 구분 Master DB Slave 1 Slave 2 Connections 20 7 13 SQL Insert 151,317 Update 303,864 Delete 152,379 Select 578,875 1,248,409 합계 607,560 578,875 1,248,409 비중 25.0% 23.8% 51.3% ■ DB별 작업량 ■ 서버별 CPU % 서버 CPU Master 20.0 Candidate 10.9 Slave 1 21.8 Slave 2 36.4 ■ QPS & 평균 실행시간(ms) ※ 테스트 부하 : 10 user ※ JDBC DataSource: Read용 2개(loadbalance), Write용 1개 모든 DataSource의 Pool size는 20 ※ JDBC 설정: AutoCommit=true, useServerPrepStmts=false
• 10.
  9 0.0 5.0 10.0 15.0 20.0 25.0 30.0 23:00:37 23:00:47 23:00:57 23:01:07 23:01:17 23:01:27 23:01:37 23:01:47 23:01:57 23:02:07 23:02:17 23:02:27 23:02:37 23:02:47 23:02:57 23:03:07 23:03:17 23:03:27 23:03:37 23:03:47 23:03:57 23:04:07 23:04:17 23:04:27 23:04:37 23:04:47 23:04:57 23:05:07 23:05:17 Master Candidate Slave 1 Slave 2 MaxScale 0.00 0.50 1.00 1.50 2.00 2.50 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 23:00:27 23:00:41 23:00:55 23:01:09 23:01:23 23:01:37 23:01:51 23:02:05 23:02:19 23:02:33 23:02:47 23:03:01 23:03:15 23:03:29 23:03:43 23:03:57 23:04:11 23:04:25 23:04:39 23:04:53 23:05:07 23:05:21 QPS:7,061, elapsed(ms): 1.37 QPS elapsed(ms) ■ 서버별 CPU % 서버 CPU MaxScale 20.2 Master 18.6 Candidate 10.9 Slave 1 25.6 Slave 2 24.8 구분 Master DB Slave 1 Slave 2 Connections 41 41 41 SQL Insert 133,040 Update 265,548 Delete 132,326 Select 869,227 726,044 합계 530,914 869,227 726,044 비중 25.0% 40.9% 34.1% ■ DB별 작업량■ QPS & 평균 실행시간(ms) ※ 테스트 부하 : 10 user ※ JDBC DataSource: Read용 하나(loadbalance), Write용 하나 모든 DataSource의 Pool size는 20 ※ JDBC 설정: AutoCommit=true, useServerPrepStmts=false 4. 부하 테스트 결과 상세 – MaxScale
• 11.
  10 0.0 5.0 10.0 15.0 20.0 25.0 30.0 23:09:26 23:09:36 23:09:46 23:09:56 23:10:06 23:10:16 23:10:26 23:10:36 23:10:46 23:10:56 23:11:06 23:11:16 23:11:26 23:11:37 23:11:47 23:11:57 23:12:07 23:12:17 23:12:27 23:12:37 23:12:47 23:12:57 23:13:07 23:13:17 23:13:27 23:13:37 23:13:47 23:13:57 23:14:07 Master Candidate Slave 1 Slave 2 ProxySQL 0.00 0.50 1.00 1.50 2.00 2.50 0 1000 2000 3000 4000 5000 6000 7000 8000 9000 23:09:16 23:09:30 23:09:44 23:09:58 23:10:12 23:10:26 23:10:40 23:10:54 23:11:08 23:11:22 23:11:36 23:11:50 23:12:04 23:12:18 23:12:32 23:12:46 23:13:00 23:13:14 23:13:28 23:13:42 23:13:56 23:14:10 QPS:7,061, elapsed(ms): 1.37 QPS elapsed(ms) ■ 서버별 CPU % 서버 CPU ProxySQL 15.6 Master 18.2 Candidate 11.0 Slave 1 24.4 Slave 2 25.4 구분 Master DB Slave 1 Slave 2 Connections 11 9 9 SQL Insert 132,661 Update 265,844 Delete 132,405 Select 806,094 789,742 합계 530,910 806,094 789,742 비중 25.0% 37.9% 37.1% ■ DB별 작업량■ QPS & 평균 실행시간(ms) ※ 테스트 부하 : 10 user ※ JDBC DataSource: Read용 하나(loadbalance), Write용 하나 모든 DataSource의 Pool size는 20 ※ JDBC 설정: AutoCommit=true, useServerPrepStmts=false 4. 부하테스트 결과 상세 – ProxySQL
• 12.
  11 5. Backend Server장애 테스트 결과 - MaxScale ■ 테스트결과 테스트번호 Proxy 솔루션 Master Slave1 Slave2 확인되는 DB 상태 Connections F-1 MaxScale Running Running Down Server |...| Status ---------+---+----------- MasterDB |...| Master, Running Slave1DB |...| Slave, Running Slave2DB |...| Down • 기존 Connection : 유지 Client는 장애 상황을 알 수 없지만, 내부적으로는 다운된 Slave대신 살아있는 Slave로 새로 연결됨 • 신규 Connection : 허용 남은 노드로 자동 연결됨 F-2 MaxScale Running Down Down Server |...| Status ---------+---+----------- MasterDB |...| Running Slave1DB |...| Down Slave2DB |...| Down • 기존 Connection : 연결 끊김 ERROR 2013 (HY000): Lost connection to MySQL server during query • 신규 Connection : 불가 ERROR 2006 (HY000): MySQL server has gone away F-3 MaxScale Down Running Running Server |...| Status ---------+---+----------- MasterDB |...| Down Slave1DB |...| Running Slave2DB |...| Running • 기존 Connection : 연결 끊김 ERROR 2013 (HY000): Lost connection to MySQL server during query • 신규 Connection : 불가 ERROR 2006 (HY000): MySQL server has gone away
• 13.
  12 5. Backend Server장애 테스트 결과 - ProxySQL ■ 테스트 결과 테스트번호 Proxy 솔루션 Master Slave1 Slave2 확인되는 DB 상태 Connections F-4 ProxySQL Running Running Down hostgroup | hostname | status ----------+-------------+------- 0 | M_ReadVIP | ONLINE 1 | M_WrriteVIP | ONLINE 1 | Slave2DB | ONLINE 1 | Slave1DB | SHUNNED • 기존 Connection: 유지 • 신규 Connection : 허용 남은 노드로 자동 연결됨 F-5 ProxySQL Running Down Down hostgroup | hostname | status ----------+-------------+-------- 0 | M_ReadVIP | ONLINE 1 | M_WrriteVIP | ONLINE 1 | Slave2DB | SHUNNED 1 | Slave1DB | SHUNNED • 기존 Connection: 유지 • 신규 Connection : 허용 남은 노드로 자동 연결됨 F-6 ProxySQL Down Running Running hostgroup | hostname | status ----------+-------------+-------- 0 | M_ReadVIP | SHUNNED 1 | M_WrriteVIP | SHUNNED 1 | Slave2DB | ONLINE 1 | Slave1DB | ONLINE • 기존 Connection: 유지, 일부 쿼리만 실행가능 SELECT 쿼리는 Slave에서 정상 처리되지만, 다른 쿼리를 실행하면 에러 발생 ERROR 2013 (HY000): Lost connection to MySQL server during query • 신규 Connection : 허용, SELECT 쿼리만 실행가능
• 14.
  13 6. 기타 -편의성 비교  운영 중인 서비스 환경 정보 확인 ■ ProxySQL■ MaxScale 확인 안 됨 $ mysql -u admin -padmin -h 127.0.0.1 -P6032 mysql> SELECT * FROM main.global_variables WHERE variable_name LIKE 'mysql-shun%'; +------------------------------+----------------+ | variable_name | variable_value | +------------------------------+----------------+ | mysql-shun_on_failures | 5 | | mysql-shun_recovery_time_sec | 10 | +------------------------------+----------------+ 2 ROWS IN SET (0.01 sec)  Admin 도구 maxadmin mysql cli  서비스 상세 $ maxadmin -uadmin -pmariadb MaxScale> show service "Read-Write Service" Service 0x1932610 Service: Read-Write Service Router: readwritesplit State: Started Number of router sessions: 0 Current no. of router sessions: 0 Number of queries forwarded: 0 Number of queries forwarded to master: 0 Number of queries forwarded to slave: 0 Number of queries forwarded to all: 0 Master/Slave percentage: 0.00% Started: Mon Oct 10 15:59:08 2016 Root user access: Disabled Filter chain: NamedServerFilter Backend databases host03:3306 Protocol: MySQLBackend host02:3306 Protocol: MySQLBackend host01:3306 Protocol: MySQLBackend host00:3306 Protocol: MySQLBackend Users data: 0x1a091b0 Total connections: 1 Currently connected: 1 ■ 실행중인 서비스정보 (main스키마의 runtime 테이블) runtime_mysql_servers : 현재 사용중인 호스트그룹과 Backend 서버 정보 runtime_mysql_users : 현재 사용중인 DB 유저 정보 runtime_mysql_replication_hostgroups : 현재 사용중인 replica tion_hostgroups 정보 runtime_mysql_query_rules : 현재 사용중인 쿼리 규칙 정보 runtime_global_variables : 현재 사용중인 전역변수 정보 runtime_scheduler ■ 집계 테이블 (stats 스키마) stats_mysql_connection_pool : 각 서버로 전달되는 트래픽 집계 stats_mysql_commands_counters : 쿼리 종류(SELECT, INSERT, U PDATE 등) 별 실행시간과 횟수 집계 stats_mysql_query_digest : 쿼리 문장별 실행시간과 횟수 집계 stats_mysql_commands_counters 보다 자세한 쿼리문으로 집계됨
• 15.
  14 7. Proxy 소프트웨어검토 결과 1. 두 솔루션 모두 부하 분산과 트랜잭션을 지원하며, Proxy 솔루션이 없는 환경과 비교하면 두 솔루션 모두 처리 성능이 약 13% 하락함 2. MaxScale과 ProxySQL가 서비스를 하는 동안, 해당 서비스를 하는 서버의 자원 사용량은 일정하게 유지됨 - 동일 부하일 때, MaxScale의 서버 사용량이 ProxySQL보다 높음(약5%) 3. MaxScale과 ProxySQL 모두 Backend Server로 부하를 거의 균등하게 분산함 4. MaxScale과 ProxySQL 모두 트랜잭션 관리를 위해서는 start transaction을 사용해야 함 - autocommit=off 로 설정하면 MaxScale은 트랜잭션 완료 후에도 Master 노드만 사용하고, ProxySQL은 모든 트랜잭션을 autocommit=on으로 설정을 강제로 변경함 5. MaxScale은 Master와 Slave가 각각 최소 하나 이상 필요(기존 접속 에러 처리, 신규 접속 불가)하고, ProxySQL은 하나의 노드만 있어도 서비스가 가능함 - Master노드만 있을 때: Read/Write 가능, Slave노드만 있을 때: Read만 가능 6. 운영 중 DB Node의 추가/삭제는 ProxySQL에서만 가능하며, MaxScale은 서비스 중지 후 재시작해야 함 7. MaxScale 1.4.3GA의 경우, 모니터링 정보가 매우 부족함 - 서비스 중에 설정값을 확인할 수 있는 방법이 없음 : MaxScale 2.0이 아니어서 그럴 수도 있음 8. Read/Write 분산용으로만 사용한다면 (나라면…) MaxScale 보다는 ProxySQL을 사용하겠음
• 16.
  15 MaxScale 추가 기능별첨 • MaxScale은 모니터링하면서 특정 이벤트가 감지될 때 사용자 정의 스크립트를 호출할 수 있는데, 이 때 Master Node Switch를 하는 스크립트를 등록해서 처리할 수 있음 • 이 기능을 사용하면 MHA 프로세스를 따로 가동하지 않고, MaxScale에서 MHA 관리까지 가능하게 됨 MaxScale의 모니터링 기능을 이용하여 MHA를 직접 제어할 수 있음 [MASTER Monitor] type=monitor module=mysqlmon servers=server1,server2,server3,server4 user=maxsclusr passwd=A23796A073859CEAEBA8DD1E9BB4E45D monitor_interval=1000 script=/mha4mysql/scripts/mha4maxscale_monitor event=$EVEN T events=master_down, server_up ■ config #!/bin/bash echo $1 PARAM=`echo $1 | awk -F'=' '{print $1}'` VALUE=`echo $1 | awk -F'=' '{print $2}'` echo $PARAM echo $VALUE case "$VALUE" in 'master_down') echo "Master Server is down" DEAD_MASTER=`maxadmin -pmariadb --port=4040 "list servers" | grep -i down | aw k '{print $3}'` /usr/bin/masterha_master_switch --master_state=dead --conf=/mha4mysql/conf/app1.cnf --dead_master_host=$DEAD_MASTER --interactive=0 rm -f /mha4mysql/app1.failover.complete ;; 'server_up') echo "Slave Server is up" sh /mha4mysql/scripts/mha4maxscale_serverup ;; esac ■ mha4maxscale_monitor