38.4. InnoDB-Specific Optimizations Several strategies may be used with InnoDB to improve performance. Some of these can be used at the application level. Others are a result of the way that the database administrator configures InnoDB itself. Application-level optimizations may be made in terms of how you design tables or issue queries: Take advantage of InnoDB indexing structure. Use a primary key in each table, but make the key values as short as possible. InnoDB uses the primary key to locate the table rows. Other (secondary) indexes are keyed to the primary key values, which means that there is a level of indirection to find the table rows. Thus, shorter primary key values make for quicker lookups not only for queries that use the primary key, but also for queries that use secondary indexes. Secondary indexes will also take less space because each secondary index record contains a copy of the corresponding primary key value. Use VARCHAR columns rather than CHAR columns in InnoDB tables. The average amount of space used will be less, resulting in less disk I/O during query processing. (This behavior differs from that of MyISAM tables, which, due to their storage format, generally are faster for fixed-length rows than for dynamic-length rows.) Avoid using the FOR UPDATE or LOCK IN SHARE MODE locking modifiers for queries if there is no index that InnoDB can use to look up rows. These modifiers cause InnoDB to acquire a row lock for each row examined. In the absence of a usable index, InnoDB must perform a complete table scan, which results in a lock being acquired for every row. Avoid using SELECT COUNT(*) FROM table_name queries with InnoDB tables. Although this type of query is very efficient for MyISAM tables because MyISAM stores a row count in the table, InnoDB does not store a row count and must perform a table scan to determine how many rows there are. Modifications made over the course of multiple statements should be grouped into a transaction whenever it makes sense to do so. This minimizes the number of flush operations that must be performed. For example, if you need to run 100 UPDATE statements that each modify a single row based on its primary key value, it's faster to run all the statements within a single transaction than to commit each one as soon as it executes. (A corollary to this principle is that you should avoid making updates with autocommit mode on. That causes the effects of each statement to be flushed individually.) Do periodic table rebuilds as necessary. Indexes in an InnoDB table may become fragmented due to deletes and updates if they modify rows at arbitrary positions within the table. This causes index pages to be underfilled and to be spread around on disk in an order that differs from their logical order. Rebuilding an InnoDB table periodically corrects these problems and reclaims index space. There are two ways to perform a rebuild: Use a "null" ALTER TABLE operation: mysql> ALTER TABLE table_name ENGINE = InnoDB;
Dump and reload the table: shell> mysqldump db_name table_name > dump_file shell> mysql db_name < dump_file
In MySQL 5, InnoDB implements a table format that typically results in a savings of about 20% for disk and memory. If you have InnoDB tables that were created before MySQL 5, you can convert them to use the newer more compact storage format. The table-rebuilding techniques described in the previous item can be used to accomplish this conversion.
It is also possible to make administrative optimizations through the way you configure InnoDB. The following list briefly mentions some of the possibilities: To reduce flushing from the in-memory log buffer to disk, configure InnoDB to use a larger buffer. Choose a log flushing method that best matches your goals. You can opt to guarantee ACID properties such as durability (no loss of committed changes), or to get faster performance at the possible cost of losing approximately the last second's worth of committed changes in the event of a crash. ACID properties are defined in Section 29.4.2, "InnoDB and ACID Compliance and ACID Compliance." Use raw disk partitions in the tablespace to avoid a level of filesystem-access overhead normally incurred when using regular files.
For information on the options that control these aspects of InnoDB operation, see Section 29.4.7, "Configuring and Monitoring InnoDB." |
