Mysql InnoDB Performance Tuning Tips

· If the top tool for UNIX or Windows Task Manager shows that the CPU usage of your database workload is less than 70%, your workload may be disk bound, you may be generating too many transactions and submissions, or the buffer pool is too small. Making the buffer pool larger will help, but do not set the buffer pool to equal or exceed 80% of the physical memory.

· Put several changes in one transaction. If a transaction is modified against the database, InnoDB must refresh the log to disk when the transaction commits. Because the disk rotates at a rate of up to 167 rpm, this limits the number of submissions to 167 times per second if the disk does not cheat the operating system.

· If you can accept the loss of some recent committed transactions, you can set the parameter innodb_flush_log_at_trx_commit to 0. In any case InnoDB try to refresh the log every second, although the refresh is not allowed.

· Use a large log file and even make it as large as the buffer pool. When InnoDB writes a log file, it has to write the buffer pool's modified contents to disk at a checkpoint. Small log files cause a lot of unnecessary sucker write operations. Large log files have a longer recovery time when they are weak.

· It also makes the log buffer quite large (a number similar to 8MB).

· If you store variable-length strings, or columns may contain many null values, use the varchar column type instead of the char type. A char (n) column always occupies N bytes to store, even if the string is shorter or the value of the string is null. The smaller tables are better suited to the buffer pool and reduce disk I/O.

When using Row_format=compact (the default InnoDB record format in MySQL 5.1) and variable-length character sets, such as GB2312 or Sjis,char (n), will occupy a variable amount of space, at least N bytes.

· On some versions of Gnu/linux and UNIX, the files are flushed to disk using Unix Fsync (innodb default), and other similar methods are surprisingly slow. If you're not satisfied with the write performance of your database, you can try setting the parameter Innodb_flush_method value to O_dsync, although O_dsync looks slower on most systems.

· When using the InnoDB storage engine for the x86_64 architecture (AMD Opteron) on Solaris 10, it is important to use the Forcedirectio option to install any data systems that are used to store InnoDB-related files. (The File system installation option is not used by default on Solaris 10/x86_64). Failure to use Forcedirectio can lead to a severe decline in the speed and performance of InnoDB on this platform.

· When importing data into InnoDB, be assured that MySQL does not allow autocommit mode because autocommit mode requires that the log be flushed to disk each time it is inserted. To suppress the autocommit mode in the import operation, wrap the import statement with the set AUTOCOMMIT and the commit statement:

SET autocommit=0;

/* SQL Import Statements ... * *

COMMIT;

· If you use the mysqldump option--opt, even if you do not wrap the set autocommit and commit statements, you make the fast dump file imported into the InnoDB table.

· Watch out for large inserts: InnoDB use Insert buffering to conserve disk I/O in the insert, but no such mechanism is used in the corresponding rollback. A rollback of a disk binding can be performed 30 times times as long as the corresponding insertion takes. Killing the database process is not helpful because the rollback starts again when the server starts. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU bound and run fast, or use a private step, see section 15.2.8.1, "force recovery."

· Also be careful of other large disk binding operations. Empty a table with a DROP table or CREATE table instead of the DELETE from Tbl_name.

· If you need to insert many rows, use multiline insertion syntax to reduce the communication overhead between the client and the server: