MySQL基准测试工具

一、基准测试

基准测试(benchmark)是针对系统设计的一种压力测试。

基准测试是简化了的压力测试。

1.1 常见指标

TPS
QPS
响应时间
并发量

1.2 收集与分析数据脚本

收集数据的shell脚本gather.sh

#!/bin/sh

INTERVAL=5

PREFIX=$INTERVAL-sec-status

RUNFILE=/home/benchmarks/running

mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables

while test -e $RUNFILE; do

    file=$(date +%F_%I)

    sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")

    sleep $sleep

    ts="$(date +"TS %s.%N %F %T")"

    loadavg="$(uptime)"

    echo "$ts $loadavg" >> $PREFIX-${file}-status

    mysql -e 'show global status;' >> $PREFIX-${file}-status &

    echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus

    mysql -e 'show engine innodb status;' >> $PREFIX-${file}-innodbstatus &

    echo "$ts $loadavg" >> $PREFIX-${file}-processlist

    mysql -e 'show full processlist;' >> $PREFIX-${file}-processlist &

    echo $ts

done

echo Exiting because $RUNFILE does not exist.

分析数据脚本analyze.sh

#!/bin/sh

awk '

    BEGIN {

        printf "#ts date time load QPS"

        fmt = " %.2f"

    }

    /^TS/ {

        ts = substr($2, 1, index($2, ".")-1);

        load = NF-2;

        diff = ts - prev_ts;

        prev_ts = ts;

        printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load) - 1);

    }

    /Queries/ {

        printf fmt, ($2-Queries)/diff;

        Queries=$2;

    }

' "$@"

二、基准测试工具

2.1 mysqlslap

常用参数

#　mysqlslap --help

--auto-generate-sql 由系统自动生成SQL脚本进行测试

--auto-generate-sql-add-autoincrement 在生成的表中增加自增ID

--auto-generate-sql-load-type=name 指定测试中使用的查询类型

--auto-generate-sql-execute-number= 指定初始化数据时生成的数据量

--concurrency= 指定并发线程的数量

--engine 指定要测试表的存储引擎，可以用逗号分割多个存储引擎

--no-drop 指定不清理测试数据

--iterations 指定测试运行的次数

--number-of-queries 指定每一个线程执行的查询数量

--debug-info 指定输出额外的内存及CPU统计信息

--number-int-cols 指定测试表中包含的INT类型列的数量

--number-char-cols 指定测试表中包含的varchar类型的数量

--create-schema 指定了用于执行测试的数据库的名字

--query 用于指定自定义SQL的脚本

--only-print 并不运行测试脚本，而是把生成的脚本打印出来

示例

mysqlslap \

--concurrency=1,50,100,200 \

--iterations=3 \

--number-int-cols=5 \

--number-char-cols=5 \

--auto-generate-sql \

--auto-generate-sql-add-autoincrement \

--engine=myisam,innodb \

--number-of-queries=10 \

--create-schema=test \

> 1.txt

测试结果

[root@mysql211 ~]# cat 1.txt

Benchmark

	Running for engine myisam

	Average number of seconds to run all queries: 0.001 seconds

	Minimum number of seconds to run all queries: 0.001 seconds

	Maximum number of seconds to run all queries: 0.001 seconds

	Number of clients running queries: 1

	Average number of queries per client: 10

Benchmark

	Running for engine myisam

	Average number of seconds to run all queries: 0.043 seconds

	Minimum number of seconds to run all queries: 0.037 seconds

	Maximum number of seconds to run all queries: 0.052 seconds

	Number of clients running queries: 50

	Average number of queries per client: 0

Benchmark

	Running for engine myisam

	Average number of seconds to run all queries: 0.082 seconds

	Minimum number of seconds to run all queries: 0.082 seconds

	Maximum number of seconds to run all queries: 0.084 seconds

	Number of clients running queries: 100

	Average number of queries per client: 0

Benchmark

	Running for engine myisam

	Average number of seconds to run all queries: 0.245 seconds

	Minimum number of seconds to run all queries: 0.245 seconds

	Maximum number of seconds to run all queries: 0.246 seconds

	Number of clients running queries: 200

	Average number of queries per client: 0

Benchmark

	Running for engine innodb

	Average number of seconds to run all queries: 0.007 seconds

	Minimum number of seconds to run all queries: 0.002 seconds

	Maximum number of seconds to run all queries: 0.010 seconds

	Number of clients running queries: 1

	Average number of queries per client: 10

Benchmark

	Running for engine innodb

	Average number of seconds to run all queries: 0.229 seconds

	Minimum number of seconds to run all queries: 0.212 seconds

	Maximum number of seconds to run all queries: 0.238 seconds

	Number of clients running queries: 50

	Average number of queries per client: 0

Benchmark

	Running for engine innodb

	Average number of seconds to run all queries: 0.519 seconds

	Minimum number of seconds to run all queries: 0.471 seconds

	Maximum number of seconds to run all queries: 0.555 seconds

	Number of clients running queries: 100

	Average number of queries per client: 0

Benchmark

	Running for engine innodb

	Average number of seconds to run all queries: 0.849 seconds

	Minimum number of seconds to run all queries: 0.767 seconds

	Maximum number of seconds to run all queries: 0.909 seconds

	Number of clients running queries: 200

	Average number of queries per client: 0

[root@mysql211 ~]#

2.2 sysbench 0.5

下载

# MySQL上找到的版本太低

# https://dev.mysql.com/downloads/benchmarks.html

wget https://downloads.mysql.com/source/sysbench-0.4.12.14.tar.gz

# https://github.com/akopytov/sysbench/releases

wget https://github.com/akopytov/sysbench/archive/1.0.13.tar.gz

wget https://github.com/akopytov/sysbench/archive/0.5.zip

安装，参照解压后的README.md

# 解压 解压后目录为   ~/sysbench-0.5

unzip sysbench-0.5.zip

cd sysbench-0.5

# 运行后没报错就可以继续

./autogen.sh

# 配置

./configure \

--with-mysql-includes=/usr/local/mysql/include \

--with-mysql-libs=/usr/local/mysql/lib

# 编译安装

make && make install

常用参数

sysbench --help

--test 用于指定所要执行的测试类型，支持以下参数

  fileio - File I/O test 文件系统I/O性能测试

  cpu - CPU performance test CPU性能测试

  memory - Memory functions speed test 内存性能测试

  threads - Threads subsystem performance test

  mutex - Mutex performance test

Oltp测试要指定具体的lua脚本

Lua脚本位于sysbench-0.5/sysbench/tests/db

--mysql-db 用于指定执行基准测试的数据库名

--mysql-table-engine 用于指定所使用的存储引擎

--oltp-tables-count 执行测试的表的数量

--oltp-table-size 指定每个表中的数据行数

--num-threads 指定测试的并发线程数量

--max-time 指定最大的测试时间

--report-interval 指定间隔多长时间输出一次统计信息

--mysql-user 指定执行测试的MySQL用户

--mysql-password 指定执行测试的MySQL用户的密码

prepare 用于准备测试数据

run 用于实际进行测试

cleanup 用于清理测试数据

测试CPU

sysbench --test=cpu --cpu-max-prime=1000 run

测试磁盘I/O

# free -m看内存

sysbench --test=fileio --file-total-size=1G prepare

# 查看相关参数

sysbench --test=fileio --help

# 测试

sysbench --test=fileio \

--num-threads=8 \

--init-rng=on \

--file-total-size=1G \

--file-test-mode=rndrw \

--report-interval=1 \

run

2.3 sysbench 测试数据库

创建库和表

create database dbtest;

grant all privileges on *.* to dbtest@'localhost' identified by '123456';

准备生成表和数据

# 进入脚本目录

cd ~/root/sysbench-0.5/sysbench/tests/db

# 查看脚本

ls -l *.lua

# 准备

sysbench --test=./oltp.lua \

--mysql-table-engine=innodb \

--oltp-table-size=10000 \

--mysql-db=dbtest \

--mysql-user=dbtest \

--mysql-password=123456 \

--oltp-tables-count=10 \

--mysql-socket=/tmp/mysql.sock \

prepare

运行收集测试数据的脚本 gather.sh

# 创建脚本需要的目录

mkdir /home/benchmarks && cd /home/benchmarks

# 将gather.sh脚本放在上述目标，并创建running

touch running

# 后台运行

sh gather.sh &

进行基准测试

# 运行

sysbench --test=./oltp.lua \

--mysql-table-engine=innodb \

--oltp-table-size=10000 \

--mysql-db=dbtest \

--mysql-user=dbtest \

--mysql-password=123456 \

--oltp-tables-count=10 \

--mysql-socket=/tmp/mysql.sock \

run

测试结束后，运行分析数据的脚本 analyze.sh

# 查看获取的数据

[root@mysql211 benchmarks]# ll -h 5*

-rw-r--r--. 1 root root 124K Mar 12 16:41 5-sec-status-2018-03-12_04-innodbstatus

-rw-r--r--. 1 root root  11K Mar 12 16:41 5-sec-status-2018-03-12_04-processlist

-rw-r--r--. 1 root root 236K Mar 12 16:41 5-sec-status-2018-03-12_04-status

# 分析这些数据 analyze.sh 文件名

sh analyze.sh /home/benchmarks/5-sec-status-2018-03-12_04-innodbstatus

三、参考

《高性能MySQL(第三版)》第二章

巴特西

MySQL基准测试工具

一、基准测试

1.1 常见指标

1.2 收集与分析数据脚本

二、基准测试工具

2.1 mysqlslap

2.2 sysbench 0.5

2.3 sysbench 测试数据库

三、参考

最新文章

热门文章