Storage Engine Specifics

Most storage engines have their own specific processes that can be addressed to improve the performance of the operations performed against them. Two of the main storage engines; MyISAM and InnoDB are not exception. In the case of speeding up insert operations, these two storage engines will be addressed.

4.3.1 Speeding up Bulk Inserts with MyISAM

One of the drawbacks of performing bulk inserts is the amount of time that takes place to accomplish the task. Even though bulk inserts tend to be faster than other means of inserting large amounts of data, there are still ways to fine-tune MySQL to improve the inherent performance gains from such an operation. One of these ways is to disable non-unique index keys during the bulk insert operation. The following discussion applies to bulk insert operations performed against tables utilizing the MyISAM storage engine.

Disabling keys

The process by which non-unique indexes are disabled with MyISAM tables is to alter the table that the operation will be performed against:

mysql> ALTER TABLE table_name DISABLE KEYS;

Once this statement is executed, the server itself will ignore these indexes when determining the best query execution plans either by using SELECT or evaluating any statements with the EXPLAIN feature.

Enabling keys

The process by which non-unique indexes are enabled (or re-enabled) with MyISAM tables is to alter the table that the operation will be performed against:

mysql> ALTER TABLE table_name ENABLE KEYS;

Once this statement is executed, the server itself will evaluate these indexes when determining the best query execution plans either by using SELECT or evaluating any statements with the EXPLAIN feature.

Delaying insert

Bullk inserts can be improved by using the DELAYED option with INSERT. This is due to the client not having to wait until the bulk insert is completed to execute other statements.

Rebuilding at one time

The advantage of disabling keys and then re-enabling them after a bulk insert operation comes in the form of rebuilding the indexes once, rather than having the server performing a modification against the physical storage of the indexes after each individual operation. Even though the re-enabling operation requires the server to rebuild the indexes for the whole table, the time that it takes is much faster than if the keys were enabled during the bulk insert operation. If there is a desire to achieve any benefit from this process, it is best to perform these actions against an empty table.

When should this be used?

To gain benefit from this process, the rows being inserted must be significantly more than the rows already existing in the table.

100 99

Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable

license to use this Student Guideฺ

4.3.2 Speeding up Bulk Inserts with InnoDB InnoDB Defaults

InnoDB default options are very conservative and in several situations increasing the buffers can have a high impact on the performance. To speed up inserts on an InnoDB-only setup, the rule of a thumb is to set the innodb_buffer_pool size to 50-80% of the computer's memory and to set innodb_log_file_size to about 25% of the innodb_buffer_pool_size.

If the MySQL server is running on several threads, which all work on the InnoDB table(s), performance can be gained by setting the innodb_flush_log_at_trx_commit value to 0. This adds some risk of losing some transactions in the case of a power failure or an unclean mysqld shutdown, but it can improve speed.

101

Primary key order inserts

Please note that when inserting several rows at once in InnoDB table, it is fastest, if the rows are inserted in primary key order. This is because of the clustered index, which InnoDB always uses.

Disable AUTOCOMMIT

102

When importing data into InnoDB, make sure that the MySQL server does not have autocommit mode enabled because that requires a log flush to disk for every insert. To disable autocommit during the import operation, surround it with SET AUTOCOMMIT and COMMIT statements:

mysql> SET AUTOCOMMIT=0;

... SQL import statements ...

mysql> COMMIT;

If using the mysqldump option --opt, dump files are faster to import into an InnoDB table, even without wrapping them with the SET AUTOCOMMIT and COMMIT statements.

Turn off UNIQUE_CHECKS

103 _{If there are UNIQUE constraints on secondary keys, table import performance can be improved by}

temporarily turning off the uniqueness checks during the import session:

mysql> SET UNIQUE_CHECKS=0;

... import operation ...

mysql> 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. UNIQUE_CHECKS allows but does not require storage engines to ignore duplicate keys.

Turn off FOREIGN_KEY_CHECKS

104

If there are FOREIGN KEY constraints in the InnoDB tables, table import performance can be improved by turning foreign key checks off for the duration of the import session:

mysql> SET FOREIGN_KEY_CHECKS=0;

... import operation ...

mysql> SET FOREIGN_KEY_CHECKS=1;

For big tables, this can save a lot of disk I/O.

4-10

_________________________________________________________________________________________________

_________________________________________________________________________________________________ _________________________________________________________________________________________________

Thi Nguyet Tran (moonฺtran@spts21ฺcomฺvn) has a non-transferable

license to use this Student Guideฺ