Make MySQL faster β€” innodb_flush_log_at_trx_commit=2

MySQL Tuning mit InnoDB innodb_flush_log_at_trx_commit

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.

MySQL Tuning mit InnoDB innodb_flush_log_at_trx_commit