The InnoDB buffer pool caches data and index information for InnoDB tables. Making the buffer pool larger reduces disk I/O for frequently accessed InnoDB table contents. The buffer pool size is controlled by innodb_buffer_pool_size system variable. Its default value is 8MB. On a machine dedicated to MySQL, you can set this variable anywhere from 50% to 80% of total amount of memory. However, the setting should take into account how large we set the key_buffer_size value.
innodb_buffer_pool_size is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply – if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
The InnoDB log buffer holds information about modification made during transaction processing. Ideally, you want a transaction's changes to be held in the buffer until the transaction commits, at which point the can be written to the InnoDB log file all at once. If the buffer is too small, changes might need to be written several times before commit time, resulting in additional disk activity. The log buffer size is controlled by innodb_log_buffer_size system variable. Typical values range from 1MB to 8MB. The default is 1MB.
innodb_log_buffer_size: Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory – it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
innodb_additional_mem_pool_size does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
innodb_log_file_size is very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
innodb_flush_log_at_trx_commit: Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash. See http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/
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 result in 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 its row storage formats, 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 result 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 effect 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 correct these problems and reclaims index space. There are two ways to perform a rebuild:
1. Use a "null" ALTER TABLE operation:
mysql> ALTER TABLE table_name ENGINE = InnoDB;
2. Dump and reload the table:
shell> mysqldump db_name table_name > dump_file shell> mysql db_name < dump_file
Configure InnoDB to use a larger buffer (to reduce flushing from in-memory log buffer to disk)
Choose a log flushing method that best matches our goals. We 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.
Use raw disk partitions in the tablespace to avoid a level of filesystem-access overhead normally incurred when using regular files.
InnoDB Buffer Pool and Log Buffers
Two memory-related InnoDB resources are the buffer pool and the log buffer:
- The InnoDB buffer pool caches data and index information for InnoDB tables. Making the buffer pool larger reduces disk I/O for frequently accessed InnoDB table contents. The buffer pool size is controlled by innodb_buffer_pool_size system variable. Its default value is 8MB. On a machine dedicated to MySQL, you can set innodb_bufffer_pool_size variable to anywhere from 50% to 80% of the total amount of memory. However, this setting should take into account how large you set the key_buffer_size value for MyISAM.
- The InnoDB log buffer holds information about modifications made during transaction processing. Ideally, you want a transaction's changes to be held in the buffer until the transaction commits, at which point they can be written to the InnoDB log file all at once. If the buffer is too small, changes might need to be written several times before commit time, resulting in additional disk activity. The log buffer size is controlled by the innodb_log_buffer_size system variable. Typical values range from 1MB to 8MB. The default is 1MB.
See section 29.4.7 "Configuring and Monitoring InnoDB"