Regarding Monitoring Details

[ashishhbi]

Hi,

 

I have just installed the trail version of MONyog & I am able to successfully connect it to my DB server but I have few queries.

 

1. In Monitoring Section, Index Usage is showing warnings like SELECTs requiring full table scan, Percentage of full table scans etc. Check the attached file for the same.

 

How can I get the details of table/queries which are using full table scan ? 

 

2. Similarly In the Table Cache & Locks Section, System is showing “Number of table cache misses: 38.28K (12.708/sec)”.

 

It means system is not able to cache tables/seconds ? How can I get the details of these tables.

 

 

Thanks

Ashish Kumar

 

 

 

 

 

[Kartik Grover]

Hi

 

MONyog’s counter Percentage of full table scans in the Index Usage monitor returns the percentage of the rows accessed via full table scan, the formula for which is as follows:

(Handler_read_rnd_next + Handler_read_rnd) / (Handler_read_rnd_next + Handler_read_rnd + Handler_read_first + Handler_read_next + Handler_read_key + Handler_read_prev)

These values can be obtained from the MySQL server using a simple SHOW GLOBAL STATUS LIKE “%handler%” query. MONyog only reflects the information retrieved from the handler status variables. If you are using MySQL version 5.6.14 or greater then you can enable Custom SQL Object(CSO) in MONyog which will query the performance_schema database to monitor the full table scan. For more details on this please visit: http://www.markleith.co.uk/2012/07/04/mysql-performance-schema-statement-digests/.

 

The Monitors that you see in MONyog cannot give you such information. They give the values from Status Variables that do not fetch such information. For a full table scan and the relevant query details you can view the Query Analyser. The queries that may cause a problem or may trigger an alert situation would be slow queries, and that can be seen in the query analyser.

 

Same goes for the details of the table_cache. It is what the status variable opened_tables fetch.  There is no option in MySQL to provide you the details of the opened tables. It also depends on the number of max_connections at a time.

 

Regards

Kartik

[Author]

Posts