Detailed tutorial on sysbench stress testing for mysql_MySQL

Foreword

When benchmarking the overall performance of the website, you can use a variety of tools, such as the famous ab (Apache bench), http_load and other tools. We don’t focus on their use here. If you want to know, you can find the answer online by yourself.

The key point is how to conduct MySQL benchmark testing. There are many tools for us to choose from, such as mysqlslap, sysbench, Super Smack, etc. Among them, the use of mysqlslap is introduced on the MySQL official website. Super Smack is a powerful tool for server stress testing. , then sysbench is a very beautiful tool for us to conduct MySQL benchmark testing.

sysbench

sysbench is an open source multi-threaded performance testing tool that can perform performance testing on CPU/memory/threads/IO/database, etc. The database currently supports MySQL/Oracle/PostgreSQL;

It mainly includes testing in the following ways:

1. CPU performance

2. Disk io performance

3. Scheduler performance

4. Memory allocation and transmission speed

5. POSIX thread performance

6. Database performance (OLTP benchmark test)

sysbench’s database OLTP test supports MySQL, PostgreSQL, and Oracle. It is currently mainly used for Linux operating systems. The open source community has ported sysbench to Windows and supports SQL Server benchmark tests.

Without further ado, let’s get started.

1. sysbench installation

mysql version: mysql-community-server-5.6.29

OS: CentOS 6.7 X86_64

sysbench 0.5 has some changes compared to version 0.4, including the oltp test combined with lua scripts, and some more hidden options, which this article will cover.

// 先安装编译依赖环境
$ sudo yum install gcc gcc-c++ automake make libtool mysql-community-devel
$ cd /tmp && git clone https://github.com/akopytov/sysbench.git
$ cd /tmp/sysbench && ./autogen.sh
$ ./configure --prefix=/usr/local/sysbench-0.5
$ ./make && sudo make install
// 0.5版本需要oltp.lua测试脚本
// 如果是rpm包方式安装的，在 /usr/share/doc/sysbench/tests/db/ 下可找到
$ cd /usr/local/sysbench && sudo mkdir -p share/tests/db
$ cp /tmp/sysbench/sysbench/tests/db/*.lua share/tests/db/
$ ./bin/sysbench --version
sysbench 0.5

If you need to test PostgreSQL or Oracle, you need to add –with-oracle or –with-pgsql parameters when configure

2. Use sysbench to stress test mysql

2.1 Read-only example

./bin/sysbench --test=./share/tests/db/oltp.lua \
--mysql-host=10.0.201.36 --mysql-port=8066 --mysql-user=ecuser --mysql-password=ecuser \
--mysql-db=dbtest1a --oltp-tables-count=10 --oltp-table-size=500000 \
--report-interval=10 --oltp-dist-type=uniform --rand-init=on --max-requests=0 \
--oltp-test-mode=nontrx --oltp-nontrx-mode=select \
--oltp-read-only=on --oltp-skip-trx=on \
--max-time=120 --num-threads=12 \
[prepare|run|cleanup]

Pay attention to the last line. Before starting a test, you need to use prepare to prepare the table and data, run to perform the real stress test, and cleanup to clear the data and tables. Actual prepared table structure:

mysql> desc dbtest1a.sbtest1;
+-------+------------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra     |
+-------+------------------+------+-----+---------+----------------+
| id  | int(10) unsigned | NO  | PRI | NULL  | auto_increment |
| k   | int(10) unsigned | NO  | MUL | 0    |        |
| c   | char(120)    | NO  |   |     |        |
| pad  | char(60)     | NO  |   |     |        |
+-------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

The above test command represents: oltp benchmark test on mysql, the number of tables is 10, the number of rows in each table is about 50w (almost as many deletes as there are inserts), and it is a non-transactional read-only test, lasting 60s, concurrent threads Number 12.

Options that need explanation:

mysql-db=dbtest1a: The target database used for testing. This database name must be created in advance

 ​ --oltp-tables-count=10: The number of generated tables

--oltp-table-size=500000: The number of record rows generated by each table

​​ --oltp-dist-type=uniform: Specify the random sampling type. Optional values ​​are uniform (uniform distribution), Gaussian (Gaussian distribution), special (spatial distribution). The default is special

​​ --oltp-read-only=off: Indicates that more than just read-only SQL is generated, that is, when using oltp.lua, a mixed read-write mode will be used. The default is off. If set to on, update, delete, and insert SQL will not be generated.

--oltp-test-mode=nontrx: Execution mode, here is non-transactional. Optional values ​​are simple, complex, and nontrx. The default is complex

Simple: Simple query, Select C FROM SBTEST WHERE ID = N <br />

: The transaction mode adds begin and commit before starting and ending the transaction. There can be multiple statements in a transaction, such as point query, range query, sort query, update, delete, insert, etc., and In order not to destroy the data of the test table, after deleting a record in this mode, an identical record will be added in the same transaction. <br />              

: Similar to simple, but can perform operations such as update/insert, so if you do continuous comparative stress testing, you may need to cleanup and prepare again. <br />

: Omit the begin/commit statement. The default is off D an an --rand-init = On : Whether to initialize the data randomly, if it is not randomized, then the initial good data Each line is except the same as the main key.

--num-threads=12： 并发线程数，可以理解为模拟的客户端并发连接数

--report-interval=10：表示每10s输出一次测试进度报告

--max-requests=0：压力测试产生请求的总数，如果以下面的max-time来记，这个值设为0

--max-time=120：压力测试的持续时间，这里是2分钟。

注意，针对不同的选项取值就会有不同的子选项。比如oltp-dist-type=special，就有比如oltp-dist-pct=1、oltp-dist-res=50两个子选项，代表有50%的查询落在1%的行（即热点数据）上，另外50%均匀的(sample uniformly)落在另外99%的记录行上。

再比如oltp-test-mode=nontrx时, 就可以有oltp-nontrx-mode，可选值有select（默认）, update_key, update_nokey, insert, delete，代表非事务式模式下使用的测试sql类型。

以上代表的是一个只读的例子，可以把num-threads依次递增（16,36,72,128,256,512），或者调整my.cnf参数，比较效果。另外需要注意的是，大部分mysql中间件对事务的处理，默认都是把sql发到主库执行，所以只读测试需要加上oltp-skip-trx=on来跳过测试中的显式事务。

ps1: 只读测试也可以使用share/tests/db/select.lua进行，但只是简单的point select。

ps2: 我在用sysbench压的时候，在mysql后端会话里有时看到大量的query cache lock，如果使用的是uniform取样，最好把查询缓存关掉。当然如果是做两组性能对比压测，因为都受这个因素影响，关心也不大。

2.2 混合读写

读写测试还是用oltp.lua，只需把--oltp-read-only等于off。

./bin/sysbench --test=./share/tests/db/oltp.lua --mysql-host=10.0.201.36 --mysql-port=8066 --mysql-user=ecuser --mysql-password=ecuser --mysql-db=dbtest1a --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=120 --num-threads=128 prepare
./bin/sysbench --test=./share/tests/db/oltp.lua --mysql-host=10.0.201.36 --mysql-port=8066 --mysql-user=ecuser --mysql-password=ecuser --mysql-db=dbtest1a --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=120 --num-threads=128 run
./bin/sysbench --test=./share/tests/db/oltp.lua --mysql-host=10.0.201.36 --mysql-port=8066 --mysql-user=ecuser --mysql-password=ecuser --mysql-db=dbtest1a --oltp-tables-count=10 --oltp-table-size=500000 --report-interval=10 --rand-init=on --max-requests=0 --oltp-test-mode=nontrx --oltp-nontrx-mode=select --oltp-read-only=off --max-time=120 --num-threads=128 cleanup

然而oltp-test-mode=nontrx一直没有跟着我预期的去走，在mysql general log里面看到的sql记录与complex模式相同。所以上面示例中的--oltp-test-mode=nontrx --oltp-nontrx-mode=select可以删掉。

update:

原来sysbench 0.5版本去掉了这个选项，因为作者正在准备1.0版本，所以也就没有更新0.5版本的doc。网上的博客漫天飞，就没有一个提出来的，也是没谁了。

分析一下oltp.lua脚本内容，可以清楚单个事务各操作的默认比例：select:update_key:update_non_key:delete:insert = 14:1:1:1:1，可通过oltp-point-selects、oltp-simple-ranges、oltp-sum-ranges、oltp-order-ranges、oltp-distinct-ranges，oltp-index-updates、oltp-non-index-updates这些选项去调整读写权重。

同只读测试一样，在atlas,mycat这类中间件测试中如果不加oltp-skip-trx=on，那么所有查询都会发往主库，但如果在有写入的情况下使用--oltp-skip-trx=on跳过BEGIN和COMMIT，会出现问题：

ALERT: failed to execute MySQL query: INSERT INTO sbtest4 (id, k, c, pad) VALUES (48228, 47329, '82773802508-44916890724-85859319254-67627358653-96425730419-64102446666-75789993135-91202056934-68463872307-28147315305', '13146850449-23153169696-47584324044-14749610547-34267941374'):
ALERT: Error 1062 Duplicate entry ‘48228' for key ‘PRIMARY'
FATAL: failed to execute function `event': (null)

原因也很容易理解，每个线程将选择一个随机的表，不加事务的情况下高并发更新（插入）出现重复key的概率很大，但我们压测不在乎这些数据，所以需要跳过这个错误--mysql-ignore-errors=1062，这个问题老外有出过打补丁的方案允许--mysql-ignore-duplicates=on，但作者新加入的忽略错误码这个功能已经取代了它。mysql-ignore-errors选项是0.5版本加入的，但目前没有文档标明。

这里不得不佩服老外的办事效率和责任心，提个疑惑能立马得到回复，反观国内，比如在atlas,mycat项目里提到问题到现在都没人搭理。。。

2.3 只更新

如果基准测试的时候，你只想比较两个项目的update（或insert）效率，那可以不使用oltp脚本，而直接改用update_index.lua：

./bin/sysbench --test=./share/tests/db/update_index.lua \
--mysql-host=10.0.201.36 --mysql-port=8066 --mysql-user=ecuser --mysql-password=ecuser \
--mysql-db=dbtest1a --oltp-tables-count=10 --oltp-table-size=500000 \
--report-interval=10 --rand-init=on --max-requests=0 \
--oltp-read-only=off --max-time=120 --num-threads=128 \
[ prepare | run | cleanup ]

此时像oltp-read-only=off许多参数都失效了。需要说明的是这里 (非)索引更新，不是where条件根据索引去查找更新，而是更新索引列上的值。

三. 结果解读

sysbench 0.5: multi-threaded system evaluation benchmark
Running the test with following options:
Number of threads: 128
Report intermediate results every 20 second(s)
Initializing random number generator from timer.
Random number generator seed is 0 and will be ignored
Initializing worker threads...
Threads started!
[ 20s] threads: 128, tps: 2354.54, reads: 33035.89, writes: 9423.39, response time: 66.80ms (95%), errors: 0.00, reconnects: 0.00
[ 40s] threads: 128, tps: 2377.75, reads: 33274.26, writes: 9507.55, response time: 66.88ms (95%), errors: 0.00, reconnects: 0.00
[ 60s] threads: 128, tps: 2401.35, reads: 33615.30, writes: 9607.40, response time: 66.40ms (95%), errors: 0.00, reconnects: 0.00
[ 80s] threads: 128, tps: 2381.20, reads: 33331.50, writes: 9522.55, response time: 67.30ms (95%), errors: 0.00, reconnects: 0.00
[ 100s] threads: 128, tps: 2388.85, reads: 33446.10, writes: 9556.35, response time: 67.00ms (95%), errors: 0.00, reconnects: 0.00
[ 120s] threads: 128, tps: 2386.40, reads: 33421.35, writes: 9545.35, response time: 66.94ms (95%), errors: 0.00, reconnects: 0.00
OLTP test statistics:
  queries performed:
    read:              4003048 //总select数量
    write:              1143728 //总update、insert、delete语句数量
    other:              571864  //commit、unlock tables以及其他mutex的数量
    total:              5718640
  transactions:            285932 (2382.10 per sec.) //通常需要关注的数字(TPS)
  read/write requests:         5146776 (42877.85 per sec.)
  other operations:          571864 (4764.21 per sec.)
  ignored errors:           0   (0.00 per sec.) //忽略的错误数
  reconnects:             0   (0.00 per sec.)
General statistics:
  total time:             120.0334s //即max-time指定的压测实际
  total number of events:       285932  //总的事件数，一般与transactions相同
  total time taken by event execution: 15362.6623s
  response time:
     min:                 17.60ms
     avg:                 53.73ms //95%的语句的平均响应时间
     max:                252.90ms
     approx. 95 percentile:       66.88ms
Threads fairness:
  events (avg/stddev):      2233.8438/9.04
  execution time (avg/stddev):  120.0208/0.01

我们一般关注的用于绘图的指标主要有：

     response time avg: 平均响应时间。（后面的95%的大小可以通过--percentile=98的方式去更改）

transactions: To be precise, it is the TPS behind this item. But if -oltp-skip-trx=on is used, the number of transactions is always 0. You need to divide the total time by the total number of events to get tps (actually it can also be divided into read tps and write tps)

​​ read/write requests: Divide it by the total time to get the throughput QPS

Of course there are some system-level cpu, io, mem related indicators

sysbench can also test file system IO, CPU performance, and memory allocation and transmission speed, which will not be introduced here.

Summary

The disadvantage of sysbench is that the simulated table structure is too simple and is not a complete transaction system like tpcc-mysql. But it is still very useful for performance stress testing comparison, because the environmental parameter limits used by sysbench are the same. The above is the entire content of this article. I hope the content of this article can bring some help to everyone's study or work. If you have any questions, you can leave a message to communicate.