Make MySQL faster – innodb_flush_log_at_trx_commit=2
Multimedia
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