MySql CPU彪高到百分之1000的排查思路

  原文内容来自于LZ(楼主)的印象笔记，如出现排版异常或图片丢失等情况，可查看当前链接：https://app.yinxiang.com/fx/bf7839b3-5f7b-4212-9f7d-5f5577e952ea  

MySql CPU彪高到百分之1000的排查思路

  查看当前MySql的CPU已经在百分之 1019   下述为当前MySql的所以子线程的CPU使用状况，可以看到当前已经有11个线程的CPU都是在99%左右   进行问题的排查： 1、首先怀疑是否是存在较大的全盘扫描的SQL语句，导致MySql查询时间过长导致的CPU彪高现象 2、查询当前MySql的my.cnf配置，是否是MySql配置问题（由于我司运维同学有一个统一的MySql公共配置可用，所以并没有怀疑是MySql的配置不合理而导致的问题，最终确认，也的确不是MySql的配置问题，博客结尾会给出一份优化过的MySQL5.7.20的安装包，需要的朋友可自取） 3、验证当前服务器的IO，是否是过于频繁的读写引起的IO异常而导致的CPU彪高；   1、使用 show FULL PROCESSLIST 查询当前MySql的线程执行情况   由于之前出现过由于用户的使用过于频繁，而导致的表数据量过大引起的数据查询缓慢的问题，所以在此CPU彪高之前，已经频繁使用 show processlist 查看过执行时间过长的SQL，并且皆已经做了相对应的处理（相关的业务皆已经分表分层，以及都增加了相关的表索引），所以，我在人工观察 show processlist的过程当中，的确没有再出现过 Query过程中Time占用时间很长的情况；   并且查看了对应的 mysql-slow.log 日志（在最初部署Mysql的同时，MySql的配置中已经开启过了慢SQL记录的日志功能），查询了对应的慢SQL的记录日志后，的确是不存在在当前CPU彪高的时间点中存在慢SQL查询的情况；   所以，也就是说，当前已经不在存在由于表数据量较大导致的SQL查询慢的情况；那么此时则开始转向思考，难道是我的服务在使用MySql的过程中，读写频率太高了？而导致的MySql服务CPU异常？LZ（楼主）当前所负责开发的产品是一款坐席的实时通话的产品，相关的业务逻辑中的确是存在频繁的读写DB的；所以此时开始转向思考是否是读写频率太高而引起的CPU彪高的问题，于是开始了以下的排查；   2、查询CPU的IO读写速率 分别使用sar 命令获取 IO的读写速率监控： 1、sar -b 1 30 2、sar -u -o 1 30   通过使用，sar 命令查看了相关的IO信息后，发现并没有出现 IOWAIT过高的问题，并且也没看到每秒钟物理设备读写频率过高的问题，所以此处就很疑惑；并且由于此处使用sar是分析的系统级IO使用情况，所以也很 难避免是否存在MySql自身的机制，比如某些Sql的查询频繁被命中缓存，所以走缓存查询而并没有直接走IO读取的情况；由于上述使用TOP命令时，所对应的wa值也是很低的，那么到底是什么原因引起的MySQL的CPU彪高呢，而且还是如此高？   此时，在逐步的排查中，重点来了：   当首先出现了CPU彪高的时候，我们对于Mysql的第一印象就是某SQL执行较慢，导致的线程卡死最终引起的CPU彪高，我们会去排查慢SQL的执行日志，或者查看MySql的执行日志，是否有异常的情况出现；甚至还会怀疑是否是应用程序链接Mysql的线程池太多，没有及时回收线程链接，而导致不断创建线程链接最终导致的MySQL服务彪高（此时使用 top -Hp 查看了进程所创建的线程数量，也是正常数据）；   但始终就一直没怀疑，是否是并发过高，而导致的MySQL CPU问题，其实LZ最初也怀疑过，难道是应用程序的并发过高而导致的吗？但简单想了一下，当前的应用程序并还没有到达完全的负载状态，理论上MySQL这样一个成熟的DB，不至于如此便随意彪高了，虽然是在简单的回想，但手上的动作还是敲起了熟悉的命令：show full processlist ，因为如果并发高的情况下，采取每1秒获取一次正在运行的线程数量的方式（采用SQL脚本for输出），可以判断出是否存在运行线程递增的情况。     此时则发现了一个新的问题，我每一次不管任何时候查询当前正在运行的线程，都会看到这样一个SQL在执行中过程中，尽管对应的Time查询时间很短，该线程的ID便直接结束了，但意外的是，每一次查看时，都能看到该SQL在运行中；换句话理解则是，该SQL查询结果是很快的，但是不管在任何一个时间查看线程时都能看到该SQL正在执行中，这只能说明一个问题，，那就是该SQL的查询频率极高！高到每毫秒不间断的都一直在重新查询该SQL；   所以这只能是代码BUG问题，最终根据该线程所一直在执行的SQL反向推到应用程序中以后发现，。。。。的确是存在一个定时器的业务BUG，由于定时器是每秒钟都会被执行，且当前定时器设置的是并行模式（即当前定时器线程无论是否执行完成，只要1秒以后，都会紧接着开启新的线程执行该定时器代码块，所以，这就导致了一旦线程执行了1分钟，甚至更久，那么将会导致后续的所有线程都会执行1分钟的时间，并且还在源源不断的创建新的线程，且由于线程代码快中，对该SQL查询代码还是循环的在查询........（后续这块代码被重构了），所以这将导致不断的for循环查询，再加上不断的新的创建的创建查询，引起了CPU的循环彪高的情况。。。。。）     所以如果有遇到相同CPU彪高的情况，但是一直在生产环境没法定位到具体SQL的情况下，此时记得也一定要关注下SQL的执行频率问题，因为最初的确没有想到执行频率可以高到这种程度，一个如此高的执行频率竟然可以让MySQL彪高到如此大的CPU；     此处做下简单汇总： MySQL的服务器本质上是一个很稳定的软件了，正常情况下只要是给与正确的服务器配置（my.cnf），很难再出现因为连接数过多，或者线程所占用内存较大，导致的内存或者CPU问题，因为在对应的mysql的配置中，可以设置对应的线程链接数，以及线程所占用的内存大小，包括缓冲区的内存大小等配置，只要给与合理的配置，这些问题所导致的服务异常都很难看到，也正是因为如此，所以MySQL这样的服务对外所提供的排查手段也是极为有限的，类似排查MySQL的运行日志，以及查看慢SQL的日志，和查看线程的执行状况等，只有寥寥几个是MySQL所提供的排查工具； 所以如果出现了MySql的服务异常： 1、排查MySql运行日志，是否有错误提示 2、排查是否是全表查询慢SQL导致的服务异常 3、排查MySQL内部线程的执行情况 4、排查SQL线程的执行频率   MySQL本质上还是一个DB服务，提供数据的存储和检索，一般情况下更大的问题还是来自于不合理的表的使用，以及不合理的全表检索，和不合理的索引等使用上而导致的服务异常的情况偏多，而对于上述LZ做碰到的频繁的线程查询而导致的CPU彪高，应该也是很难遇到的。。。当然，，如果遇到类似的问题，一定也要记得关注下执行频率而会导致的问题。   此处推荐一份已经被验证过的常用的MySQL服务器配置方式，以及一个简单优化了my.cnf后的mysqlDB.5.7.20的实例安装包： 链接：https://pan.baidu.com/s/1hF99W4DJY8vSZ0rx3NYYow 提取码：a47j [client] port = 3306 default-character-set=utf8 #character_set_server=utf8     [mysqld] port = 3306 character_set_server=utf8 basedir = /opt/mysqlDB/mysql datadir = /opt/mysqlDB/dbData server-id = 1 #表示是本机的序号为1,一般来讲就是master的意思 skip-name-resolve #skip-networking back_log = 600 max_connections = 1000 open_files_limit = 65535 table_open_cache = 128 max_allowed_packet = 4M binlog_cache_size = 1M max_heap_table_size = 8M tmp_table_size = 16M read_buffer_size = 2M read_rnd_buffer_size = 8M sort_buffer_size = 8M join_buffer_size = 8M thread_cache_size = 16 query_cache_size = 8M query_cache_limit = 2M key_buffer_size = 4M log_bin = /opt/mysqlDB/logs/binLog/mysql-bin binlog_format = mixed expire_logs_days = 7 #超过7天的binlog删除 log_error = /opt/mysqlDB/logs/errorLog/mysql-error.log #错误日志路径 slow_query_log = 1 long_query_time = 10 #慢查询时间 超过1秒则为慢查询 slow_query_log_file = /opt/mysqlDB/logs/slowLog/mysql-slow.log performance_schema = 0 explicit_defaults_for_timestamp lower_case_table_names = 1 #不区分大小写 skip-external-locking #MySQL选项以避免外部锁定。该选项默认开启 default-storage-engine = InnoDB #默认存储引擎     innodb_file_per_table = 1 innodb_open_files = 500 innodb_buffer_pool_size = 5G innodb_write_io_threads = 4 innodb_read_io_threads = 4 innodb_thread_concurrency = 0 innodb_purge_threads = 1 innodb_flush_log_at_trx_commit = 2 innodb_log_buffer_size = 2M innodb_log_file_size = 32M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120     bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 interactive_timeout = 28800 wait_timeout = 28800     sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES [mysqldump] quick max_allowed_packet = 16M #服务器发送和接受的最大包长度 [myisamchk] key_buffer_size = 8M sort_buffer_size = 8M read_buffer = 4M write_buffer = 4M         相关链接参考推荐： 1、使用sar 命令进行IO以及CPU读写速率的排查，相关 sar 命令参数使用详解可参考如下链接： http://lovesoo.org/linux-sar-command-detailed.html?aonypw=msmxi   https://blog.csdn.net/u012167045/article/details/55056940 2、索引的创建及优化方式：  https://www.cnblogs.com/lfs2640666960/p/9147768.html  3、show processlist 字段解释 https://blog.csdn.net/dhfzhishi/article/details/81263084