mysql_MySQL에 대한 sysbench 스트레스 테스트에 대한 자세한 튜토리얼

머리말

웹사이트의 전반적인 성능을 벤치마킹할 때 유명한 ab(Apache 벤치), http_load 및 기타 도구와 같은 다양한 도구를 사용할 수 있습니다. 여기서는 사용법에 중점을 두지 않습니다. 알고 싶다면 온라인에서 직접 답변을 찾을 수 있습니다.

핵심은 MySQL 벤치마크 테스트를 어떻게 진행하느냐이다. mysqlslap, sysbench, Super Smack 등 우리가 선택할 수 있는 도구가 많다. 그 중 mysqlslap의 사용법은 MySQL 공식 홈페이지에 소개되어 있다. Super Smack은 강력한 서버 스트레스 테스트 도구이고, sysbench는 MySQL 벤치마크 테스트를 수행하는 데 매우 유용한 도구입니다.

sysbench

sysbench는 CPU/메모리/스레드/IO/데이터베이스 등에 대한 성능 테스트를 수행할 수 있는 오픈소스 멀티스레드 성능 테스트 도구입니다. 데이터베이스는 현재 MySQL/Oracle/PostgreSQL을 지원합니다.

주로 다음과 같은 방법으로 테스트를 진행합니다.

1. CPU 성능

2. 디스크 IO 성능

3. 스케줄러 성능

4. 메모리 할당 및 전송 속도

5. POSIX 스레드 성능

6. 데이터베이스 성능(OLTP 벤치마크 테스트)

sysbench의 데이터베이스 OLTP 테스트는 MySQL, PostgreSQL, Oracle을 지원하며 현재 주로 Linux 운영 체제에서 사용됩니다. 오픈 소스 커뮤니티에서는 sysbench를 Windows로 이식하고 SQL Server 벤치마크 테스트를 지원합니다.

더 이상 고민하지 말고 시작해 보겠습니다.

1. sysbench 설치

mysql 버전: mysql-community-server-5.6.29

OS: CentOS 6.7 X86_64

Sysbench 0.5에는 Lua 스크립트와 결합된 oltp 테스트와 이 기사에서 다룰 추가 숨겨진 옵션을 포함하여 버전 0.4와 비교하여 몇 가지 변경 사항이 있습니다.

// 先安装编译依赖环境
$ 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

PostgreSQL 또는 Oracle을 테스트해야 하는 경우 구성 시 –with-oracle 또는 –with-pgsql 매개변수

를 추가해야 합니다.

2. sysbench를 사용하여 mysql 스트레스 테스트

2.1 읽기 전용 예시

./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]

테스트를 시작하기 전에 준비를 사용하여 테이블과 데이터를 준비하고, 실행하여 실제 스트레스 테스트를 수행하고, 정리하여 데이터와 테이블을 지워야 합니다. 실제 준비된 테이블 구조:

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)

위 테스트 명령은 다음을 나타냅니다. mysql의 oltp 벤치마크 테스트, 테이블 수는 10개, 각 테이블의 행 수는 약 500,000개(삽입 수만큼 삭제 수)이며 비트랜잭션입니다. 읽기 전용 테스트이며 60초 동안 지속됩니다. 동시 스레드 수는 12개입니다.

설명이 필요한 옵션:

mysql-db=dbtest1a: 테스트에 사용되는 대상 데이터베이스입니다. 이 라이브러리 이름은 미리 생성되어야 합니다.

--oltp-tables-count=10: 생성된 테이블 수

--oltp-table-size=500000: 각 테이블에서 생성된 레코드 행 수

--oltp-dist-type=uniform: 무작위 샘플링 유형을 지정합니다. 선택적 값은 균일(균등 분포), 가우스(가우스 분포), 특수(공간 분포)입니다. 기본값은 특별

--oltp-read-only=off: 단순히 읽기 전용 SQL을 생성하는 것 이상, 즉 oltp.lua를 사용할 때 혼합 읽기-쓰기 모드가 사용됨을 나타냅니다. 기본값은 off입니다. on으로 설정하면 SQL 업데이트, 삭제, 삽입이 생성되지 않습니다.

--oltp-test-mode=nontrx: 실행 모드, 여기서는 트랜잭션이 아닙니다. 선택적 값은 simple, complex, nontrx입니다. 기본값은 복잡합니다

       간단: 간단한 쿼리, SELECT c FROM sbtest WHERE id=N<code>SELECT c FROM sbtest WHERE id=N<br>

      복잡(고급 트랜잭션) : 트랜잭션 모드는 트랜잭션을 시작하고 종료하기 전에 시작과 커밋을 추가합니다. 트랜잭션에는 포인트 쿼리, 범위 쿼리, 정렬 쿼리, 업데이트 등 여러 문이 있을 수 있습니다. 삭제, 삽입 등을 할 수 있으며 테스트 테이블의 데이터가 파괴되지 않도록 하기 위해 이 모드에서는 레코드가 삭제된 후 동일한 트랜잭션에 동일한 레코드가 추가됩니다.

      nontrx(비트랜잭션): 단순하지만 업데이트/삽입 등의 작업을 수행할 수 있으므로 지속적인 비교 스트레스 테스트를 수행할 경우 정리하고 다시 준비해야 할 수도 있습니다.

--oltp-skip-trx=[on|off] : 시작/커밋 문을 생략합니다. 기본값은 꺼짐

--rand-init=on: 데이터를 무작위로 초기화할지 여부입니다. 무작위로 초기화되지 않으면 초기화된 데이터의 각 행의 내용은 기본 키를 제외하고 완전히 동일합니다.

     --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: 정확히 말하면 이 아이템의 이면에는 TPS가 있습니다. 하지만 -oltp-skip-trx=on을 사용하면 트랜잭션 수는 항상 0이고, 총 시간을 총 이벤트 수로 나눠야 tps를 얻을 수 있습니다. (실제로는 읽기 tps와 쓰기 tps로 나눌 수도 있습니다.)

read/write requests: 처리량 QPS를 얻기 위해 총 시간으로 나눕니다

물론 시스템 수준의 CPU, IO, MEM 관련 지표도 있습니다

Sysbench는 여기서 소개하지 않은 파일 시스템 IO, CPU 성능, 메모리 할당 및 전송 속도도 테스트할 수 있습니다.

요약

sysbench의 단점은 시뮬레이션된 테이블 구조가 너무 단순하고 tpcc-mysql만큼 완전한 트랜잭션 시스템이 아니라는 것입니다. 그러나 sysbench에서 사용하는 환경 매개변수 제한이 동일하기 때문에 성능 스트레스 테스트 비교에는 여전히 매우 유용합니다. 이상이 이 글의 전체 내용입니다. 모든 분들의 공부나 업무에 조금이나마 도움이 되었으면 좋겠습니다. 궁금한 점이 있으시면 메시지를 남겨주세요.