Mysql 专栏 – 线上调优与压力测试

前言

​ 本节内容讲述线上的调优伎俩以及压力测试的相干工具，联合一些理论的命令参数，咱们将会介绍运行后果的具体含意。本节内容为大抵的介绍如何压力测试和如何浏览参数，具体的运行成果须要本人部署一台机器测试，对于这部分的内容受到不同的机器影响会呈现齐全不同的成果，须要理论测试所以没有进行记录。

​

概述

1. 介绍常见的 mysql 零碎性能剖析指标，介绍吞吐量和机器的抉择
2. 压力测试工具的介绍，以及数据库压力测试的实战。
3. 最初将会依据 Linux 零碎的命令介绍如何浏览 mysql 服务器的性能
4. 简略介绍 Prometheus 和 Grafana 两个零碎。

​

零碎指标剖析

小型零碎：

​ 小型并发零碎不须要思考其余条件，因为那种零碎可能每隔几分钟才会有一波申请发到数据库下来，而且数据库里一张表兴许就几百条、几千条或者几万条数据，数据量很小，并发量很小，操作频率很低，用户量很小，并发量很小，只不过可能零碎的业务逻辑很简单而已，对于这类零碎的数据库机器选型，就不在咱们的思考范畴之内了。

通常抉择:

​ 大多数状况下个别 8 核 16G 的机器部署的 MySQL 数据库，每秒抗个一两千并发申请是没问题的，然而如果并发量再高一些，假如每秒有几千并发申请，那么可能数据库就会有点危险了，因为数据库的 CPU、磁盘、IO、内存 的负载都会很高，数据库压力过大就会宕机。

吞吐量：

​ 如果一个零碎解决一个 mysql 申请须要 1s，那么一分钟可能只解决 100 个申请，4 核 8G 的机器部署一般的 Java 利用零碎，通常每秒大抵就是抗下几百的并发拜访，然而同一个配置的机器能够从每秒一两百申请到每秒七八百申请都是有可能的，要害是看你每个申请解决须要消耗多长时间。

固态硬盘

​ 因为数据库最大的简单就在于 大量的磁盘 IO，他须要大量的读写磁盘文件，所以如果能应用 SSD 固态硬盘，那么你的数据库每秒能抗的并发申请量就会更高一些。

数据库压力测试

​ 有了数据库之后，第一件事就是做压力测试：

什么是 qps，什么是 tps？

​ 压测数据库，每秒能扛下多少申请，每秒会有多少申请，如果要断定性能能够通过上面的指标：

​ Qps: 全称是 query per second，意味着数据库每秒能够解决多少个申请，一个申请就是一个 sql 语句，在 mysql 中意味着一秒能够解决多少个 sql 语句。

​ Tps 全程是 transaction per second，Tps 是用来掂量一个数据库每秒实现事务的数量，有一些人会把 tps 了解为数据库的每秒钟申请多数量，然而不太谨严。

​ 每秒能够解决的事务量，这个数据用在数据库外部中用的比拟多，意味着数据库每秒会执行多少次事务提交或者回滚。

Io 的性能指标

​ 关注的 io 相干性能指标，大家也要跟他做一个理解：

​ (1) IOPS：这个指的是机器的随机 IO 并发解决的能力，比方机器能够达到 200 IOPS，意思就是说每秒能够执行 200 个随机 IO 读写申请。

​ (2) 吞吐量 ：这个指的是机器的磁盘存储 每秒能够读写多少字节的数据量

​ (3) latency：这个指标说的是往磁盘里写入一条数据的提早。

​ 通常状况下一个一般磁盘的程序写入都是能够达到 200mb 的申请上，通常而言，磁盘吞吐量都能够到 200mb

 通常状况下一块硬盘的读写提早越低，数据库的性能就越高，执行 sql 和事务的速度就会越快。

压力测试的其余性能指标

1. cpu 负载：是一个重要的性能指标，假如数据库压测到了 3000 了，然而 cpu 负载曾经满了，也就意味着它最多只能 解决这么多数据了。

   2. 网络负载：压测到肯定的 qps 或者 tps 的时候，每秒钟都机器网卡都输出多少 mb 数据，输入多少 mb 数据，qps1000 的时候，网络负载打满了，每秒传输 100mb 达到下限也是无奈压力测试的。
   3. 内存负载：机器的消耗到了极限也是不能压力测试的。

给你一台 4 核 8G 的机器，他能够扛住每秒几千甚至每秒几万的并发申请吗?

​ 扛下多少申请，须要看理论的 cpu，硬盘，内存，网络带宽等环境。一台机器扛下 500+ 的申请曾经很高了，如果每秒 1000+ 申请，根本负载会打满。机器有可能挂掉。同时此时的内存也根本打满了，同时 jvm 的 gc 频率可能会十分高。

压力测试工具介绍

​ 应用压力测试的工具是：sysbench工具

装置教程：

装置教程如下：

curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash 
sudo yum -y install sysbench
sysbench --version 

数据库压测实战

上面是一个案例的指令，能够在应用的时候边运行后果比照理解：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable prepare

咱们别离来介绍参数的内容：

--db-driver=mysql: 这个很简略，就是说他基于 mysql 的驱动去连贯 mysql 数据库，你要是 oracle，或者 sqlserver，那 天然就是其余的数据库的驱动了 
--time=300: 这个就是说间断拜访 300 秒 --threads=10: 这个就是说用 10 个线程模仿并发访 问
--report-interval=1: 这个就是说每隔 1 秒输入一下压测状况
--mysql-host=127.0.0.1 --mysql-port=3306 --mysql-user=test_user --mysql-password=test_user: 连贯到哪台机器的哪个端口上的 MySQL 库，他的用户名和明码是什么
--mysql-db=test_db --tables=20 --table_size=1000000: 这一串的意思，就是说在 test_db 这个库里，结构 20 个测试 表，每个测试表里结构 100 万条测试数据，测试表的名字会是相似于 sbtest1，sbtest2 这个样子的 oltp_read_write: 这个就是说，执行 oltp 数据库的读写测试
--db-ps-mode=disable: 这个就是禁止 ps 模式
--prepare 这个参数会依照设置构建测试须要的数据，主动创立 20 个表，每个表 100 万数据。

全方位测试

​ 测试数据库的综合读写 TPS，应用的是 oltp_read_write 模式(大家看命令中最初不是 prepare，是 run 了，就是运行压测)：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable run

​ 测试数据库的只读性能，应用的是 oltp_read_only 模式(大家看命令中的 oltp_read_write 曾经变为 oltp_read_only 了)：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_only --db-ps-mode=disable run

​ 测试数据库的删除性能，应用的是 oltp_delete 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 --table_size=1000000 oltp_delete --db-ps-mode=disable run

​ 应用下面的命令，sysbench 工具会依据你的指令结构出各种各样的 SQL 语句去更新或者查问你的 20 张测试表里的数据，同时 监测出你的数据库的压测性能指标，最初实现压测之后，能够执行上面的 cleanup 命令清理数据：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

​ 测试数据库的更新索引字段的性能，应用的是 oltp_update_index 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_index --db-ps-mode=disable run

​ 测试数据库的更新非索引字段的性能，应用的是 oltp_update_non_index 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

​ 测试数据库的更新非索引字段的性能，应用的是 oltp_update_non_index 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_update_non_index --db-ps-mode=disable run

​ 测试数据库的插入性能，应用的是 oltp_insert 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_insert --db-ps-mode=disable run

​ 测试数据库的写入性能，应用的是 oltp_write_only 模式:

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_write_only --db-ps-mode=disable run

​ 最初实现压测之后，能够执行上面的 cleanup 命令清理数据：

sysbench --db-driver=mysql --time=300 --threads=10 --report-interval=1 --mysql-host=127.0.0.1 --mysqlport=3306 --mysql-user=test_user --mysql-password=test_user --mysql-db=test_db --tables=20 -table_size=1000000 oltp_read_write --db-ps-mode=disable cleanup

测试后果剖析

​ 上面是一个 sysbench 的运行后果：

[22s] thds: 10 tps: 380.99 qps: 7312.66 (r/w/o: 5132.99/1155.86/1321.35) lat (ms, 95%): 21.33 err/s: 0.00 reconn/s: 0.00

剖析：

​ 下面说的是第 22s 产生的事件，以及其余字段别离代表的含意：

Tdhs: 示意 10 个线程
Tps: 每秒执行了 380 个事务
Qps: 每秒执行了 7312 个申请
(r/w/o)…: 这一段内容示意 7312.66 个申请当中，有 5132.99 个是读申请。1155.86 是写申请，1321 个其余类型的申请。其实就是对整个 qpsj 你想拆分
Lat(ms, 95%): 意思是说，95% 提早在 21.33 毫秒以下
Err/s：每秒 0 个失败，产生了 0 次网络重连。

压测报告的解释

​ 依据下面的压测命令在后果的最初是压测的整个报告，对于报告的内容其解释如下：

SQL statistics:queries performed:

  read: 1480084 
  // 这就是说在 300s 的压测期间执行了 148 万屡次的读申请 write: 298457 
  // 这是说在压测期间执行了 29 万屡次的写申请

  other: 325436 
  // 这是说在压测期间执行了 30 万屡次的其余申请

  total: 2103977 // 这是说一共执行了 210 万屡次的申请
  // 这是说一共执行了 10 万多个事务，每秒执行 350 多个事务 transactions: 105180(350.6 per sec.)
  // 这是说一共执行了 210 万屡次的申请，每秒执行 7000+ 申请 queries: 2103977 (7013.26 per sec.)

  ignored errors: 0 (0.00 per sec.) reconnects: 0 (0.00 per sec.)
  // 上面就是说，一共执行了 300s 的压测，执行了 10 万 + 的事务 General staticstics:

total time: 300.0052s

total number of events: 105180

Latency (ms):

  min: 4.32 
  // 申请中提早最小的是 4.32ms

  avg: 13.42 
  // 所有申请均匀提早是 13.42ms

  max: 45.56 
  // 提早最大的申请是 45.56ms

    95th percentile: 21.33 
    // 95% 的申请提早都在 21.33ms 以内

​ 其实压测就是用命令察看对应的状况 用减少线程的伎俩试探机器的极限，另外一个就是通过 linux 的相干命令查看是否是失常状况。

命令查看压力测试性能：

Top：

​ top 命令最直观展现 cpu 负载的，在 linux 执行 top 指令，咱们能够看到上面的内容：

top 15:52:00 up 42:35, 1 user, load average: 0.15, 0.05, 0.01    

​ 上面是对于下面的后果内容展现：

这里指的是零碎运行了 42:35 分钟，15:52 指的是以后的工夫，1 user 就是一个用户在应用
Load averatge 0.15, 0.05, 0.01 示意的则是 cpu 在 1 分钟，5 分钟，15 分钟的负载状况, 假如一个 4 外围的 cpu，负载时 0.15，就是说 4 个外围一个外围都没用满。如果你的负载是 1，阐明 4 核有一个比较忙了

​ 测时如何察看机器的内存负载状况?

• Mem: 33554432k total, 20971520k used, 12268339 free, 307200k buffers
• Top 命令最初的内容是如下，总内存有 32gb，曾经应用了 20gb，还有 10 多个是闲暇的。

如何看磁盘 io

​ 应用 dstat -d 命令，会看到如下的货色:

-dsk/total read writ 103k 211k
0 11 

​ 在硬件的肯定正当的负载范畴内，把数据库的 QPS 进步到最大，这就是数据库压测的时候最正当的一个极限 QPS 值。

Prometheus 和 Grafana 两个零碎

​ 这两个零碎请自行百度查阅理解和学习：

​ prometheus：是一个监控数据采集和存储系统，能够看作是定期从 mysql 中采集须要的监控数据。

​ granfana：实用于和 prometheus 进行辅助组合应用的，能够对于 mysql 进行一个可视化的监控动作。

思考题：

1. 假如开发的 Java 零碎部署在一台 4 核 8G 的机器上，那么咱们假如这个 Java 零碎解决一个申请十分十分快，每个申请只须要 0.01ms 就能够解决完了，那你感觉这一台机器部署的 Java 零碎，能够实现每秒抗下几千并发 申请吗? 能够实现每秒抗下几万并发申请吗?

答案起源：MySQL：5 生产环境下的数据库机器配置_136.la

答：每个申请解决 0.01ms，应该是不波及磁盘的纯内存操作

在 4 核 8G 的机器上，也就是同时有 4 个线程数为最佳，多了反而会因为竞态问题导致频繁上下文切换，节约性能。

实践上的并发申请数量

这里依照数据库应用 3 个线程，另一个 CPU 外围线程给其余过程应用。

在不思考网卡资源的状况下，实践上能够实现每秒 3 * 1s/0.01ms = 30 万申请。

生产环境下的影响因素

在实在生产环境下，还须要思考很多的因素。生产环境必然不只是有 4 个线程在工作，那么就导致会存在 CPU 线程竞态切换的工夫；当并发量高的时候还要算下内存耗费产生 YGC 和 Full GC 的 STW 工夫也算进去；当并发很高，对 CPU 负载也很高，解决会变慢，此时 TPS 会很长。还有磁盘 IO、网卡等因素也须要思考进去。

此时，依照均值每 个申请所须要的耗时可能达到 100ms 左右。

2. 对于 QPS 和 TPS 的。如果一个交易系统拆分为了很多服务，那么每个服务每秒接管的 并发申请是 QPS 还是 TPS 呢?

这个显著是 QPS，因为每个服务就负责干本人的一些事儿，其实对他来说，每秒并发申请数量就是 QPS。

总结

​ 本次咱们从简略的零碎测试动手，介绍了影响 mysql 服务的指标，其实影响一个 mysql 服务性能的参数有很多，包含内存，处理器，io 性能，网络带宽都有影响，所以不能齐全依照理性化的配置去猜想数据库能接受多少压力，而是要依据压力测试对于数据库进行理论的压测之后，通过减少压力的形式找到 mysql 服务器的压力极限，最初通过两个思考题咱们能够看到掂量一个 mysql 的性能须要从多方面思考，哪怕是现实状况下可能解决的申请其实也不是很多。

写在最初

​ 本文介绍的内容较为根底和简略，心愿能够通过这篇文章引发更多对于 mysql 性能的思考。