Innodb commits

Raj Thukral suggests setting the innodb_log_flush_at_trx_commit parameter in my.cnf to 0, thereby causing innodb to flush the commits to disc once a second rather than with every commit. This will speed up innodb if there are lots of commits going on which will interfere with other activity.

He does make the point that using this really depend on whether are comfortable with losing 1 second’s worth of data or not.

I am not sure I have that level of intestinal fortitude, and I have had a few bad experiences with 0, so I prefer 1.

I would nonetheless strongly recommend reading the documentation on innodb’s parameters, spefically on innodb_flush_log_at_trx_commit:

When innodb_flush_log_at_trx_commit is set to 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. When this value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When set to 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

The default value of this variable is 1, which is the value that is required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. If you set the value to 0, then any mysqld process crash can erase the last second of transactions. If you set the value to 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and thus crash recovery does work regardless of the value. Note that many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

Note: For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, you should use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master server my.cnf file.

I did spend some time with MySQL tech support on this and came up with the following as an optimal configuration for innodb for large installation:

# InnoDB options, see:

# Data directory, and data file
innodb_data_home_dir = /var/lib/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend

# Use one file per table

# Buffer pool size
#innodb_buffer_pool_size = 1G
innodb_buffer_pool_size = 10G
innodb_additional_mem_pool_size = 32M

# Transaction log location and sizes
innodb_log_group_home_dir = /var/lib/mysql/logs
innodb_log_files_in_group = 4
#innodb_log_file_size = 50M
innodb_log_file_size = 512M
innodb_log_buffer_size = 8M

# Percentage of unwritten dirty pages not to exceed
innodb_max_dirty_pages_pct = 80

# Transaction commit policy
innodb_flush_log_at_trx_commit = 1

# Timeout to wait for a lock before rolling back a transaction
innodb_lock_wait_timeout = 50

# Flush method
innodb_flush_method = O_DIRECT

# Number of concurrent threads to run
innodb_thread_concurrency = 32

# Prevent extra locking, we can only use this if we have row
# level replication, see 'binlog_format'

# Enable fast innodb shutdown (skip full purge and insert buffer merge)
innodb_fast_shutdown = 1
#innodb_fast_shutdown = 0

# Dont delay insert, update and delete operations when purge
# operations are lagging
innodb_max_purge_lag = 0
#innodb_max_purge_lag = 1

# Force recovery mode, do not mess with unless you really know
# what you are doing, see:
#innodb_force_recovery = 4


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: