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
In InnoDB
, having a long PRIMARY
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.
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(
column always takes N
)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 COMPACT
row format (the
default InnoDB
format in MySQL
5.5) and variable-length character sets, such
as utf8
or sjis
,
CHAR(
will occupy a variable amount of space, at least
N
)N
bytes.
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
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. 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.
Disk I/O Tips
innodb_buffer_pool_size
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.4.6, “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 13.6.6.2, “Forcing InnoDB
Recovery”.
Beware also of other big disk-bound operations. Use
DROP TABLE
and
CREATE TABLE
to empty a
table, not DELETE FROM
.
tbl_name
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
. 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 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, set
innodb_flush_method =
O_DIRECT
if you do not want to affect the entire
file system. This causes InnoDB
to call
directio()
instead of
fcntl()
. However, setting
innodb_flush_method
to
O_DIRECT
causes InnoDB
to use direct I/O only for data files, not the log files.
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 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 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
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
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.
Logging Tips
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 longer.
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 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.
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;
... 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:
SET foreign_key_checks=0;
... SQL import statements ...
SET foreign_key_checks=1;
For big tables, this can save a lot of disk I/O.
Other Tips
Unlike MyISAM
, InnoDB
does 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 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 often have recurring queries for tables that are not updated frequently, enable the query cache:
[mysqld] query_cache_type = 1 query_cache_size = 10M
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.
User Comments
On NetBSD "innodb_flush_method parameter to O_DSYNC"
makes a HUGE difference.
If you are doing a huge batch insert, try avoiding the "select from last_insert_id" that follows the insert as it seriously slows down the insertions (to the order of making a 6 minute insert into a 13 hour insert) if you need the number for another insertion (a subtable perhaps) assign your own numbers to the id's (this obviously only works if you are sure nobody else is doing inserts at the same time).
innodb_flush_method = O_DSYNC really did a big difference when I worked with restore and InnoDB on my environment. In conjunction of that, having the correct size of transaction logs, log buffer and start the restore with autocommit = 0 makes a good differece too.
Add your own comment.