如何快速定位當前數(shù)據(jù)庫消耗CPU最高的sql語句？
來源：https://www.toutiao.com/i6923526305795293707?wid=1623686217615
概述
如果是Oracle數(shù)據(jù)庫我們可以很容易通過sql來定位到當前數(shù)據(jù)庫中哪些消耗CPU高的語句，而mysql數(shù)據(jù)庫可以怎么定位呢？這里用一個簡單例子說明下...
主要是了解如何定位的思路，具體看官網(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.
主要意思是針對定位CPU的問題，Percona增加了對通過信息的TID列將processlist ID映射到OS線程ID的支持，而MySQL在5.7版本后在 PERFORMANCE_SCHEMA.THREADS表加了一個THREAD_OS_ID新列來實現(xiàn)，以下方法適用于在其他內(nèi)核正常運行時，某個特定CPU的查詢過載的情況。
find out which session is using the most CPU resources in my database?
1、定位線程
pidstat -t -p <mysqld_pid> 1 5
通過該命令我們可以定位到802、4445等線程消耗了大量的CPU，這里盡量確保在pidstat的多個樣本中驗證消耗是恒定的。根據(jù)這些信息，我們可以登錄到數(shù)據(jù)庫，并使用以下查詢找出哪個MySQL線程是罪魁禍首。
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表找到對應的會話，如下：
3、查看問題sql執(zhí)行計劃
這里對應看一下執(zhí)行計劃基本就可以判斷當前數(shù)據(jù)庫CPU為什么消耗這么高了...
至于優(yōu)化的點只需要在dock建一個索引即可，這里就不介紹了。
有道無術，術可成；有術無道，止于術
歡迎大家關注Java之道公眾號
好文章，我在看??