InnoDBuse it to cache data and indexes. The larger you set this value, the less disk I/O is needed to access data in tables. click here for more detail about innodb buffer pool
You can examine Innnodb buffer pool efficiency by looking at STATUS variables:
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_rea%'; Innodb_buffer_pool_read_requests | 4519597979 |
Innodb_buffer_pool_reads | 55253 |
Innodb_buffer_pool_read_requests are number of request to read a row from the buffer pool and Innodb_buffer_pool_reads is the number of times Innodb has to perform read data from disk to fetch required data pages.
So innodb_buffer_pool_reads/innodb_buffer_pool_read_requests*100= 0.001 is the efficiency. Thus, we see the vast majority of times INNODB is able to statisfy requests from memory, it's pretty normal for databases to have hot spots in which you're accessing only a portion of the data the majority of the time.
Let's look at this example,
mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_rea%'; Innodb_buffer_pool_read_requests | 2905072850 |
Innodb_buffer_pool_reads | 1073291394 |
Calculate Innodb buffer pool efficiency:
(107329139/ 2905072850*100) = 37
Here the Innodb is doing more disk reads, Innodb buffer pool is not big enough!
Another way to examine innodb efficiency is to examine SHOW ENGINE INNODB STATUS output:
BUFFER POOL AND MEMORY
Total memory allocated 1838823262; in additional pool allocated 10550784
Buffer pool size 102400
Free buffers 0
Database pages 101424
Modified db pages 24189
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 2050214912, created 313658031, written 755828632
36.95 reads/s, 12.12 creates/s, 7.05 writes/s
Buffer pool hit rate 993 / 1000
Database size is usually much bigger than available RAM, so most of the time it's no feasible to have buffer pool size equals to database size. Luckily, innodb creates hot spots inside buffer pool, that is, most frequently accessed data pages stay in memory but if your application performs a lot of random disk I/Os and your database is too large to fit in memory and only small percentage of data pages can be cached in innodb buffer pool, then adding more RAM is the best solution for random-read I/O problem.
If you still get poor Buffer pool hit rate, enable slow query log to capture bad queries that perform table scans and hence blow out buffer pool cache.