MySQLDBA修炼之道-测试篇

标签：-- 数据库 之道 修炼 MySQLDBA 测试 test CPU sysbench

八、测试基础

1. 基础概念

数据库性能测试一般是指通过运行测试程序来衡量硬件或软件（编译器、数据库等）在不同架构下的性能。

衡量数据库性能的主要指标包括事务吞吐率和响应时间。事务吞吐率是指数据库操作的速率，即每秒能完成多少事务，由于MySQL InnoDB默认的模式是自动提交，所以也可以近似地将其看作每秒查询数。响应时间指的是响应请求的总耗时，包括等待时间、执行时间及传输数据的时间。

2. 性能测试的目的

测试的内容：

• 建立自己的基准指标，也就是基准测试。
• 在采购服务器时，可能需要测试不同软硬件组合配置下的数据库性能，以选取性价比较高的那个方案。
• 对比不同系统参数或数据库参数配置下的数据库性能。
• 对比不同的数据库产品。
• 对比数据库不同版本之间的差异。
• 对一些新特性进行试用和验证。
• 对一些操作系统补丁和数据库补丁进行验证。
• 对比不同的操作系统、文件系统和库的差异。

进行测试的目的是在损失可以接受的范围内，进行合理地软硬件配置。

3. 基准测试

基准测试是我们依据自身的软硬件配置所做的一个数据库性能测试，它能够尽可能地覆盖生产中的一般场景。随着软硬件的升级换代，基准测试的相应指标可能也需要做一些改变。

一个好的基准测试，应满足如下的一些要素。

• 有现实意义

  基准测试需要具有现实意义，工作负荷、样本数据、系统配置应该和我们测试的目的相关，这样才更有实际意义。

• 具有可观察性、易理解、文档化

  基准测试必须充分文档化，其他人在阅读文档时能够知道你的软硬件环境配置是如何进行测试的，可能还要附上你的配置文件。

• 可运行且具有可重复性

  基准测试是可以重复进行得到类似结果的。所以务必要减少干扰因素，尽可能让其他人可以按照你文档描述的步骤得到一样的结果。

• 收集足够的信息

  基准测试应该尽可能地收集信息，比如内存占用、I/O性能、CPU性能等。收集尽可能多的信息总是一件好事，因为这样做有利于分析问题和发现问题。

• 有分析结果

  要对基准测试结果进行分析、看和我们预期的是否一样，和经验常识是否一致。不能只提供数据而不提供分析结果。

• 要对基准测试结果进行解释和说明

  应该说明测试结果中的一些异常状况，比如是否有错误、异常或干扰，如果有一些不可理解的地方，也请描述出来。

4. 性能/基准测试的步骤

性能测试需要合理的计划和有条理的步骤，不能随意得出结论，性能测试的大概步骤如下

1. 明确测试目的。
2. 设计测试模型。
3. 准备测试集群环境。
4. 准备压力测试工具或编写压力测试脚本。
5. 明确性能指标并加以监控。
6. 根据2）设计的测试模型准备测试数据。
7. 测试执行。
8. 测试分析。

5. 测试的注意事项

• 需要明白，干扰是必然存在的。

  有一个足够真实的模拟环境，可以及早发现干扰的源头。各个组件对照物理环境独立部署，互不影响，可以更好地确保测试结果的可靠性。

• 性能/压力测试，往往需要时间预热，需要不那么平均分布的数据。

  测试是需要足够多的时间的，要有足够多的时间进行预热，当一些热点数据加载到内存中时，数据才可能更符合实际生产情况。现在流行的测试工具或方法，往往是对平均分布的数据进行测试，但真实的负载往往是不均匀的，可能某部分数据比较“热”，某部分数据则基本没有被访问，或者基于某些索引值只有少量结果，而另一些索引值则会检索到大量的记录，所以如果我们的真实数据确实存在比较突出的数据不均匀现象，那么测试的时候最好也让数据变得不均匀。

• 性能/压力测试，需要真实的数据。数据量不够大，往往难以反映真实的瓶颈所在。

• 模拟真实的环境总是困难的，从真实环境引流是一个可以考虑的策略。

• 测试程序应该是多线程的。如果是单线程的话，则需要多个实例来运行，以提高吞吐率。

• 测试需要和各方都进行信息沟通，在充分了解软件的情况下再设计测试场景。

九、测试实践

1. 硬件测试

需要测量的硬件有：内存、CPU、磁盘、网卡、网络等。

常用的测试工具：

• 内存测试的工具有sysbench、stream、RamSpeed、stress等。

• CPU测试的工具有sysbench、cpuburn、stress等。

• 磁盘测试的工具有sysbench、iozone等。

1.1. CPU测试

sysbench命令通过进行素数运算来测试CPU的性能。cpu-max-prime选项指定了最大的素数为20000，如下：

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

要重点关注三个指标：上下文切换（contextswitch）、运行队列（run queue）和使用率（utilization）。

• 上下文切换

  即将当前运行任务转为就绪（或者挂起、删除）状态，让另一个被选定的就绪任务成为当前任务。上下文切换包括保存当前任务的运行环境，恢复将要运行任务的运行环境等。过多的上下文切换会给系统造成很大的开销。

• 运行队列

  当Linux内核要寻找一个新的进程在CPU上运行时，需要考虑处于可运行状态的进程，运行队列容纳了系统中所有可运行的进程。队列越大，程序执行的时间就越长。“load”用于表示正在等待运行的队列长度，top命令可以让我们看到在一分钟、5分钟和15分钟内CPU运行队列的大小。这个值越大则表明系统负荷越大。

• 使用率

  CPU使用率可分为以下几个部分。

  • User Time：执行用户进程的时间占全部时间的百分比，通常是期望这个值越高越好。
  • SystemTime：CPU内核运行及中断的时间占全部时间的百分比，通常是希望这个值越低越好，系统CPU占用率过高时，通常表明系统的某部分存在瓶颈。
  • Wait I/O：I/O等待的CPU时间占全部时间的百分比，如果I/O等待过高，那么说明系统中存在I/O瓶颈。
  • Idle：CPU处于Idle状态的时间占全部时间的百分比。

CPU性能要求

• 对于CPU的每一个核来说运行队列不要超过3，例如，如果是双核CPU就不要超过6。

• 如果CPU正处于满负荷运行状态，那么使用率应该符合下列分布。

  User Time：65%~70%

  SystemTime：30%~35%

  Idle：0%~5%

• 对于上下文切换，要结合CPU使用率来看，如果CPU使用率满足上述分布，那么大量的上下文切换也是可以接受的。常用的监视上下文切换的工具有：vmstat、top、dstat和mpstat。

1.2 内存测试

使用sysbench测试内存的命令如下。

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

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

1.3 I/O测试

1. 普通磁盘阵列测试

• 使用hdparm

  可用hdparm初步判断磁盘的性能，确定工作是否正常。如果要更可靠地验证磁盘、RAID性能，建议使用专门的测试工具，如iozone或sysbench。

  hdparm /dev/sda
  /dev/sda:
    IO_support = 0 (default 16-bit)
    readonly = 0 (off)
    readahead = 256 (on)
    geometry = 60801/255/63, sectors = 976773168, start = 0
  

  解释：geometry=60801【柱面数】/255【磁头数】/63【扇区数】，sectors=976773168【总扇区数】，start=0【起始扇区数】。

  以下命令用于检测硬盘的读取速率（buffered disk reads），需要多运行几次，以便更准确。

  hdparm -t /dev/sda
  /dev/sda:
  Timing buffered disk reads: 426 MB in 3.00 seconds = 141.82 MB/sec
  

  以下命令用于检测硬盘快取时的读取速率（cached reads），需要多运行几次，以便更准确。

ahdparm -T /dev/sda
/dev/sda:
Timing cached reads: 22096 MB in 2.00 seconds = 11070.65 MB/sec

其中的参数解释如下。

* -t：衡量顺序读取的能力，不经过操作系统缓存。

* -T：衡量系统的吞吐性能，未访问底层的物理设备，直接从操作系统缓存里读取数据。

* 使用dd

通过dd命令可实现用指定大小的块复制一个文件，并在复制的同时进行指定的转换。

dd if= of= bs= skip= seek= count= conv=

* if=<source>：指定源文件。默认为标准输入。
* of=<target>：指定目标文件。默认为标准输出。
* bs=<bytes>：同时设置读入/输出的块大小为bytes个字节。也可以指定其他单位，如KB、MB等。
* skip=<blocks>：从输入文件的开头跳过blocks个块后再开始复制。·seek=<blocks>：从输出文件的开头跳过blocks个块后再开始复制。
* count=<blocks>：仅复制blocks个块。
* conv=<conversion>：用指定的参数转换文件，参数如下，可组合使用，中间用逗号分隔。
  * ascii：转换ebcdic为ascii。
  * ebcdic：转换ascii为ebcdic。
  * ibm：转换ascii为alternateebcdic。
  * block：使每一行的长度都为cbs，不足部分用空格填充。
  * unblock：使每一行的长度都为cbs，不足部分用空格填充。
  * lcase：把大写字符转换为小写字符。
  * ucase：把小写字符转换为大写字符。
  * swab：交换输入的每对字节。
  * noerror：如果发生错误，程序也将继续运行。
  * notrunc：不截断输出文件。
  * sync：填充每个块到指定字节，不足部分用空（NUL）字符补齐。



#### 2. SSD测试

SSD的测试建议

* 测试要尽可能避免缓存（cache）的影响。
* 要注意碎片、空间占用的影响。
* RAID卡、I/O控制器、缓存等因素也会影响到SSD的性能。
* 可测试单盘性能，如果需要做RAID，那么可测试一下RAID5和RAID1+0。
* 不仅要测试吞吐率，还需要测试响应的稳定性，以反映真实的环境。
* 验证案例是否覆盖了所有的情况，而且要使用成熟稳定的工具来完成，以免破坏数据。
* SSD对于温度会比较敏感，需要留意温度的影响。



#### 3. 网络测试

一般网卡出故障的可能性非常低，所以我们可以不用过多地进行验证测试，在系统部署后验证即可，可用ethtool命令验证网卡，也可以使用专门的网络评测工具进行验证，还可以通过网络传输文件来验证，比如，直接ftp一个大文件到ftp服务器以验证网络传输是否正常。



## 2. MySQL测试



### 2.1 常用测试工具的介绍和使用

MySQL的测试工具，推荐用sysbench。因为sysbench的文件I/O测试与InnoDB的行为很相似，针对MySQL也有比较完善的测试模型，还可以方便地修改lua脚本，以实现更强大、更灵活的测试功能。



#### 1. sysbench的使用

目前sysbench主要支持MySQL、PostgreSQL、Oracle这3种数据库。

主要包括以下几种方式的测试：

* Fileio：文件I/O测试。
* Cpu：CPU性能测试。
* Memory：内存性能测试。
* Threads：线程性能测试。
* Mutex：Mutex性能测试。
* Oltp：OLTP测试，MySQL一般会选择此种测试类型。



**开始测试**

在`sysbench --test=memory`命令后添加help可以查看帮助。

* --percentile 95%：响应时间，也就是删除5%的响应时间最长的请求，然后从剩余的请求中选取最大的响应时间值。
* --max-time：运行时间限制，单位是秒。·--num-threads：线程数。
* --max-requests：查询数限制。



* CPU性能测试

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

• 线程测试

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

• 磁盘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
  

  第一条命令初始化文件，第二条命令执行测试，第三条命令清理文件。–num-threads参数指定了最大创建16个线程，–file-total-size参数指定创建文档的总大小为12GB，–file-test-mode指定文档的读写模式为随机读写。

  磁盘I/O性能测试是进行数据库基准测试时要着重加以研究的。我们需要衡量各种因素，比如操作类型、读写的频率、I/O大小、是随机读写还是顺序读写、写的类型是异步还是同步、并发线程情况、操作系统缓存状态及文件系统有哪些调优等因素。

  文件测试类型（file-test-mode）有如下几种。

  • seqwr：顺序写。
  • seqrewr：顺序重写（rewrite）。
  • seqrd：顺序读。
  • rndrd：随机读。
  • rndwr：随机写。
  • rndrw：随机读写。

• 内存测试

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

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

• OLTP测试

  在测试之前请预先创建数据库，并给予测试用户足够的权限。

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

  多线程测试MySQL

  • 先初始化数据

    sysbench --test=./sysbench/tests/db/oltp.lua --mysql-table-engine=innodb --oltp-tables-count=256 --oltp-table-size=1000000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock prepare
    

    上述参数指定了本次测试的表存储引擎类型为InnoDB，指定了表的最大记录数为1000000，初始化生成256个表。测试OLTP时，可以自己先创建数据库sbtest，或者自己用参数–mysql-db来指定其他数据库。

  • 进行实际测试，测试模型是OLTP，并发8个线程，执行1个小时

    sysbench --test=./sysbench/tests/db/oltp.lua --oltp-tables-count=256 --oltp-table-size=1000000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock --max-time=3600 --max-requests=0 --num-threads=8 --report-interval=10 run
    

    其中，–report-interval=10表示每10s就输出一次数据

  • 测试完成后，清理数据

    sysbench --test=./sysbench/tests/db/oltp.lua --oltp-tables-count=256 --oltp-table-size=1000000 --mysql-user=test --mysql-password=test --mysql-socket=/tmp/mysql.sock cleanup
    

2. mysqlslap的使用

可以模拟多个客户端对数据库进行施压，并生成报告以衡量数据库的一些指标。

工作原理可分为如下三个步骤。

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

使用示例

• 分别并发10个线程或100个线程进行混合测试。

  mysqlslap --uroot --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-charcols=1 --number-int-cols=8 --auto-generate-sql-secondary-indexes=1 --debug-info --verbose
  

  以上命令的大致步骤是，首先生成1000条数据（–number-of-queries=1000），然后进行混合测试（–auto-generate-sql-load-type=mixed，SELECT操作和INSERT操作大致各占一半），此时数据会不断增长。

  然后，先使用10个并发线程进行测试，再用100个并发线程进行测试（–concurrency=10,100），进行新的并发测试前会清理和初始化测试数据。

  需要留意的是，自动生成的SELECT语句是全表扫描。

• 测试基于主键查找的性能

  time mysqlslap -uroot --engine=innodb --auto-generate-sql-load-type=key \--auto-generate-sql --auto-generate-sql-write-number=100000 --auto-generate-sql-guidprimary \--number-char-cols=10 --number-int-cols=10 --concurrency=10,100 --number-of-queries=5000
  

  以上命令的大致步骤是，首先创建一个表，使用单线程初始化插入100000条记录，然后并发10条线程执行基于主键的查询。接着删除库表，再初始化插入

  100000条记录，然后并发100条线程执行基于主键的查询。基于主键的查询可能被缓存，所以有必要生成不同的SELECT语句。

• 生成一张两千万条记录的表，进行混合型负荷测试（SELECT+INSERT）

  mysqlslap -uroot -p --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 --debug-info
  

  以上命令的大致步骤是，初始化记录时使用自增主键（–auto-generate-sql-add-autoincrement）并发50个线程进行查询，一共执行100万个查询，也就是说平均每个线程大概执行2万个查询，如果有自增ID，那么SELECT语句是基于自增ID的，这样更能反映生产环境实际情况。

2.2 MySQL基准测试模型

基准测试的目的之一是在平时做好数据准备，记录标准的数据库软硬件配置下的性能数据，以便在未来更改数据库配置或调整升级数据库主机时有一个参照。

具体的思路和设计方法：

• 当我们选择硬件的时候，需要考虑到各项成本，对于项目风险、开发成本和维护成本比较难以衡量，而计算机性能相对来说是更好地限定和比较的，所以可以考虑建立一个MySQL的基准测试模型。
• 性能测试很难模拟真实环境的负荷，一般使用比较简单的模型，因为真实环境下的负荷具有不确定、变化较大、复杂且难以理解等特点，故而难以得出结论，不容易对比。
• 单个产品的基准测试，主要用于对比版本和衡量软硬件调整的效果，对于整个应用系统的测试没有太大的参考意义，应用系统自己的基准测试模型会比单个MySQL测试模型更全面更准确。
• ·明确目标后，再进行基准测试，才能更好地选择工具和测试方法
• 如果是SSD，建议文件的最大空间不超过磁盘空间的85%，以避免SSD空间占比可能带来的性能下降。
• 除了关注吞吐率（TPS），还需要关注响应时间（responsetime）。
• 需要留意并发性（concurrency），如MySQL Server同时运行的线程数（threads_running）和伸缩性（scalability）等。假如我们增加了一倍线程，那么好的伸缩性就意味着系统的吞吐也可以线性地增加一倍，或者说当我们增加了一倍的硬件资源，那么系统的吞吐也可以翻倍。
• 基准测试需运行足够长的时间。关于数据预热所需要的时间，一般查看吞吐量的曲线图就可以大致判断出来，很多情况下，可能需要运行半个小时以上才会稳定下来。
• 尽量确保测试结果在同样的配置下可以重现。
• 衡量MySQL性能需要考虑诸多因素，包括但不限于以下这些因素。
  • 硬件：CPU速度、CPU架构、CPU个数、CPU核数、总线速度、内存访问速度、设备I/O性能、RAID卡、磁盘条带、块大小、网络设备。
  • 操作系统：原生API性能、线程、锁、内存、I/O调度算法。
  • 客户端连接次数
  • 数据库服务器处理任务的线程个数
  • 数据库设计。
  • 数据量。
  • 应用类型。
  • 数据访问模式：一般来说我们的应用热点数据较小，读远大于写。如果你的应用热点数据比较大，访问各种数据比较分散，分布比较均匀，那么这种测试更考验了数据库的原始性能。
  • 数据库版本：社区版、企业版还是第三方分支版本。
  • 引擎。
  • 数据库配置。比如NUMA策略、页块大小（pagesize）、是独立表空间还是共享表空间、顺序访问和随机访问文件的分布、InnoDBbuffer pool的大小及其他一些影响重大的参数。
  • 重要的参数修改，每次尽可能少更改点参数，一次更改太多的参数不容易判断问题的所在。

1. 模型简介

以下是一个测试MySQL数据库的简单模型。

## MySQL基准测试模型介绍。
## 组合以下不同条件
:测试类型、线程数、表个数、表记录数(大小) 进行测试
## ================开始测试=======================
## 测试逻辑
:
##对每种测试类型
##对各种并发线程数
## 对指定的表个数
## 对不同表大小
## prepare
## sysbench 测试
,默认测试
1200s
## cleanup
## ====================结束测试========================

测试类型一般选择oltp。对于单个表，将按顺序执行如下操作。

1. 几个基于主键的查询。
2. 主键范围查找。
3. 主键范围查找+聚合函数。
4. 主键范围查找+文件排序。
5. 主键范围查找+临时表+文件排序。
6. 更新操作（基于主键查询）。
7. 删除操作（基于主键查询）。
8. 插入操作。
9. 提交。

2. 测试脚本示例

测试脚本可实现如下功能：

• 收集操作系统、硬件信息和MySQL脚本信息。如下是收集到的一些信息。

  =========================Host==============================
  Hostname | xxxx
  Release | Red Hat Enterprise Linux Server release 5.4 (Tikanga)
  Processors | physical = 2, cores = 8, virtual = 16, hyperthreading = yes
  Models | 16xIntel(R) Xeon(R) CPU E5520 @ 2.27GHz
  Total | 15.6G
  # RAID Controller ############################################
  Controller | No RAID controller detected
  # Disk Schedulers And Queue Size #############################
  sda | [cfq] 128
  sdb | [cfq] 128
  ============================MySQL===========================
  # Report On Port 3306 ########################################
  User | root@localhost
  Hostname | xxxxxx
  Version | 5.1.58-log MySQL Community Server (GPL)
  Built On | unknown-linux-gnu x86_64
  Databases | 5
  # Table cache ################################################
  Size | 4096
  # InnoDB #####################################################
  Version | default
  Buffer Pool Size | 2.5G
  File Per Table | OFF
  Page Size | 16k
  Log File Size | 2 * 360.0M = 720.0M
  Flush Log At Commit | 2
  Thread Concurrency | 16
  Txn Isolation Level | READ-COMMITTED
  sync_binlog | 20
  innodb_max_dirty_pages_pct = 50
  

• 可以在脚本中调整MySQL的参数设置。

• 收集操作系统的性能信息，在测试期间的内存、CPU、磁盘等各种信息都应该收集起来，不管目前有没有用，收集尽可能多的信息会方便自己以后的分析。

• 能使用sysbench的oltp模型进行测试，并且能够生成图形。

3. 基准测试的不足及注意事项

• 不容易测试系统的其他特性：稳定性、安全性、扩展性、可用性、灾难恢复性等。

• 没有计算成本（磁盘、内存等），但即使计算了成本，也可能会被厂商欺骗，厂商会使用最优的配置，以尽可能低的成本支撑更大的吞吐。

• 没有衡量能源消耗

• 没有考虑到复杂的网络环境。

• 用户考虑的是这个产品能够满足何种服务品质协议（service-levelagreement），比如说99.99%的时间是可用的，而基准测试更多考虑的是能够达到的平均分数。

• 一般来说，新版本的功能更强，优化器更复杂，更擅长处理复杂的查询。在高并发和大数据集下会更具备优势。

• 可能很难模拟复杂的实际访问。

• 针对不同问题设计负荷，比如，是模拟计算密集型（CPU-bound）的负荷还是I/O密集型（I/O-bound）的负荷呢？

• 向上扩展（scale-up）可以提高吞吐，但存在一个最佳配置，超过这个配置后，吞吐不会再有明显增加。

• 研发、测试人员往往不擅长做数据库的压力测试，可能存在的问题包括不熟悉硬件，测试时MySQL Server的参数并没有经过优化，没有使用足够多的真实可靠的数据，没有运行足够长时间的测试计划以预热数据等。

• 生产环境数据库和Web服务器一般分布在不同的主机上，严格来说，测试工具部署在非数据库机器上更好，但是，测试工具、客户端和数据库在同一台机器上

  做测试一般也是可行的。

3. 应用数据库性能测试

一般研发、测试人员可能会专门使用一些Web测试工具，如Apache自带的工具ab，包括http_load、Webbench、Siege、JMeter等。这些工具会调用一些Web页面程序，对数据库间接进行施压，如果选择的测试模型和步骤合适，往往比直接使用工具对数据库进行压力测试更合理。

标签：--,数据库,之道,修炼,MySQLDBA,测试,test,CPU,sysbench
From： https://blog.csdn.net/g527064298/article/details/143342475