MySQL probe checkpoint queries

Products

• Unified Infrastructure Management for Mainframe
• DX Unified Infrastructure Management (Nimsoft / UIM)
• CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)
• CA Unified Infrastructure Management SaaS (Nimsoft / UIM)

Issue/Introduction

What queries does the mysql probe use to generate metrics for the available set of checkpoints?

What are the mysql Checkpoint Queries used by the mysql probe?

Resolution

The attached mysql_checkpoint.txt file contains the list of checkpoints that are made by the mysql probe for the default set of checkpoints and identifies the mysql query used by the probe to extract the metrics from the monitored mySQL database.

For each checkpoint listed in the "Checkpoint Name" column the probe issues one of a fixed set of MYSQL queries as listed in the corresponding "QueryFlags" column.

For example, for the "number_of_databases" checkpoint, the probe issues the following SQL query:

SHOW DATABASES;  

The probe parses the output of the query to report the number of databases metric returned by this query.

Another example:

For the "myisam_total_disk_write_ratio" checkpoint, the probe issues the following SQL query:

SHOW GLOBAL STATUS;

From the mysql Metrics docops page, the myisam_total_disk_write_ratio checkpoint is described as follows:

"Monitors the ratio of disk writes to all write requests."

which is calculated as: Key_writes/Key_write_requests.

 

Review the following link for reference: 

Database Administrators MySQL Formulas  

The only checkpoint which executes an actual query is table_size_ratio which executes the following:

"SELECT table_name, avg_row_length, data_length, max_data_length, index_length, data_free, table_schema FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE' AND max_data_length > 0 ORDER BY table_schema ASC"

All other QoS is populated using SHOW GLOBAL STATUS or SHOW VARIABLES, and the output is parsed to populate QoS metrics.

For other checkpoints Review the following link for reference for starters:

Server Status Variables

 

Additional Information

  Please see attachment.