3

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.

0

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 Innodb_% grow.

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 pt-query-digest or mysqldumpslow -s t to find the few "worst" queries. More discussion: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog

|improve this answer|||||
  • Re "'elapsed' is more interesting than CPU vs I/O". Question: When there are multiple active connections on a server, will "elapsed" time depend on server load? Do I need to test during a quiet period, to get an accurate sense of the cpu time actually consumed by each query? I'll test it both during busy and quiet times, so I'll know then, but I thought it would be good for others to know "a priori" what "elapsed time" really measures. – ToolmakerSteve Oct 23 '19 at 12:47
  • 1
    @ToolmakerSteve - Yes, any other activity can impact the elapsed time. If your CPU has 8 cores, you need 9 things running for that to have much effect. If other queries are touching the same rows, etc, then locks can block one or the other. – Rick James Oct 24 '19 at 0:09
  • 1
    @ToolmakerSteve - If you have a slowlog covering 1000 instances of a particular query, nearly all will have about the same elapsed time. But a few may take 10 times as long. If, for example, long_qiuery_time = 1 (second) and that query normally takes 0.2 sec, a few instances of those 1000 will show up in the log. – Rick James Oct 24 '19 at 0:13
0

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;

|improve this answer|||||
  • Thank for information pankaj kumar. My doubt is does mysql stores these information in any tables or views?? Lets take an example I have ran a query before two days. How can I get the details like how much memory been utilized for the query when I had executed it. – sreekanth Mar 9 '18 at 14:18
  • SHOW PROFILES displays a list of the most recent statements sent to the server. The size of the list is controlled by the profiling_history_size session variable, which has a default value of 15. The maximum value is 100. Setting the value to 0 has the practical effect of disabling profiling. Optional type values may be specified to display specific additional types of information: – Pankaj Kumar Mar 12 '18 at 5:50
  • -- ALL displays all information -- BLOCK IO displays counts for block input and output operations -- CONTEXT SWITCHES displays counts for voluntary and involuntary context switches -- CPU displays user and system CPU usage times -- IPC displays counts for messages sent and received -- MEMORY is not currently implemented -- PAGE FAULTS displays counts for major and minor page faults -- SOURCE displays the names of functions from the source code, together with the name and line number of the file in which the function occurs -- SWAPS displays swap counts – Pankaj Kumar Mar 12 '18 at 5:51
  • Now set maximum profiling 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; – Pankaj Kumar Mar 12 '18 at 5:51

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.