Applicable to:
- Plesk for Linux
- Plesk for Windows
Question
How to change the value of innodb_log_file_size in MySQL/MariaDB?
Answer
-
Connect to a Plesk server via SSH.
-
Stop the MySQL service. The command will differ depending on an OS and installed MySQL version:
# service mariadb stop
# service mysql stop -
Open the MySQL configuration file
my.cnfin a text editor. In this example, we are using the vi editor:-
on CentOS/RHEL-based distributions:
# vi /etc/my.cnf
-
on Debian/Ubuntu-based distributions:
# vi /etc/mysql/my.cnf
-
-
Under the
[mysqld]section, change theinnodb_log_file_sizevalue according to your needs (the default value is 48M):Note: If the
innodb_log_file_sizedirective does not exist in the file, add it manually under the[mysqld]section.CONFIG_TEXT: [mysqld]
<...>
innodb_log_file_size=128M
<...> -
Move the redo log files
ib_logfile0andib_logfile1to another directory. In this example, the files are moved to/root:# mv /var/lib/mysql/ib_log* /root
-
Start the MySQL service:
# service mariadb start
# service mysql start
In Plesk for Windows, there are tow MySQL instances with their own configuration:
- Plesk SQL server for Plesk system databases (location
%plesk_dir%MySQL\) - MySQL server for customers' databases (location
%plesk_dir%Databases\MySQL\)
-
Connect to a Plesk server via RDP.
-
Open MySQL configuration file
my.iniin any text editor (Just paste the below path in Windows Explorer to open):-
for Plesk SQL server:
C:\> %plesk_dir%MySQL\my.ini
-
for MySQL server:
C:\> %plesk_dir%Databases\MySQL\my.ini
-
-
Under the
[mysqld]innodb_log_file_sizeCONFIG_TEXT: innodb_log_file_size=128M
-
Remove the redo log files:
-
for Plesk SQL server:
Browse to the folder
%plesk_dir%MySQL\Dataand delete the filesib_logfile0ib_logfile1 -
for MySQL server:
Browse to the folder
%plesk_dir%Databases\MySQL\dataand delete the filesib_logfile0ib_logfile1
-
-
Start the Plesk SQL Server/MySQL57 service via Plesk Services Monitor or Windows Services.
Comments
4 comments
should point 6 not be START instead of STOP?
Thanks, Robert!
That was fixed.
Kuzma Ivanov Do we need to configure anything else after increasing innodb log file size?
Like innodb buffer pool size or innodb_log_files_in_group?
Currently I am facing high cpu usage after increasing innodb_log_file_size. Kind of feels like there is some bottleneck between mysql and php-fpm. I see php-fpm causing 70% cpu spike and mysql causing 20-24%.
Hi Omkar More
Thanks for the question.
This guide is for increasing the innodb_log_file_size limit in MySQL, nothing else.
To troubleshoot high CPU usage caused by MySQL on your server, check out this KB article.
Please sign in to leave a comment.