need to know about mysql checkpoint 'mysiam_total_disk_write_ratio' for the mysql probe.

Products

• DX Unified Infrastructure Management (Nimsoft / UIM)
• Unified Infrastructure Management for Mainframe

Issue/Introduction

Question:

DBA team wants to know that how the myisam_total_disk_write_ratio checkpoint value is calculated. 

What query is being used to fetch the information by the probe?

Actually this checkpoint is repeatedly generating alarm. The current value is hovering around 3.5% only and threshold is set at <=70

 

Environment

Release: CNMSPP99000-7.6-Unified Infrastructure Mgmt-Server Pack-- On Prem or later
Component:  UIMMSQ any version

Resolution

The alarm for a value of 3.5% is correct.

 

Please see MySQL documentation below:

check myisam_total_disk_write_ratio checkpoint

Below is the description. 

Ratio of disk writes to all write requests. 
Calculated as: Key_writes/Key_write_requests 

Also review the following link for reference: 
http://dba.stackexchange.com/questions/20083/whats-the-formula-for-calculating-key-efficiency-key-buffer-used-and-query-ca 

>>The ratio of the number of physical writes of a key block to the cache to the number of requests to write a key block to the cache in percentage. For a good performance of the MySQL server, the value of Key Write Efficiency must be 90 percent and above.<< 

So the <= threshold operator is correct

 

QOS_MYSQL_MYISAM_TOTAL_DISK_WRITE_RATIO 

Monitors the ratio of disk writes to all write requests . 
https://techdocs.broadcom.com/us/en/ca-enterprise-software/it-operations-management/ca-unified-infrastructure-management-probes/GA/monitoring/bigdata-databases-and-storage/mysql-mysql-server-monitoring.html

Attached is a kb article with all of the queries used by the mysql probe for review. 
https://knowledge.broadcom.com/external/article/136531/mysql-probe-checkpoint-queries.html

 

From the attached document, it specifies the following:

"myisam_total_disk_write_ratio", QUERY_SHOW_GLOBAL_STATUS, 

The probe is using the MYSQL query SHOW GLOBAL STATUS to get the information for this QOS. 


So to find the information, execute a query such as: 
available" -> Just to check whether connection is possible or not. 

"number_of_databases", QUERY_SHOW_DATABASES, 

SHOW DATABASES 

The probe then uses code to count the databases. 

Another example

 
The probe issues SHOW GLOBAL STATUS 
then goes through the data returned to find the value it is looking for such as 
Bytes_sent 

example 

https://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html 

There is no more information to provide. 

This is why the mysql probe Release Notes documents the following: 

The mysql user configured in probe must have access to the following queries on the mysql server. 

SHOW GLOBAL STATUS 
SHOW SLAVE STATUS 
SHOW VARIABLES 
SHOW DATABASES 
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 

This is what the probe is using to pull the information for the monitored mySQL database.