Understanding MySQL innodb_flush_log_at_trx_commit variable

The main objective of this post is to understand why innodb_fush_log_at_trx_commit variable is used? What are the applicable values for innodb_fush_log_at_trx_commit? How innodb_fush_log_at_trx_commit value will impact the MySQL performance and data safety?  How to change innodb_fush_log_at_trx_commit value? and How to change the frequency of InnoDB log flush?

Why innodb_fush_log_at_trx_commit?


In order to understand why we need innodb_fush_log_at_trx_commit variable, we should know about how InnoDB works. It is a huge and complex topic and it is not the scope of this article. I am trying to cover this topic in simple words and it is given below:

  1. InnoDB performs most of its operations at the memory (InnoDB Buffer Pool)
  2. It will write all changes from memory to the transaction log (InnoDB Log File)
  3. From transaction log - it will flush (write) data to storage disk (durable storage)

In order to achieve durability of data, we need to store each and every transaction data into hard disk storage. But consider, in a busy system, for each transaction commit, if InnoDB trying to flush (write) data to slow running disk, what will happen?, So how do we manage this situation, where we need to store each transaction data and at the same time maintaining good performance of the system.

The InnoDB provides the solution for this situation, based on your system, you can tell InnoDB, when you want to flush (write) data to disk. For example, you can tell InnoDB to work as mentioned below:

  1. Write to log file and flush data to disk in specified interval, not for every transaction commit.
  2. Write to log and flush to disk for each transaction commit.
  3. Write to log for every transaction commit but flush to disk at an interval not for each transaction commit.






Based on the application standard you can adjust these settings to maintain balance between performance and data safety. InnoDB provides a configurable variable to achieve this balance, this variable is called innodb_fush_log_at_trx_commit. This variable controls how often the log buffer is flushed.

What are the applicable values for innodb_fush_log_at_trx_commit and its impact?


InnoDB supports following 3 values for innodb_fush_log_at_trx_commit variable. Let see how these variables will change flush to disk behaviour:

innodb_flush_log_at_trx_commit=0

When you set innodb_flush_log_trx_at_commit=0, InnoDB will write the modified data (in InnoDB Buffer Pool) to log file (ib_logfile) and flush the log file (write to disk) every second, but it will not do anything at transaction commit.

Here, in case if there is a power failure or system crash, all the unflushed data will not be recoverable, since, it is not either written to log file or stored disk.

innodb_flush_log_at_trx_commit=1

When you set innodb_flush_log_trx_commit=1, InnoDB will write the log buffer to transaction log and flush to durable storage for every transaction.

Here, for all transaction commit, InnoDB will write to log and then write to disk, if in case the slower disk storage, it will badly impact the performance, i.e. the number of InnoDB transaction per second will be reduced.

innodb_flush_log_at_trx_commit=2

When you set innodb_flush_log_trx_commit = 2, InnoDB will write the log buffer to log file at every commit, but don't write data to disk. InnoDB flushes data once in every second.
Option 2, even if there is a power failure or system crash, data will be available in log file and can be recoverable.






So which option to be used on your application?


We have seen, the behaviour of each values, now it is based on the application requirement, we need to choose this variable value.

If performance is the main goal, you can set the value as 2. Since, InnoDB writing to disk once in every second, not for every transaction commit and it will improve the performance dramatically. In case if there is power failure or crash, data can be recoverable from transaction log.

If data safety is the main goal, you can set the value as 1, so that for every transaction commit, InnoDB will flush to disk. But performance may reduce in this case.

If you set value as 0, InnoDB will write from buffer to log once in every second and it will not perform any flush to disk operation for every transaction commit. The problem with this option is if in case of any power failure or system crash, there may be a chance of losing data up to one second.

Key points:


During buffer to log operation, i.e. writing from InnoDB buffer pool to InnoDB transaction log file - data is simply moved from InnoDB buffer to Operating system's cache, actually not written to the durable storage. So if you set innodb_fush_log_at_trx_commit either 0 or 2, there is a possibility of losing data up to one second.

If innodb_fush_log_at_trx_commit is set to 1 - InnoDB compels Operating System to flush data to durable storage. Writing to disk is a slow process and it is I/O blocking operation to ensure data is written to disk. So using this option, there is a chance; number of transaction per second will be reduced.

Note that, by default MySQL will auto commit the transactions.

Setting innodb_fush_log_at_trx_commit as 2 is useful when restoring huge databases, it will reduce the restoration time dramatically, and there are different opinions on this point, it is better to test in our own hand. In my experience, it really helped with the reduced restoration time.

Data Definition Language (DDL) changes flush the InnoDB log, independent of the innodb_fush_log_at_trx_commit setting.

Crash recovery operation works regardless of the innodb_fush_log_at_trx_commit setting, I am writing another article on InnoDB crash recovery.







How to configure innodb_fush_log_at_trx_commit variable?


The scope of the innodb_fush_log_at_trx_commit is GLOBAL and it can be set at dynamically without restarting server.

Dynamically on command line you can set innodb_fush_log_at_trx_commit as follows:

SET GLOBAL innodb_fush_log_at_trx_commit = 2;

On configuration file, you can set it as follows:

[mysqld]
innodb_fush_log_at_trx_commit = 2

Note: It requires server restart. Before making changes to configuration file, analyse the impact by setting it on dynamically.

If you try to set innodb_fush_log_at_trx_commit as session level variables, you will encounter the following error:

mysql> set session innodb_fush_log_at_trx_commit = 2;
ERROR 1229 (HY000): Variable 'innodb_fush_log_at_trx_commit' is a GLOBAL variable and should be set with SET GLOBAL
mysql>

How to change the frequency of InnoDB log file flushing?


The variable innodb_flush_log_at_timeout controls the frequency in which InnoDB flush log files. The frequency ranges from 1 to 2700 seconds, with the default value 1.






Note that, the higher this number, there is a higher the chance of losing data, in case of power failure or system crash.

For example, if you set this value as 5 seconds, in case of power failure, you may lose data upto 5 seconds.

In replication topology, to maintain durability and consistency of data, you can leave the default value, i.e. innodb_fush_log_at_trx_commit = 1.

I hope this article help you to understand, how InnoDB flush data and how it impact MySQL performance and data safety, also, how you can configure this variable to achieve maximum benefit. 

In future article let us see about how InnoDB crash recovery works and its configuration settings. If I missed something or if you wish to share your thoughts on this article, please mention in comment section, I will edit the post after review.




24 comments:

  1. Good Post Ratish

    ReplyDelete
  2. This particular papers astounding, and My spouse and that i enjoy every of the take impact which you have placed into this. Im firm that you'll be making a in truth useful area. I has been moreover deferential. exact play-success! Facial Treatment

    ReplyDelete
  3. Thus, while multitasking, children are neglecting to give their studies full attention.pad

    ReplyDelete
  4. A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Military smart Watches for men

    ReplyDelete
  5. I think this is an informative post and it is very useful and knowledgeable. therefore, I would like to thank you for the efforts you have made in writing this article. Silver Jewelry for Women Wedding

    ReplyDelete
  6. What a fantabulous post this has been. Never seen this kind of useful post. I am grateful to you and expect more number of posts like these. Thank you very much. What is bitcoin revolution

    ReplyDelete
  7. I can see that you are an expert at your field! I am launching a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business. https://whyandhow.net/

    ReplyDelete
  8. I think this is an informative post and it is very beneficial and knowledgeable. Therefore, I would like to thank you for the endeavors that you have made in writing this article. All the content is absolutely well-researched. Thanks. hoverboard for sale

    ReplyDelete
  9. This is my first time I visit here. I found many interesting stuff in your site especially its discussion. From the tons of comments on your articles, I guess I am not the only one having all the enjoyment here keep up the good work. Beautiful happiness quotes

    ReplyDelete
  10. Understanding credit is half the battle of understanding why, what's in them is so important to your consumer reputation. By way of analogy, we see why your reputation is so important to the consumer community. In my initial question, should you repair your credit? I'm asked that more than you think. When I ask my clients, you would think the answer would be resoundingly obvious; however, as we'll see, there isn't anything obvious about it. First, we start by reviewing what credit is, the different types of credit and how credit became such a necessary component in our society. Additionally, we find out in this article, there are actually five credit bureaus and one can have a very positive effect on the consumer. Not only that, this credit report is reported to by none other than, the consumer. Weight watcher tips

    ReplyDelete
  11. Understanding credit is half the battle of understanding why, what's in them is so important to your consumer reputation. By way of analogy, we see why your reputation is so important to the consumer community. In my initial question, should you repair your credit? I'm asked that more than you think. When I ask my clients, you would think the answer would be resoundingly obvious; however, as we'll see, there isn't anything obvious about it. First, we start by reviewing what credit is, the different types of credit and how credit became such a necessary component in our society. Additionally, we find out in this article, there are actually five credit bureaus and one can have a very positive effect on the consumer. Not only that, this credit report is reported to by none other than, the consumer. Pan for Frying Bacon

    ReplyDelete
  12. Simply desire to say your article is as astonishing. The clarity in your post is simply excellent and i could assume you are an expert on this subject. Well with your permission let me to grab your RSS feed to keep updated with forthcoming post. Thanks a million and please continue the gratifying work. DEEP INSPIRATIONAL QUOTES

    ReplyDelete
  13. In the past few decades there has been a revolution in computing and communications, and all indications are that technological progress and use of information technology will continue at a rapid pace. Accompanying and supporting the dramatic increases in the power and use of new information technologies has been the declining cost of communications as a result of both technological improvements and increased competition. According to Moore's law the processing power of microchips is doubling every 18 months. These advances present many significant opportunities but also pose major challenges. Today, innovations in information technology are having wide-ranging effects across numerous domains of society, and policy makers are acting on issues involving economic productivity, intellectual property rights, privacy protection, and affordability of and access to information. Choices made now will have long lasting consequences, and attention must be paid to their social and economic impacts. Wallet with coin pocket pattern

    ReplyDelete
  14. Joe, I have read a few of your posts and I want to tell you that I like the consistent format of your blog. This goes well with one of the key points of this week's chapter, the importance of uniformity and consistency in the course design. I have worked really hard in my course to use templates for lesson activities, formative assessments, and summative assessments, all of which are housed in modules. Having that process and routine in place makes it an environment conducive to adding new technology tools and resources. I look forward to reading more of your blog posts! . Best scooter accessories

    ReplyDelete
  15. Really a great addition. I have read this marvelous post. Thanks for sharing information about it. I really like that. Thanks so lot for your convene. buy 50 real likes on instagram

    ReplyDelete
  16. This was really an interesting topic and I kinda agree with what you have mentioned here! nodytalk

    ReplyDelete
  17. Very nice article, I enjoyed reading your post, very nice share, I want to twit this to my followers. Thanks!. gm

    ReplyDelete
  18. Great article, I will be investigating this setting for our database setup. By the way part of the variable name is mispelt most of the way through as fush instead of flush.

    ReplyDelete
  19. The website is looking bit flashy and it catches the visitors eyes. Design is pretty simple and a good user friendly interface. Dell R740 Server

    ReplyDelete
  20. Thanks for a very interesting blog. What else may I get that kind of info written in such a perfect approach? I’ve a undertaking that I am simply now operating on, and I have been at the look out for such info. Auto call recorder

    ReplyDelete
  21. I really enjoy reading and also appreciate your work. is can i run it accurate

    ReplyDelete
  22. Thank you for a good posting however, I have a question. Your explanation about 'innodb_flush_log_at_trx_commit=2' is different with the public MySQL document.

    https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
    - With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.

    It seems that When you set innodb_flush_log_trx_commit = 2, InnoDB will *not* write the log buffer to log file at every commit.

    ReplyDelete
  23. if we set innodb_flush_log_trx_commit = 0; and innodb_log_file_size=48MB

    now concern is that, if innodb_log_file_size overfull the within a seconds with heavy load data. what will be happened.

    because data will be not flushed into disk before one seconds.

    ReplyDelete