InnoDB Buffer pool hit rate missing from engine status output

Question

I want to check the buffer pool hit rate for InnoDB on MySQL but when I query the database the hit rate seems to be gone from the results:

Using query: SHOW ENGINE INNODB STATUS\G

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 169799966; in additional pool allocated 6386688
Dictionary memory allocated 1122462
Buffer pool size   8192
Free buffers       1
Database pages     8163
Old database pages 2993
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 610, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 3483, created 6047, written 184508
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 8163, unzip_LRU len: 0
I/O sum[41]:cur[0], unzip sum[0]:cur[0]

I was expecting a line like Buffer pool hit rate 999 / 1000, where has it gone?

By the way: any notes on the output above? Should I increase the pool size?

score 2 · Accepted Answer · 2013-09-27T21:54:07.773

It's right where it should be, above the line starting with 'Pages read ahead'. If there is no activity hitting the buffer pool before a subsequent SHOW ENGINE INNODB STATUS\G, you have 'No buffer pool page gets'.

Run a query on an innodb table and you should see it again.

mysql> SHOW ENGINE INNODB STATUS\G SELECT COUNT(id) FROM table;SHOW ENGINE INNODB STATUS\G

No buffer pool page gets:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 26461863936; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 657921776   (407996024 + 249925752)
    Page hash           25500536 (buffer pool 0 only)
    Dictionary cache    102125423   (102000592 + 124831)
    File system         88288   (82672 + 5616)
    Lock system         63750528    (63750152 + 376)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 124831
Buffer pool size        1572863
Buffer pool size, bytes 25769787392
Free buffers            0
Database pages          1557609
Old database pages      574956
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 464944064, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 470241613, created 2454749, written 331978152
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1557609, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Query to innodb:

+-----------+
| COUNT(id) |
+-----------+
|       291 |
+-----------+
1 row in set (0.01 sec)

Now you see 'Buffer pool hit rate':

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 26461863936; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 657921776   (407996024 + 249925752)
    Page hash           25500536 (buffer pool 0 only)
    Dictionary cache    102125423   (102000592 + 124831)
    File system         88288   (82672 + 5616)
    Lock system         63750528    (63750152 + 376)
    Recovery system     0   (0 + 0)
Dictionary memory allocated 124831
Buffer pool size        1572863
Buffer pool size, bytes 25769787392
Free buffers            0
Database pages          1557609
Old database pages      574956
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 464944064, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 470241613, created 2454749, written 331978152
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 1557609, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

As for your second question, there's really not enough information in show engine innodb output to make that determination. You need to factor in the amount of RAM the server has, whether it's doing anything else or if it's a dedicated server, whether you are mixing in MyISAM tables or not (so you don't starve the OS cache, killing MyISAM performance).

I'm a fan of the Percona Configuration Wizard for MySQL when I'm unsure about my settings. It won't give you the 'best' performance, but it should give you a pretty decent place to start your tuning.

Cheers, I think I'm on the right track for the configuration but a second opinion is always nice. Thanks for the link. — Bart, Sep 28 '13 at 15:16

score -3 · Answer 2 · answered Dec 12 '14 at 21:37

-3

SELECT FLOOR(NUMBER_PAGES_GET/(NUMBER_PAGES_GET+NUMBER_PAGES_READ) * 100) as InnoDB_Buffer_Pool_Hit_Rate 
FROM information_schema.INNODB_BUFFER_POOL_STATS;

answered Dec 12 '14 at 21:37

InnoDB

1

This needs a better explanation. – Sven Dec 12 '14 at 21:39
-1 This may or may not be a valid answer but without context its impossible to tell.. – HBruijn Dec 12 '14 at 22:15

InnoDB Buffer pool hit rate missing from engine status output

2 Answers2