1. sysbench 설치
## 설치가 안되는 경우, epel-release 부터 설치 필요
[root@localhost ~]# yum install epel-release
[root@localhost ~]# yum -y install sysbench
2. 테스트 database 및 계정 생성
| [root@localhost ~]# mysql -h localhost -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1059 Server version: 8.0.33 MySQL Community Server - GPL Copyright (c) 2000, 2023, Oracle and/or its affiliates. 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> create database sysbench; Query OK, 1 row affected (0.00 sec) mysql> create user 'sysbench'@'localhost' identified by 'P@ssw0rd'; Query OK, 0 rows affected (0.01 sec) mysql> create user 'sysbench'@'127.0.0.1' identified by 'P@ssw0rd'; Query OK, 0 rows affected (0.01 sec) mysql> grant all on sysbench.* to 'sysbench'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql> grant all on sysbench.* to 'sysbench'@'127.0.0.1'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) |
3. mysql 8.0 버전 이상에서 sysbench 실행 시 다음과 같이 오류가 발생하는 것을 방지하기 위해 인증 방식을 native 로 변경
Authentication plugin 'caching_sha2_password' cannot be loaded:
dlopen(/usr/local/mysql/lib/plugin/caching_sha2_password.so, 2): image not found
mysql> alter user 'sysbench'@'localhost' identified with mysql_native_password by 'P@ssw0rd';
Query OK, 0 rows affected (0.01 sec)
4. 위에서 생성한 테스트용 DB에 Table 및 데이터 생성
## Root 파티션에 20GB 정도 여유 공간이 있어야 합니다.
[root@localhost ~]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=P@ssw0rd --mysql-db=sysbench --table-size=5000000 --tables=5 /usr/share/sysbench/oltp_read_write.lua prepare
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Creating table 'sbtest1'...
Inserting 5000000 records into 'sbtest1'
Creating a secondary index on 'sbtest1'...
Creating table 'sbtest2'...
Inserting 5000000 records into 'sbtest2'
Creating a secondary index on 'sbtest2'...
Creating table 'sbtest3'...
Inserting 5000000 records into 'sbtest3'
Creating a secondary index on 'sbtest3'...
Creating table 'sbtest4'...
Inserting 5000000 records into 'sbtest4'
Creating a secondary index on 'sbtest4'...
Creating table 'sbtest5'...
Inserting 5000000 records into 'sbtest5'
Creating a secondary index on 'sbtest5'...
5. Read/Write 테스트
[root@localhost ~]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=P@ssw0rd --mysql-db=sysbench --threads=20 --report-interval=5 --table-size=5000000 --tables=5 /usr/share/sysbench/oltp_read_write.lua run
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Running the test with following options:
Number of threads: 20
Report intermediate results every 5 second(s)
Initializing random number generator from current time
Initializing worker threads...
Threads started!
[ 5s ] thds: 20 tps: 1109.78 qps: 22252.70 (r/w/o: 15581.85/4447.30/2223.55) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
[ 10s ] thds: 20 tps: 1126.83 qps: 22531.60 (r/w/o: 15775.02/4503.12/2253.46) lat (ms,95%): 28.16 err/s: 0.00 reconn/s: 0.00
SQL statistics:
queries performed:
read: 156856
write: 44816
other: 22408
total: 224080
transactions: 11204 (1118.39 per sec.)
queries: 224080 (22367.75 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 10.0164s
total number of events: 11204
Latency (ms):
min: 2.91
avg: 17.86
max: 148.24
95th percentile: 28.16
sum: 200092.28
Threads fairness:
events (avg/stddev): 560.2000/7.98
execution time (avg/stddev): 10.0046/0.00
6. 테스트 데이터 삭제
[root@localhost ~]# sysbench --mysql-host=localhost --mysql-port=3306 --mysql-user=sysbench --mysql-password=P@ssw0rd --mysql-db=sysbench --table-size=5000000 --tables=5 /usr/share/sysbench/oltp_read_write.lua cleanup
sysbench 1.0.17 (using system LuaJIT 2.0.4)
Dropping table 'sbtest1'...
Dropping table 'sbtest2'...
Dropping table 'sbtest3'...
Dropping table 'sbtest4'...
Dropping table 'sbtest5'...
'Database' 카테고리의 다른 글
| How to install mysql (0) | 2023.06.11 |
|---|
