1

I am trying to find out the Query which is consuming high CPU, but can't do it so.

This command top -H gives me the high CPU consuming thread PID, but i cannot match them with my processlist output.

Is there a way to do this?.

0

You can see which queries are slow by enabling the slow query log (SET GLOBAL slow_query_log=1; and setting a suitable value for the long query time variable to set a threshold for what you consider to be "long". (E.g. maybe SET GLOBAL long_query_time=3; - the unit is seconds). However, queries may be slow for other reasons than high CPU utilisation, e.g. due to high I/O utilisation. Still, this can be a starting point for at least identifying poor queries.

The slow log is by default written to a file in the data dir called '${HOSTNAME}-slow.log'. You can then use a tool like mysqldumpslow to analyse this file, e.g.: mysqldumpslow -s t /var/lib/mysql/localhost-slow.log. It will extract each slow query and order them from slowest to least slow. Or you can use pt-query-digest to do the same thing

You can then enable query profiling for your session (SET profiling = 1;), execute each of the candidate queries you have identified in the slow query log, and do:

SHOW PROFILES;

This gives you a list of the recent queries and their query IDs. Use this ID to find the CPU utilisation for each query with:

SHOW PROFILE CPU FOR QUERY <query id>;  

This will show the various stages of the query execution, and the duration and CPU utilisation (user and system) for each of them.

Note that this isn't 100% accurate - the documentation page for SHOW PROFILE notes that:

Profiling is only partially functional on some architectures. For values that depend on the getrusage() system call, NULL is returned on systems such as Windows that do not support the call. In addition, profiling is per process and not per thread. This means that activity on threads within the server other than your own may affect the timing information that you see.

Note also that the page says SHOW PROFILE is deprecated as of MySQL 5.6.7, and that the performance_schema is meant to replace it; see Query Profiling Using Performance Schema. However, there doesn't seem to be a way to extract CPU utilisation info from it, so you may just have to use SHOW PROFILE CPU ...

  • 1
    Get the first few entries from the slowlog via pt-query-digest or mysqldumpslow -s t, show us the SHOW CREATE TABLE and EXPLAIN SELECT ... – Rick James Jun 10 '18 at 18:04
  • Good point, @RickJames , I've updated the answer. – dbdemon Jun 10 '18 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.