환경 구성
Create Aurora Instance
- Region : N.Virginia
- Engine : Amazon Aurora MySQL 5.6 Compatible
- Instance Type : db.r4.xlarge (4 Core, 30GiB)
- Network : Public Accessible
- Multi-AZ : No
- Read-Replica : Yes (Seoul)
- Enhanced Monitoring : No
Create EC2 Instance
- Region : Seoul
- OS : CentOS 7
- AMI : ami-c74789a9
- Instance Type : m4.large
- Network : Public Accessible
Installing the Sysbench
sysbench는 EC2 인스턴스에 설치한다
일단 EC2 인스턴스에 SSH 접속
]$ ssh -i <key pair> centos@13.125.213.255
Last login: Tue Apr 10 00:32:29 2018 from 211.44.198.26
[centos@ip-10-0-3-133 ~]$
wget 명령어로 binary 설치를 진행
[centos@ip-10-0-3-133 ~]$ sudo curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash
[centos@ip-10-0-3-133 ~]$ sudo yum -y install sysbench
설치가 완료 됌
Prepare the Database
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.10-log MySQL Community Server (GPL)
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
Sysbench 로 실제 테스트 수행
READ ONLY
<Prepare>
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Creating table 'sbtest1'...
Inserting 10000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 10000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 10000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 10000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 10000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
- Table : 5개
- Row : 테이블 당 10000 row
- mode : oltp_read_only
<Run>
1. Master
준비가 다 되었으면 일단 마스터에 쿼리를 날려봅니다.
[centos@ip-10-0-3-133 ~]$ sysbench --db-driver=mysql --mysql-host=<Aurora Endpoint> --max-requests=100 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --table-size=10000 --tables=5 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-requests is deprecated, use --events instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 56
write: 0
other: 8
total: 64
transactions: 4 (0.32 per sec.)
queries: 64 (5.05 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 12.6586s
total number of events: 4
Latency (ms):
min: 3147.83
avg: 3164.53
max: 3196.35
95th percentile: 3208.88
sum: 12658.11
Threads fairness:
events (avg/stddev): 4.0000/0.00
execution time (avg/stddev): 12.6581/0.00
max-requests 를 100 을 주고 쿼리를 날렸지만 왜인지 토탈 이벤트가 4 밖에 되질 않습니다.
Sysbench 내부적으로 timeout값을 가지고 있는지는 모르겠지만 왠지 100개의 리퀘스트를 버지니아 리전까지 트랜잭션을 날렸다가 받는 것을 버거워 하는 것 같아 리퀘스트가 아닌 시간으로 max를 걸어주기로 합니다.
[centos@ip-10-0-3-133 ~]$ sysbench --db-driver=mysql --mysql-host=<Aurora Endpoint> --max-requests=0 --max-time=20 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --table-size=10000 --tables=5 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-time is deprecated, use --time instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 98
write: 0
other: 14
total: 112
transactions: 7 (0.32 per sec.)
queries: 112 (5.09 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 22.0103s
total number of events: 7
Latency (ms):
min: 3135.30
avg: 3144.25
max: 3157.04
95th percentile: 3151.62
sum: 22009.76
Threads fairness:
events (avg/stddev): 7.0000/0.00
execution time (avg/stddev): 22.0098/0.00
예상대로 4개 이상의 이벤트를 실행한 값을 출력합니다.
버지니아까지 너무 오래 걸리는 관계로 그냥 이벤트를 한번만 발생시킨 값을 비교하기로 합니다.
[centos@ip-10-0-3-133 ~]$ sysbench --db-driver=mysql --mysql-host=a<Aurora Endpoint> --max-requests=1 --mysql-user=sysbench --mysql-password=sysbench --mysql-db=sysbench --table-size=10000 --tables=5 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-requests is deprecated, use --events instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 14
write: 0
other: 2
total: 16
transactions: 1 (0.34 per sec.)
queries: 16 (5.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 2.9466s
total number of events: 1
Latency (ms):
min: 2946.17
avg: 2946.17
max: 2946.17
95th percentile: 2932.60
sum: 2946.17
Threads fairness:
events (avg/stddev): 1.0000/0.00
execution time (avg/stddev): 2.9462/0.00
2. Read-Reaplica
[centos@ip-10-0-3-133 ~]$ sysbench --db-driver=mysql --mysql-host=<Read Replica Endpoint> --mysql-user=sysbench --max-requests=100 --mysql-password=sysbench --mysql-db=sysbench --table-size=10000 --tables=5 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-requests is deprecated, use --events instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 1400
write: 0
other: 200
total: 1600
transactions: 100 (53.14 per sec.)
queries: 1600 (850.18 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 1.8799s
total number of events: 100
Latency (ms):
min: 17.81
avg: 18.79
max: 21.52
95th percentile: 19.65
sum: 1879.11
Threads fairness:
events (avg/stddev): 100.0000/0.00
execution time (avg/stddev): 1.8791/0.00
짱 빠른 결과...!!
이벤트 한 번 발생시킨 값
[centos@ip-10-0-3-133 ~]$ sysbench --db-driver=mysql --mysql-host=<Read Replica Endpoint> --mysql-user=sysbench --max-requests=1 --mysql-password=sysbench --mysql-db=sysbench --table-size=10000 --tables=5 /usr/share/sysbench/oltp_read_only.lua run
WARNING: --max-requests is deprecated, use --events instead
sysbench 1.0.14 (using bundled LuaJIT 2.1.0-beta2)
Running the test with following options:
Number of threads: 1
Initializing random number generator from current time
Initializing worker threads...
Threads started!
SQL statistics:
queries performed:
read: 14
write: 0
other: 2
total: 16
transactions: 1 (44.71 per sec.)
queries: 16 (715.43 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 0.0202s
total number of events: 1
Latency (ms):
min: 19.90
avg: 19.90
max: 19.90
95th percentile: 20.00
sum: 19.90
Threads fairness:
events (avg/stddev): 1.0000/0.00
execution time (avg/stddev): 0.0199/0.00
짱 빠름
Clean Up
여태 사용하연 sysbench 명령어 맨 뒤에 prepare/run 대신 cleanup을 붙여 실행시키면 테이블이 모두 삭제됩니다.
참고 링크
Alexy Kopytov, "Sysbench GitHub", https://github.com/akopytov/sysbench