mysql查看占用内存或者CPU高的SQL

获取mysql进程id

ps -ef | grep -i mysql

实时查看mysql进程中占用CPU，内存最多的操作系统线程ID

top -p 2296-H

[root@node2 ~]# top -p 2296 -Htop - 14:59:55 up 42 min, 4 users, load average: 0.41, 0.81, 1.39 Threads: 67 total, 2 running, 65 sleeping, 0 stopped, 0 zombie %Cpu(s): 94.7 us, 4.7 sy, 0.0 ni, 0.0 id, 0.0 wa, 0.0 hi, 0.7 si, 0.0 st KiB Mem : 2238208 total, 77696 free, 943520 used, 1216992 buff/cacheKiB Swap: 2621436 total, 2621436 free, 0 used. 1139404 avail Mem PID USERPR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAN2966 mysql20 0 6212608 868916 30128 R 97.3 38.8 11:46.51 mysqld 2460 mysql20 0 6212608 868916 30128 R 0.3 38.8 0:09.28 mysqld 2296 mysql20 0 6212608 868916 30128 S 0.0 38.8 0:00.79 mysqld

根据操作系统线程ID，查看mysql数据库中对应的线程ID

root@localhost 15:03:34 [performance_schema]>select thread_id,name ,PROCESSLIST_ID,THREAD_OS_ID from `performance_schema`.threads where thread_os_id = 2966 ;+-----------+---------------------------+----------------+--------------+| thread_id | name | PROCESSLIST_ID | THREAD_OS_ID |+-----------+---------------------------+----------------+--------------+| 78 | thread/sql/one_connection | 10 | 2966 |+-----------+---------------------------+----------------+--------------+

根据mysql数据库的线程ID获取sql

select sql_text from performance_schema.events_statements_current where thread_id = 78 \G;