How can I lower MySQL single-page reads?

Question

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?

show the results of Innodb_buffer_pool_read_requests also – Mike Sep 01 '12 at 02:40 — Mike, Sep 01 '12 at 02:40
Innodb_buffer_pool_read_requests - 3.5 G – Asaz Sep 01 '12 at 02:50 — Asaz, Sep 01 '12 at 02:50

score 1 · Answer 1 · answered Aug 28 '14 at 20:00

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.

score 0 · Answer 2 · answered Sep 01 '12 at 02:57

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.

Listen to this -- the original question is looking at the wrong stats. — Gavin Towey, Sep 04 '12 at 02:16

How can I lower MySQL single-page reads?

2 Answers2