InnoDB Performance Tuning Tips

Storage Layout Tips

• In InnoDB, having a long PRIMARY KEY wastes a lot of disk space because its value must be stored with every secondary index record. (SeeSection 14.2.10, “InnoDB Table and Index Structures”.) Create anAUTO_INCREMENT column as the primary key if your primary key is long.
• Use the VARCHAR data 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 characters 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 COMPACT row format (the default InnoDB 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.
  Transaction Management Tips
  • Wrap several modifications into a single transaction to reduce the number of flush operations. InnoDB must flush the log to disk at each transaction commit if that transaction made modifications to the database. The rotation speed of a disk is typically at most 167 revolutions/second (for a 10,000RPM disk), which constrains the number of commits to the same 167^th of a second if the disk does not “fool” the operating system.
  • If you can afford the loss of some of the latest committed transactions if a crash occurs, you can set theinnodb_flush_log_at_trx_commit parameter to 0. InnoDB tries to flush the log once per second anyway, although the flush is not guaranteed.
    Disk I/O Tips
    • innodb_buffer_pool_size specifies the size of the buffer pool. If your buffer pool is small and you have sufficient memory, making the pool larger can improve performance by reducing the amount of disk I/O needed as queries access InnoDB tables. For more information about the pool, see Section 8.6.2, “The InnoDB Buffer Pool”.
    • 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 as long to perform as the corresponding insert. Killing the database process does not help because the rollback starts again on 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 14.2.6.2, “Forcing InnoDB Recovery”.
    • Beware also of other big disk-bound operations. Use DROP TABLE and CREATE TABLE to empty a table, notDELETE FROM tbl_name.
    • In some versions of GNU/Linux and Unix, flushing files to disk with the Unix fsync() call (which InnoDB uses by default) and other similar methods is surprisingly slow. If you are dissatisfied with database write performance, you might try setting the innodb_flush_method parameter to O_DSYNC. The O_DSYNC flush method seems to perform slower on most systems, but yours might not be one of them.
    • When using the InnoDB storage engine on Solaris 10 for x86_64 architecture (AMD Opteron), it is important to use direct I/O for InnoDB-related files. Failure to do so may cause degradation of InnoDB's speed and performance on this platform. To use direct I/O for an entire UFS file system used for storing InnoDB-related files, mount it with the forcedirectio option; see mount_ufs(1M). (The default on Solaris 10/x86_64 is not to use this option.) Alternatively, as of MySQL 5.0.42 you can set innodb_flush_method = O_DIRECT if you do not want to affect the entire file system. This causes InnoDB to call directio() instead of fcntl(). However, settinginnodb_flush_method to O_DIRECT causes InnoDB to use direct I/O only for data files, not the log files.
      When using the InnoDB storage engine with a large innodb_buffer_pool_size value on any release of Solaris 2.6 and up and any platform (sparc/x86/x64/amd64), a significant performance gain might be achieved by placingInnoDB data files and log files on raw devices or on a separate direct I/O UFS file system using theforcedirectio mount option as described earlier (it is necessary to use the mount option rather than settinginnodb_flush_method if you want direct I/O for the log files). Users of the Veritas file system VxFS should use the convosync=direct mount option. You are advised to perform tests with and without raw partitions or direct I/O file systems to verify whether performance is improved on your system.
      Other MySQL data files, such as those for MyISAM tables, should not be placed on a direct I/O file system. Executables or libraries must not be placed on a direct I/O file system.
    • 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 equal to more than 80% of physical memory.
    Logging Tips
    • Make your log files big, even as big as the buffer pool. When InnoDB has written the log files full, it must write the modified contents of the buffer pool to disk in a checkpoint. Small log files cause many unnecessary disk writes. The disadvantage of big log files is that the recovery time is longer.
    • Make the log buffer quite large as well (on the order of 8MB).
    Bulk Data Loading Tips
    • When importing data into InnoDB, make sure that MySQL does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during your import operation, surround it withSET 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.
    • If you have UNIQUE constraints on secondary keys, you can speed up table imports by temporarily turning off the uniqueness checks during the import session:

      SET unique_checks=0;... SQL import statements ...
      SET unique_checks=1;
      

      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. Be certain that the data contains no duplicate keys.
      • If you have FOREIGN KEY constraints in your tables, you can speed up table imports by turning the foreign key checks off for the duration of the import session:

        SET foreign_key_checks=0;... SQL import statements ...
        SET foreign_key_checks=1;
        

        For big tables, this can save a lot of disk I/O.
      Other Tips
      • Unlike MyISAM, InnoDB does not store an index cardinality value in its tables. Instead, InnoDB computes a cardinality for a table the first time it accesses it after startup. With a large number of tables, this might take significant time. It is the initial table open operation that is important, so to “warm up” a table for later use, access it immediately after startup by issuing a statement such as SELECT 1 FROM tbl_name LIMIT 1.
      • 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, not just InnoDB tables.
      • If you often have recurring queries for tables that are not updated frequently, enable the query cache:

        [mysqld]
        query_cache_type = 1
        query_cache_size = 10M