need to know about mysql checkpoing ?mysiam_total_disk_write_ratio? for the mysql probe.

Document ID : KB000041424
Last Modified Date : 14/02/2018
Show Technical Document Details

Question:

Our DBA team wants to know that how is this checkpoint value calculated.
What query is being used to fetch the information at back 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

 

Answer:

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 I have found below link for reference please have a look.
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://docops.ca.com/ca-unified-infrastructure-management-probes/en/alphabetical-probe-articles/mysql-mysql-server-monitoring/mysql-metrics

I have attached a kb article with all of the queries used by the mysql probe for your review.
tec000003092


From the attached document we can see the following:
"myisam_total_disk_write_ratio", QUERY_SHOW_GLOBAL_STATUS,

We are using the MYSQL query SHOW GLOABL STATUS to get the information for this QOS.



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

"number_of_databases", QUERY_SHOW_DATABASES,

SHOW DATABASES

We then use code to count the databases.

Another example
We issue SHOW GLOBAL STATUS
then go through the data returned to find the value we are 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 we document 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 we are using to pull the information for the probe.