(mysql.info.gz) InnoDB tuning

(mysql.info.gz) InnoDB transaction model
(mysql.info.gz) InnoDB Multi-Versioning
 
 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 will cause many unnecessary disk writes. The
      drawback of big log files is that recovery time will be 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.
 
    * (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.
 
    * 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 will 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 will not help because the rollback will start 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.   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.
(mysql.info.gz) InnoDB tuning

Menu
