Make MySQL faster – innodb_flush_log_at_trx_commit=2 - Multimedia Pool 💥 Software – Media – Web Services

MySQL optimization – Increase the speed of disk accesses of the transaction log by caching the commits in the cache.
Thus, the data is written to the hard disk with a 1 second delay, which is hugely accumulated with thousands of accesses.
The InnoDB value innodb_flush_log_at_trx_commit = 1 is changed to 2.

For many years Woltlab has been informing its customers of the optimization in the MySQL database with a note in the maintenance area of the software:

Setting default value 1 of innodb_flush_log_at_trx_commit to 2.

In a competent support article on the Woltlab website the technical background is also explained to the laity.

The following guide shows "where" and "how" the InnoDB change is performed:

The example shown is MySQL Server 5.7.18 on Ubuntu 16.04.2 LTS server with Plesk 17.5.3. Therefore the main MySQL configuration file can also be located in a different directory, depending on the system environment and preinstalled components.

It is advisable, if you are not familiar with database and server systems to find mysql.cnf by using ~# find mysql.cnf and/or ~# locate mysql.cnf.

Our my.cnf is located in the <root> /etc/mysql directory. It was opened, edited and also saved with WinSCP.

Now open the MySQL configuration file my.cnf with an SSH program or editor.
Below [mysqld] the complete statement must be inserted with its value: innodb_flush_log_at_trx_commit = 2
Save my.cnf file.
Restart MySQL server with ~# /etc/init.d/mysql restart