[MDEV-8932] innodb buffer pool hit rate is less than zero Created: 2015-10-13  Updated: 2019-12-04  Resolved: 2015-10-28

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB, Storage Engine - XtraDB
Affects Version/s: 10.0
Fix Version/s: 10.0.22

Type: Bug Priority: Major
Reporter: Zhoukai Lin Assignee: Jan Lindström (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-21212 buf_page_get_gen -> buf_pool->stat.n_... Closed

 Description   

When I set my.cnf like this:

innodb_buffer_pool_size = 102400M
 
innodb_buffer_pool_dump_at_shutdown=1
 
innodb_buffer_pool_dump_now=1
 
innodb_buffer_pool_load_at_startup=1
 
innodb_buffer_pool_load_now=1

and use （1-Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests）*100% to calculate innodb buffer pool hit rate ,I got a number less than zero,like 

+----------------------+
 
| hit_rate             |
 
+----------------------+
 
| -0.04231484607035707 |
 
+----------------------+

,and 

MariaDB [(none)]> show global status like 'innodb_buffer_pool_read%';
 
+---------------------------------------+----------+
 
| Variable_name                         | Value    |
 
+---------------------------------------+----------+
 
| Innodb_buffer_pool_read_ahead         | 0        |
 
| Innodb_buffer_pool_read_ahead_evicted | 0        |
 
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
 
| Innodb_buffer_pool_read_requests      | 13973819 |
 
| Innodb_buffer_pool_reads              | 13979732 |
 
+---------------------------------------+----------+

I found Innodb_buffer_pool_reads is bigger than Innodb_buffer_pool_read_requests!

When the server is loading pages at the start time,I execute show engine innodb status ,and I also see a negative number like:

Buffer pool hit rate 18446744073709549183 / 1000, young-making rate 0 / 1000 not 0 / 1000
 
Buffer pool hit rate 18446744073708648991 / 1000, young-making rate 0 / 1000 not 0 / 1000
 
Buffer pool hit rate 18446744073709549537 / 1000, young-making rate 0 / 1000 not 0 / 1000
 
Buffer pool hit rate 18446744073709551510 / 1000, young-making rate 0 / 1000 not 0 / 1000

Obviously it's a negative number in unsigned long int.

 Comments   
Comment by Elena Stepanova [ 2015-10-19 ]

Assigning to jplindst for advice on why it could be happening. I can only think of disk problems, but I suppose it would have been detected.

Comment by Zhoukai Lin [ 2015-10-21 ]

There are several servers have this problem. So it may not be disk problems.

Comment by Jan Lindström (Inactive) [ 2015-10-28 ]

Innodb_buffer_pool_read_requests indicates the the number of logical read requests (read from memory) InnoDB has done.
Innodb_buffer_pool_reads indicates the number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from the disk (physical reads). This means that read_requests and reads may be different. You should calculate the hit ratio with

Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

There seems to be a bug on calculation when buffer pool is loaded.

Comment by Jan Lindström (Inactive) [ 2015-10-28 ]

commit 4834d822efe5a812d251f254387b3f80beb9380d
Author: Jan Lindström <jan.lindstrom@mariadb.com>
Date: Wed Oct 28 08:42:51 2015 +0200

MDEV-8932: innodb buffer pool hit rate is less than zero

In buffer pool read hit rate calculation can lead rate that is
less than zero.

Generated at Thu Feb 08 07:30:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.