MySQL & Maria
ProxySQL 성능 테스트
- -
반응형
이번에 DB Proxy를 사용하게 되는 일이 생겨서 성능 테스트를 해보았습니다.
■ 테스트 서버
- SYSBENCH : 192.168.100.35
- ProxySQL : 192.168.100.50 ( 4C / 4M )
- DB : 192.168.100.37 ( 4C / 4M )
■ ProxySQL 통한 DML
1. Write DML
1. Prepare
[root@monitoring oltp_legacy]# sysbench --mysql-db=lee --mysql-host=192.168.100.50 --mysql-port=6033 --mysql-user=lee --mysql-password=lee --threads=50 --report-interval=10 --max-time=150 --max-requests=0 /usr/share/sysbench/oltp_insert.lua prepare
2. Run ( 1차 )
[root@monitoring oltp_legacy]# sysbench --mysql-db=lee --mysql-host=192.168.100.50 --mysql-port=6033 --mysql-user=lee --mysql-password=lee --threads=50 --report-interval=10 --max-time=150 --max-requests=0 /usr/share/sysbench/oltp_insert.lua 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
Run ( 2차 )
[ 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
3. MySQL show processlist
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | | Daemon | 82540 | Waiting on empty queue | |
| 29 | repl | 192.168.100.38:55600 | | Binlog Dump | 81477 | Master has sent all binlog to slave; waiting for more updates | |
| 4682 | lee | 192.168.100.50:52614 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5032, '23648873302-62547180669-81827501072-5474163851 |
| 4683 | lee | 192.168.100.50:52622 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5003, '93805712783-10422723236-21959660006-9841300193 |
| 4684 | lee | 192.168.100.50:52624 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5042, '17413407126-15581549663-18498543399-1516071580 |
| 4685 | lee | 192.168.100.50:52628 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5019, '97638611830-66992116160-29735224856-1782804435 |
| 4686 | lee | 192.168.100.50:52626 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5027, '59929528971-35383719693-60417014695-7743538337 |
(생략)
| 4712 | lee | 192.168.100.50:52706 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5038, '79180962405-01385028656-73085985949-8144810896 |
| 4713 | lee | 192.168.100.50:52708 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5041, '92328188256-66701829234-15302966746-9465311275 |
| 4714 | lee | 192.168.100.50:52710 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5007, '81101220323-61527933349-44657157185-6318199105 |
| 4715 | lee | 192.168.100.50:52712 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5040, '66012934687-41959234794-92709141222-7636894478 |
| 4716 | lee | 192.168.100.50:52714 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5027, '60605422448-22863225906-12806571266-8720155962 |
| 4717 | lee | 192.168.100.50:52716 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5046, '36287605021-43081627370-02781209832-2173479421 |
| 4718 | lee | 192.168.100.50:52720 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 3897, '09105741912-70182213010-61600932179-7729845526 |
| 4719 | lee | 192.168.100.50:52718 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5046, '17979377978-53653670280-18132660549-3845280461 |
| 4720 | lee | 192.168.100.50:52722 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5018, '26204907806-27383019226-42689656576-9269517388 |
| 4721 | lee | 192.168.100.50:52726 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 6155, '65307603853-37565396423-64446623646-3891962430 |
| 4722 | lee | 192.168.100.50:52724 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5033, '01952904760-56088527239-62568873788-1928371810 |
| 4723 | lee | 192.168.100.50:52728 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 4059, '06181886525-32123978060-33896650490-9168221750 |
| 4724 | lee | 192.168.100.50:52730 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5000, '28531353877-35989449120-05125354243-2322689654 |
| 4725 | lee | 192.168.100.50:52732 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5035, '82927202417-85114964405-79650477125-9501446691 |
| 4726 | lee | 192.168.100.50:52734 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5012, '55685578972-45389114705-43656868728-3939307202 |
| 4727 | lee | 192.168.100.50:52736 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5013, '77567200104-03781061934-32380655442-2871310604 |
| 4728 | lee | 192.168.100.50:52738 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 4533, '97106811153-19065816763-41545078788-9241924761 |
| 4729 | lee | 192.168.100.50:52740 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5720, '98147093080-33036841163-27412703862-0529330746 |
| 4730 | lee | 192.168.100.50:52744 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 5006, '82237569109-32749558360-62730969314-3455519559 |
| 4731 | lee | 192.168.100.50:52742 | lee | Query | 0 | waiting for handler commit | INSERT INTO sbtest1 (id, k, c, pad) VALUES (0, 4985, '08689067535-85784790888-63294924108-0385700884 |
| 5077 | monitor | 192.168.100.50:53050 | | Sleep | 4 | | |
| 6184 | root | localhost | lee | Sleep | 71 | | |
| 6201 | root | localhost | | Query | 0 | init | show processlist |
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+------------------------------------------------------------------------------------------------------+
2. Read ( https://hoing.io/archives/1867 ) ⇒ Read 의 경우 2차 성능으로 평가함 ( 1차는 warmup )
→ 대략 1GB 테이블 5개 생성 / index 생성
→ Prepare Table Size 용량
- 2444444 : 테이블당 428MB
- 3444444 : 테이블당 630MB
- 5444444 : 테이블당 990MB
1. Prepare
[root@monitoring sysbench]# sysbench --mysql-host=192.168.100.50 --mysql-port=6033 --mysql-user=lee --mysql-password=lee --mysql-db=lee --table-size=5444444 --tables=5 /usr/share/sysbench/oltp_read_only.lua prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Creating table 'sbtest1'...
Inserting 5444444 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 5444444 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
(생략)
mysql> show index from sbtest1;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| sbtest1 | 0 | PRIMARY | 1 | id | A | 4914217 | NULL | NULL | | BTREE | | | YES | NULL |
| sbtest1 | 1 | k_1 | 1 | k | A | 863334 | NULL | NULL | | BTREE | | | YES | NULL |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2. Run ( 1차 ) -> Thread 50개 ( Sysbench 1.1 Version 부터는 warmup-time 옵션이 존재 )
sysbench --mysql-host=192.168.100.50 --mysql-port=6033 --mysql-user=lee --mysql-password=lee --mysql-db=lee --table-size=5444444 --tables=5 --threads=50 --report-interval=10 --max-time=150 /usr/share/sysbench/oltp_read_only.lua run
[ 10s ] thds: 50 tps: 317.69 qps: 5127.79 (r/w/o: 4487.41/0.00/640.39) lat (ms,95%): 434.83 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 398.80 qps: 6387.97 (r/w/o: 5590.37/0.00/797.60) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 475.70 qps: 7603.01 (r/w/o: 6651.61/0.00/951.40) lat (ms,95%): 337.94 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 531.91 qps: 8508.32 (r/w/o: 7444.49/0.00/1063.83) lat (ms,95%): 292.60 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 588.38 qps: 9416.81 (r/w/o: 8240.06/0.00/1176.75) lat (ms,95%): 272.27 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 626.81 qps: 10026.07 (r/w/o: 8772.55/0.00/1253.52) lat (ms,95%): 248.83 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 639.90 qps: 10240.45 (r/w/o: 8960.55/0.00/1279.89) lat (ms,95%): 257.95 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 745.79 qps: 11931.64 (r/w/o: 10440.06/0.00/1491.58) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 915.63 qps: 14655.15 (r/w/o: 12823.89/0.00/1831.26) lat (ms,95%): 183.21 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 1110.86 qps: 17773.95 (r/w/o: 15552.23/0.00/2221.72) lat (ms,95%): 147.61 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 1297.62 qps: 20761.40 (r/w/o: 18166.36/0.00/2595.04) lat (ms,95%): 112.67 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 1530.51 qps: 24484.91 (r/w/o: 21423.98/0.00/3060.93) lat (ms,95%): 66.84 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 1533.70 qps: 24542.24 (r/w/o: 21474.65/0.00/3067.59) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 1529.91 qps: 24474.32 (r/w/o: 21414.79/0.00/3059.53) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 1527.27 qps: 24432.37 (r/w/o: 21377.53/0.00/3054.83) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 1928570
write: 0
other: 275510
total: 2204080
transactions: 137755 (917.14 per sec.)
queries: 2204080 (14674.30 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.1985s
total number of events: 137755
Latency (ms):
min: 7.80
avg: 54.45
max: 877.87
95th percentile: 219.36
sum: 7500784.98
Threads fairness:
events (avg/stddev): 2755.1000/93.60
execution time (avg/stddev): 150.0157/0.03
3. show processlist
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+--------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+--------------------------------------------------------------------------------+
| 5 | event_scheduler | localhost | | Daemon | 85257 | Waiting on empty queue | |
| 29 | repl | 192.168.100.38:55600 | | Binlog Dump | 84194 | Master has sent all binlog to slave; waiting for more updates | |
| 4682 | lee | 192.168.100.50:52614 | lee | Execute | 0 | statistics | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN 2182275 AND 2182374 ORDER BY c |
| 4683 | lee | 192.168.100.50:52622 | lee | Execute | 0 | statistics | SELECT c FROM sbtest1 WHERE id=2454120 |
| 4684 | lee | 192.168.100.50:52624 | lee | Execute | 0 | statistics | SELECT c FROM sbtest3 WHERE id=2936928 |
| 4685 | lee | 192.168.100.50:52628 | lee | Execute | 1 | statistics | SELECT c FROM sbtest3 WHERE id=1894378 |
(생략)
| 4729 | lee | 192.168.100.50:52740 | lee | Execute | 1 | statistics | SELECT c FROM sbtest2 WHERE id=2607417 |
| 4730 | lee | 192.168.100.50:52744 | lee | Sleep | 1 | | |
| 4731 | lee | 192.168.100.50:52742 | lee | Execute | 0 | statistics | SELECT c FROM sbtest1 WHERE id BETWEEN 2854567 AND 2854666 |
| 5077 | monitor | 192.168.100.50:53050 | | Sleep | 1 | | |
| 6201 | root | localhost | | Query | 0 | init | show processlist |
| 6324 | root | localhost | lee | Sleep | 1302 | | |
| 6350 | lee | 192.168.100.50:58086 | lee | Execute | 1 | statistics | SELECT DISTINCT c FROM sbtest3 WHERE id BETWEEN 2959222 AND 2959321 ORDER BY c |
| 6351 | lee | 192.168.100.50:58088 | lee | Execute | 1 | statistics | SELECT c FROM sbtest1 WHERE id BETWEEN 2990824 AND 2990923 ORDER BY c |
+------+-----------------+----------------------+-----+-------------+-------+---------------------------------------------------------------+--------------------------------------------------------------------------------+
2. Run ( 2차 ) -> Thread 50개
[ 10s ] thds: 50 tps: 1540.33 qps: 24686.95 (r/w/o: 21601.60/0.00/3085.36) lat (ms,95%): 70.55 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 1526.14 qps: 24419.70 (r/w/o: 21367.22/0.00/3052.49) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 1526.41 qps: 24417.73 (r/w/o: 21365.21/0.00/3052.52) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 1532.27 qps: 24525.18 (r/w/o: 21460.45/0.00/3064.74) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 1532.41 qps: 24520.41 (r/w/o: 21455.68/0.00/3064.73) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 1529.72 qps: 24468.86 (r/w/o: 21409.13/0.00/3059.73) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 1529.00 qps: 24473.47 (r/w/o: 21415.58/0.00/3057.90) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 1527.49 qps: 24439.01 (r/w/o: 21384.23/0.00/3054.78) lat (ms,95%): 74.46 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 1532.42 qps: 24509.09 (r/w/o: 21444.25/0.00/3064.84) lat (ms,95%): 71.83 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 1525.77 qps: 24415.74 (r/w/o: 21364.01/0.00/3051.73) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 1536.10 qps: 24573.17 (r/w/o: 21501.16/0.00/3072.01) lat (ms,95%): 75.82 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 1526.00 qps: 24414.66 (r/w/o: 21362.76/0.00/3051.89) lat (ms,95%): 77.19 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 1530.58 qps: 24492.44 (r/w/o: 21431.37/0.00/3061.07) lat (ms,95%): 78.60 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 1533.40 qps: 24532.01 (r/w/o: 21465.01/0.00/3067.00) lat (ms,95%): 73.13 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 1529.13 qps: 24467.99 (r/w/o: 21409.43/0.00/3058.56) lat (ms,95%): 80.03 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3214764
write: 0
other: 459252
total: 3674016
transactions: 229626 (1530.49 per sec.)
queries: 3674016 (24487.78 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.0332s
total number of events: 229626
Latency (ms):
min: 9.02
avg: 32.66
max: 539.43
95th percentile: 75.82
sum: 7500300.86
Threads fairness:
events (avg/stddev): 4592.5200/111.77
execution time (avg/stddev): 150.0060/0.01
■ DB 다이렉트 DML
1. Write DML
Run ( 1차 )
[root@monitoring sysbench]#sysbench --mysql-db=lee --mysql-host=192.168.100.37 --mysql-port=3306 --mysql-user=lee --mysql-password=lee --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: 5303.17 qps: 5303.17 (r/w/o: 0.00/5303.17/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 5283.15 qps: 5283.15 (r/w/o: 0.00/5283.15/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 5272.04 qps: 5272.04 (r/w/o: 0.00/5272.04/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 5200.82 qps: 5200.82 (r/w/o: 0.00/5200.82/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 5179.44 qps: 5179.44 (r/w/o: 0.00/5179.44/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 5110.20 qps: 5110.20 (r/w/o: 0.00/5110.20/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 5117.39 qps: 5117.39 (r/w/o: 0.00/5117.39/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 5140.59 qps: 5140.59 (r/w/o: 0.00/5140.59/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 4975.54 qps: 4975.54 (r/w/o: 0.00/4975.54/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 4689.18 qps: 4689.28 (r/w/o: 0.00/4689.28/0.00) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 4586.90 qps: 4586.80 (r/w/o: 0.00/4586.80/0.00) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 4613.09 qps: 4613.09 (r/w/o: 0.00/4613.09/0.00) lat (ms,95%): 18.28 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 4627.58 qps: 4627.58 (r/w/o: 0.00/4627.58/0.00) lat (ms,95%): 18.61 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 4851.91 qps: 4851.91 (r/w/o: 0.00/4851.91/0.00) lat (ms,95%): 17.95 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 5324.97 qps: 5324.97 (r/w/o: 0.00/5324.97/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 752822
other: 0
total: 752822
transactions: 752822 (5018.18 per sec.)
queries: 752822 (5018.18 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.0174s
total number of events: 752822
Latency (ms):
min: 1.71
avg: 9.96
max: 113.99
95th percentile: 17.63
sum: 7498969.87
Threads fairness:
events (avg/stddev): 15056.4400/39.68
execution time (avg/stddev): 149.9794/0.01
Run ( 2차 )
[ 10s ] thds: 50 tps: 5507.95 qps: 5507.95 (r/w/o: 0.00/5507.95/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 5482.65 qps: 5482.65 (r/w/o: 0.00/5482.65/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 5490.83 qps: 5490.83 (r/w/o: 0.00/5490.83/0.00) lat (ms,95%): 17.01 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 5316.47 qps: 5316.47 (r/w/o: 0.00/5316.47/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 5250.30 qps: 5250.30 (r/w/o: 0.00/5250.30/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 5286.00 qps: 5286.00 (r/w/o: 0.00/5286.00/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 5298.98 qps: 5298.98 (r/w/o: 0.00/5298.98/0.00) lat (ms,95%): 17.32 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 5287.92 qps: 5287.92 (r/w/o: 0.00/5287.92/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 5271.94 qps: 5271.94 (r/w/o: 0.00/5271.94/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 5283.00 qps: 5283.00 (r/w/o: 0.00/5283.00/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 5207.87 qps: 5207.87 (r/w/o: 0.00/5207.87/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 5224.89 qps: 5224.89 (r/w/o: 0.00/5224.89/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 5305.62 qps: 5305.62 (r/w/o: 0.00/5305.62/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 5233.19 qps: 5233.19 (r/w/o: 0.00/5233.19/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 5242.36 qps: 5242.36 (r/w/o: 0.00/5242.36/0.00) lat (ms,95%): 17.63 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 0
write: 796961
other: 0
total: 796961
transactions: 796961 (5312.61 per sec.)
queries: 796961 (5312.61 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.0114s
total number of events: 796961
Latency (ms):
min: 1.76
avg: 9.41
max: 202.30
95th percentile: 17.32
sum: 7498715.29
Threads fairness:
events (avg/stddev): 15939.2200/54.69
execution time (avg/stddev): 149.9743/0.002. Read
Run ( 1차 )
sysbench --mysql-host=192.168.100.37 --mysql-port=3306 --mysql-user=lee --mysql-password=lee --mysql-db=lee --table-size=5444444 --tables=5 --threads=50 --report-interval=10 --max-time=150 /usr/share/sysbench/oltp_read_only.lua run
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: 1602.49 qps: 25697.80 (r/w/o: 22487.82/0.00/3209.98) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 20s ] thds: 50 tps: 1592.98 qps: 25481.90 (r/w/o: 22296.13/0.00/3185.76) lat (ms,95%): 219.36 err/s: 0.00 reconn/s: 0.00
[ 30s ] thds: 50 tps: 1595.30 qps: 25521.80 (r/w/o: 22331.10/0.00/3190.70) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 40s ] thds: 50 tps: 1598.90 qps: 25573.64 (r/w/o: 22375.83/0.00/3197.80) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 50s ] thds: 50 tps: 1602.64 qps: 25652.37 (r/w/o: 22447.09/0.00/3205.28) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 60s ] thds: 50 tps: 1596.05 qps: 25540.97 (r/w/o: 22349.06/0.00/3191.91) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 50 tps: 1598.82 qps: 25580.89 (r/w/o: 22383.15/0.00/3197.74) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 50 tps: 1600.29 qps: 25607.19 (r/w/o: 22406.50/0.00/3200.69) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 90s ] thds: 50 tps: 1599.08 qps: 25568.52 (r/w/o: 22370.96/0.00/3197.57) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 100s ] thds: 50 tps: 1595.95 qps: 25555.35 (r/w/o: 22362.75/0.00/3192.59) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 110s ] thds: 50 tps: 1598.92 qps: 25576.59 (r/w/o: 22378.95/0.00/3197.64) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 120s ] thds: 50 tps: 1601.44 qps: 25622.85 (r/w/o: 22419.87/0.00/3202.98) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 130s ] thds: 50 tps: 1598.11 qps: 25566.39 (r/w/o: 22370.18/0.00/3196.21) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 140s ] thds: 50 tps: 1598.38 qps: 25579.75 (r/w/o: 22382.98/0.00/3196.77) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
[ 150s ] thds: 50 tps: 1595.56 qps: 25530.83 (r/w/o: 22339.60/0.00/3191.23) lat (ms,95%): 223.34 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 3357228
write: 0
other: 479604
total: 3836832
transactions: 239802 (1597.99 per sec.)
queries: 3836832 (25567.90 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 150.0629s
total number of events: 239802
Latency (ms):
min: 4.26
avg: 31.28
max: 873.89
95th percentile: 223.34
sum: 7501446.04
Threads fairness:
events (avg/stddev): 4796.0400/584.19
execution time (avg/stddev): 150.0289/0.02
■ Write & Read 성능
1. Write ( Sec ) => Write 성 쿼리일 경우 TPS / QPS를 보았을떄 성능은 대부분 비슷한 것으로 보인다.
2. Read ( Sec ) => Read 성 쿼리일 경우 TPS / QPS를 보았을때 성능은 대부분 비슷한 것으로 보인다.
반응형
'MySQL & Maria' 카테고리의 다른 글
| MySQL Python 사용하여 원하는 데이터 가져오기 (0) | 2022.09.26 |
|---|---|
| ProxySQL 설치 및 DB connection (0) | 2022.09.22 |
| MHA 구성 - MySQL ( Using Ansible ) (0) | 2022.09.22 |
| 성능테스트 SYSBENCH CUSTOM SCRIPT (0) | 2022.08.10 |
| [Maria] Galera Cluster to Single DB(DR) (0) | 2022.05.07 |
Contents
소중한 공감 감사합니다