MySQL-sysbench

1. 源码编译安装

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. 服务器硬件性能测试

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

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

3. mysql性能测试

3.1 创建测试数据

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 测试

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秒钟输出一次指标

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

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