I used to manage a couple very large MySQL Databases (like, 1TB+). They were huge, unforgiving beasts with an endless appetite to cause me stomach problems.
I read everything I could find on MySQL Performance Tuning and innodb. Here’s a summary of what helped me:
1. The book High Performance MySQL is good, but only gets you so far.
2. The blog MySQL Performance Blog (this link is to their posts tagged ‘innodb’) was the most useful overall resource I found on the net. They go into detail on a lot of innodb tuning issues. It gets ‘ranty’ at times, but overall it’s great. Here’s another link there on InnoDB Performance Optimization Basics that’s good.
3. The last main thing I did to learn it was to simply read the MySQL Docs themselves. I read how every last parameter works, changed them on my server and then did some basic profiling. After a while you figure out what works by running big queries and seeing what happens. Here’s a good place to start:
InnoDB Performance Tuning and Troubleshooting
In the end, it’s just experimenting and working through things until you gain enough knowledge to know what works.
Please share any other ideas, pointers or resources you know of in the comments — Thanks!