IncreasememoryusageforInnoDBMySQLdatabasetoimprovep_MySQL

If your MySQL database tables still run on the MyISAM engine (formerly the default), you may want to consider switching to the InnoDB engine instead, for better reliability and scalability. To update a table from MyISAM to InnoDB you can run this SQL:

ALTER TABLE table_name ENGINE = InnoDB;

Once you’ve switched all your tables to InnoDB, you can adjust some memory usage settings.

Update MySQL memory usage settings for InnoDB

Firstly, check the current settings for innerdb_buffer_pool_size . You can view these settings by running the following SQL (you can run this in phpMyAdmin):

SHOW VARIABLES;

Look for innerdb_buffer_pool_size . You’ll see it’s been assigned a particular number of bytes. This allocated cache stores table and index data, and keeps queries and query results in memory for faster lookup. So the more memory you can afford to dedicate to it the better – MySQL recommends to use 80% of the available memory. You can read about it here .

I had 2GB of server memory to play with, so I chose a moderate 1GB to allocate to the innerdb_buffer_pool_size . To add this setting, we’ll create and load our own custom MySQL cnf file, which will house some extra settings.

On Linux Ubuntu , add a new cnf file here:

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

The file name must end in .cnf , but call it whatever you like, so long as it’s not clashing with another file name.

Inside of this file, we add our new memory allocation:

[mysqld]innodb_buffer_pool_size = 1024Mkey_buffer_size = 8M

I’ve also added a new key_buffer_size value of 8MB. If you’ve deprecated your use of the MyISAM engine, it’s recommended to reduce this memory allocation. Previously I had 16M for key_buffer_size , so I decided to half it.

Finish off by restarting MySQL so that the changes can be applied:

sudo service mysql restart

If you check the MySQL variables once more:

SHOW VARIABLES;

You’ll hopefully now have some new and importantly increased memory values for innodb_buffer_pool_size and key_buffer_size

You’ve successfully optimised your MySQL database a bit more!