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.
Mysql – CPU,Memory and disk I/O utilization for each query in thesql
MySQLmysql-5.7
Related Question
- MySQL CPU & Memory Spikes
- MySQL – Multiple Full Text Indexing for InnoDB and Memory Efficiency
- MySQL – High CPU System Time Usage Solutions
- Mysql – How do we identify service shutdown time in MySQL
- Mysql – Tuning thesql 5.7.1 for predominantly write workload on huge partitioned innodb tables
- MySQL – InnoDB High I/O Parameters Causing Crashes
- MySQL 5.7 + InnoDB: thesqld is CPU bound, with ever increasing memory usage
Best Answer
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
STATUSvalues ofInnodb_%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-digestormysqldumpslow -s tto find the few "worst" queries. More discussion: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog