Some tweaks for MySQL InnoDB performance

In most recent mysql-based applications today, InnoDB is used as the database engine due to its great supported features such as transaction, foreign key, etc. But if the DB server is not configured properly, it might cause significant impact on the application performance. This quick article  summarize some suggested tweaks for MySQL InnoDB performance.

The most important settings are:

• innodb_buffer_pool_size: 70-80% of memory is a safe bet. It is suggested to set it to 12G on 16GB machine. (If you’re looking for more details, check out detailed guide on tuning innodb buffer pool)
  • 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
• innodb_log_file_size: This depends on your recovery speed needs but 256M seems to be a good balance between reasonable recovery time and good performance
• innodb_log_buffer_size=4M: 4M is good for most cases unless you’re piping large blobs to Innodb in this case increase it a bit.
• innodb_flush_log_at_trx_commit=2: If you’re not concern about ACID and can loose transactions for last second or two in case of full OS crash than set this value. It can dramatic effect especially on a lot of short write transactions.
  • 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.
• innodb_thread_concurrency=8: Even with current Innodb Scalability Fixes having limited concurrency helps. The actual number may be higher or lower depending on your application and default which is 8 is decent start
• innodb_flush_method=O_DIRECT: Avoid double buffering and reduce swap pressure, in most cases this setting improves performance. Though be careful if you do not have battery backed up RAID cache as when write IO may suffer.
• innodb_file_per_table: If you do not have too many tables use this option, so you will not have uncontrolled innodb main tablespace growth which you can’t reclaim. This option was added in MySQL 4.1 and now stable enough to use.
• Also check if your application can run in READ-COMMITED isolation mode – if it does – set it to be default as transaction-isolation=READ-COMMITTED. This option has some performance benefits, especially in locking in 5.0 and even more to come with MySQL 5.1 and row level replication.
• In InnoDB, having a long PRIMARY KEY wastes a lot of disk space because its value must be stored with every secondary index record. 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(N) column always takes 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.