Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?

Question

I am running a Nextcloud instance and I suffer from a slow sync when a lot of small files are synchronized. I found out that innodb_flush_log_at_trx_commit = 1 is responsible for this so I was forced to set innodb_flush_log_at_trx_commit = 2. Now I am thinking about moving my database to an SSD. And I wanted to confirm that I am indeed running into an access time issue.

Is the same amount of data written to the disk when innodb_flush_log_at_trx_commit is set to 1 as if it was set to 2?

Answer 1

You just asked

Is the same amount of data written to the disk when innodb_flush_log_at_trx_commit is set to 1 as if it was set to 2?

The answer is yes because of a bottelneck. Where is this bottleneck?

Please have a look at the InnoDB Architecture (courtesy of Percona CTO Vadim Tkachenko)

Please note the lower righthand corner of the Memory Side of InnoDB. It's the Log Buffer. Where is log information flushed? Look at the lowere lefthand corner of the Disk Side of InnoDB. It's the Redo Logs.

In order to improve InnoDB's write performance, please note the following suggestions:

SUGGESTION #1

By default, the Log Buffer size (sized by innodb_log_buffer_size) is 8M (8388608).

Please increase innodb_log_buffer_size to 64M.

SUGGESTION #2

By default, the redo logs (sized by innodb_log_file_size) is 48M (50331648).

Please increase innodb_log_file_size to 1G.

HOW TO IMPLEMENT

Step 01 : Add these options to /etc/my.cnf (or my.ini for Windows)

[mysqld]
innodb_log_file_size=1G
innodb_log_buffer_size=64M

Step 02 : Login to MySQL and run

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Step 03 : Shutdown MySQL

service mysql stop

or from Windows Command Line as Administrator

C:\> net stop mysql

Step 04 : Rename the redo log files

mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak

or from Windows Command Line as Administrator

rename ib_logfile0 ib_logfile0.bak
rename ib_logfile1 ib_logfile1.bak

Step 05: Start MySQL

service mysql start

or from Windows Command Line as Administrator

C:\> net start mysql

NOTE: This steps may take 2-3 minutes because it is creating two different logs files 1G each.

WHY CHANGE THESE SETTINGS?

When SSDs perform random writes to small redo logs, the wear and tear is loclalized to one section of the disk. Making the redo logs spreads out the writes from the log buffer. Making the log buffer bigger reduces frequency of writes in exchange for increase amount of data to write.

If an entire MySQL Instance is within an SSD, you must do this kind of tuning, go with a hybrid disk layout (See my old post MySQL on SSD - what are the disadvantages?).

MORE INFORMATION

Please read MySQL Documentation on Optimizing InnoDB Disk I/O for more InnoDB Tuning Options.

Answer 2

innodb_flush_logs_at_trx_commit
When 1:
for each Txn Commit { 
    - Write log buffer to log file
    - flush log files to disk
    }

When 0:
Approx every second {
    - Write log buffer to log file
    - flush log files to disk
    }

When 2:
    {
    - For each commit: { Write log buffer to log file }
    - Appx every sec: { flush log files to disk }
    }

Because the flush to disk operation only occurs approximately once per second, you can lose up to a second of transactions in an operating system crash or a power outage.

Well I just copied all of the above from documentation ;)

Anyways, now about your points.

• innodb_flush_logs_at_trx_commit changed: Choice is between ACID vs speed. You changed it to 2 for performance.okay but you know the risk.
• Moving to SSDs: They're proven to be faster than their ancestors... and considering that, you better benchmark your traffic with the preferable value of innodb_flush_logs_trx_commit of 1 (default) on SSDs.
• Is the same amount of data...?: Well yes amount of data will be ofcourse same; though you might wanted to ask something else. We know that how it improves the perfomance is by reducing number of disk ops.

So, "Does innodb_flush_log_at_trx_commit = 1 wear out SSDs more than innodb_flush_log_at_trx_commit = 2?" -> Regardless of disk type, value of 1 is bound to do more work (disk ops) than value of 2 and hence the performance difference.

Answer 3

A "commodity" SSD can be worn out. An "enterprise" SSD can be written nearly continually for years.

Any SSD has a limit to how many times a block can be written before it is damaged beyond repair. (Reads are no a problem.)

An Enterprise SSD has firmware to do "wear leveling". It actually moves blocks around, thereby wearing each block out at the same, slow, rate. This is slow enough that it will takes years to wear out.

The blocks are found via a lookup table.

So, with Enterprise-grade SSDs it does not matter if you are pounding on the log or the double-write buffer, or any other blocks on the SSD. For casual usage, there won't be a problem with cheap SSDs.