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 14.2.9, “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.
COMPACT row format (the
InnoDB format in MySQL
5.0) and variable-length character sets, such
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.
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 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 18.104.22.168, “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
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 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.)
Alternatively, as of MySQL 5.0.42 you can set
O_DIRECT if you do not want to affect the entire
file system. This causes
InnoDB to call
directio() instead of
fcntl(). However, setting
to use direct I/O only for data files, not the log files.
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 (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
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, you can speed up table imports by
temporarily turning off the uniqueness checks during the
... 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
... 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:
[mysqld] query_cache_type = 1 query_cache_size = 10M