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

My innodb buffer pool size is 8GB and my database is 6GB in size but I see this:

Innodb_buffer_pool_reads 263.5k - The number of logical reads that InnoDB could not satisfy from buffer pool and had to do a single-page read.

With the hardware (i7, 24GB RAM, SSD) and that much RAM available why are the queries doing "single page reads at all?

How can I reduce this number of single-page reads?

share|improve this question
    
show the results of Innodb_buffer_pool_read_requests also –  Mike Sep 1 '12 at 2:40
    
Innodb_buffer_pool_read_requests - 3.5 G –  Asaz Sep 1 '12 at 2:50

2 Answers 2

So those numbers aren't in bytes.. but # of queries.. From the math it seems you are very low which is a good thing. The math is

(Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads) * 100

If that number is less then 1 you are in good shape. Another good way to know if you need more memory to the pool is

SHOW ENGINE INNODB STATUS;

Towards the top you will see this

Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 0 / 1000

If you are close to 1000/1000 that is a good thing. Anything lower then 900 / 1000 I'd start to add more memory.

share|improve this answer
    
Listen to this -- the original question is looking at the wrong stats. –  Gavin Towey Sep 4 '12 at 2:16

why are the queries doing "single page reads at all?

Because the pages need to be read into the buffer pool at least once before they are available to the engine. Looking at your numbers the 263,500 reads would account to

263,500 pages x 16 KB/page = 4,216,000 KB read

which is not even as much as your database's size. Also, compared to your 3,500,000,000 innodb_buffer_pool_read_requests this is a tiny number - in your case virtually everything is served from the buffer pool without disk I/O.

share|improve this answer

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.