Thesql innodb_flush_log_at_trx_commit clarification

innodbMySQL

I have been reading up on innodb as a storage engine, mainly because I have recently moved to AWS and they do not recommend myISAM which my databases current run on (and have for about 10 years)

So moving to a new engine is a little bit of a scary operation.

I am apprehensive about the move, as it has been on myISAM for so long without issue, but if it gives me peace of mind long term, then it is better for the DB.

I have read the whitepapers on InnoDB and it seems fairly straight forward, The one thing that I want to clear in my mind is the innodb_flush_log_at_trx_commit option

By default this is set to 1, however from my reading this causes additional overheads, if the data was credit card transactions or something like that, I can understand its needs to be there, but it seems when dealing with non life changing data, that innodb_flush_log_at_trx_commit=2 is a better option.

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

Best Answer

What I want to know is, this does not affect the time that the query is actually committed does it? It only affects its recovery ?

It only affects recovery.

I just want to make sure that when I do an insert or update that the query will run right at the time of processing and not 1 second later, no matter what the flush_log is set to.

Correct. There may be reasons to hide changes that other transactions see (as part of multi-version concurrency control). If you're trying to build tests to prove things - make sure you understand how transaction-isolation levels work :)

My understanding is that in the case of a crash , setting to 1 will allow it to recover all queries run on the server, where as setting it to 2 may lose the last second or two of data when trying to recover from the crash, is this correct?

Correct. To be able to recover all (setting: 1) ti also assumes that the underlying hardware is not adding additional buffering to improve IO performance. I have no reason to assume that Amazon doesn't do this, so I think setting it to 2 is a good cloud practice.

Also, if there is slow periods of updates/inserts (ie, not much happening on the server) does setting it to 2 add additional overheads on the server, or is it a case of the benefits of applying innodb_flush_log_at_trx_commit=2 to the database when the database is busy outweighs any additional overhead caused during slow times?

What happens when you set to 2 (and why you get better performance) is that you just buffer changes for longer, and therefor get more IO request merging, and better performance. It doesn't create more work.

Shameless plug for additional reading - When does MySQL perform IO?

Related Solutions

Mysql – Which thesql storage engine to choose

Both works with b-tree index, but MyISAM can work with FULLTEXT index also.

MySQL 5.6 is supposed to support FULLTEXT indexing as well. Look for it when it goes GA.

InnoDB is more strict in data integrity, while MyISAM is loose

InnoDB has transactions while MyISAM does not.

InnoDB has foreign keys and relationship contraints while MyISAM does not.

This is true in light of the fact that InnoDB supports transaction isolation via MVCC. That, in part, makes InnoDB fully ACID compliant.

MyISAM are faster to read but slower to write (I'm not sure about this one)

This depends entirely on the row format of the MyISAM table (Fixed or Dynamic). Slow is relative. After all, each DML statement against a MyISAM performs a full table lock each and every time.

Given a large enough cache, InnoDB can be faster for both reads and writes because COMMITs can be delayed. This leaves data already cached in the InnoDB Buffer Pool available for reads right out of memory.

InnoDB caches both Data and Indexes while MyISAM only caches Indexes. Thus, disk reads for data from MyISAM is always required.

Mysql – Dynamic change to innodb_flush_log_at_trx_commit

While I agree with Rolando's recommendation to change innodb_flush_method, I wasn't 100% clear what you meant by:

it did not make the actual MySQL setting changed

I want to point out the caveat that making a change to the GLOBAL variable affects any new connections, but does not modify the current session (emphasis mine):

The global variable change does not affect the session variable for any client that is currently connected (not even that of the client that issues the SET GLOBAL statement).

So to check that:

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)


mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit=2;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 1     |
+--------------------------------+-------+
1 row in set (0.00 sec)

mysql> connect;
Connection id:    6
Current database: *** NONE ***

mysql> SHOW SESSION VARIABLES LIKE 'innodb_flush_log%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+
1 row in set (0.00 sec)

Best Answer

Related Solutions

Mysql – Which thesql storage engine to choose

Mysql – Dynamic change to innodb_flush_log_at_trx_commit

Related Question