A Simple Approach to Troubleshooting High CPU in MySQL

最新推荐文章于 2022-04-29 11:35:00 发布
最新推荐文章于 2022-04-29 11:35:00 发布
阅读量198 收藏
点赞数
分类专栏： MySQL
原文链接：https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/?utm_campaign=Blog%202016%3A%20Subscribers%20To%20Blog%20Weekly%20Recap%20Email%20--%202.15.16&utm_source=hs_email&utm_medium=email&utm_content=86927141&_hsenc=
版权

A Simple Approach to Troubleshooting High CPU in MySQL

我们的一位客户最近问，是否有可能从MySQL方面识别导致系统CPU使用率高的查询。 PostgreSQL和Oracle DBA长期以来一直使用简单的OS工具查找罪魁祸首，但是它对MySQL无效，因为历史上我们一直缺乏将OS线程与内部线程进行匹配的工具 - 直到最近。

Percona添加了支持，可从针对MySQL 5.6.27的Percona Server开始，通过information_schema.processlist表的TID列将进程列表ID映射到OS线程ID。 在5.7发行版中，MySQL扩展了PERFORMANCE_SCHEMA.THREADS表并添加了一个名为THREAD_OS_ID的新列，从而实现了自己的实现，Percona Server for MySQL代替了自己的列，因为它通常尽可能地靠近上游。

对于在其他内核正常运行时查询使一个特定CPU过载的情况，以下方法很有用。 对于一般的CPU使用问题，可以使用不同的方法，例如另一篇博客文章Reducing High CPU on MySQL: A Case Study的方法.

我们如何使用这个新列来找出哪个会话使用了数据库中最多的CPU资源?

让我们举个例子：

要解决CPU问题，我们可以使用几种工具，例如top或pidstat（需要sysstat程序包）。 在下面的示例中，我们将使用pidstat。 该工具有一个选项（-t），可将其视图从进程（默认值）更改为线程，在其中显示给定进程内的关联线程。 我们可以使用它来找出哪个线程在服务器中消耗了最多的CPU。 将-p参数与mysql进程ID一起添加，以便该工具仅显示MySQL线程，从而使我们更容易进行故障排除。 最后一个参数（1）是每秒显示一个样本：

命令为pidstat -t -p <mysqld_pid> 1：

shell> pidstat -t -p 31258 1
03:31:06 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:31:07 PM 10014         -     32039    5.00    1.00    0.00    6.00    22  |__mysqld
03:31:07 PM 10014         -     32040    5.00    1.00    0.00    6.00    23  |__mysqld
03:31:07 PM 10014         -     32042    6.00    1.00    0.00    7.00     8  |__mysqld
03:31:07 PM 10014         -     32047    5.00    1.00    0.00    6.00     6  |__mysqld
03:31:07 PM 10014         -     32048    5.00    1.00    0.00    6.00    15  |__mysqld
03:31:07 PM 10014         -     32049    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32052    5.00    1.00    0.00    6.00    14  |__mysqld
03:31:07 PM 10014         -     32053   94.00    0.00    0.00   94.00     9  |__mysqld
03:31:07 PM 10014         -     32055    4.00    1.00    0.00    5.00    10  |__mysqld
03:31:07 PM 10014         -      4275    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4276    5.00    1.00    0.00    6.00     7  |__mysqld
03:31:07 PM 10014         -      4277    6.00    1.00    0.00    7.00    15  |__mysqld
03:31:07 PM 10014         -      4278    5.00    1.00    0.00    6.00    18  |__mysqld
03:31:07 PM 10014         -      4279    5.00    1.00    0.00    6.00    10  |__mysqld
03:31:07 PM 10014         -      4280    5.00    1.00    0.00    6.00    12  |__mysqld
03:31:07 PM 10014         -      4281    5.00    1.00    0.00    6.00    11  |__mysqld
03:31:07 PM 10014         -      4282    4.00    1.00    0.00    5.00     2  |__mysqld
03:31:07 PM 10014         -     35261    0.00    0.00    0.00    0.00     4  |__mysqld
03:31:07 PM 10014         -     36153    0.00    0.00    0.00    0.00     5  |__mysqld

我们可以看到线程32053在很大程度上消耗了最多的CPU，并且我们确保验证了在pidstat的多个样本之间的消耗是否恒定。 利用这些信息，我们可以登录数据库，并使用以下查询来找出哪个MySQL Thread是罪魁祸首：

mysql > select * from performance_schema.threads where THREAD_OS_ID = 32053 \G
*************************** 1. row ***************************
          THREAD_ID: 686
               NAME: thread/sql/one_connection
               TYPE: FOREGROUND
     PROCESSLIST_ID: 590
   PROCESSLIST_USER: msandbox
   PROCESSLIST_HOST: localhost
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Query
   PROCESSLIST_TIME: 0
  PROCESSLIST_STATE: Sending data
   PROCESSLIST_INFO: select * from test.joinit where b = 'a a eveniet ut.'
   PARENT_THREAD_ID: NULL
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: SSL/TLS
       THREAD_OS_ID: 32053
1 row in set (0.00 sec)

好了！ 现在我们知道，CPU高消耗来自joinit表中的查询，该查询由数据库测试中来自本地主机的用户msandbox执行。 使用此信息，我们可以对查询进行故障排除，并使用EXPLAIN命令检查执行计划，以查看是否还有任何改进的余地。

mysql > explain select * from test.joinit where b = 'a a eveniet ut.' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: joinit
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7170836
     filtered: 10.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

In this case, it was a simple index that was missing!

mysql > alter table test.joinit add index (b) ;
Query OK, 0 rows affected (15.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

After we create the index, we are no longer seeing CPU spikes:

shell> pidstat -t -p 31258 1
03:37:53 PM   UID      TGID       TID    %usr %system  %guest    %CPU   CPU  Command
[...]
03:37:54 PM 10014         -     32039   25.00    6.00    0.00   31.00     0  |__mysqld
03:37:54 PM 10014         -     32040   25.00    5.00    0.00   30.00    21  |__mysqld
03:37:54 PM 10014         -     32042   25.00    6.00    0.00   31.00    20  |__mysqld
03:37:54 PM 10014         -     32047   25.00    4.00    0.00   29.00    23  |__mysqld
03:37:54 PM 10014         -     32048   25.00    7.00    0.00   32.00    22  |__mysqld
03:37:54 PM 10014         -     32049   23.00    6.00    0.00   29.00     4  |__mysqld
03:37:54 PM 10014         -     32052   23.00    7.00    0.00   30.00    14  |__mysqld
03:37:54 PM 10014         -     32053   10.00    2.00    0.00   12.00    11  |__mysqld
03:37:54 PM 10014         -     32055   24.00    6.00    0.00   30.00     1  |__mysqld
03:37:54 PM 10014         -      4275   25.00    6.00    0.00   31.00     7  |__mysqld
03:37:54 PM 10014         -      4276   25.00    6.00    0.00   31.00     1  |__mysqld
03:37:54 PM 10014         -      4277   24.00    5.00    0.00   29.00    14  |__mysqld
03:37:54 PM 10014         -      4278   24.00    6.00    0.00   30.00     9  |__mysqld
03:37:54 PM 10014         -      4279   25.00    5.00    0.00   30.00     6  |__mysqld
03:37:54 PM 10014         -      4280   26.00    5.00    0.00   31.00    14  |__mysqld
03:37:54 PM 10014         -      4281   24.00    6.00    0.00   30.00    10  |__mysqld
03:37:54 PM 10014         -      4282   25.00    6.00    0.00   31.00    10  |__mysqld
03:37:54 PM 10014         -     35261    0.00    0.00    0.00    0.00     4  |__mysqld
03:37:54 PM 10014         -     36153    0.00    0.00    0.00    0.00     5  |__mysqld

为什么不使用这种方法来解决IO和内存问题?

从OS端测量线程IO的问题在于，大多数MySQL IO操作是由后台线程完成的，例如读取，写入和页面清理程序线程。 要测量线程IO，您可以使用带有-d（IO而不是CPU）选项的pidstat或带有-H（每个线程）的iostat之类的工具。 如果您有一个非常消耗IO的线程，您也许可以看到它，但是要警告，由于后台线程操作，结果可能会产生误导。

内存消耗是要从OS端衡量的一个更加棘手的资源，因为所有内存都是在MySQL进程下分配的，并且由于是MySQL管理其内存访问，因此对于OS来说哪个线程消耗最多的内存是透明的。 为此，我们可以使用从5.7开始使用perfomance_schema memory instrumentation available starting in 5.7.

结论

有很多方法可以解决CPU使用率过高的问题，但是从5.7版开始，我们在Oracle和PostgreSQL数据库上提供了一种简单且广泛使用的方法，该方法可以适应MySQL。 通过从OS线程消耗跟踪到数据库端，我们可以快速检测到影响系统性能的CPU密集型查询。

Fan_-_
关注
专栏目录
Mysql快速定位消耗 CPU、内存的 sql 语句
06-02 708
如何快速定位消耗 CPU 最高的 sql 语句，怎么做？看看下面的介绍。 概述 如果是Oracle数据库我们可以很容易通过sql来定位到当前数据库中哪些消耗CPU高的语句，而mysql数据库可以怎么定位呢？这里用一个简单例子说明下... 主要是了解如何定位的思路，具体看官网介绍.. 参考： https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/ 主要意思是针
SQL面试：如何快速定位消耗CPU最高的sql语句
小小博客爱分享
07-02 190
如何快速定位消耗 CPU 最高的 sql 语句？看看下面的介绍。 概述 如果是Oracle数据库我们可以很容易通过sql来定位到当前数据库中哪些消耗CPU高的语句，而mysql数据库可以怎么定位呢？这里用一个简单例子说明下… 主要是了解如何定位的思路，具体看官网介绍.. 参考： https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/ 主要意思是针对定位CPU的.
mysql5.7 Performance Schema 一
LOST_9的博客
04-29 363
25.1 性能模式快速入门 25.1 性能模式快速入门 本节简要介绍 Performance Schema，并通过示例说明如何使用它。有关其他示例，请参阅第 25.19 节，“使用性能模式诊断问题”。 性能模式默认启用。要显式启用或禁用它，请使用performance_schema设置为适当值的变量启动服务器。例如，在服务器my.cnf文件中使用这些行： [mysqld] performance_schema=ON 当服务器启动时，它会看到performance_schema并尝试...
mysql5.7占用大_MySQL5.7 如何找出占用CPU高的SQL
weixin_31286719的博客
01-18 436
One of our customers recently asked whether it is possible to identify, from the MySQL side, the query that is causing high CPU usage on his system. The usage of simple OS tools to find the culprit ha...
mysql performance schema_MySQL 5.7 Performance Schema 详解
weixin_39838231的博客
01-20 164
MySQL Performance Schema 用于监视MySQL服务器，且运行时消耗很少的性能。Performance Schema收集数据库服务器性能参数，并且表的存储引擎均为PERFORMANCE_SCHEMA，而用户是不能创建存储引擎为PERFORMANCE_SCHEMA的表。Performance Schema 具有以下特征：Performance Schema 提供了一种在服务器...
mysql内存使用监控_mysql 5.7 内存使用监控
weixin_33812391的博客
01-20 218
5.7 中的performance_schema 已经有能力监控mysql 的内存使用情况了，对于这一点也是要通过instrument 来实现的，由于内存这一块没有对应的consumer 所以只要配置好instrument 就行了1、例子：/etc/my.cnf[mysqld]datadir =/usr/local/mysql/...
【Database Connection Pool】: The Secret to Efficient Connection Management with Python and MySQL
# The Secret to Efficient Connection Management: Python and MySQL Connection Pooling In modern IT architectures, database connection pooling technology is an indispensable component for enhancing ...
如何快速定位当前消耗 CPU 最高的 sql 语句？
Z1605931399的博客
09-06 155
如果是Oracle数据库我们可以很容易通过sql来定位到当前数据库中哪些消耗CPU高的语句，而mysql数据库可以怎么定位呢？这里用一个简单例子说明下... 主要是了解如何定位的思路，具体看官网介绍.. 参考：https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/ 主要意思是针对定位CPU的问题，Percona增加了对通过信息的TID列将processlist I..
【Error Handling and Debugging】: Common Issues and Solutions for Operating MySQL with Python
# 1. Basic Interaction between Python and MySQL ... As a widely-used dynamic programming language, Python boasts clear and concise syntax and is easy to learn. It excels in various fields such as d
MySQL参数配置优化
weixin_34204057的博客
07-16 76
MySQL参数配置优化max_connectionsVariableScope：GlobalDynamicVariable：YesDefault：151（mysql5.5+）Meaning：允许...
MySQL CPU 使用率高的原因和解决方法(来自aliyun官方文档)
u011239989的博客
06-05 3万+
MySQL CPU 使用率高的原因和解决方法 更新时间：2017-03-17 13:25:52   分享：    用户在使用 MySQL 实例时，会遇到 CPU 使用率过高甚至达到 100% 的情况。本文将介绍造成该状况的常见原因以及解决方法，并通过 CPU 使用率为 100% 的典型场景，来分析引起该状况的原因及其相应的解决方案。 常见原因 系统执行应用提交查询（
mysql占用cpu特别高的解决_mysql占用CPU过高的解决办法
weixin_42516600的博客
01-27 4194
下面是MYSQL占用CPU高处理的一个例子，希望对遇到类似问题的朋友们有点启发。一般来说MYQL占用CPU高，多半是数据库查询代码问题，查询数据库过多。所以一方面要精简代码，另一方面最好对频繁使用的代码设置索引。今天早上起来 机器报警 一查负载一直都在4以上top了一下 发现 mysql 稳居 第一 而且相当稳定重启一下mysql不行mysql> show processlist;一下发现x...
mysql cpu飙高的处理案例1
w1346561235的博客
07-10 1283
mysql是一个多线程的架构，所以从linux主机上观察到的mysqld进程的cpu飙高，通常都是由其下的某个或者某几个线程消耗大量cpu资源。这种情况下，第一步要先确定具体是哪个线程消耗cpu。linux上，最简单的就是用top命令的-H选项： -H :Threads-mode operation Instructs top to display individual threads. Without this command-line option a summation ...
mysql占用CPU超过100%解决过程
热门推荐
jimshen的专栏
12-04 14万+
2017年12月2日上午，将学校新闻网2015年之前的45000多条记录迁移到了新网站的mysql数据库，新网站上有2015年1月1日之后的9000多条记录，数据量一下子增加了5倍。 2017年12月3日晚上9点多，有领导和老师反映新闻网无法访问，立即登录服务器进行排查。 一、使用top命令看到的情况如下： 可以看到服务器负载很高，，mysql CPU使用已达到接近400%（因为是四
1300张图片训练效果
最新发布
10-31
1300张图片训练效果
springboot116基于java的教学辅助平台.zip
10-31
教学辅助平台的出现，是为了更好地服务于教育工作者和学生，提高教学效果和学习效率。该平台集成了多个功能模块，旨在为用户提供全面、便捷的教学辅助服务。 平台首页作为导航入口，提供了清晰的界面布局和便捷的导航功能，方便用户快速找到所需功能。需要注意的是，“首页”这一选项在导航菜单中出现了多次，可能是设计上的冗余，需要进一步优化。 “个人中心”模块允许用户查看和管理自己的个人信息，包括修改密码等账户安全设置，确保用户信息的准确性和安全性。 在教育教学方面，“学生管理”和“教师管理”模块分别用于管理学生和教师的信息，包括学生档案、教师资料等，方便教育工作者进行学生管理和教学安排。同时，“课程信息管理”、“科目分类管理”和“班级分类管理”模块提供了课程信息的发布、科目和班级的分类管理等功能，有助于教育工作者更好地组织和管理教学内容。 此外，“课程作业管理”模块支持教师布置和批改作业，学生可以查看和提交作业，实现了作业管理的线上化，提高了教学效率。而“交流论坛”模块则为学生和教师提供了一个交流和讨论的平台，有助于促进师生互动和学术交流。 最后，“系统管理”模块为平台管理员提供了系统配置.
yolo算法-火灾探测数据集-3466张图像带标签-火灾fire_detect-oqlpv.zip
10-31
yolo系列算法目标检测数据集，包含标签，可以直接训练模型和验证测试，数据集已经划分好，适用yolov5,yolov8,yolov9,yolov7,yolov10,yolo11算法； 包含两种标签格:yolo格式（txt文件）和voc格式（xml文件），分别保存在两个文件夹中; yolo格式：<class> <x_center> <y_center> <width> <height>， 其中： <class> 是目标的类别索引（从0开始）。 <x_center> 和 <y_center> 是目标框中心点的x和y坐标，这些坐标是相对于图像宽度和高度的比例值，范围在0到1之间。 <width> 和 <height> 是目标框的宽度和高度，也是相对于图像宽度和高度的比例值
基于go语言的参数解析校验器项目资源.zip
10-31
基于go语言的参数解析校验器项目资源
mysql error:event Scheduler: Unable to schedule event
07-14
If you're encountering the error message "Event Scheduler: Unable to schedule event" in MySQL, there could be a few possible reasons for this issue. Here are some troubleshooting steps you can try: 1...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值