How can we identify the exact CPU,Memory and disk I/O, when a query is executed. I am using mysql 5.7.20 community version. I am able to get only cpu details for session level by setting SET profiling=1. How can we identify all these details in global level for all the sessions.
Memory usage -- InnoDB has a common "buffer pool" where it caches blocks. there is no metric for memory usage. On the other hand, you can find out various statistics for all queries over a period of time by watching the
STATUS values of
For CPU time, I recommend turning on the "slowlog" with
long_query_time=0. That will rapidly fill disk with lots of details about every query, including the elapsed time. Note that "elapsed" is more interesting that CPU vs I/O.
The slowlog gives you the best view into which queries you should work on improving. Perhaps that is what you are really looking for? Use either
mysqldumpslow -s t to find the few "worst" queries. More discussion: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Using IOTOP command you can see CPU,Memory and disk I/O utilization for each query. For each query mysql generates an ID. You can find CPU,Memory and disk I/O utilization against your query ID.
How ever you may find more command in detail at http://www.dbatodba.com/db2/useful-aix-commands/monitor-cpu-mem-and-i-o-usage/
SET profiling = 100 -- Now you can find query ID and running sql query by SHOW PROFILES; IF you want TO CHECK IN detail FOR ANY QUERY, run this QUERY- -- SHOW PROFILE FOR QUERY QueryID; -- If you want to check CPU USAGE for any query, run this query- SHOW PROFILE CPU FOR QUERY QueryID;