1.安装sysbench
yum -y install make automake libtool pkgconfig libaio-devel
yum -y install mariadb-devel
yum -y install postgresql-devel
tar -xvzf sysbench-1.0.14.tar.gz
cd sysbench-1.0.14/
./autogen.sh
./configure
make -j
make install
2.调整配置(不修改线程数过大时会报错)
ulimit -HSn 1000000
ulimit -m unlimited
3.导入数据
create database sbtest;
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --tables=32 --table-size=10000000 prepare
4.数据预热与统计信息收集
SELECT count(pad) from sbtest1 use index(k_1);
SELECT count(pad) from sbtest2 use index(k_2);
SELECT count(pad) from sbtest3 use index(k_3);
SELECT count(pad) from sbtest4 use index(k_4);
SELECT count(pad) from sbtest5 use index(k_5);
SELECT count(pad) from sbtest6 use index(k_6);
SELECT count(pad) from sbtest7 use index(k_7);
SELECT count(pad) from sbtest8 use index(k_8);
SELECT count(pad) from sbtest9 use index(k_9);
SELECT count(pad) from sbtest10 use index(k_10);
SELECT count(pad) from sbtest11 use index(k_11);
SELECT count(pad) from sbtest12 use index(k_12);
SELECT count(pad) from sbtest13 use index(k_13);
SELECT count(pad) from sbtest14 use index(k_14);
SELECT count(pad) from sbtest15 use index(k_15);
SELECT count(pad) from sbtest16 use index(k_16);
SELECT count(pad) from sbtest17 use index(k_17);
SELECT count(pad) from sbtest18 use index(k_18);
SELECT count(pad) from sbtest19 use index(k_19);
SELECT count(pad) from sbtest20 use index(k_20);
SELECT count(pad) from sbtest21 use index(k_21);
SELECT count(pad) from sbtest22 use index(k_22);
SELECT count(pad) from sbtest23 use index(k_23);
SELECT count(pad) from sbtest24 use index(k_24);
SELECT count(pad) from sbtest25 use index(k_25);
SELECT count(pad) from sbtest26 use index(k_26);
SELECT count(pad) from sbtest27 use index(k_27);
SELECT count(pad) from sbtest28 use index(k_28);
SELECT count(pad) from sbtest29 use index(k_29);
SELECT count(pad) from sbtest30 use index(k_30);
SELECT count(pad) from sbtest31 use index(k_31);
SELECT count(pad) from sbtest32 use index(k_32);
ANALYZE TABLE sbtest1;
ANALYZE TABLE sbtest2;
ANALYZE TABLE sbtest3;
ANALYZE TABLE sbtest4;
ANALYZE TABLE sbtest5;
ANALYZE TABLE sbtest6;
ANALYZE TABLE sbtest7;
ANALYZE TABLE sbtest8;
ANALYZE TABLE sbtest9;
ANALYZE TABLE sbtest10;
ANALYZE TABLE sbtest11;
ANALYZE TABLE sbtest12;
ANALYZE TABLE sbtest13;
ANALYZE TABLE sbtest14;
ANALYZE TABLE sbtest15;
ANALYZE TABLE sbtest16;
ANALYZE TABLE sbtest17;
ANALYZE TABLE sbtest18;
ANALYZE TABLE sbtest19;
ANALYZE TABLE sbtest20;
ANALYZE TABLE sbtest21;
ANALYZE TABLE sbtest22;
ANALYZE TABLE sbtest23;
ANALYZE TABLE sbtest24;
ANALYZE TABLE sbtest25;
ANALYZE TABLE sbtest26;
ANALYZE TABLE sbtest27;
ANALYZE TABLE sbtest28;
ANALYZE TABLE sbtest29;
ANALYZE TABLE sbtest30;
ANALYZE TABLE sbtest31;
ANALYZE TABLE sbtest32;
5.开始压测
点查
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_point_select --tables=32 --table-size=10000000 run
综合只读查询
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_read_only --tables=32 --table-size=10000000 run
更新带索引列命令
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_update_index --tables=32 --table-size=10000000 run
更新不带索引列命令
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_update_non_index --tables=32 --table-size=10000000 run
纯写测试
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_write_only --tables=32 --table-size=10000000 run
读写复合测试
sysbench --mysql-host=192.168.0.1 --mysql-port=4000 --mysql-db=sbtest --mysql-user=root --time=600 --threads=256 --report-interval=10 --db-driver=mysql oltp_read_write --tables=32 --table-size=10000000 run