I have been looking for hours on Google and and tried several ways to change the innodb_buffer_pool_size on my VPS but i seriously need help please!!!
Tried putty but innodb_buffer_pool_size doesn't come up for me to change.
I have no experience at all.
Thanks in advance for any advice.
Hello
MartinM90,
The
InnoDB buffer pool size sets how much RAM is allocated to MySQL for caching data and indexes. It is to be set as large a value as possible without causing excessive paging by other processes. Therefore, you will need to adjust the
innodb_buffer_pool_size to take advantage of the increased memory.
By default, the InnoDB buffer pool size is
128 MB. Therefore, when you adjust memory allocated to MySQL, you will have to update only the value of
innodb_buffer_pool_size. Do not update other parameters, like
innodb_data_file_path. In order to change
innodb_buffer_pool_size follow the steps given below,
Locate the my.cnf File for MySQL:
The
my.cnf is a configuration file containing the various options for MySQL. You can find this file at the following path
/etc/my.cnf.
Take Backups of my.cnf:
- Firstly, navigate to the folder containing my.cnf
- Then, type
It will copy
my.cnf file to another file called
my.cnf.bk
- Now, verify the created backup using the below command,
Change the value of the innodb_buffer_pool_size:
- Firstly, load Terminal via ctrl+Alt+T.
- Then, type cd /etc/.
- Now, enter the below command,
- Using vi(m), locate the line innodb_buffer_pool_size, and press I to start making changes.
- After changing, press Esc, shift+colon, and then type wq at the end.
- wq - It will save the changes and return you to the terminal
- Lastly, restart MySQL services using the below commands,
Code:
sudo service mysqld stop
sudo service mysqld start
Or,
Code:
sudo systemctl restart mysqld
Note: The changes won't take effect if you are not restarting the MySQL service.
I hope the above information will help you to change
innodb_buffer_pool_size. If you still face any issues, update here will be glad to assist you for the same.
-------------------------
Regards,
Rex M
