InnoDB, having a long
KEY wastes a lot of disk space because its value
must be stored with every secondary index record. (See
Section 13.6.10, “
InnoDB Table and Index Structures”.) Create an
AUTO_INCREMENT column as the primary key
if your primary key is long.
If you have
UNIQUE constraints on
secondary keys, you can speed up table imports by
temporarily turning off the uniqueness checks during the
... 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. 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
... import operation ...SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
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 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, which constrains the number of
commits to the same 167th of a
second if the disk does not “fool” the
If you can afford the loss of some of the latest committed
transactions if a crash occurs, you can set the
parameter to 0.
InnoDB tries to flush the
log once per second anyway, although the flush is not
guaranteed. You should also set the value of
innodb_support_xa to 0,
which will reduce the number of disk flushes due to
synchronizing on disk data and the binary log.
Make your log files big, even as big as the buffer pool.
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).
VARCHAR data type
CHAR if you are
storing variable-length strings or if the column may contain
NULL values. A
column always takes
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.
COMPACT row format (the
InnoDB format in MySQL
5.1) and variable-length character sets, such
will occupy a variable amount of space, at least
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
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 mount any file systems 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
causes a serious degradation of
speed and performance on this platform.
When using the
InnoDB storage engine with
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 placing
InnoDB data files
and log files on raw devices or on a separate direct I/O UFS
file system (using the
mount option; see
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
When importing data into
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
... SQL import statements ...COMMIT;
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 18.104.22.168, “Forcing
Use the multiple-row
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
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
does not store an index cardinality value in its tables.
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
MySQL Enterprise. For optimization recommendations geared to your specific circumstances subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.