sysbench壓測實踐

軟體地址：

Redhat/CentOS採用如下方式安裝：

• curl -s | sudo bash
  sudo yum -y install sysbench

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.177.130 --mysql-port=3306 --mysql-db=testdb \

--mysql-user='root' --mysql-password='123456' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=500000 \

--oltp-tables-count=8 --num-threads=4 --max-time=1800 --max-requests=0 --report-interval=1 prepare  

需要先做prepare， 否則會提示表不存在的錯誤

另外，mysql 8密碼認證方式相比5.7有所變化，需要修改使用者預設的密碼認證方式

 ALTER USER 'root'@'%' IDENTIFIED BY '123456' PASSWORD EXPIRE NEVER;   #修改加密規則 

 ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';   #更新一下使用者的密碼 

[root@server2 ~]# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.177.130 --mysql-port=3306 --mysql-db=testdb \

> --mysql-user='root' --mysql-password='123456' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=500000 \

> --oltp-tables-count=8 --num-threads=4 --max-time=1800 --max-requests=0 --report-interval=1 prepare  

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.

WARNING: --num-threads is deprecated, use --threads instead

WARNING: --max-time is deprecated, use --time instead

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Creating table 'sbtest1'...

Inserting 500000 records into 'sbtest1'

Creating secondary indexes on 'sbtest1'...

Creating table 'sbtest2'...

Inserting 500000 records into 'sbtest2'

Creating secondary indexes on 'sbtest2'...

Creating table 'sbtest3'...

Inserting 500000 records into 'sbtest3'

Creating secondary indexes on 'sbtest3'...

Creating table 'sbtest4'...

Inserting 500000 records into 'sbtest4'

Creating secondary indexes on 'sbtest4'...

Creating table 'sbtest5'...

Inserting 500000 records into 'sbtest5'

Creating secondary indexes on 'sbtest5'...

Creating table 'sbtest6'...

Inserting 500000 records into 'sbtest6'

Creating secondary indexes on 'sbtest6'...

Creating table 'sbtest7'...

Inserting 500000 records into 'sbtest7'

Creating secondary indexes on 'sbtest7'...

Creating table 'sbtest8'...

Inserting 500000 records into 'sbtest8'

接下來壓測：

sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.177.130 --mysql-port=3306 --mysql-db=testdb \

--mysql-user='root' --mysql-password='123456' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=500000 \

--oltp-tables-count=8 --num-threads=4 --max-time=1800 --max-requests=0 --report-interval=1 run  

root@server2 ~]# sysbench --test=oltp --db-driver=mysql --mysql-table-engine=innodb --mysql-host=192.168.177.130 --mysql-port=3306 --mysql-db=testdb --mysql-user='root' --mysql-password='123456' --test=/usr/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-table-size=500000 --oltp-tables-count=8 --num-threads=4 --max-time=1800 --max-requests=0 --report-interval=1 run

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.

WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.

WARNING: --num-threads is deprecated, use --threads instead

WARNING: --max-time is deprecated, use --time instead

sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)

Running the test with following options:

Number of threads: 4

Report intermediate results every 1 second(s)

Initializing random number generator from current time

Initializing worker threads...

Threads started!

[ 1s ] thds: 4 tps: 17.07 qps: 395.50 (r/w/o: 283.58/73.98/37.94) lat (ms,95%): 350.33 err/s: 0.00 reconn/s: 0.00

[ 2s ] thds: 4 tps: 25.89 qps: 526.10 (r/w/o: 367.65/106.67/51.78) lat (ms,95%): 314.45 err/s: 0.00 reconn/s: 0.00

[ 3s ] thds: 4 tps: 40.30 qps: 798.40 (r/w/o: 561.38/156.42/80.61) lat (ms,95%): 150.29 err/s: 0.00 reconn/s: 0.00

[ 4s ] thds: 4 tps: 46.25 qps: 910.32 (r/w/o: 633.86/185.01/91.45) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00

[ 5s ] thds: 4 tps: 47.04 qps: 934.70 (r/w/o: 655.49/184.14/95.07) lat (ms,95%): 164.45 err/s: 0.00 reconn/s: 0.00

[ 6s ] thds: 4 tps: 33.97 qps: 691.36 (r/w/o: 483.55/139.87/67.94) lat (ms,95%): 227.40 err/s: 0.00 reconn/s: 0.00

[ 7s ] thds: 4 tps: 43.04 qps: 844.84 (r/w/o: 590.59/168.17/86.09) lat (ms,95%): 161.51 err/s: 0.00 reconn/s: 0.00

[ 8s ] thds: 4 tps: 35.92 qps: 734.32 (r/w/o: 514.83/147.66/71.84) lat (ms,95%): 179.94 err/s: 0.00 reconn/s: 0.00

[ 9s ] thds: 4 tps: 29.03 qps: 557.67 (r/w/o: 387.46/112.13/58.07) lat (ms,95%): 200.47 err/s: 0.00 reconn/s: 0.00