Now that you've explored general MySQL database engine-tuning options, as well as specific things you can do to boost MyISAM performance, this chapter turns your attention to increasing the InnoDB storage engine's responsiveness.
This chapter begins by examining the InnoDB data storage architecture, as well as how to make all disk I/O operations as efficient as possible. Next, it studies how to leverage InnoDB's powerful memory management capabilities. Finally, it reviews several ways that administrators can provide guidance to InnoDB to improve operational activities.
Before this chapter begins, you should note the following important points:
This chapter only focuses on those InnoDB server variables and status indicators that directly affect performance; it doesn't discuss unrelated InnoDB options.
Although Chapter 9, "Developing High-Speed Applications," has already examined InnoDB-based transactions, locking, and concurrency, some of these observations are resurrected here.
Instead of one dedicated section on InnoDB performance monitoring tools, SHOW INNODB STATUS and MySQL Administrator-based examples are interspersed throughout the chapter to help illustrate the suggestions.
This chapter spends significant time examining the InnoDB buffer pool, which caches frequently used data and index content to help speed performance. Of course, even if you incorrectly undersize your buffer pool, it's quite possible that your operating system might end up caching this information anyway. However, for clarity's sake, this chapter doesn't examine the potential interaction between operating system and database caching.
As stated previously, chances are that at least one of the preconfigured settings files that ship with InnoDB will go a long way toward delivering optimal performance in your environment. The best return on investment when analyzing response tuning usually is found in schema and index alterations; replication and/or clustering configuration is also very important.