[MySQL - mysql-proxy] part 2. Read/Write Split & 부하테스트

Master 서버로는 Read & Write 작업을하고 Slave 서버로는 Read 작업을하여 select 쿼리에 대한 부하분산 구성을 하고 sysbench를 이용해 부하테스트를 진행하여 ProxySQL 툴과의 성능 비교를 진행하였습니다.

 

읽기와 쓰기 분리 기술을 구현하는 방법에는 여러 가지가 있습니다.  읽기 및 쓰기가 분리된 lua 파일이 포함되어 있습니다. 이 파일은 mysql-proxy를 사용하여 읽기 및 쓰기 분리를 달성하는 데 사용해야 하는 파일이기도 합니다. 구문 분석을 위해 lua 파서가 필요합니다. 따라서 루아 파서를 설치해야 합니다.

 

 

■ config 파일 생성

[root@mysqlproxy mysql-proxy]# mkdir /usr/local/mysql-proxy/conf/
[root@mysqlproxy conf]# vi /usr/local/mysql-proxy/conf/mysql-proxy.conf
[mysql-proxy]
Daemon = true
Keepalive = true
Log-level = debug
Log-file =/usr/local/mysql-proxy/log/mysql-proxy.log
Basedir =/usr/local/mysql-proxy
Proxy-address = 0.0.0.0:4040
Proxy-backend-addresses = 192.168.100.82:3306
Proxy-read-only-backend-addresses = 192.168.100.81:3306
Proxy-lua-script =/usr/local/mysql-proxy/rw-splitting.lua


※ 설명
Daemon = true # 나중에 데몬 시작
Keepalive = true # 프로세스 실패 후 자동으로 다시 시작
Log-level = debug # 디버그할 로그 수준을 설정합니다. 디버깅 후 정보로 변경할 수 있습니다.
Log-file =/usr/local/mysql-proxy/log/mysql-proxy.log # 로그 파일 경로 설정
Basedir =/usr/local/mysql-proxy # mysql-proxy의 홈 디렉토리 설정
Proxy-address = 0.0.0.0:4040 # 지정 mysql-proxy의 수신 주소
Proxy-backend-addresses = 192.168.100.82:3306 # 백엔드 마스터 서버 설정
Proxy-read-only-backend-addresses = 192.168.100.81:3306 # 백그라운드 슬레이브 서버 설정
Proxy-lua-script =/usr/local/mysql-proxy/rw-splitting.lua # 읽기/쓰기 분할 스크립트 경로 설정

Admin-address = 192.168.216.132: 4041 # set 상위 관리자 플러그인이 필요한 mysql-proxy 관리 주소
Admin-username = admin # 로그온 관리 주소 설정 user
Admin-password = admin # 관리 사용자 암호 설정
Admin-lua-script =/usr/local/mysql -proxy/share/doc/mysql-proxy/admin. lua # 관리 백그라운드에서 lua 스크립트 경로를 설정합니다. 스크립트는 기본적으로 자동으로 정의되지 않습니다.


[root@mysqlproxy mysql-proxy]# chmod 600 /usr/local/mysql-proxy/conf/mysql-proxy.conf


## 기동
[root@mysqlproxy bin]# ./mysql-proxy --plugins=proxy --defaults-file=/usr/local/mysql-proxy/conf/mysql-proxy.conf &

 

 

■ read/write split 확인

※ proxy로 select 쿼리 실행
[root@jh-mha002 ~]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "select * from kim.sbtest1;"



## Master로 쿼리가 간다..
MariaDB [kim]> select * from information_schema.processlist where user='kim';
+-----+------+----------------------+------+---------+------+-------+------+----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| ID  | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO | TIME_MS  | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID   |
+-----+------+----------------------+------+---------+------+-------+------+----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| 248 | kim  | 192.168.100.84:43388 | NULL | Sleep   |    4 |       | NULL | 4847.333 |     0 |         0 |    0.000 |       77816 |           77816 |             0 |      312 | NULL        | 21612 |
+-----+------+----------------------+------+---------+------+-------+------+----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+


## slave
MariaDB [(none)]> select * from information_schema.processlist where user='kim';
Empty set (0.001 sec)


Master는 read/write이 모두 되고 Slave는 read만 가능.


## Mater로 2번의 select 쿼리
MariaDB [kim]> select * from information_schema.processlist where user='kim';
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| ID  | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO | TIME_MS   | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID   |
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| 250 | kim  | 192.168.100.84:43400 | NULL | Sleep   |   13 |       | NULL | 13457.032 |     0 |         0 |    0.000 |       77816 |           77816 |             0 |      312 | NULL        | 21612 |
| 249 | kim  | 192.168.100.84:43398 | NULL | Sleep   |   13 |       | NULL | 13444.218 |     0 |         0 |    0.000 |       77816 |           77816 |             0 |      317 | NULL        | 20453 |
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
2 rows in set (0.001 sec)



## Slave로 2번의 select 쿼리
MariaDB [(none)]> select * from information_schema.processlist where user='kim';
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| ID  | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO | TIME_MS   | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID   |
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
| 721 | kim  | 192.168.100.84:44484 | NULL | Sleep   |   34 |       | NULL | 34593.397 |     0 |         0 |    0.000 |       69632 |           69632 |             0 |      897 | NULL        | 18253 |
| 720 | kim  | 192.168.100.84:44480 | NULL | Sleep   |   34 |       | NULL | 34580.873 |     0 |         0 |    0.000 |       69632 |           69632 |             0 |      915 | NULL        | 18227 |
+-----+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+-------+
2 rows in set (0.002 sec)



※ proxy로 insert 쿼리 실행 (4번실행)
[root@jh-mha002 sysbench]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "insert into kim.sbtest1 values(1000000000,1,'kim','jh');"
[root@jh-mha002 sysbench]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "insert into kim.sbtest1 values(1000000003,1,'kim','jh');"
[root@jh-mha002 sysbench]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "insert into kim.sbtest1 values(1000000004,1,'kim','jh');"
[root@jh-mha002 sysbench]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "insert into kim.sbtest1 values(1000000005,1,'kim','jh');"



## master로 세션이 붙는거 확인
MariaDB [kim]> select * from information_schema.processlist where user='kim';
+------+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+
| ID   | USER | HOST                 | DB   | COMMAND | TIME | STATE | INFO | TIME_MS   | STAGE | MAX_STAGE | PROGRESS | MEMORY_USED | MAX_MEMORY_USED | EXAMINED_ROWS | QUERY_ID | INFO_BINARY | TID  |
+------+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+
| 1105 | kim  | 192.168.100.84:46764 | NULL | Sleep   |    2 |       | NULL |  2199.329 |     0 |         0 |    0.000 |       69632 |           69632 |             0 |  2912197 | NULL        | 8925 |
| 1104 | kim  | 192.168.100.84:46762 | NULL | Sleep   |   14 |       | NULL | 14114.485 |     0 |         0 |    0.000 |       69632 |           69632 |             0 |  2912029 | NULL        | 8926 |
| 1103 | kim  | 192.168.100.84:46760 | NULL | Sleep   |   14 |       | NULL | 14110.676 |     0 |         0 |    0.000 |       77976 |           77976 |             0 |  2912281 | NULL        | 8927 |
| 1102 | kim  | 192.168.100.84:46758 | NULL | Sleep   |    2 |       | NULL |  2195.826 |     0 |         0 |    0.000 |       77976 |           77976 |             0 |  2912283 | NULL        | 8928 |
+------+------+----------------------+------+---------+------+-------+------+-----------+-------+-----------+----------+-------------+-----------------+---------------+----------+-------------+------+
4 rows in set (0.001 sec)


## slave
MariaDB [(none)]> select user, host, command from information_schema.processlist where user='kim';
Empty set (0.002 sec)

 

 

※  에러 발생

proxy를 통해서 접속이 안된다. 아무래도 proxy에 설정한 connection 수 문제인거 같다..

[root@jh-mha002 ~]# mysql -ukim -pkim --port 4040 -h 192.168.100.84 -e "select * from kim.sbtest1;"
ERROR 1047 (08S01): Unknown command


## idle_connections 수 조정
[root@mysqlproxy mysql-proxy]# vi rw-splitting.lua
...생략
--- config
--
-- connection pool
if not proxy.global.config.rwsplit then
        proxy.global.config.rwsplit = {
                min_idle_connections = 100,
                max_idle_connections = 1,                    

                is_debug = false
        }
end

 

 

■ 부하테스트 (Write)

[root@jh-mha002 ~]# yum -y install epel-release
[root@jh-mha002 ~]# yum -y install sysbench
## prepare
[root@jh-mha002 ~]# sysbench --mysql-db=kim --mysql-host=192.168.100.84 --mysql-port=4040 --mysql-user=kim --mysql-password=kim --threads=50 --report-interval=10 --max-time=150 --max-requests=0 /usr/share/sysbench/oltp_insert.lua prepare



## Run (1차)
[root@jh-mha002 ~]# sysbench --mysql-db=kim --mysql-host=192.168.100.84 --mysql-port=4040 --mysql-user=kim --mysql-password=kim --threads=50 --report-interval=10 --max-time=150 --max-requests=0 /usr/share/sysbench/oltp_insert.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 50 tps: 11054.89 qps: 11054.89 (r/w/o: 0.00/11054.89/0.00) lat (ms,95%): 6.91 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 10776.86 qps: 10776.86 (r/w/o: 0.00/10776.86/0.00) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 10712.71 qps: 10712.71 (r/w/o: 0.00/10712.71/0.00) lat (ms,95%): 7.84 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 8927.42 qps: 8927.42 (r/w/o: 0.00/8927.42/0.00) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 10791.95 qps: 10791.95 (r/w/o: 0.00/10791.95/0.00) lat (ms,95%): 7.98 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 10331.18 qps: 10331.18 (r/w/o: 0.00/10331.18/0.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 9829.07 qps: 9829.07 (r/w/o: 0.00/9829.07/0.00) lat (ms,95%): 8.90 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 8904.98 qps: 8904.98 (r/w/o: 0.00/8904.98/0.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 9959.46 qps: 9959.46 (r/w/o: 0.00/9959.46/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 10101.32 qps: 10101.32 (r/w/o: 0.00/10101.32/0.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 8527.15 qps: 8527.15 (r/w/o: 0.00/8527.15/0.00) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 10650.07 qps: 10650.07 (r/w/o: 0.00/10650.07/0.00) lat (ms,95%): 8.13 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 10106.67 qps: 10106.67 (r/w/o: 0.00/10106.67/0.00) lat (ms,95%): 8.58 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 10303.24 qps: 10303.24 (r/w/o: 0.00/10303.24/0.00) lat (ms,95%): 8.74 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 7809.99 qps: 7809.99 (r/w/o: 0.00/7809.99/0.00) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           1487950
        other:                           0
        total:                           1487950
    transactions:                        1487950 (9918.44 per sec.)
    queries:                             1487950 (9918.44 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          150.0166s
    total number of events:              1487950

Latency (ms):
         min:                                    1.02
         avg:                                    5.04
         max:                                 1762.27
         95th percentile:                        8.28
         sum:                              7496536.93

Threads fairness:
    events (avg/stddev):           29759.0000/315.58
    execution time (avg/stddev):   149.9307/0.00




## Run (2차)
[ 10s ] thds: 50 tps: 10326.17 qps: 10326.17 (r/w/o: 0.00/10326.17/0.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 10193.19 qps: 10193.19 (r/w/o: 0.00/10193.19/0.00) lat (ms,95%): 8.43 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 10056.12 qps: 10056.12 (r/w/o: 0.00/10056.12/0.00) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 8510.06 qps: 8510.06 (r/w/o: 0.00/8510.06/0.00) lat (ms,95%): 9.56 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 9764.79 qps: 9764.79 (r/w/o: 0.00/9764.79/0.00) lat (ms,95%): 9.39 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 9541.55 qps: 9541.55 (r/w/o: 0.00/9541.55/0.00) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 7707.91 qps: 7707.91 (r/w/o: 0.00/7707.91/0.00) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 9570.07 qps: 9570.07 (r/w/o: 0.00/9570.07/0.00) lat (ms,95%): 10.09 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 9297.82 qps: 9297.82 (r/w/o: 0.00/9297.82/0.00) lat (ms,95%): 10.84 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 9081.51 qps: 9081.51 (r/w/o: 0.00/9081.51/0.00) lat (ms,95%): 11.24 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 7644.00 qps: 7644.00 (r/w/o: 0.00/7644.00/0.00) lat (ms,95%): 10.27 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 8817.55 qps: 8817.55 (r/w/o: 0.00/8817.55/0.00) lat (ms,95%): 11.45 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 8196.68 qps: 8196.68 (r/w/o: 0.00/8196.68/0.00) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 8715.71 qps: 8715.71 (r/w/o: 0.00/8715.71/0.00) lat (ms,95%): 11.04 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 8666.87 qps: 8666.87 (r/w/o: 0.00/8666.87/0.00) lat (ms,95%): 12.30 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           1360980
        other:                           0
        total:                           1360980
    transactions:                        1360980 (9072.17 per sec.)
    queries:                             1360980 (9072.17 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          150.0151s
    total number of events:              1360980

Latency (ms):
         min:                                    0.91
         avg:                                    5.51
         max:                                 1713.78
         95th percentile:                        9.91
         sum:                              7496840.66

Threads fairness:
    events (avg/stddev):           27219.6000/266.99
    execution time (avg/stddev):   149.9368/0.00

 

 

■ ProxySQL

회사 선배와 동일한 환경에서 proxy툴만 다르게하여 부하테스트를 진행했습니다. (성능 비교를 위함)

1차 RUN
sysbench 1.0.17 (using system LuaJIT 2.0.4)

Running the test with following options:
Number of threads: 50
Report intermediate results every 10 second(s)
Initializing random number generator from current time


Initializing worker threads...

Threads started!

[ 10s ] thds: 50 tps: 5534.42 qps: 5534.42 (r/w/o: 0.00/5534.42/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 5264.19 qps: 5264.19 (r/w/o: 0.00/5264.19/0.00) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 5260.26 qps: 5260.26 (r/w/o: 0.00/5260.26/0.00) lat (ms,95%): 16.71 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 5184.56 qps: 5184.56 (r/w/o: 0.00/5184.56/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 5075.29 qps: 5075.29 (r/w/o: 0.00/5075.29/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 5067.74 qps: 5067.74 (r/w/o: 0.00/5067.74/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 5014.62 qps: 5014.62 (r/w/o: 0.00/5014.62/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 5165.57 qps: 5165.57 (r/w/o: 0.00/5165.57/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 5069.40 qps: 5069.40 (r/w/o: 0.00/5069.40/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 4642.95 qps: 4642.95 (r/w/o: 0.00/4642.95/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 4635.90 qps: 4635.90 (r/w/o: 0.00/4635.90/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 4616.47 qps: 4616.47 (r/w/o: 0.00/4616.47/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 4959.28 qps: 4959.28 (r/w/o: 0.00/4959.28/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 5039.15 qps: 5039.15 (r/w/o: 0.00/5039.15/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 5058.73 qps: 5058.73 (r/w/o: 0.00/5058.73/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           755945
        other:                           0
        total:                           755945
    transactions:                        755945 (5038.84 per sec.)
    queries:                             755945 (5038.84 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          150.0221s
    total number of events:              755945

Latency (ms):
         min:                                    1.86
         avg:                                    9.92
         max:                                  107.20
         95th percentile:                       17.32
         sum:                              7499242.62

Threads fairness:
    events (avg/stddev):           15118.9000/40.71
    execution time (avg/stddev):   149.9849/0.00



2차 RUN
[ 10s ] thds: 50 tps: 5306.31 qps: 5306.31 (r/w/o: 0.00/5306.31/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 5447.00 qps: 5447.00 (r/w/o: 0.00/5447.00/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 5507.89 qps: 5507.89 (r/w/o: 0.00/5507.89/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 5475.89 qps: 5475.89 (r/w/o: 0.00/5475.89/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 5258.86 qps: 5258.86 (r/w/o: 0.00/5258.86/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 5206.74 qps: 5206.74 (r/w/o: 0.00/5206.74/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 5219.05 qps: 5219.05 (r/w/o: 0.00/5219.05/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 5115.34 qps: 5115.34 (r/w/o: 0.00/5115.34/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 5427.07 qps: 5427.07 (r/w/o: 0.00/5427.07/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 5591.13 qps: 5591.13 (r/w/o: 0.00/5591.13/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 5597.61 qps: 5597.61 (r/w/o: 0.00/5597.61/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 5341.36 qps: 5341.36 (r/w/o: 0.00/5341.36/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 5354.90 qps: 5354.90 (r/w/o: 0.00/5354.90/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 5288.96 qps: 5288.96 (r/w/o: 0.00/5288.96/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 5209.85 qps: 5209.85 (r/w/o: 0.00/5209.85/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            0
        write:                           803539
        other:                           0
        total:                           803539
    transactions:                        803539 (5356.40 per sec.)
    queries:                             803539 (5356.40 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          150.0133s
    total number of events:              803539

Latency (ms):
         min:                                    1.91
         avg:                                    9.33
         max:                                  253.80
         95th percentile:                       17.32
         sum:                              7498755.26

Threads fairness:
    events (avg/stddev):           16070.7800/40.37
    execution time (avg/stddev):   149.9751/0.00

 

Proxy를 타고  Write작업을 진행할때 Proxy SQL보단 MySQL-Proxy가 더 성능이 잘나왔다. Read 부하 테스트를 아직 진행못해서 무엇이 더 낫다고는 못하지만 뭔가 mysql-proxy는 안정성이 떨어진다.

 

그리고 무엇보다 현직 Mysql dba 고수님들의 얘기를 들었을때 보통 haproxy 쓴다고 합니다ㅎㅎ..

 

 

 

참고

 

https://medium.com/@mena.meseha/mysql-read-and-write-separation-4167d99b337c

MySQL read and write separation