반응형

MySQL 부하 테스트 (MySQL, MariaDB, Percona)

 

설치

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

 

[root@OpenSource ~]# which sysbench

/usr/bin/sysbench

[root@OpenSource ~]# sysbench --version

sysbench 1.0.20

 

[root@OpenSource sysbench]# cd /usr/share/sysbench

[root@OpenSource sysbench]# ls

bulk_insert.lua  oltp_delete.lua  oltp_point_select.lua  oltp_read_write.lua    oltp_update_non_index.lua  select_random_points.lua  tests

oltp_common.lua  oltp_insert.lua  oltp_read_only.lua     oltp_update_index.lua  oltp_write_only.lua        select_random_ranges.lua

 

[root@OpenSource ~]# alias | grep client

alias client_commu5.7='/engine/mysql/commu5.7/bin/mysql -uroot -p1 -S /data/mysql/commu5.7/mysql.sock'

alias client_commu8.0='/engine/mysql/commu8.0/bin/mysql -uroot -p1 -S /data/mysql/commu8.0/mysql.sock'

alias client_enter5.7='/engine/mysql/enter5.7/bin/mysql -uroot -p1 -S /data/mysql/enter5.7/mysql.sock'

alias client_enter8.0='/engine/mysql/enter8.0/bin/mysql -uroot -p1 -S /data/mysql/enter8.0/mysql.sock'

alias client_maria10.3='/engine/mariadb/maria10.3/bin/mysql -uroot -p1 -S /data/mariadb/maria10.3/mysql.sock'

alias client_maria10.5='/engine/mariadb/maria10.5/bin/mysql -uroot -p1 -S /data/mariadb/maria10.5/mysql.sock'

alias client_maria10.6='/engine/mariadb/maria10.6/bin/mysql -uroot -p1 -S /data/mariadb/maria10.6/mysql.sock'

alias client_maria10.7='/engine/mariadb/maria10.7/bin/mysql -uroot -p1 -S /data/mariadb/maria10.7/mysql.sock'

alias client_percona5.6='/engine/percona/percona5.6/bin/mysql -uroot -p1 -S /data/percona/percona5.6/mysql.sock'

alias client_percona5.7='/engine/percona/percona5.7/bin/mysql -uroot -p1 -S /data/percona/percona5.7/mysql.sock'

alias client_percona8.0='/engine/percona/percona8.0/bin/mysql -uroot -p1 -S /data/percona/percona8.0/mysql.sock'

 

 

유저 / DB 생성

 

MySQL 5.7 ~ 8.0

client_commu5.7 -e "create database sysbench; create user 'sysbench'@'%' identified WITH mysql_native_password by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_commu8.0 -e "create database sysbench; create user 'sysbench'@'%' identified WITH mysql_native_password by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_enter5.7 -e "create database sysbench; create user 'sysbench'@'%' identified WITH mysql_native_password by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_enter8.0 -e "create database sysbench; create user 'sysbench'@'%' identified WITH mysql_native_password by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

 

client_commu5.7 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_commu8.0 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_enter5.7 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_enter8.0 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

 

MariaDB 10.3 ~ 10.7

client_maria10.3 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_maria10.5 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_maria10.6 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_maria10.7 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

 

client_maria10.3 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_maria10.5 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_maria10.6 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_maria10.7 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

 

percona 5.6 ~ 8.0

client_percona5.6 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_percona5.7 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

client_percona8.0 -e "create database sysbench; create user 'sysbench'@'%' identified by 'sysbench'; grant all on sysbench.* to 'sysbench'@'%' ; flush privileges;"

 

client_percona5.6 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_percona5.7 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

client_percona8.0 -e "select user , host from mysql.user where user = 'sysbench' ; show databases ;"

 

 

Sysbench

 

1. 테스트 데이터 생성

 

sysbench /usr/share/sysbench/oltp_read_write.lua \
--time=20 --max-requests=0 \
--table_size=100000 --tables=15 --report-interval=1 \
--mysql-host=localhost --mysql-port=3304 --mysql-socket=/data/mysql/commu5.7/mysql.sock \
--db-driver=mysql \
--mysql-db=sysbench --mysql-user=sysbench --mysql-password=sysbench \
--threads=100 \
prepare

 

2. 수행

 

sysbench /usr/share/sysbench/oltp_read_write.lua \
--time=20 --max-requests=0 \
--table_size=100000 --tables=15 --report-interval=1 \
--mysql-host=localhost --mysql-port=3304 --mysql-socket=/data/mysql/commu5.7/mysql.sock \
--db-driver=mysql \
--mysql-db=sysbench --mysql-user=sysbench --mysql-password=sysbench \
--threads=100 \
run

 

3. 테스트 데이터 삭제

 

sysbench /usr/share/sysbench/oltp_read_write.lua \
--time=20 --max-requests=0 \
--table_size=100000 --tables=15 --report-interval=1 \
--mysql-host=localhost --mysql-port=3304 --mysql-socket=/data/mysql/commu5.7/mysql.sock \
--db-driver=mysql \
--mysql-db=sysbench --mysql-user=sysbench --mysql-password=sysbench \
--threads=100 \
cleanup
 
반응형

+ Recent posts