Decoding "Innodb_buffer_pool_reads"

[pkiula]

I see this in my phpmyadmin stats for the database:

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

This number is in red, and is therefore quite high. My innodb_buffer is 11GB on a 32GB RAM dual quadcore server with 5 SCSI hard disks on RAID10.

With that hardware and that much RAM available to a basically small DB (largest table has 100,000 records!) why are the queries doing "single page read"s at all?

Is there a way for me to find queries that require this? The slow query log is now almost empty, so I cannot find much there.

Thanks!

[vgatto]

Innodb_buffer_pool_reads is the number of times MySQL needed to read a data page from disk because it wasn’t already in the buffer pool. Innodb_buffer_pool_read_requests is a count of the number of pages that were needed to satisfy your queries, and these pages can come either from disk or from the buffer pool. The ratio of these two tells you how often you’re really going to disk, so just one of the values doesn’t tell you the whole story. The rule of thumb is that innodb_buffer_pool_read_requests should be more than 100x innodb_buffer_pool_reads.

You should think of the buffer pool like a cache for data pages, and innodb_buffer_pool_reads as cache misses. When the server comes up, the cache is empty, so ANY request will result in a cache miss and increment the count of innodb_buffer_pool_reads as the page is retrieved from disk and stored in the buffer pool. Given your large buffer pool size, chances are the 9,404 page reads represent all the data that has been accessed since the last restart. With a 16K page size, that would be about 150 MB, which doesn’t seem unreasonable for a small database.

Is it just phpmyadmin scaring you, or have you noticed any problems with server performance?