MySQL-sysbench

1. 源码编译安装

?

01 02 03 04 05 06 07 08 09 10 11 12 13 14

RHEL/CentOS     yum -y install make automake libtool pkgconfig libaio-devel     # For MySQL support, replace with mysql-devel on RHEL/CentOS 5     yum -y install mariadb-devel openssl-devel     # For PostgreSQL support     yum -y install postgresql-devel -----------------------------------------------------------------------------     ./autogen.sh     $(which mysql_config) && $(which pg_config) && ./configure --prefix=/ups/app/sysbench --with-mysql --with-pgsql   ./configure --prefix=/ups/app/sysbench --with-mysql --with-pgsql --with-pgsql-includes=/ups/app/postgresql/pgsql-12/include  --with-pgsql-libs=/ups/app/postgresql/pgsql-12/lib           make -j     make install

2. 服务器硬件性能测试

?

01	sysbench --threads=10  memory --memory-block-size=8k --memory-total-size=6G --memory-oper=read run

?

01 02

# IO测试 sysbench --threads=10  fileio --file-num=20 --file-total-size=20G --file-test-mode=rndrw run

3. mysql性能测试

3.1 创建测试数据

?

01 02 03 04 05 06 07

mycli -e "create database if not exists sysbentest"   sysbench --db-driver=mysql --mysql-user=root --mysql-password=root \   --mysql-socket=/ups/app/mysql/mysql3308/logs/mysql3308.sock \   --mysql-db=sysbentest --range_size=100 --table_size=10000 \   --tables=2 --threads=1 --events=0 --time=60 \   --rand-type=uniform /ups/app/sysbench/share/sysbench/oltp_read_only.lua prepare

3.2 测试

?

01 02 03 04 05

sysbench --db-driver=mysql --mysql-user=root --mysql-password=root \   --mysql-socket=/ups/app/mysql/mysql3308/logs/mysql3308.sock \   --mysql-db=sysbentest --range_size=100 --table_size=10000 \   --tables=2 --threads=1 --events=0 --time=60 \   --rand-type=uniform /ups/app/sysbench/share/sysbench/oltp_read_only.lua run

-- 每5秒钟输出一次指标

?

01 02 03 04 05

sysbench /ups/app/sysbench/share/sysbench/oltp_read_only.lua --db-driver=mysql --mysql-user=root --mysql-password=root \   --mysql-socket=/ups/app/mysql/mysql3308/logs/mysql3308.sock \   --mysql-db=sysbentest --range_size=100 --table_size=10000 \   --tables=2 --threads=2 --events=0 --time=60 \   --rand-type=uniform --report-interval=5 run

3.3 sysbench自带lua脚本

3.4 定制lua脚本测试

function thread_init ()
    drv = sysbench.sql.driver()
    con = drv:connect()
end

function event ()
    con:query ("SELECT 1"
)
end

function therad_done ()
    con:disconnect()
end

?

01 02 03

sysbench test.lua --mysql-host=localhost --mysql-user=root --mysql-password=root \ --mysql-socket=/ups/app/mysql/mysql3308/logs/mysql3308.sock \ --mysql-db=sysbentest run