How to permanently increase innodb_buffer_pool_size on Ubuntu

Question

I have read lot of posts & manuals to try to find out how to increase memory in MySQL 5.6 on Ubuntu, which say there are 3 methods:

1. By editing innodb_buffer_pool_size in the my.cnf
2. By command line option on starting MySQL
3. Dynamically with SQL commands.

I assume 2 and 3 will be lost when MySQL is restarted, which leaves 1.

The problem is, there are no such settings in my /etc/mysql/my.conf.

The string "inno" does not exist in the conf file, nor does "buffer_size" or "buffer-size" (but "key_buffer" is in there).

I installed it via:

# apt-get install mysql-server-5.6 

Any ideas where I can change the value?

Did I somehow install MySQL without the InnoDB engine?

Am I looking in the wrong file?

There are some other .cnf files under /etc/mysql, but they have nothing like innodb in them either.

mysql> show variables like 'inno%'
:
innodb_buffer_pool_size                  | 134217728              |

So it looks like InnoDB is installed, I just can't find where its config file is.

Any help appreciated.

Solution 1

Here's what worked for me (ubuntu 19, mysql 8):

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

add in this at the bottom of the file

innodb_buffer_pool_size        = 8G

save and exit. restart MySQL.

sudo systemctl restart mysql

Solution 2

The problem is, there are no such settings in my /etc/mysql/my.conf.

So add the lines needed to the [mysqld] section of the config file. The default my.cnf file only contains a tiny fraction of the configuration values that are available.

Solution 3

Write these commands in the terminal :

root:$cd /etc/mysql/
root:/etc/mysql$ sudo gedit my.cnf

Add this at the end of the file (2 GiB)

[mysqld]
innodb_buffer_pool_size = 2147483648

restart apache / mysql

root:~$ sudo service mysql restart
root:~$ sudo service apache2 restart

Solution 4

On Ubuntu 20.04 LTS this work for me:

Go to /etc/mysql/conf.d/mysql.cnf

add this under [mysql]:

[mysqld]

innodb_buffer_pool_size=1G

then restart mysql:

sudo systemctl restart mysql

Go to mysql to check result:

sudo mysql

SELECT @@innodb_buffer_pool_size/1024/1024/1024;

View more solutions

Comments

[nandoP]

there is strangeness in the syntax of my.cnf.... mostly due to the backwards compatibility of "-" vs "_" and other depricated varnames.... mysql has been around since forever

[eos]

THanks EEAA. I actually tried this, although it was guesswork as I cant find a complete example. I added the line "innodb_buffer_pool_size=512M" directly below the section [mysql], and restarted mysql (service mysql restart). Unfortunately, it made no difference, "show variables" still showed it to be 134217728 = 128MB. Any ideas?

[eos]

Ok, found it, you have to put the settings under [mysqld]. Not sure why only my install is missing these settings.

[EEAA]

Right, which I mentioned in my answer. :)

[BadHorsie]

You can simply write 2G instead of 2147483648