How do i change innodb_buffer_pool_size?

[MartinM90]

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.

 

[samK]

1. Find your mysql cnf file - Most probably located in /etc/mysql/
2. Find innodb_buffer_pool_size and change it to the required size.
3. Also, set innodb_buffer_pool_instances such that each pool is of equal size. ...
4. Save the file.
5. Restart MySQL service (without restart settings won't take effect)

 

[Rex M]

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

  cp my.cnf my.cnf.bk

It will copy my.cnf file to another file called my.cnf.bk

• Now, verify the created backup using the below command,

ls -l my*

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,

sudo vi my.cnf

• 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,

sudo service mysqld stop
sudo service mysqld start

Or,

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