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 13.2.11, “InnoDB Table and Index Structures”.) Create an
AUTO_INCREMENT column as the primary key
if your primary key is long.
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.
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
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
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 queries access
InnoDB tables. For more
information about the pool, see
Section 7.5.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”.
(Relevant from 3.23.39 up.) 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.
(Verified using MySQL 4.1, assumed for other MySQL versions,
given that this is a platform architecture issue.) When
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
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.)
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
option as described earlier. 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
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.
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
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;
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:
... 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, 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:
... SQL import statements ...SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
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
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 (available as of MySQL 4.0):
[mysqld] query_cache_type = 1 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.