MySQL运维13-数据库性能测试

豪杰笑开怀

2215人浏览 · 2023-04-12 19:00:10

豪杰笑开怀 · 2023-04-12 19:00:10 发布

文章目录

1、数据库性能指标
- 1.1、事务吞吐率
- 1.2、响应时间
2、数据库性能测试的目的
3、数据库性能测试的注意事项
4、数据库性能测试工具选型
- 4.1、sysbench
- 4.2、mysqlslap
5、sysbench测试介绍
6、myslap测试介绍
- 6.1、测试原理及步骤介绍
- 6.2、测试场景
7、测试数据的快速生成
8、总结

1、数据库性能指标

衡量数据库性能的主要指标包括事务吞吐率和响应时间，所以测试时也主要考虑这两个指标。注意不要过于看重事务吞吐率而忽略了响应时间，因为合理的响应时间意味着良好的用户体验，所以在合理的响应时间范围内的事务吞吐率才有意义。

1.1、事务吞吐率

事务吞吐率是指数据库操作的速率，即每秒能完成多少事务，由于MySQL InnoDB默认的模式是自动提交，所以也可以近似地将其看作每秒查询数。

1.2、响应时间

响应时间指的是响应请求的总耗时，包括等待时间、执行时间及传输数据的时间。

2、数据库性能测试的目的

建立自己的基准指标，一是作为服务器、操作系统、存储、网络。灾备及数据库(不同数据库、不同版本、特性的启用等)的选型及采购的依据，二是作为系统性能余量的估算依据。
基准测试的另一个目的是在平时做好数据准备，记录标准的数据库软硬件配置下的性能数据，以便在未来更改数据库配置或调整升级数据库主机时有一个参照。很多人在新机器上线后不想做基准测试，认为基准测试繁琐、耗时，而且难以度量。如果不是标准化的采购、安装、部署，就很难说主机配置得完全正确，所以，如果有一个工具可以预先针对CPU、磁盘、网络、数据库进行压力测试，验证硬件是否已经正确配置，就省事多了。我们还可以把基准测试的历史数据记录下来，在以后进行选购或升级软硬件的时候，再重新进行基准测试，从而验证软硬件的升级效果。

3、数据库性能测试的注意事项

预热：测试是需要足够多的时间的，当一些热点数据加载到内存中时，数据才可能更符合实际生产情况。
模拟生产中的不均匀：真实的负载往往是不均匀的，可能某部分数据比较“热”，某部分数据则基本没有被访问，，所以测试的时候最好也让数据变得不均匀。应该尽量采取一些操作，让数据变得不那么“整齐”，比如在INSERT、UPDATE或DELETE数据的时候按随机的key顺序进行操作，模拟出这种效果。
真实数据和真实数据量：性能/压力测试，需要真实的数据。数据量不够大，往往难以反映真实的瓶颈所在。
通过多线程模拟多客户端。
注意数据库是否具备伸缩性（scalability）：假如增加了一倍线程，那么好的伸缩性就意味着系统吞吐量也线性增加一倍，或者说当增加一倍的硬件资源，那么系统的吞吐也可以翻倍。
每次只修改少量甚至只修改一个参数：一次更改太多的参数不容易判断问题的所在。

4、数据库性能测试工具选型

以下这两个工具可以满足大部分情况下的性能测试和压力测试，特别推荐sysbench。

4.1、sysbench

MySQL的测试工具，推荐用sysbench。虽然hammerora、super-mark、tpc-c等一些其他工具也很强大，但sysbench的文件I/O测试与InnoDB的行为很相似，针对MySQL也有比较完善的测试模型，还可以方便地修改lua脚本，以实现更强大、灵活的测试。其实设计sysbench的初衷就是为了衡量MySQL的性能，而很多其他工具，对于MySQL的支持往往只是一个选项，功能还不够强大，难以模拟真实的数据库负荷。
sysbench可以自定义lua脚本，开发人员可以编写适合自己业务逻辑的lua脚本。当然也可以使用其他高级语言编写测试工具，这样会更灵活，更接近实际业务数据库操作。
Sysbench支持的测试类型：文件I/O、CPU、内存、线程、Mutex、OLTP测试；
Sysbench支持的数据库类型：MySQL、PostgreSQL、Oracle；

4.2、mysqlslap

MySQL自带的mysqlslap也是一个不错的工具，它是从5.1.4版开始的一个MySQL官方提供的压力测试工具，可通过模拟多个并发客户端访问MySQL来执行压力测试。

5、sysbench测试介绍

5.1、sysbench的安装

5.1.1、sysbench的正常安装步骤

安装环境：操作系统为Centos6.8 64bit，数据库为MySQL5.7

预先安装依赖包

yum install automake libtool unzip -y

cd /opt/software/
wget https://github.com/akopytov/sysbench/archive/1.0.zip -O "sysbench-1.0.zip"
unzip sysbench-1.0.zip

安装。注意安装之前，确保位于之前解压的sysbench目录中。

cd sysbench-1.0
./autogen.sh
./configure --with-mysql-includes=/usr/local/mysql/include --with-mysql-libs=/usr/local/mysql/lib
make
make install

修改/etc/ld.so.cnf文件，为如下内容，注意这里的/usr/local/mysql为你的MySQL安装目录

include ld.so.conf.d/*.conf
/usr/local/mysql/lib

让/etc/ld.so.cnf文件的修改生效，sysbench的安装就完成了。

/sbin/ldconfig -v

5.1.2、安装中的异常处理

安装时如果报如下错误：

error while loading shared libraries: libmysqlclient.so.20: cannot open shar

解决方案如下：

先删除原文件夹：

shell>  rm  -rf  sysbench-1.0

找到libmysqlclient.so.20所在位置，如下所示：

shell> find /  -name 'libmysqlclient.so.20'
/usr/local/mysql/lib/libmysqlclient.so.20

建立软连接

shell> ln -s /usr/local/mysql/lib/libmysqlclient.so.20 /usr/lib/

然后重新按之前的步骤安装，即可成功。

5.2、sysbench的输入、输出参数说明

5.2.1、sysbench的主要输入参数/选项

-max-time：运行时间限制，单位是秒，执行到max-time即结束并输出，不等任务完全执行完
–num-threads：线程数。
–max-requests：查询数限制。

5.2.2、sysbench的主要输出参数

percentile 95%：95%请求的最大响应时间，也就是删除5%的响应时间最长的请求，然后从剩余的请求中选取最大的响应时间值。
execution time (avg/stddev)：执行总时间/方差。

5.3、服务器及操作系统性能测试

服务器及操作系统的性能测试，基本一次测试就能够得到一个基线数据，不需要反反复复测试了。
可以使用命令sysbench --test=cpu help得到如下的结果。可以看到重要的关键字prime，即质数，比如查找小于一千万的最大质数。

$ sysbench --test=cpu help
WARNING: the --test option is deprecated. You can pass a script name or path on the command line without any options.
sysbench 1.0.20 (using bundled LuaJIT 2.1.0-beta2)
cpu options:
  --cpu-max-prime=N upper limit for primes generator [10000]

5.3.1、CPU性能测试

CPU性能测试主要是进行素数的运算，以下指定了最大的素数为20000，也可以根据机器CPU的性能来适当调整数值。

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

测试CPU：如下会启用10个并发线程，最大请求数是100，用求最大质数测试CPU：

$ sysbench --num-threads=20 --max-requests=100 --test=cpu --debug  --cpu-max-prime=10000000 run

5.3.2、内存性能测试

内存测试，如下参数指定了本次测试的整个过程是在内存中传输4GB的数据量，每个块（block）的大小为8KB。

sysbench --test=memory --memory-block-size=8K --memory-total-size=4G run

内存读测试：可以根据业务场景对不同内存块大小进行对比测试，如下是按照8k的大小进行测试的，默认是1k。

$ sysbench --num-threads=10 --max-requests=100 --test=memory --memory-block-size=8k --memory-total-size=32G --memory-oper=read run

内存写测试：

$ sysbench --num-threads=10 --max-requests=100 --test=memory --memory-block-size=8k --memory-total-size=32G --memory-oper=write run

5.3.3、线程性能测试

线程测试

sysbench --test=threads --num-threads=64 --thread-yields=1000 --thread-locks=8 run

5.3.4、磁盘I/O性能测试

5.3.4.1、概述

磁盘性能测试很重要，MySQL很多优化跟磁盘有关，特别是将磁盘顺序读改为内存中随机读等。

5.3.4.2、测试选项

对于磁盘的测试，可以通过如下命令查看sysbench提供了哪些测试选项：

shell> sysbench  --test=fileio  help

5.3.4.3、测试示例

磁盘I/O性能测试示例如下：

sysbench --test=fileio --num-threads=16 --file-total-size=12G --file-test-mode=rndrw prepare
sysbench --test=fileio --num-threads=16 --file-total-size=12G --file-test-mode=rndrw run
sysbench --test=fileio --num-threads=16 --file-total-size=12G --file-test-mode=rndrw cleanup

上述代码分为3个步骤，第一条命令初始化文件，第二条命令执行测试，第三条命令清理文件。
–num-threads参数指定了最大创建16个线程，–file-total-size参数指定创建文档的总大小为12GB，–file-test-mode指定文档的读写模式为随机读写。
磁盘I/O性能测试是进行数据库基准测试时要着重加以研究的。我们需要衡量各种因素，比如操作类型、读写的频率、I/O大小、是随机读写还是顺序读写、写的类型是异步还是同步、并发线程情况、操作系统缓存状态及文件系统有哪些调优等因素。
文件测试类型（f i le-test-mode）有如下几种。
- seqwr：顺序写。
- seqrewr：顺序重写（rewri te）。
- seqrd：顺序读。
- rndrd：随机读。
- rndwr：随机写。
- rndrw：随机读写。
下面场景测试的是，20个文件，每个10GB，随机读写，文件大小总量在200G。

$ sysbench --file-num=20 --num-threads=20 --test=fileio --file-total-size=200G --max-requests=1000000 --file-test-mode=rndrw prepare
$ sysbench --file-num=20 --num-threads=20 --test=fileio --file-total-size=200G --max-requests=1000000 --file-test-mode=rndrw run
$ sysbench --file-num=20 --num-threads=20 --test=fileio --file-total-size=200G --max-requests=1000000 --file-test-mode=rndrw cleanup

下面场景在相应的目录下就会产生16个文件，因为总大小是2GB,所以每个文件的大小应该是128MB。然后是在16个线程下的随机读取性能，最大随机读取请求是10000000次，如果在180秒内不能完成，测试即结束。测试结束后执行cleanup删除测试产生的文件。

shell> sysbench --test=fileio  --file-num=16  --file-total-size=2G prepare		
shell> sysbench  --test=fileio --file-num=16  --file-total-size=2G  --file-test-mode=rndrd  --time=180  --events=10000000 --threads=16  --file-extra-flags=direct  --file-fsync-freq=0  --file-block-size=16384  run
shell> sysbench  --test=fileio  --file-num=16  --file-total-size=2G  cleanup

如果报如下错误，很可能是–file-block-size参数写错了，没写成16384：

FATAL: Failed to read file! file: 14 pos: 126887736 errno = 22 (Inv

5.3.4.4、测试结果分析

如下图所示的测试结果说明：IOPS为29031.95，随机读的性能为453.62 MB

......
File operations:
    reads/s:                      29031.95
    writes/s:                     0.00
    fsyncs/s:                     0.00

Throughput:
    read, MiB/s:                  453.62
    written, MiB/s:               0.00

General statistics:
    total time:                          180.0006s
    total number of events:              5225792

Latency (ms):
         min:                                    0.04
         avg:                                    0.55
         max:                                   42.86
         95th percentile:                        1.08
         sum:                              2869653.40

Threads fairness:
    events (avg/stddev):           326612.0000/48808.82
    execution time (avg/stddev):   179.3533/0.15

5.3.4.5、批量测试脚本

可能用户需要测试随机读、随机写、随机读写、顺序写、顺序读等所有这些模式，并且还需要测试不同的线程和不同文件块下磁盘的性能表现，这时可能需要类似如下的脚本来帮用户自动完成这些测试：

#!/bin/sh
set -u
set -x
set -e
for size in 8G,64G; do
	for mode in seqwr seqrewr seqrd rndrd rndwr rndrw; do
		for blksize in 4096 16384; do
			sysbench  --test=fileio  --file-num=16  --file-total-size=$size  prepare
			for threads in 1 4 8 16 32; do
				echo "=====  test $blksize  in $threads threads"
				echo PARAMS $size $mode $threads $blksize> sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize
				for i in 1 2 3; do
					sysbench  --test=fileio  --file-total-size=$size  --file-test-mode=$mode  --time=180  --events=1000000 --threads=$threads  --file-num=16  --file-extra-flags=direct  --file-fsync-freq=0  --file-block-size=$blksize  run | tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize 2 >&1
				done
			done
			sysbench  --test=fileio  --file-num=16  --file-total-size=$size  cleanup
		done
	done
done

5.4、使用sysbench对MySQL做OLTP性能测试

5.4.1、简单示例1

测试前创建数据库，创建测试用户，并授予足够权限

mysql> create database sbtest;
mysql> grant all privileges on sbtest.* to test@'localhost' identified by 'test';

初始化数据：

/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua：这个参数要取决于你自己服务器上oltp.lua的位置，可以使用find / -name oltp.lua找到；
参数指定了本次测试的表存储引擎类型为InnoDB，指定了表的最大记录数为100000，初始化生成32个表。
测试OLTP时，可以自己先创建数据库sbtest，或者自己用参数–mysql -db来指定其他数据库。

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua  --mysql-table-engine=innodb  --oltp-tables-count=32  --oltp-table-size=100000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock prepare

开始实际测试，测试模型是OLTP，并发2个线程，执行60秒，–report-interval=10表示每10s就输出一次数据：

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua  --oltp-tables-count=32  --oltp-table-size=100000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock --max-time=60 --max-requests=0 --num-threads=2  --report-interval=10 run

测试完成后，清理数据：

sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua   --oltp-tables-count=32  --oltp-table-size=100000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock cleanup

5.4.2、简单示例2

测试前先创建测试数据库和用户，并赋予足够权限：

mysql> CREATE DATABASE IF NOT EXISTS sysbenchtest;
mysql> CREATE USER sysbenchtest IDENTIFIED BY 'sysbenchtest';
mysql> GRANT ALL ON sysbenchtest.* TO sysbenchtest;

下面的测试，测试模式complex，并发线程数30，最大请求数5000000。

$ sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-tables-count=32 --oltp-table-size=100000 --mysql-user=sysbenchtest --mysql-password=sysbenchtest --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-db=sysbenchtest --mysql-table-engine=innodb --threads=30 --events=5000000 prepare

$ sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-tables-count=32 --oltp-table-size=100000 --mysql-user=sysbenchtest --mysql-password=sysbenchtest --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-db=sysbenchtest --mysql-table-engine=innodb --threads=30 --events=5000000 run

$ sysbench --test=/usr/local/share/sysbench/tests/include/oltp_legacy/oltp.lua --oltp-tables-count=32 --oltp-table-size=100000 --mysql-user=sysbenchtest --mysql-password=sysbenchtest --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-db=sysbenchtest --mysql-table-engine=innodb --threads=30 --events=5000000 cleanup

测试结果如下所示，可以看到事务吞吐率TPS在640左右：

SQL statistics:
    queries performed:
        read:                            85092
        write:                           24312
        other:                           12156
        total:                           121560
    transactions:                        6078   (604.46 per sec.)
    queries:                             121560 (12089.21 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          10.0539s
    total number of events:              6078

Latency (ms):
         min:                                    6.54
         avg:                                   49.52
         max:                                  282.87
         95th percentile:                      112.67
         sum:                               300997.27

Threads fairness:
    events (avg/stddev):           202.6000/6.28
    execution time (avg/stddev):   10.0332/0.01

5.4.3、sysbench对MySQL做压力测试

5.4.3.1、数据准备

数据准备：测试前先准备数据。新版的sysbench需要指定一个Lua模板，在sysbench安装目录下自带了一批模板。原本的参数，如下：●oltp-test-mode=complex，已经失效；●mysql-table-engine=innodb，选项也不存在；●oltp-num-tables=10，需要改为–tables=10；●oltp-table-size=5000000，需要改为–table-size=5000000。

$ sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-user=sysbenchtest --mysql-password=sysbenchtest --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbenchtest  --tables=10  --table-size=1000000 --events=5000000 --report-interval=5 --time=180 --threads=30  prepare

5.4.3.2、做不同线程数下的比对

先开启30个线程的测试。变成50，100个线程只需要调整–threads即可

$ sysbench /usr/local/share/sysbench/oltp_read_write.lua --mysql-user=sysbenchtest --mysql-password=sysbenchtest --mysql-host=127.0.0.1 --mysql-port=3306 --mysql-db=sysbenchtest  --tables=10  --table-size=1000000 --events=5000000 --report-interval=5 --time=180 --threads=30  run

如果要测试200个甚至更多线程，就要调整如下几个参数

mysql> SET GLOBAL max_connections=2000;
mysql> SET GLOBAL max_prepared_stmt_count=500000;

得到的结果类似下面的输出，每5秒钟输出一次，TPS，QPS指标一目了然。

......
[ 70s ] thds: 100 tps: 531.80 qps: 10692.57 (r/w/o: 7469.57/2159.61/1063.39) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 75s ] thds: 100 tps: 534.80 qps: 10644.45 (r/w/o: 7451.63/2123.01/1069.80) lat (ms,95%): 376.49 err/s: 0.00 reconn/s: 0.00
[ 80s ] thds: 100 tps: 525.12 qps: 10512.63 (r/w/o: 7369.91/2092.68/1050.04) lat (ms,95%): 442.73 err/s: 0.00 reconn/s: 0.00
......

5.4.3.3、调整参数以提高性能

修改/etc/security/limits.conf

* soft nproc 65535
* soft nofile 65535
* hard nproc 65535
* hard nofile 65535

调大redo文件大小：redo文件设置为多大其实没有一个绝对的概念，Percona建议在压力测试中可以设置为1G或者2G，最大可设置为4G，因为本身会直接影响到恢复的效率。怎么修改redo的大小呢，要正常停库，然后删除默认的两个redo文件，保险起见，你可以先备份出来也行，然后修改参数文件的redo文件参数，启动MySQL，当然开始时识别不到redo文件，会自动创建两个新的。

innodb_log_file_size=1073741824

将sync_binlog设置为0、

sync_binlog=0

调大InnoDB缓冲池大小，比如如下从128MB调大到1GB：

innodb_buffer_pool_size=107374182

做如上修改以后，发觉性能又有提升：

......
[ 60s ] thds: 100 tps: 872.70 qps: 17428.43 (r/w/o: 12188.62/3494.61/1745.20) lat (ms,95%): 196.89 err/s: 0.00 reconn/s: 0.00
[ 65s ] thds: 100 tps: 850.92 qps: 17098.77 (r/w/o: 11952.05/3444.48/1702.24) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
[ 70s ] thds: 100 tps: 870.40 qps: 17401.23 (r/w/o: 12194.02/3466.21/1741.00) lat (ms,95%): 204.11 err/s: 0.00 reconn/s: 0.00
......

6、myslap测试介绍

6.1、测试原理及步骤介绍

mysqlslap是MySQL 5.1.4及以后版本自带的一个用于实现负载性能测试和压力测试的工具。它可以模拟多个客户端对数据库进行施压，并生成报告以衡量数据库的一些指标。其工作原理可分为如下三个步骤。

首先生成测试数据，即创建表，导入数据。这个步骤将使用单个客户端连接执行。
然后运行性能测试，可以使用单线程或多线程。
最后清理测试数据。这个步骤将使用单个客户端连接执行。

6.2、测试场景

分别并发10个线程或100个线程进行混合测试：大致步骤是，首先生成1000条数据（–number-of -queries=1000），然后进行混合测试（–auto-generate-sql -load-type=mixed，SELECT操作和INSERT操作大致各占一半），此时数据会不断增长。然后，先使用10个并发线程进行测试，再用100个并发线程进行测试（–concurrency=10,100），进行新的并发测试前会清理和初始化测试数据。

mysqlslap -u root  --engine=innodb --auto-generate-sql  
	--auto-generate-sql-unique-query-number=100 --auto-generate-sql-unique-write-number=100 --auto-generate-sql-write-number=1000 
	--create-schema=test --auto-generate-sql-load-type=mixed --concurrency=10,100 --number-of-queries=1000  --iterations=1  
	--number-char-cols=1 --number-int-cols=8  --auto-generate-sql-secondary-indexes=1  --verbose

测试基于主键查找的性能：首先创建一个表，使用单线程初始化插入100000条记录，然后并发10条线程执行基于主键的查询。接着删除库表，再初始化插入100000条记录，然后并发100条线程执行基于主键的查询。基于主键的查询可能被缓存，所以有必要生成不同的SELECT语句。

time  mysqlslap  -u root  --engine=innodb --auto-generate-sql-load-type=key 
	--auto-generate-sql --auto-generate-sql-write-number=100000 --auto-generate-sql-guid-primary 
	--number-char-cols=10 --number-int-cols=10  --concurrency=10,100 --number-of-queries=5000

生成一张两千万条记录的表，进行混合型负荷测试（SELECT+INSERT）：大致步骤是，初始化记录时使用自增主键（–auto-generate-sql -add-autoincrement）并发50个线程进行查询，一共执行100万个查询，也就是说平均每个线程大概执行2万个查询，如果有自增ID，那么SELECT语句是基于自增ID的，这样更能反映生产环境实际情况。

mysqlslap -uroot --engine=innodb --auto-generate-sql --auto-generate-sql-write-number=20000000 --auto-generate-sql-add-autoincrement --auto-generate-sql-secondary-indexes=2 --concurrency=50 --number-of-queries=1000000 --number-char-cols=3 --number-int-cols=2

7、测试数据的快速生成

先通过shell脚本生成测试数据，再使用LOAD DATA导入数据，是最快的测试数据生成方式。

创建测试表：

CREATE TABLE `users` (
  `userid` int(11) unsigned NOT NULL,
  `user_name` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

通过shell脚本生成测试数据

$ vi init_data.sh
for i in {1..1000}
do
  echo $i,user_$i
done > a.lst

给shell脚本加执行权限，执行该脚本，生成数据文件a.lst，然后将a.lst的权限更改为mysql，否则无法导入。

$ chmod +x init_data.sh
$ ./init_data.sh
$ chown mysql:mysql a.lst

进入mysql，使用LOAD DATA将a.lst文件的数据导入数据库：

mysql> LOAD DATA LOCAL INFILE '/root/a.lst' INTO TABLE d1.users FIELDS TERMINATED BY ',';
Query OK, 1000 rows affected (0.01 sec)
Records: 1000  Deleted: 0  Skipped: 0  Warnings: 0