Has MySQL a built-in way to easily take a look at the overall server status without the need to execute many different custom query or program ? Maybe something similar to an oracle AWR report ?
Starting with MySQL 5.7.9 and later we can use the SYS procedure “diagnostics()” for that which rely the information available on the PERFORMANCE_SCHEMA !
Let’s give it a try by generating a HTML report for the currently running instance (starts an iteration every 30 seconds and runs for at most 60 seconds using the current Performance Schema settings ) :
mysql -u root -p -H -e"CALL sys.diagnostics(60, 30, 'current');" > ./current_instance_report.html
The report contain information such as :
Wait event :
Statement execution :
Standard Monitor output :
For historical analysis we can decide to schedule a cron job for this procedure example :
0 * * * * mysql -u root -H -e"CALL sys.diagnostics(3600, 1800, 'current');" > /home/blabla/instance_report_$(date +"%Y-%m-%d_%H-%M")
And that was my first MySQL blog post 😀