[MariaDB - Galera Cluster & sysbench 이용 동기화 테스트 ] part 3

안녕하세요 이번 글에선 galera cluster의 sysbench 이용하여 대량의 데이터를 insert 시켜 동기화 성능 테스트를 진행해 보겠습니다.

 

 

 

 

 사전 구성

 

part 1 URL : https://jhdatabase.tistory.com/34

[MariaDB - Galera Cluster 구성] part 1

 

part 2 URL : https://jhdatabase.tistory.com/35

[MariaDB - Galera Cluster & maxscale 구성 및 failover test] part 2

 

 

 

 

 Sysbench 설치

 

■ sysbench install (maxscale)

[root@maxscale ~]# wget https://github.com/akopytov/sysbench/archive/master.zip
--2022-02-23 10:56:19--  https://github.com/akopytov/sysbench/archive/master.zip
Resolving github.com (github.com)... 15.164.81.167
Connecting to github.com (github.com)|15.164.81.167|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://codeload.github.com/akopytov/sysbench/zip/refs/heads/master [following]
--2022-02-23 10:56:19--  https://codeload.github.com/akopytov/sysbench/zip/refs/heads/master
Resolving codeload.github.com (codeload.github.com)... 13.124.243.66
Connecting to codeload.github.com (codeload.github.com)|13.124.243.66|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [application/zip]
Saving to: ‘master.zip’

    [  <=>                                               ] 2,274,128   6.87MB/s   in 0.3s   

2022-02-23 10:56:20 (6.87 MB/s) - ‘master.zip’ saved [2274128]




[root@maxscale ~]# unzip master.zip

[root@maxscale ~]# cd sysbench-master/




## dependency 패키지 설치
[root@maxscale sysbench-master]# yum -y install libtool mysql-devel automake lua



## 컴파일 및 설치
[root@maxscale sysbench-master]# ./autogen.sh
[root@maxscale sysbench-master]# ./configure
[root@maxscale sysbench-master]# make && make install

 

 

 

■ 유저와 데이터베이스 생성

MariaDB [(none)]> create database sysbench;
Query OK, 1 row affected (0.00 sec)


MariaDB [(none)]> create user sysbench@'%' identified by 'sysbench';
Query OK, 0 rows affected (0.00 sec)


MariaDB [(none)]> grant all on sysbench.* to sysbench@'%';
Query OK, 0 rows affected (0.00 sec)

 

 

 

■ sysbench 이용 데이터 1개짜리 테이블 6개 생성

[root@maxscale sysbench-master]# sysbench --db-driver=mysql --mysql-host=10.70.101.81 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench /usr/local/share/sysbench/oltp_insert.lua --table-size=1 --tables=6  --threads=128 prepare
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

Initializing worker threads...

Creating table 'sbtest5'...
Creating table 'sbtest2'...
Creating table 'sbtest4'...
Creating table 'sbtest3'...
Creating table 'sbtest1'...
Creating table 'sbtest6'...
Inserting 1 records into 'sbtest5'
Inserting 1 records into 'sbtest2'
Inserting 1 records into 'sbtest4'
Inserting 1 records into 'sbtest3'
Inserting 1 records into 'sbtest1'
Inserting 1 records into 'sbtest6'
Creating a secondary index on 'sbtest5'...
Creating a secondary index on 'sbtest2'...
Creating a secondary index on 'sbtest4'...
Creating a secondary index on 'sbtest3'...
Creating a secondary index on 'sbtest1'...
Creating a secondary index on 'sbtest6'...

 

 

 

 

■ 데이터 입력

root@maxscale ~]# sysbench --db-driver=mysql --mysql-host=10.70.101.81 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench /usr/local/share/sysbench/oltp_read_write.lua --table-size=200000 --tables=6  --threads=64 --events=100000 --time=600 --report-interval=3 run
sysbench 1.1.0 (using bundled LuaJIT 2.1.0-beta3)

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

Initializing worker threads...

Threads started!

[ 3s ] thds: 64 tps: 458.31 qps: 11304.61 (r/w/o: 8110.74/594.54/2599.33) lat (ms,95%): 308.84 err/s: 104.25 reconn/s: 0.00
[ 6s ] thds: 64 tps: 582.72 qps: 11745.44 (r/w/o: 8208.78/753.74/2782.93) lat (ms,95%): 170.48 err/s: 1.33 reconn/s: 0.00
[ 9s ] thds: 64 tps: 615.64 qps: 12305.53 (r/w/o: 8601.34/795.97/2908.22) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 12s ] thds: 64 tps: 625.35 qps: 12559.00 (r/w/o: 8815.90/784.35/2958.74) lat (ms,95%): 137.35 err/s: 1.33 reconn/s: 0.00
[ 15s ] thds: 64 tps: 627.67 qps: 12560.76 (r/w/o: 8783.40/791.67/2985.69) lat (ms,95%): 142.39 err/s: 0.33 reconn/s: 0.00
[ 18s ] thds: 64 tps: 627.99 qps: 12456.45 (r/w/o: 8727.18/793.65/2935.62) lat (ms,95%): 139.85 err/s: 1.67 reconn/s: 0.00
[ 21s ] thds: 64 tps: 598.01 qps: 12016.11 (r/w/o: 8422.41/763.34/2830.36) lat (ms,95%): 155.80 err/s: 0.33 reconn/s: 0.00
[ 24s ] thds: 64 tps: 592.33 qps: 11827.96 (r/w/o: 8276.97/740.33/2810.66) lat (ms,95%): 179.94 err/s: 0.33 reconn/s:
 0.00
[ 27s ] thds: 64 tps: 597.99 qps: 12037.46 (r/w/o: 8412.19/778.99/2846.28) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 64 tps: 603.02 qps: 12006.00 (r/w/o: 8419.24/761.02/2825.75) lat (ms,95%): 153.02 err/s: 0.33 reconn/s: 0.00
[ 33s ] thds: 64 tps: 590.36 qps: 11900.14 (r/w/o: 8308.33/793.36/2798.44) lat (ms,95%): 142.39 err/s: 0.33 reconn/s: 0.00
[ 36s ] thds: 64 tps: 594.31 qps: 11849.94 (r/w/o: 8324.06/756.64/2769.24) lat (ms,95%): 153.02 err/s: 0.67 reconn/s: 0.00
[ 39s ] thds: 64 tps: 562.33 qps: 11242.59 (r/w/o: 7872.61/726.33/2643.65) lat (ms,95%): 170.48 err/s: 0.00 reconn/s: 0.00
[ 42s ] thds: 64 tps: 616.33 qps: 12308.69 (r/w/o: 8611.68/805.33/2891.67) lat (ms,95%): 142.39 err/s: 0.33 reconn/s: 0.00
[ 45s ] thds: 64 tps: 597.99 qps: 11923.80 (r/w/o: 8312.19/789.99/2821.62) lat (ms,95%): 155.80 err/s: 0.67 reconn/s: 0.00
[ 48s ] thds: 64 tps: 558.02 qps: 11139.46 (r/w/o: 7817.65/730.03/2591.77) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 51s ] thds: 64 tps: 541.34 qps: 10891.10 (r/w/o: 7640.07/713.67/2537.36) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 54s ] thds: 64 tps: 586.30 qps: 11741.61 (r/w/o: 8220.16/771.62/2749.83) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 57s ] thds: 64 tps: 604.72 qps: 12119.32 (r/w/o: 8486.69/813.73/2818.90) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 64 tps: 526.99 qps: 10529.78 (r/w/o: 7342.18/711.32/2476.28) lat (ms,95%): 186.54 err/s: 0.00 reconn/s: 0.00
[ 63s ] thds: 64 tps: 560.97 qps: 11203.00 (r/w/o: 7871.53/740.62/2590.85) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 66s ] thds: 64 tps: 562.35 qps: 11173.63 (r/w/o: 7794.88/750.35/2628.40) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 69s ] thds: 64 tps: 570.34 qps: 11459.90 (r/w/o: 8028.49/777.68/2653.72) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 72s ] thds: 64 tps: 563.32 qps: 11228.01 (r/w/o: 7831.11/755.64/2641.26) lat (ms,95%): 164.45 err/s: 0.33 reconn/s: 0.00
[ 75s ] thds: 64 tps: 570.66 qps: 11507.12 (r/w/o: 8058.52/780.65/2667.95) lat (ms,95%): 155.80 err/s: 0.00 reconn/s: 0.00
[ 78s ] thds: 64 tps: 563.02 qps: 11173.38 (r/w/o: 7846.93/757.36/2569.09) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 81s ] thds: 64 tps: 537.66 qps: 10784.85 (r/w/o: 7552.23/723.66/2508.97) lat (ms,95%): 176.73 err/s: 0.00 reconn/s: 0.00
[ 84s ] thds: 64 tps: 540.01 qps: 10840.91 (r/w/o: 7564.83/743.35/2532.72) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 87s ] thds: 64 tps: 520.32 qps: 10422.08 (r/w/o: 7313.49/713.65/2394.94) lat (ms,95%): 240.02 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 64 tps: 548.00 qps: 10973.02 (r/w/o: 7666.01/749.00/2558.00) lat (ms,95%): 189.93 err/s: 0.00 reconn/s: 0.00
[ 93s ] thds: 64 tps: 575.32 qps: 11403.65 (r/w/o: 8004.09/761.31/2638.25) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 96s ] thds: 64 tps: 549.35 qps: 11101.66 (r/w/o: 7774.23/762.02/2565.41) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00
[ 99s ] thds: 64 tps: 564.01 qps: 11219.80 (r/w/o: 7866.10/763.68/2590.03) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 102s ] thds: 64 tps: 535.66 qps: 10753.90 (r/w/o: 7492.26/758.99/2502.64) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 105s ] thds: 64 tps: 559.67 qps: 11114.36 (r/w/o: 7800.35/752.67/2561.34) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 108s ] thds: 64 tps: 533.97 qps: 10789.44 (r/w/o: 7524.61/758.96/2505.87) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 111s ] thds: 64 tps: 576.03 qps: 11402.00 (r/w/o: 7995.13/776.05/2630.82) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 114s ] thds: 64 tps: 556.66 qps: 11148.52 (r/w/o: 7808.57/772.66/2567.30) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 117s ] thds: 64 tps: 568.01 qps: 11380.96 (r/w/o: 7980.21/785.35/2615.40) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 64 tps: 583.66 qps: 11647.50 (r/w/o: 8155.55/805.99/2685.96) lat (ms,95%): 142.39 err/s: 0.00 reconn/s: 0.00
[ 123s ] thds: 64 tps: 570.99 qps: 11367.07 (r/w/o: 7926.15/810.65/2630.27) lat (ms,95%): 144.97 err/s: 0.33 reconn/s: 0.00
[ 126s ] thds: 64 tps: 560.65 qps: 11258.72 (r/w/o: 7900.81/796.98/2560.94) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 129s ] thds: 64 tps: 585.36 qps: 11812.20 (r/w/o: 8246.04/855.71/2710.46) lat (ms,95%): 139.85 err/s: 0.00 reconn/s: 0.00
[ 132s ] thds: 64 tps: 590.31 qps: 11660.55 (r/w/o: 8155.35/840.63/2664.56) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 135s ] thds: 64 tps: 573.67 qps: 11556.99 (r/w/o: 8118.33/814.67/2624.00) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 138s ] thds: 64 tps: 575.68 qps: 11411.56 (r/w/o: 7976.83/822.68/2612.05) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 141s ] thds: 64 tps: 554.68 qps: 11210.27 (r/w/o: 7858.52/798.35/2553.39) lat (ms,95%): 183.21 err/s: 0.67 reconn/s: 0.00
[ 144s ] thds: 64 tps: 554.67 qps: 11014.07 (r/w/o: 7701.71/765.67/2546.68) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00
[ 147s ] thds: 64 tps: 509.32 qps: 10191.75 (r/w/o: 7137.83/729.65/2324.28) lat (ms,95%): 167.44 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 64 tps: 568.64 qps: 11396.17 (r/w/o: 7980.99/821.96/2593.22) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
[ 153s ] thds: 64 tps: 543.02 qps: 10879.83 (r/w/o: 7629.35/790.04/2460.45) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 156s ] thds: 64 tps: 514.68 qps: 10352.97 (r/w/o: 7262.55/744.02/2346.40) lat (ms,95%): 211.60 err/s: 0.33 reconn/s: 0.00
[ 159s ] thds: 64 tps: 547.64 qps: 10806.20 (r/w/o: 7516.34/797.63/2492.22) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 162s ] thds: 64 tps: 551.03 qps: 11136.59 (r/w/o: 7835.41/791.71/2509.47) lat (ms,95%): 153.02 err/s: 0.00 reconn/s: 0.00
[ 165s ] thds: 64 tps: 561.31 qps: 11188.11 (r/w/o: 7815.61/832.63/2539.87) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00
[ 168s ] thds: 64 tps: 546.01 qps: 10904.58 (r/w/o: 7626.84/790.35/2487.39) lat (ms,95%): 158.63 err/s: 0.00 reconn/s: 0.00
[ 171s ] thds: 64 tps: 566.66 qps: 11380.79 (r/w/o: 7964.85/820.65/2595.28) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00
[ 174s ] thds: 64 tps: 573.00 qps: 11471.39 (r/w/o: 8044.04/831.67/2595.68) lat (ms,95%): 144.97 err/s: 0.00 reconn/s: 0.00
SQL statistics:
    queries performed:
        read:                            1404774
        write:                           136352
        other:                           465012
        total:                           2006138
    transactions:                        100000 (567.21 per sec.)
    queries:                             2006138 (11379.02 per sec.)
    ignored errors:                      341    (1.93 per sec.)
    reconnects:                          0      (0.00 per sec.)

Throughput:
    events/s (eps):                      567.2103
    time elapsed:                        176.3015s
    total number of events:              100000

Latency (ms):
         min:                                    4.44
         avg:                                  112.80
         max:                                  818.96
         95th percentile:                      161.51
         sum:                             11280308.69

Threads fairness:
    events (avg/stddev):           1562.5000/6.76
    execution time (avg/stddev):   176.2548/0.03


※10만건 처리하는데 176초 걸리고 TPS 567.21입니다. Threads 128로 설정 평균 초단 567건 insert 작업 처리.

 

 

 

 

 

■ DB별 실시간 count 수 비교

동시에 3개의 세션에 카운트를 날리면 엇비슷한 갯수가 나옵니다.

실시간 data count수가 각자 차이가 좀 있지만 최종 Insert 작업이 끝나고 나선 전부 동일합니다. 최종값이 나오는 시간이 거의 비슷합니다.

<node1>
MariaDB [(none)]> select count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
|    19640 |
+----------+
1 row in set (0.04 sec)


<node2>
MariaDB [(none)]> select count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
|    19590 |
+----------+
1 row in set (0.00 sec)


<node3>
MariaDB [(none)]> select count(*) from sysbench.sbtest1;
+----------+
| count(*) |
+----------+
|    19440 |
+----------+
1 row in set (0.01 sec)

 

 

 

생각보다 동기화 속도가 빠르고 일반적인 트랜잭션을 처리하기에 무리가 없어 쓸만한거 같습니다.

 

 

 

참고

 

https://rastalion.me/galera-cluster-for-mysql-8-3-%eb%8f%99%ea%b8%b0%ed%99%94-%ec%84%b1%eb%8a%a5/

Galera cluster for MySQL 8 – #.3 동기화 성능 - RastaLion's IT Blog