Linux Today: Linux News On Internet Time.

MySQL: Five More Dials To Turn

Jul 09, 2009, 16:33 (0 Talkback[s])
(Other stories by Sean Hull)

"6. Innodb
InnoDB is likely your storage engine of choice for MySQL as it provides transactional support, as well as crash protection.


"Typically the InnoDB buffer cache should get the bulk of memory on the system. So for example if you are just running MySQL on your server (we hope so), then perhaps 20% of the memory can go to the OS, 20-30% to sessions, and the remaining 50% to static buffers in MySQL. If you're not using MyISAM most of that 50% will be for this InnoDB buffer pool.

calculate innodb buffer pool hit ratio
1 - (innodb_buffer_pool_reads / innodb_buffer_pool_read_requests)

"A hit ratio is not a bulletproof way to tune I/O, but it can give you a good indication and starting point from which to tune. Since InnoDB caches both data and index in this buffer pool, the hit ratio here will tell you overall the % of hits that are satisfied by going to memory versus the ones which require disk I/O. You want to do as little disk I/O as possible, so a higher hit rate here means you're caching better and more relevant blocks to your applications needs."

Complete Story

Related Stories: