CODEWITHSUNDEEP
mysqldebuggingcpu-usage
magedev207
magedev207

Reputation: 21

find out what query is a process making knowing the PID of it

By using htop command, I can see one mysqld process is taking more than 500% CPU usage and the PID of it is for example 20456 htop only show command /usr/bin/mysqld next to it

Now, what I would like to do is to examine what query is that PID making? My website does a lot of things related to database and I want to know what job cause this high CPU usage. Can someone show me the way please?

I tried show full processlist in mysql but it doesn't show me the PID in that list so I don't know what to focus on.

Upvotes: 1

Views: 2319

Answers (2)

Wilson Hauck
Wilson Hauck

Reputation: 2343

Have your tried from root logon when the query is running - 

SELECT * FROM information_schema.innodb_trx;

?

to see the content of the query(s) running?

Upvotes: 0

Raymond Nijland
Raymond Nijland

Reputation: 11602

MySQL 5.7 / 8.0 seams to have native support for that, when i look into the threads view manual

THREAD_OS_ID

The thread or task identifier as defined by the underlying operating system, if there is one:

THREAD_OS_ID contains the operating system thread ID.

For Windows, THREAD_OS_ID corresponds to the thread ID visible in Process Explorer (https://technet.microsoft.com/en-us/sysinternals/bb896653.aspx).

For Linux, THREAD_OS_ID corresponds to the value of the gettid() function. This value is exposed, for example, using the perf or ps -L commands, or in the proc file system (/proc/[pid]/task/[tid]). For more information, see the perf-stat(1), ps(1), and proc(5) man pages

Besides this view also contains a PROCESSLIST_ID column so you can JOIN it with the information_schema.processlist to know which query is using which OS PID's

Upvotes: 1

Related Questions