52

I turned innodb_flush_log_at_trx_commit = 2 and get a very fast write speed. But is it safe be used in production web site?

55

You can lose up to one second's worth of transactions. The default value is 1, which helps keep InnoDB ACID Compliant.

According to the MySQL Documentation on innodb_flush_log_at_trx_commit

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. When the 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 the value is 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 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.

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

Caution

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.

Based on this, values other than 1 put InnoDB at risk of losing 1 second's worth of transactions, or a transaction commit's worth of data.

The documentation also says use sync_binlog=1.

According to the MySQL Documentation on sync_binlog

A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

Your safest choice is

[mysqld]
innodb_flush_log_at_trx_commit=1
sync_binlog=1

If you do not mind possible data loss (up to 1 second's worth) then you can use either 0 or 2 at your own risk if the rewards (faster write speed) are worth it.

  • 3
    Rolando :+1 for last few lines sync_binlog=1... – Abdul Manaf Feb 10 '12 at 5:58
  • @AbdulManaf, Always go for data integrity than speed. If you want to sacrifice speed for data integrity, you'll find yourself losing much more time dealing with data problems. – Pacerier Apr 9 '15 at 11:20
  • 1
    @RolandoMySQLDBA, By "losing a second's worth of transaction", do you mean that a successful commit could actually be lost? – Pacerier Apr 9 '15 at 11:21
  • 1
    Pacerier, yes. The data is only guaranteed to be written to disk after it is "flushed to disk". Until then it might be in RAM memory only. – Emil Vikström Apr 18 '16 at 13:20
  • 2
    @RolandoMySQLDBA you are the man, seriously. If you do anything other than fulltime mysql consultancy gigs, you possibly should change your career path. – sjas Jun 10 '17 at 9:45
25

The innodb_flush_log_at_trx_commit is used with the purpose as ..

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.

When the 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 the value is 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 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB's crash recovery works regardless of the value.

In My opinion using innodb_flush_log_at_trx_commit to 2 should not be an issue.But to use 1 is the safest.

  • 3
    I just noticed that you answered only 18 seconds after me with essentially the same answer. +1 !!! – RolandoMySQLDBA Apr 24 '12 at 18:16
23

My opinion differs from other. innodb_flush_log_at_trx_commit = 0 if: it is my development computer or home mini database where is no sensitive data.

innodb_flush_log_at_trx_commit = 2 if: it is blog/stats/e-commerce (with ~100x shop in day), etc.

innodb_flush_log_at_trx_commit = 1 if: you have a lot of customers or you need to work with money transaction like bank. so this time you should split your dataflow between several servers to have speed & safety.

I prefer 2, because it has ~75x faster write speed and it fails ONLY if hardware fails.

Anyway you should know what you need more much more writing speed or up to 1 second information?

  • 1
    +1 for 75x faster write speed and it fails ONLY if hardware fails. – Naman Gala Mar 26 '15 at 5:40
  • 3
    75x faster? Citation needed. – Pacerier Apr 9 '15 at 11:22
  • 5
    My own benchmark : 5000 UPDATE with innodb_flush_log_at_trx_commit = 1 : 179 seconds. With innodb_flush_log_at_trx_commit = 2 : 1.12seconds. It's a 160x faster write speed in my case. – Kevin Nov 20 '15 at 12:16
  • Good answer. One thing to think of is that - if your machine crashes after successfull transaction, chances that transaction was not written to disk with innodb_flush_log_at_trx_commit = 2, and yet success was indicated to your application (i.e mysqld manages to send a network packet indicating transaction successfully finished to the driver in your app), that chance is very very low – Vladislav Vaintroub Feb 17 '17 at 0:01
  • can you imrove your answer by specifying what docs mean by crash – shareef Mar 20 '18 at 6:57
2

I am trying to answer, what is the purpose of innodb_flush_log_at_trx_commit?

InnoDB performs most of its operations at the memory (InnoDB Buffer Pool). Al the modified data is written to InnoDB transaction log file and then flushed (written) to durable storage (hard disk).

For data safety (Durability from ACID), InnoDB has to store modified data of each transaction into a permanent storage. At the same time, committing to disk for each transaction is costly process.

Disk I/O is a blocking process and it is very slow, it it is a slow disk, further it will reduce the number of InnoDB transaction per seconds (Disk throughput).

InnoDB provides, innodb_flush_log_at_trx_commit variable to control the frequency of this flush operation. Based on the value, InnoDB flush operation behave differently.

(Already explained in other answers)

0 - Write to log file and flush to disk at every second (data is in buffer pool not written to log file - for performance gain). 1 - Flush to disk when a transaction commits - default(For data safety - ACID compliance) 2 - write to log file for every transactions and flush to disk at every second. (For performance gain)

Depends on the application requirement (Performance Vs data safety) , you can set this variable. The difference between 0 and 2 - both will increase the performance, value 2 stores the data in transaction file and can be recoverable, in case of crash or failure, but not in 0.

In many cases, flush to disk is means, the data is written from InnoDB buffer pool (memory) to Operating systems cache not actually written to storage disk (permanent storage). In case failure, at the worst case, you may lose data up to one second)

The performance gain is depends on environment and you can benchmark and identify. In a replication environment, for data safety and consistency, set innodb_flush_log_trx_commit = 1 and sync_binlog=1.

If performance is the main goal of application, InnoDB provides a variable to control the frequency of log flushing - innodb_flush_log_at_timeout - which allows you set log flushing frequency range from 1 to 2700 seconds, by default it is 1.

Be aware, when you increase the flushing interval up to N seconds, performance gain comes with compromise in data safety up to N seconds. For example - if you set flushing occurs at every 5 seconds - throughput gain is very high, but in case of power failure or system crash, you will lose data worth 5 seconds .

This article discuss about InnoDB flushing and transaction commit operations.

You can change after you do mode 2 on aws rds:

can change after you do mode 2 on aws

previewchanges

Unmodifiable in some cases like if you have replication multi a-z:

FYI unmodifiable in some cases

1

If your hardware fails, you can loose all your data, so I use param = 2 without any worries. Anyway you can split you sensitive (order, virtual money,...) and regular (statistics, cart,...) data between 2 db servers and keep them safe and fast. For transactions between databases you can use http://dev.mysql.com/doc/refman/5.7/en/xa.html

  • "Lose all your data", or do you mean the transactions that were getting queried in the past few seconds or so? – NiCk Newman Mar 10 '16 at 7:17
  • 1
    A hardware failure may mean losing an entire hard drive, hence "lose all your data". So unless you have a replication setup your data is at the mercy of how often you do backups anyway, so the point this answer was making is that a second or two of lost data is nothing to worry about in comparison – thomasrutter Apr 27 '16 at 17:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.