MySQL overall server status report

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.

Pros:

1. No need to execute many different custom query or program.
2. Creates a report of the current server status for diagnostic purposes.
3. Information such as the one that determines queries in the 95th percentile.
4. It will give a information view of nbdinfo schema, if MySQL is a part of NDB Cluster.
5. Replication status (both master and slave).

Cons:

1. Disabled the binary logging during the execution period of the Procedure by change in SQL_LOG_BIN at session level.
2. So the procedure requires privileges sufficient to set restricted session variables.
3. The more instruments and consumers enabled, the more impact on MySQL server performance. Be careful with the medium setting and especially the full setting, 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 ) :

1	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 :

I/O :

Replication :

Metrics :

Standard Monitor output :

For historical analysis we can decide to schedule a cron job for this procedure  example  :

1	0 * * * *  mysql -u root -H  -e"CALL sys.diagnostics(3600, 1800, 'current');" > /home/blabla/instance_report_$(date +"%Y-%m-%d_%H-%M")

Share this post: on Twitter on Facebook on Google+