innodb buffer pool hit rate is less than zero

[tatref]

Hi,

I'm getting negative values for innodb hit ratio (mode innodb-bufferpool-hitrate) on multiple mysql databases (at least versions 5.5 and 5.6)

There was an issue reported on MariaDB, see https://mariadb.atlassian.net/browse/MDEV-8932

Currently, the calculation in the plugin is similar to the bug's description: ratio = (1 -Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100

The fix propose the change: Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

What do you think of this change?

[tatref]

Also, after checking more closely, it seems that on some of my servers, Innodb_buffer_pool_reads is sometime greater than Innodb_buffer_pool_read_requests

The mysql doc tells me that the max value for an int is 4294967295, and for Innodb_buffer_pool_read_requests I get values about the quarter of this max.

[tatref]

After some testing, it appears that Innodb_buffer_pool_read_requests indeed drops to 0 after hitting the max value.

Also, I don't think that the proposed change make any sense because if the read_requests drops to 0, then we'll see a hitratio of 0%

How are you people using this?

[HOSTED-POWER]

Hi,

I also has issues with division by 0. Please look to this:

https://github.com/lausser/check_mysql_health/issues/17

On all my servers I apply this fix atm:

sed -i 's/(\$self->{\$_} >= \$last_values->{\$_})/(\$self->{\$_} - \$last_values->{\$_} > 0)/g' /usr/lib/nagios/plugins/check_mysql_health

Not sure if this could fix yours too...

[HOSTED-POWER]

PS: make sure to check the path since it's different on some OS'es and that the change is applied