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.

innodb_buffer_pool_size

"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:

• Interview with Daniel Chalef of KnowledgeTree(Jul 09, 2009)
• Five Places You Can Slash IT Spending(Jul 09, 2009)
• PostgreSQL 8.4 Improves Database Management, Security(Jul 03, 2009)
• The New MySQL Server Release Model(Jul 01, 2009)
• EnterpriseDB Brings Postgres Closer to Oracle(Jun 18, 2009)
• Drizzle: Rethinking the MySQL Database Kernel(Jun 17, 2009)
• MySQL: Five Dials to Set(Jun 12, 2009)