mysql服务治理

一、数据库性能指标

1.QPS

一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单 SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。

根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。

我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了。

1.QPS 突增问题

有时候由于业务突然出现高峰，或者应用程序 bug，导致某个语句的 QPS 突然暴涨，也可能导致 MySQL 压力过大，影响服务。

我之前碰到过一类情况，是由一个新功能的 bug 导致的。当然，最理想的情况是让业务把这个功能下掉，服务自然就会恢复。

而下掉一个功能，如果从数据库端处理的话，对应于不同的背景，有不同的方法可用。我这里再和你展开说明一下。

1. 一种是由全新业务的 bug 导致的。假设你的 DB 运维是比较规范的，也就是说白名单是一个个加的。这种情况下，如果你能够确定业务方会下掉这个功能，只是时间上没那么快，那么就可以从数据库端直接把白名单去掉。

2. 如果这个新功能使用的是单独的数据库用户，可以用管理员账号把这个用户删掉，然后断开现有连接。这样，这个新功能的连接不成功，由它引发的 QPS 就会变成 0。

3. 如果这个新增的功能跟主体功能是部署在一起的，那么我们只能通过处理语句来限制。这时，我们可以使用上面提到的查询重写功能，把压力最大的 SQL 语句直接重写成"select 1"返回。

当然，这个操作的风险很高，需要你特别细致。它可能存在两个副作用：

1. 如果别的功能里面也用到了这个 SQL 语句模板，会有误伤；

2. 很多业务并不是靠这一个语句就能完成逻辑的，所以如果单独把这一个语句以 select 1 的结果返回的话，可能会导致后面的业务逻辑一起失败。

所以，方案 3 是用于止血的，跟前面提到的去掉权限验证一样，应该是你所有选项里优先级最低的一个方案。

同时你会发现，其实方案 1 和 2 都要依赖于规范的运维体系：虚拟化、白名单机制、业务账号分离。由此可见，更多的准备，往往意味着更稳定的系统。

2.IOPS

1.定义

在数据库性能优化的过程中，了解IOPS（Input/Output Operations Per Second，即每秒输入/输出操作数）是至关重要的。

IOPS是衡量数据库磁盘性能的重要指标之一，可以帮助我们了解数据库在处理输入输出操作时的效率和吞吐量。

IOPS的计算公式如下所示：

IOPS = 请求的IO总数 / 测试时间

IOPS的数值受多个因素的影响，主要包括以下几个方面：

1.硬件设备

数据库的硬件设备对IOPS有着直接的影响。例如，硬盘类型（机械硬盘还是固态硬盘）、硬盘容量、硬盘转速等都会影响IOPS的数值。一般来说，固态硬盘的IOPS要远高于机械硬盘。

2.数据库参数配置

数据库的参数配置也会对IOPS产生一定的影响。例如，innodb_io_capacity参数可以用来控制MySQL的IOPS性能，适当调整该参数的值可以提升数据库的IOPS表现。

那么如果当数据库的io负载过高，会带来哪些影响？

1.响应时间延长

因为没有io资源读写磁盘了，自然响应时间会延长

2.系统崩溃

极端情况超过系统io能力会导致系统崩溃重启等。

2.IOPS监控

可以使用一些专门的监控工具来监控数据库的IOPS。例如，MySQL自带的Performance Schema可以提供详细的性能统计信息，包括IOPS的数值。另外，还有一些第三方监控工具如pt-diskstats、mpstat等也可以提供有关IOPS的监控信息。

3.提升IOPS

思考一个问题：如果你的 MySQL 现在出现了性能瓶颈，而且瓶颈在 IO 上，可以通过哪些方法来提升性能呢？

针对这个问题，基于将io压力转移到内存的思路，可以考虑以下三种方法：

1. 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数，减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢失数据的风险。

2. 将 sync_binlog 设置为大于 1 的值（比较常见是 100~1000）。这样做的风险是，主机掉电时会丢 binlog 日志，但也总比io负载过高好

3. 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是，主机掉电的时候会丢数据。

我不建议你把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0，表示 redo log 只保存在内存中，这样的话 MySQL 本身异常重启也会丢数据，风险太大。

而 redo log 写到文件系统的 page cache 的速度也是很快的，所以将这个参数设置成 2 跟设置成 0 其实性能差不多，但这样做 MySQL 异常重启时就不会丢数据了，相比之下风险会更小，因为page cache是操作系统层面的概念，它被所有进程共享，只有当操作系统物理内存不足等原因才会进行刷盘操作，但如果主机掉电或者关机后page cache会丢失。

3.连接池资源

1.连接池大小设置

先说结论

下面公式由 PostgreSQL 提供，它适用于市面上绝大部分数据库产品。还有，你应该模拟预期的访问量，并通过下面的公式先设置一个偏合理的值，然后在实际的测试中，通过微调来寻找最合适的连接数大小。

连接数 = （核心数 *2） + 有效磁盘数

按照这个公式，如果你的服务器 CPU 是 4 核的，连接池大小应该为（4 * 2）+ 1 = 9，取个整我们就设置为 10。如果你跑个性能测试就可以看到，它能轻松支撑 3000 用户以 6000TPS 的速率并发执行简单查询的场景。

那么我们思考一下下面的问题

连接数设置过大会怎样？

1.资源浪费

设置过大的最大连接数可能导致过多的数据库连接被创建，占用过多的系统资源（如内存、线程等），从而浪费资源。这可能会导致应用程序运行缓慢，并使其他系统组件的性能受到影响。

2.负载过高

如果最大连接数设置过大，可能导致连接池无法管理和处理如此多的连接，从而导致连接泄露、内存泄漏或系统崩溃

连接数设置过小会怎样？

1.增加访问时间

最小连接数设置过低，当应用程序需要建立新的数据库连接时，可能需要等待连接池中的连接被释放或新的连接被创建。这将增加连接的延迟时间，并降低应用程序的响应性能，并且连接线程频繁的创建和消耗也会消耗操作系统资源。

下面分享一个案例

某个团队对 Oracle 数据库进行了压力测试，模拟 9600 个并发线程来操作数据库，每两次数据库操作之间 sleep 550ms，最开始的时候设置的线程池大小为 2048。测试结果是：每个请求要在连接池队列里面等待 33ms，获得连接之后，执行 SQL 需要耗时 77ms，CPU 消耗维持在 95%左右。

接下来将连接池大小改小点，设置成 1024， 其他测试参数不变，测试结果：获取连接等待时长基本不变，但是 SQL 的执行耗时降低了。

接下来再设置小一些，连接池大小降低到 96， 并发数等其他参数不变，测试结果：每个请求再连接池队列中的平均等待时间为 1ms，SQL 执行耗时为 2ms。

我们没有调整任何东西，仅仅只是将数据库连接池的大小降低了，这样就能把之前平均 100ms 的相应时间缩短到了 3ms，吞吐量指数级上升。

然后我们在看一下一些常见的命令

1.查看当前连接线程数

SHOW STATUS LIKE 'Threads%'; -- 显示线程状态信息

从图中我们看到建立连接的线程数为19个

然后执行show full processlist命令，可以看到已连接线程的详细信息

从图中我们发现，在业务低谷的时候，其实大部分连接线程都是处于空闲状态的，它会占据一定的cpu资源。

一般建议5 至 20 之间

2.查看当前最大连接数

SHOW VARIABLES LIKE 'max_connections'; -- 显示最大连接数

MySQL默认的最大连接数为100，MySQL服务器允许的最大连接数16384。、

常见的配置范围为 20 至 200 之间。

可以通过下面命令，临时调大连接池的大小

 set global max_connections=300

最大连接数是连接池能申请的最大连接数。如果数据连接请求超过此数，后面的数据连接请求将被加入到等待队列中，这会影响之后的数据库操作。如果最小连接数与最大连接数相差太大，那么，最先的连接请求将会获利，之后超过最小连接数量的连接请求等价于建立一个新的数据库连接。

不过，这些大于最小连接数的数据库连接在使用完不会马上被释放，它将被放到连接池中等待重复使用或是空闲超时后被释放。

MySQL服务器的连接数并不是要达到最大的100%为好，我一般看到超过70%就要引起注意了，还是要具体问题具体分析。

2.短连接风暴

正常的短连接模式就是连接到数据库后，执行很少的 SQL 语句就断开，下次需要的时候再重连。如果使用的是短连接，在业务高峰期的时候，就可能出现连接数突然暴涨的情况。

MySQL 建立连接的过程，成本是很高的。除了正常的网络连接三次握手外，还需要做登录权限判断和获得这个连接的数据读写权限。

在数据库压力比较小的时候，这些额外的成本并不明显。

但是，短连接模型存在一个风险，就是一旦数据库处理得慢一些，连接数就会暴涨。max_connections 参数，用来控制一个 MySQL 实例同时存在的连接数的上限，超过这个值，系统就会拒绝接下来的连接请求，并报错提示“Too many connections”。对于被拒绝连接的请求来说，从业务角度看就是数据库不可用。

在机器负载比较高的时候，处理现有请求的时间变长，每个连接保持的时间也更长。这时，再有新建连接的话，就可能会超过 max_connections 的限制。

碰到这种情况时，一个比较自然的想法，就是调高 max_connections 的值。但这样做是有风险的。因为设计 max_connections 这个参数的目的是想保护 MySQL，如果我们把它改得太大，让更多的连接都可以进来，那么系统的负载可能会进一步加大，大量的资源耗费在权限验证等逻辑上，结果可能是适得其反，已经连接的线程拿不到 CPU 资源去执行业务的 SQL 请求。

那么这种情况下，你还有没有别的建议呢？我这里还有两种方法，但要注意，这些方法都是有损的。

第一种方法：先处理掉那些占着连接但是不工作的线程。

max_connections 的计算，不是看谁在 running，是只要连着就占用一个计数位置。对于那些不需要保持的连接，我们可以通过 kill connection 主动踢掉。这个行为跟事先设置 wait_timeout 的效果是一样的。设置 wait_timeout 参数表示的是，一个线程空闲 wait_timeout 这么多秒之后，就会被 MySQL 直接断开连接。

但是需要注意，在 show processlist 的结果里，踢掉显示为 sleep 的线程，可能是有损的。我们来看下面这个例子。