![background image](http://html1.mh-extra.com/html/oracle/5-0/5-0_reference-manual_16461961305.webp)
InnoDB
Performance Tuning and Troubleshooting
1285
• 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
[1243]
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, as of
MySQL 5.0.42 you can set
innodb_flush_method = O_DIRECT
[1243]
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
[1243]
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
[1238]
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
[1243]
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
[303]
, 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;
Содержание 5.0
Страница 1: ...MySQL 5 0 Reference Manual ...
Страница 18: ...xviii ...
Страница 60: ...40 ...
Страница 396: ...376 ...
Страница 578: ...558 ...
Страница 636: ...616 ...
Страница 844: ...824 ...
Страница 1234: ...1214 ...
Страница 1426: ...MySQL Proxy Scripting 1406 The following diagram shows an overview of the classes exposed by MySQL Proxy ...
Страница 1427: ...MySQL Proxy Scripting 1407 ...
Страница 1734: ...1714 ...
Страница 1752: ...1732 ...
Страница 1783: ...Configuring Connector ODBC 1763 ...
Страница 1793: ...Connector ODBC Examples 1773 ...
Страница 1839: ...Connector Net Installation 1819 2 You must choose the type of installation to perform ...
Страница 1842: ...Connector Net Installation 1822 5 Once the installation has been completed click Finish to exit the installer ...
Страница 1864: ...Connector Net Visual Studio Integration 1844 Figure 20 24 Debug Stepping Figure 20 25 Function Stepping 1 of 2 ...
Страница 2850: ...2830 ...
Страница 2854: ...2834 ...
Страница 2928: ...2908 ...
Страница 3000: ...2980 ...
Страница 3122: ...3102 ...
Страница 3126: ...3106 ...
Страница 3174: ...3154 ...
Страница 3232: ...3212 ...