InnoDB Operational Control

Before leaving the chapter on InnoDB tuning tips, this section takes some time to explore some additional operational-specific settings that you can use to regulate server responsiveness.

Thread Performance

MySQL uses the innodb_thread_concurrency setting to gate the number of simultaneous active operating system threads supporting the InnoDB engine. With a default value of 8, it usually can be left alone. However, if you are running a database server with a combined total of greater than eight CPUs and disk drives, try boosting this value to match the sum of these critical components.

By raising the setting, you are instructing MySQL to request additional threads. However, these supplementary threads aren't fully utilized if there aren't sufficient CPUs and disk drives.

Improving Concurrency

You completed a holistic review of concurrency, locking, and transactions during Chapter 9. Recall that newer MySQL versions are more adept at detecting and correcting deadlocks. However, if you are running an older version, MySQL relies on the innodb_lock_wait_timeout setting, specified in seconds, to help it determine when a deadlock is under way.

If you believe that your older MySQL server is particularly susceptible to these kinds of problems, try lowering the value to cause deadlocks to be discovered and fixed sooner, or, even better: Take the time to upgrade to a newer version.

Improving Large-Scale Operations

Chapter 15, "Improving Import and Export Operations," cites numerous ways to help coax added performance from these costly processes. For now, there are a few key things to remember:

• You can specify that your MySQL data export process generate relatively few (but very fast) multirow INSERT statements rather than a large collection of traditional single-row INSERT statements.

• Data-loading speed is significantly better if your input file is already sorted in primary key order.

• Whenever possible, disable expensive foreign key and unique checks when loading large volumes of data into InnoDB. To do this, set FOREIGN_KEY_CHECKS and UNIQUE_CHECKS to zero.

• Automatically committed transactions are expensive: They require frequent, costly disk flushes. For large-scale data load operations, remember to set AUTOCOMMIT to zero.

Speeding Up Shutdown

You should note one final topic regarding InnoDB-specific performance. When enabled (the default), the innodb_fast_shutdown parameter instructs InnoDB to bypass the potentially expensive tasks of purging internal memory structures and then merging insert buffers. This does not affect data integrity; InnoDB still inscribes any pertinent buffer pool pages onto disk.