How do you tune MySQL for a heavy InnoDB workload?

Assuming a production OLTP system with predominantly InnoDB tables

• What are the common symptoms of a mistuned/misconfigured system?

• What configuration parameters do you most commonly change from their defaults?

• How do you spot potential bottlenecks before there is a problem?

• How do you recognize and troubleshoot active problems?

Any anecdotes detailing specific status variables and diagnostics would be appreciated.

Interestingly, in MySQL 5.5, you can now have multiple innodb buffer pools.

The parameters you care about are

• innodb_buffer_pool_instances (default is 1)

• innodb_buffer_pool_size (default is 1G)

In about a month, I am slated to implement 112 innodb buffer pools for a client. I'll let you know how it went.
UPDATE 2011-02-27 21:57 EDT

I found out the max value for innodb_buffer_pool_instances is 64
I decided to configure 144 GB , so I set innodb_buffer_pool_instances to 18 and innodb_buffer_pool_size to 8. I am currently loading the server with 450GB
UPDATE 2011-04-28 13:44 EDT

I tried out multiple InnoDB Buffer Pools. There was too much threading locking and contention. I changed over to a single 162GB Buffer Pool + setting read_io_threads and write_io_threads to 64 (maximum value). This worked way better.
UPDATE 2012-07-03 17:27 EDT

I learned something amazing about MySQL. If you allocate a single monolithic InnoDB Buffer Pool that is bigger that Total Installed Divided By Number of Physical CPUs, your will incite the OS to regular intervals memory swapping due to a full InnoDB Buffer Pool. MySQL 5.5's option known as innodb_buffer_pool_instances can be used to split up the buffer pool. Yesterday, I properly implemented this for the client I mentioned in my answer last year. I still have 162GB for the client's Buffer Pool. I have set the server's innodb_buffer_pool_instances option to 2 because each DB Server is dual hexacore. I was thinking of setting it to 12 but then a colleague showed me a blog from Jeremy Cole on MySQL and Swappiness. After reading it, I put it into practice immediately for my client. I ran this command

numactl --hardware

I saw a mapping of 192GB of server RAM as 96GB to each physical core. Therefore, I set the innodb_buffer_pool_instances to 2. Things are looking good right now. I will update my answer to see how this affects memory swapping for the next 2 montns.

StackExchange Database Administrators Q#194, answer score: 20