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.cnf
in 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_size
value according to your needs (the default value is 48M):Note: If the
innodb_log_file_size
directive 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_logfile0
andib_logfile1
to 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.ini
in 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
section, change the[mysqld]
value according to your needs (the default value is 10M):innodb_log_file_size
CONFIG_TEXT: innodb_log_file_size=128M
-
Remove the redo log files:
-
for Plesk SQL server:
Browse to the folder
%plesk_dir%MySQL\Data
and delete the files
andib_logfile0
.ib_logfile1
-
for MySQL server:
Browse to the folder
%plesk_dir%Databases\MySQL\data
and delete the files
andib_logfile0
.ib_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.