Identifying and Killing High‑CPU MySQL Queries with top and a Python Helper Script

When MySQL operations suddenly show high CPU usage, the fastest way to find the culprit is to examine the per‑thread CPU consumption of the mysqld process. The top -H -p <mysqld_pid> command lists each thread’s CPU usage, allowing you to spot the thread with the highest load.

After identifying the high‑CPU thread (e.g., thread ID 22682), you can retrieve the associated SQL statement by querying performance_schema.threads with the thread’s OS ID, and then verify the statement with SHOW PROCESSLIST to confirm it matches the slow query.

To automate this workflow, the author provides a Python script that wraps the necessary commands. The script parses command‑line arguments, connects to MySQL, fetches the top CPU‑consuming threads, retrieves their SQL via performance_schema , and optionally aggregates active thread statistics.

#!/usr/bin/python
# -*- coding:utf-8 -*-
import argparse
import MySQLdb
import commands
import sys
from warnings import filterwarnings, resetwarnings
filterwarnings('ignore', category=MySQLdb.Warning)
reload(sys)
sys.setdefaultencoding('utf8')

def init_parse():
    parser = argparse.ArgumentParser(epilog='by yayun @2022')
    parser.add_argument('-n','--num',required=False,default=1,help='Number of top CPU‑consuming records to fetch')
    parser.add_argument('-a','--active',action='store_true',default=False,help='Count active thread SQL occurrences')
    return parser

def mysql_exec(sql):
    try:
        conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='xx',port=3306,connect_timeout=15,charset='utf8')
        curs=conn.cursor()
        curs.execute(sql)
        conn.commit()
        curs.close()
        conn.close()
    except Exception as e:
        print "mysql execute: " + str(e)

def mysql_query(sql):
    conn=MySQLdb.connect(host='127.0.0.1',user='root',passwd='xx',port=3306,connect_timeout=15,charset='utf8',cursorclass=MySQLdb.cursors.DictCursor)
    cursor=conn.cursor()
    count=cursor.execute(sql)
    result=cursor.fetchall() if count else 0
    cursor.close()
    conn.close()
    return result

if __name__ == '__main__':
    parser=init_parse()
    args=parser.parse_args()
    slow_sql_numbers=args.num
    active_thread_status=args.active
    mysqld_pid=commands.getoutput('cat /data/mysql/3306/pid_mysql.pid')
    get_mysql_thread_cmd="top -H -p %s -n 1 | grep mysqld | head -n %s | awk '{print $1,$2}' | sed 's/mysql//g'" % (mysqld_pid,slow_sql_numbers)
    tmp_mysqld_thread=commands.getoutput(get_mysql_thread_cmd).split()
    mysqld_thread=[]
    for i in tmp_mysqld_thread:
        try:
            a=i.replace('\x1b[0;10m\x1b[0;10m','').replace('\x1b[0;10m','')
            mysqld_thread.append(int(a))
        except Exception:
            pass
    # Additional logic for active thread statistics and killing queries follows ...

The script first reads the MySQL PID, runs top to capture the most CPU‑intensive threads, cleans up the output, and stores the thread IDs. If the --active flag is set, it also runs a summary query that counts SELECT and UPDATE statements per user and database.

It then builds a SELECT statement against performance_schema.threads to fetch detailed information (processlist ID, user, host, database, execution time, and the full SQL text) for the identified threads. The results are printed, and the user is prompted to confirm whether the displayed queries should be terminated.

When the user answers “Y”, the script issues KILL <processlist_id> commands for each offending query and exits; otherwise it aborts gracefully.

By combining per‑thread CPU inspection with MySQL’s performance schema and a lightweight Python wrapper, administrators can rapidly pinpoint and eliminate high‑CPU queries without manually scanning thousands of rows in SHOW PROCESSLIST .

Combining active‑thread statistics with CPU usage enables extremely fast identification of problematic SQL statements.