10、生产环境如何排查慢Sql
慢查询定位总结
1、介绍一下当时产生问题的场景（我们当时的一个接口测试的时候非常的慢，压测的结果大概5秒钟/做数据计算查询数据很慢导致任计算任务执行超时）
2、我们系统中当时采用了运维工具（ Skywalking /ps数据库自带监控系统查看sql执行时间），可以监测出哪个接口，最终因为是sql的问题
3、在mysql/postgresql中开启了慢日志查询，我们设置的值就是2秒，一旦sql执行超过2秒就会记录到日志中（调试阶段）
4、上线前的压力测试，观察相关的指标 tps / qps / rt / io / cpu
sql问题排查思路：
如果已知是sql问题，直接找dba查询日志，查看执行计划或者sql执行日志，如果sql没有到数据库执行，那么需要找网络组进行抓包处理，看某个一时间段网络情况，如果网络也没有问题，那么就只能查看代码或者配置导致连接泄露。
发现:
- 系统监控:基于 arthars 运维工具、promethues 、skywalking mysql
- 慢日志: 会影响一部分系统性能
慢 SQL 处理 由宏观到微观
- 检查 系统相关性能 top / mpstat / pidstat / vmstat 检查 CPU / IO 占比，由进程到线程
- 检查 MySQL 性能情况，show processlist 查看相关进程 | 基于 MySQL Workbench 进行分析
- 检查 SQL 语句索引执行情况，explain 关注 type key extra rows 等关键字
- 检查是否由于 SQL 编写不当造成的不走索引，例如 使用函数、not in、%like%、or 等
- 其他情况: 深分页、数据字段查询过多、Join 连表不当、业务大事物问题、死锁、索引建立过多
- 对于热点数据进行前置，降低 MySQL 的压力 redis、mongodb、memory
- 更改 MySQL 配置 ， 处理线程设置一般是 cpu * 核心数 的 2 倍，日志大小 bin-log
- 升级机器性能 or 加机器
- 分表策略，分库策略
- 数据归档
实际操作流程
对于Kingbase或者postgre数据库，一般都会开启慢查询日志,一般情况下，执行时间超过1s的sql都会被记录在系统日志中，所以我们首先需要联系dba查看sys_stat_activity表，当前系统的数据库用户，哪一个sql执行的时间比较长，一般这张表中会详细记录sql的执行情况，包括事务开始时间，sql开始执行时间，如果sql有等待事件，还会显示等待的是哪一个事件，因此通过这张表，我们一般都能够查到哪些sql执行的时间长以及原因。
其次也需要关注系统数据库资源使用情况，一般生产环境中，不同系统使用不同的数据库，但是也有一些系统，使用的是同一个数据库实例，但是是不同的schema，但是这时候就要咨询dba是否不同的schema是否做了资源隔离，如果没有做资源隔离，要尽快查看资源的使用情况，一般通过监控系统zabbex可以直观的看到数据库资源的使用情况。
如果是多个数据库schema复用同一个数据库实例的情况，也要请dba看一下是否数据库中存在长时间执行的事务，长时间执行的事务可能占据更多的资源，要尽快杀掉长事务并且查明原因。
对于postgre底座的数据库，如果查询数据很慢，就是有索引的情况还是很慢，那么需要查看某一张表的死元组数量，通过sys_stat_all_tables或者sys_stat_user_tables可以查看到表中详细的活动元组和死元组情况，一般delete或者update操作，数据库并不会立马删除或者更新原始数据，而是在拷贝一份原始数据，在原始数据上进行更新操作，原始数据只是被打上删除或者更新的标签，只有触发volucm操作后，死元素才会被清除并且释放数据库空间，否则这些死元素对数据库是可见的，在执行查询操作时候，扫描了大量的死元素，并且导致执行计划走偏，进而降低查询效率。
通常，还可以使用explain analyse 分析执行查询的sql语句详细执行计划，判断查询条件是否走索引，如果是全表扫描而且表很大的话，需要对字段做索引优化。
最后，一般生产数据库也会生成当天的执行报告，通过报告查看哪些sql执行的慢，执行慢的sql是否在等待锁或者事务，进而更一步排查。
生产环境应用执行update慢问题排查
现象
生产环境，分区表大概5000w级数据，按照date字段进行分区存储，并且对订单编号，日期等多个字段建立索引结构，生产环境执行update语句，where条件后面有订单编号和日期索引，更新3.5w数据大概要15分钟，平均更新一条数据大概22ms，严重影响每天数据产出时间；
问题排查
- 首先查看数据库执行报告，发现没有执行时间超过1s的sql，因此排除慢sql原因；
- 查看数据库报告，看是否有锁等待或者事务等待，发现没有锁和事务等待，排掉此原因；
- 登录数据库客户端，查看每个分区数据，是不是有大分区数据，发现每个分区数据分布在100w级别，因此并不是数据量大导致的更新慢问题；
- 执行explain analyze执行计划，判断是否走索引：
- 数据表建立了多个索引，成交编号主键索引，日期字段建立索引；
- 通过现场环境执行查询sql发现，强制使用不同的索引发现，如果单独使用日期字段查询，查询时间在20ms左右。
- 但是如果使用成交编号和日期字段查询，查询时间不到1ms左右，因此定位到原因，执行计划走偏，单独走日期索引；
- 通过和工程师沟通，发现走索引的成本估计是根据表的统计信息确定的，理论上，通过主键索引和日期索引成本是最低的，但是现实情况是通过日期索引成本最低。
- 因此排查可能是表的统计信息收集错误，现场变更重新出发表的统计信息收集，再次执行查询sql发现效率显著提高；
为什么表的统计信息收集错误？
因为每天都有4w成交入库，但是表的统计信息收集是自动触发的，只有更新数据的比较超过表的10%左右的数据才会自动触发，但是分区表数据很大，因此没有触发统计信息收集；
生产环境执行sql导致数据库被系统kill发生主备切换
应用通过数据库用户每连接到数据库，数据库都对应创建一个数据库进程。数据库将磁盘文件通过mmap缓存到内存中供数据库进程查询，该缓存内存就统计到了数据库用户总内存。
当时操作系统统计数据库用户总内存超过64G（进程本身的内存21G， 文件缓存43G）。触发操作系统的内存限制，操作系统选择kill了一个进程。
数据库对文件缓存的释放策略：当应用的连接断了才关闭对应数据库进程； 当使用文件缓存的数据库进程都关闭了，才释放文件缓存。
应用使用druid连接池并配置心跳保活，所以有一定概率导致连接会长时间存在。
根据数据库服务器日志统计出存活160个数据库进程，有10个连接使用次数超过100（分别是：827，542，473, 431, 329, 237, 201, 125, 119，99)。 有18个连接使用次数在10~60次不等。 其余连接使用次数在3次左右。统计出数据库进程的最大内存是1.9G，对应的数据库连接是用了473次。存在最大的数据库连接使用次数800多次（数据库进程内存为183M）。所以连接使用的次数对数据库的内存的影响交小，数据库内存大的主要原因是对大表的数据的查询导致的大量的文件缓存。
数据库租户使用的内存超过分配的64G时，触发操作系统的内存限制，操作系统选择kill了一个进程后进行回滚动作，由于数据库回滚动作时间过长，导致数据库服务进行主备切换。
目前数据库内存使用判断是操作系统层面的判断，内存大小的计算包含了文件缓存的大小。文件缓存的新老缓存释放机制涉及两个场景，一个是进程的完全断开，另一个是基于整个服务器的内存使用情况来决定是否释放，系统触发淘汰机制，优先释放最久未使用的缓存页。
最终结论：
一方面是应用端查询数据后释放连接，但因使用了连接池的连接心跳保活机制，连接池中一部分曾经查询过大表数据连接并未对数据库彻底释放。而金仓数据库需连接彻底释放，才会释放文件缓存。另一方面因数仓租户文件缓存使用到43G时，整个服务器内存使用率却不高，无法触发淘汰机制，优先释放最久未使用的文件缓存。
生产环境执行查询走索引后执行慢问题排查
基本排查流程和【生产环境应用执行update慢问题排查】一样
最终和工程师沟通，查看表中的死元祖，发现死元祖过多，导致查询，更新效率很低，当日表中的数据每天都会同步到历史表，当日表中的数据量不到百万，但是死元祖超过百万，导致数据量很大，影响增删改查效率；
死元组如何影响执行效率?
被回收前的死元组任然会被执行器扫描扫，在更新频繁的表中(比如定时任务表，每秒更新一次全表)死元组累积的数量会非常多，表中可见的元组只有几十行的情况虚下，死元组可能会累计至上万行，导致数据库耗费多余时间扫描无用行。 补充一下什么是死元祖：
数据库的更新和删除操作，是逻辑上的更新和删除，对于删除的数据，只是给待删除的数据打上删除标志，存储层面并没有真正的执行删除动作，在数据库彻底吧这种逻辑删除的数据清理之前，这种逻辑删除的数据对数据库是可见的，用户在查询数据的时候，是会参与扫描过滤的。更新操作也是如此，因此导致数据库中的死元祖数据量特别大；
为什么这些死元祖没有被定时清除？
数据库执行autoVolucm操作，会清除掉死元祖，但是autovacuum的回收范围限制在当前最旧事务之前，当前最旧事务开始时间之后的元组被数据库认为仍然可见，不会被回收。生产环境光当天有一个长事务执行时间很长，一直卡住没执行完，导致没有按时清除掉死元组，并且同一个实例中，事务是实例级别可见的，无法直接隔离。所以即使不是自己数据库中的事务，长事务仍然会影响autovacuum回收死元组。
贡献者
codingLab版权所有
版权归属：
许可证：