(mysql.info) innodb-tuning
Info Catalog
(mysql.info) innodb-transaction-model
(mysql.info) innodb
(mysql.info) innodb-multi-versioning
14.2.11 `InnoDB' Performance Tuning Tips
----------------------------------------
Menu
* innodb-monitor `SHOW ENGINE INNODB STATUS' and the `InnoDB' Monitors
* 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.
* 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. The rotation speed
of a disk is typically at most 167 revolutions/second, which
constrains the number of commits to the same 167th 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 the
`innodb_flush_log_at_trx_commit' parameter 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 large as well (on the order of 8MB).
* 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 `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.
* 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'. Although
`O_DSYNC' seems to be slower on most systems, 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 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 option.) Failure to use `forcedirectio' causes a
serious degradation of `InnoDB''s speed and performance on this
platform.
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 can be achieved by placing `InnoDB' data files and
log files on raw devices or on a separate direct I/O UFS
filesystem (using mount option `forcedirectio'; see
`mount_ufs(1M)'). Users of the Veritas filesystem VxFS should use
the mount option `convosync=direct'.
Other MySQL data files, such as those for `MyISAM' tables, should
not be placed on a direct I/O filesystem. Executables or libraries
_must not_ be placed on a direct I/O filesystem.
* 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 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 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
forcing-recovery.
* Beware also of other big disk-bound operations. Use `DROP TABLE'
and `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, not just `InnoDB'
tables.
* 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;
... IMPORT OPERATION ...
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.
* 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;
... IMPORT OPERATION ...
SET FOREIGN_KEY_CHECKS=1;
For big tables, this can save a lot of disk I/O.
* If you often have recurring queries for tables that are not
updated frequently, use the query cache:
[mysqld]
query_cache_type = ON
query_cache_size = 10M
Info Catalog
(mysql.info) innodb-transaction-model
(mysql.info) innodb
(mysql.info) innodb-multi-versioning
automatically generated byinfo2html