The followings tips are grouped by category. Some of them can apply in multiple categories, so it is useful to read them all.
Storage Layout Tips
InnoDB, having a long
KEY wastes a lot of disk space because its value must
be stored with every secondary index record. (See
Section 18.104.22.168, “InnoDB Table and Index Structures”.) Create an
AUTO_INCREMENT column as the primary key if
your primary key is long.
VARCHAR data type instead
CHAR if you are storing
variable-length strings or if the column may contain many
NULL values. A
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.
COMPACT row format (the default
InnoDB format in MySQL 5.1) and
variable-length character sets, such as
will occupy a variable amount of space, at least
Transaction Management Tips
Wrap several modifications into a single transaction to reduce
the number of flush operations.
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 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
parameter to 0.
InnoDB tries to flush the log
once per second anyway, although the flush is not guaranteed.
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.
Disk I/O Tips
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
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 22.214.171.124, “Forcing InnoDB Recovery”.
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
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.1.18 you can set
innodb_flush_method = O_DIRECT
if you do not want to affect the entire file system. This causes
InnoDB to call
fcntl(). However, setting
use direct I/O only for data files, not the log files.
When using the
InnoDB storage engine with a
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
forcedirectio mount option as
described earlier (it is necessary to use the mount option
rather than setting
innodb_flush_method if you want
direct I/O for the log files). Users of the Veritas file system
VxFS should use the
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.
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
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 with
... SQL import statements ...COMMIT;
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:
... 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:
... SQL import statements ...SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
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
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