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.
2 Answers
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
answered Mar 11, 2018 at 10:39
-
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. Commented Oct 23, 2019 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. Commented Oct 24, 2019 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. Commented Oct 24, 2019 at 0:13
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;
answered Mar 9, 2018 at 7:51
-
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. Commented Mar 9, 2018 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: Commented Mar 12, 2018 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 Commented Mar 12, 2018 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; Commented Mar 12, 2018 at 5:51