如何快速定位當(dāng)前數(shù)據(jù)庫(kù)消耗CPU最高的sql語(yǔ)句？

概述

如果是Oracle數(shù)據(jù)庫(kù)我們可以很容易通過(guò)sql來(lái)定位到當(dāng)前數(shù)據(jù)庫(kù)中哪些消耗CPU高的語(yǔ)句，而mysql數(shù)據(jù)庫(kù)可以怎么定位呢？這里用一個(gè)簡(jiǎn)單例子說(shuō)明下...

主要是了解如何定位的思路，具體看官網(wǎng)介紹..

參考：https://www.percona.com/blog/2020/04/23/a-simple-approach-to-troubleshooting-high-cpu-in-mysql/

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 has been a widely used technique for a long time by PostgreSQL and Oracle DBAs, but it didn’t work for MySQL as historically we’ve lacked the instrumentation to match an OS thread with an internal processlist thread – until recently.

Percona added support to map processlist ids to OS thread ids through column TID of the information_schema.processlist table starting on Percona Server for MySQL 5.6.27. With the release of 5.7, MySQL followed with its own implementation by extending the PERFORMANCE_SCHEMA.THREADS table and adding a new column named THREAD_OS_ID, which Percona Server for MySQL adopted in place of its own, as it usually does remain as close to upstream as possible.

The following approach is useful for cases where there is a query overloading one particular CPU while other cores are performing normally. For cases where it is a general CPU usage issue, different methods can be used, such as the one in this other blog post Reducing High CPU on MySQL: A Case Study.

主要意思是針對(duì)定位CPU的問(wèn)題，Percona增加了對(duì)通過(guò)信息的TID列將processlist ID映射到OS線程ID的支持，而MySQL在5.7版本后在 PERFORMANCE_SCHEMA.THREADS表加了一個(gè)THREAD_OS_ID新列來(lái)實(shí)現(xiàn)，以下方法適用于在其他內(nèi)核正常運(yùn)行時(shí)，某個(gè)特定CPU的查詢(xún)過(guò)載的情況。

find out which session is using the most CPU resources in my database?

1、定位線程

pidstat -t -p <mysqld_pid> 1  5

通過(guò)該命令我們可以定位到802、4445等線程消耗了大量的CPU，這里盡量確保在pidstat的多個(gè)樣本中驗(yàn)證消耗是恒定的。根據(jù)這些信息，我們可以登錄到數(shù)據(jù)庫(kù)，并使用以下查詢(xún)找出哪個(gè)MySQL線程是罪魁禍?zhǔn)住?/p>

2、定位問(wèn)題sql

select * from performance_schema.threads where thread_os_id = xx ;
select * from information_schema.`PROCESSLIST` where  id=threads.processlist_id

根據(jù)操作系統(tǒng)id可以到processlist表找到對(duì)應(yīng)的會(huì)話，如下：

3、查看問(wèn)題sql執(zhí)行計(jì)劃

這里對(duì)應(yīng)看一下執(zhí)行計(jì)劃基本就可以判斷當(dāng)前數(shù)據(jù)庫(kù)CPU為什么消耗這么高了...

至于優(yōu)化的點(diǎn)只需要在dock建一個(gè)索引即可，這里就不介紹了。