In MySQL it is a very beautiful way to find the server’s over all current status or even we can generates a HTML code to get a report like Oracle AWR report ( Automated Workload Repository).
So, there is a procedure in SYS schema called diagnostics() and it is introduce in the version from MySQL 5.7.9 and later.
This procedure can fetches the information & over all stats from the Performance_schema to get the current mySQL server status as a HTML report.
So, Before generating the report there is some important points to keep in mind about to generate the report, as every thing has two faces this one also has a pros & cons.
- No need to execute many different custom query or program.
- Creates a report of the current server status for diagnostic purposes.
- Information such as the one that determines queries in the 95th percentile.
- It will give a information view of nbdinfo schema, if MySQL is a part of NDB Cluster.
- Replication status (both master and slave).
- Disabled the binary logging during the execution period of the Procedure by change in SQL_LOG_BIN at session level.
- So the procedure requires privileges sufficient to set restricted session variables.
- The more instruments and consumers enabled, the more impact on MySQL server performance. Be careful with the
mediumsetting and especially the
fullsetting, which has a large performance impact.( From the official site ).
Now lets generate a current status report of MySQL server with that starts an iteration every 30 seconds and runs for at most 60 seconds using the current Performance Schema setting.
So, here i am going to generate a current MySQL server status report from MySQL 8.0. and i will use the command to generates the report in HTML as below,
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 ) :
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 :