MySQL Reference Manual :: 15.12 InnoDB Performance Tuning Tips

If the Unix top tool or the Windows Task Manager shows that the CPU usage percentage with your workload is less than 70%, your workload is probably disk-bound. Maybe you are making too many transaction commits, or the buffer pool is too small. Making the buffer pool bigger can help, but do not set it bigger than 80% of physical memory.

Wrap several modifications into one transaction. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. Since the rotation speed of a disk is typically at most 167 revolutions/second, that constrains the number of commits to the same 167th/second if the disk does not fool the operating system.

If you can afford the loss of some of the latest committed transactions, you can set the my.cnf parameter innodb_flush_log_at_trx_commit to 0. InnoDB tries to flush the log once per second anyway, although the flush is not guaranteed.

Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it has to write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The drawback of big log files is that the recovery time is longer.

Make the log buffer quite big as well (say, 8MB).

Use the VARCHAR column type instead of CHAR if you are storing variable-length strings or if the column may contain many NULL values. A CHAR(N) column always takes N bytes to store data, even if the string is shorter or its value is NULL. Smaller tables fit better in the buffer pool and reduce disk I/O.

When using row_format=compact (the default InnoDB record format in MySQL 5.0) and variable-length character sets, such as utf8 or sjis, CHAR(N) will occupy a variable amount of space, at least N bytes.

(Relevant from 3.23.39 up.) In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() and other similar methods is surprisingly slow. The default method InnoDB uses is the fsync() function. If you are not satisfied with the database write performance, you might try setting innodb_flush_method in my.cnf to O_DSYNC, although O_DSYNC seems to be slower on most systems.

(Verified using MySQL 4.1, assumed for other MySQL versions, given that this is a platform architecture issue.) When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), it is important to to mount any filesystems used for storing InnoDB-related files using the forcedirectio option. (The default on Solaris 10/x86_64 is not to use this filesystem mounting option.) Failing to use forcedirectio will cause a serious degradation of InnoDB's speed and performance on this platform.

When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that would require a log flush to disk for every insert. To disable autocommit during your import operation, surround it with SET AUTOCOMMIT and COMMIT statements:

SET AUTOCOMMIT=0;
/* SQL import statements ... */
COMMIT;

If you use the mysqldump option --opt, you get dump files that are fast to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.

Beware of big rollbacks of mass inserts: InnoDB uses the insert buffer to save disk I/O in inserts, but no such mechanism is used in a corresponding rollback. A disk-bound rollback can take 30 times the time of the corresponding insert. Killing the database process does not help because the rollback starts again at the server startup. The only way to get rid of a runaway rollback is to increase the buffer pool so that the rollback becomes CPU-bound and runs fast, or to use a special procedure. See Section 15.9.1, “Forcing Recovery”.

Beware also of other big disk-bound operations. Use DROP TABLE + CREATE TABLE to empty a table, not DELETE FROM tbl_name.

Use the multiple-row INSERT syntax to reduce communication overhead between the client and the server if you need to insert many rows:

INSERT INTO yourtable VALUES (1,2), (5,5), ...;

This tip is valid for inserts into any table type, not just InnoDB.

If you have UNIQUE constraints on secondary keys, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

SET UNIQUE_CHECKS=0;

For big tables, this saves a lot of disk I/O because InnoDB can use its insert buffer to write secondary index records in a batch.

If you have FOREIGN KEY constraints in your tables, starting from MySQL 3.23.52 and 4.0.3, you can speed up table imports by turning the foreign key checks off for a while in the import session:

SET FOREIGN_KEY_CHECKS=0;

For big tables, this can save a lot of disk I/O.

If you often have recurring queries to tables that are not updated frequently, use the query cache available as of MySQL 4.0:

[mysqld]
query_cache_type = ON
query_cache_size = 10M

In MySQL 4.0, the query cache works only with autocommit enabled. This restriction is removed in MySQL 4.1.1 and up.