Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.