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

前言

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

概述

介绍常见的mysql零碎性能剖析指标，介绍吞吐量和机器的抉择
压力测试工具的介绍，以及数据库压力测试的实战。
最初将会依据Linux零碎的命令介绍如何浏览mysql服务器的性能
简略介绍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和事务的速度就会越快。

压力测试的其余性能指标

cpu负载：是一个重要的性能指标，假如数据库压测到了3000了，然而cpu负载曾经满了，也就意味着它最多只能解决这么多数据了。
1. 网络负载：压测到肯定的qps或者tps的时候，每秒钟都机器网卡都输出多少mb数据，输入多少mb数据，qps1000的时候，网络负载打满了，每秒传输100mb达到下限也是无奈压力测试的。
2. 内存负载：机器的消耗到了极限也是不能压力测试的。

给你一台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 sysbenchsysbench --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.0052stotal number of events: 105180Latency (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 211k0 11

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

Prometheus和Grafana 两个零碎

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

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

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

思考题：

假如开发的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左右。

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

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

总结

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

写在最初

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