MySQL Innodb config optimization tips

[hellreturn]

1. innodb_flush_log_at_trx_commit = 2 or 0.

 

1 = devastating db write performance

 

"If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit."

 

"The default value of 1 is the value required for ACID compliance."

 

If data lost of last one second is not of that much concern then user value 0 or use value 2. InnoDB can be run in a mode where it has lower reliability but in some cases higher performance. Setting innodb_flush_log_at_trx_commit to 0 switches to a mode where transactions are not committed to disk before control is returned to the caller. Instead, disk flushes happens on a timer.

 

Run a SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit' query to get the current value.

 

2. innodb_buffer_pool_size 70-80% of memory is a safe bet. I set it to 6GB on 8GB box if you are only running website/forums and not game servers.

 

3. 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

 

4. 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.

 

5. 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.

 

6. 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.

 

7. 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.

 

8. 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.

 

Note: Don't ever convert DB to Innodb engine. It will kill the performance. There are reasons why MySQL comes with MyISAM, InnoDB and Heap engine.

 

Only use Innodb for the tables having too many inserts/deletes.

Innodb = Row Level Locking

MyISAM = Table Level Locking

Heap = Good for session.

 

InnoDB is the default storage engine for MySQL as of MySQL 5.5

 

Few notes:

1. InnoDB doesn't support full-text searches. You can use 3rd party search app for it. sphinx can be good solution.

2. InnoDB will be more I/O bound. Make sure you have good RAID system set up if you have more then 300-500 QPS.

3. MyISAM uses table-level locking on updates and deletes to any existing row, with an option to append new rows instead of taking a lock and inserting them into free space. InnoDB uses row-level locking. For large database applications where many rows are often updated, row-level locking is crucial because a single table-level lock significantly reduces concurrency in the database.

4. MyISAM relies on the filesystem block cache for caching reads to the data rows and indexes, while InnoDB does this within the engine itself, combining the row caches with the index caches.