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.
But don't increase the buffer_pool so much that it causes swapping. MySQL does not expect the buffer_pool to be paged out and in; this leads to severe inefficiency.ReplyDelete
The simple rule of thumb for the buffer_pool of about 70% of available RAM works for most installations.
If you have increased the buffer pool and still have a poor hit rate, then what to do? Get more RAM. Or identify queries that can be rewritten to avoid, for example, table scans. Or redesign your schema.
In your first example the percentage is 0.001, not .1ReplyDelete
Thanks - Gavin, fixedDelete
Wooww... nice article.. thankssReplyDelete
Thanks for posting the blog. I felt comfortable while reading the post. Keep posting more blogs.ReplyDelete
Please Click Here For More Information About Any Course or Training Institute all over the world
Blockchain Training in Houston
thank you for this blog.ReplyDelete
Thank you. I'm a linux sys admin with pretty limited knowledge of Databases and this was very helpful. I was trying to figure out if my Zabbix DB was using too much RAM but it seems on point with an innodb_buffer of 18GReplyDelete
azure devops training
app v training
windows admin training