MySQL overall server status report

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 :

Capture 05

Statement execution :

Capture 01

Capture 02

I/O :

Capture 04

Replication :

Capture 08

Metrics :

Capture 06

Standard Monitor output :

Capture 03

 

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 😀

 

11 thoughts on “MySQL overall server status report

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s