How to allocate more RAM to MariaDB/MySQL on a shared server and increase the connection count

Background

If you have a server with sufficient RAM but you are running out of MySQL connections, you might want to look at two easy aspects for increasing the performance and avoiding nasty too many connections errors.

How to Allocate More RAM to MariaDB/MySQL

The first act in performance is to allocate more RAM to your server. For example, if your server has 12 GB available RAM, you can easily allocate 4 GB to MySQL. The command to do so for Mariadb on a CentOS 7.x server is:

vi /etc/my.cnf.d/server.cnf

or

vi /etc/mysql/my.cnf

innodb_buffer_pool_size = 4294967296

4294967296÷1024÷1024 = 4096M ie. 4GB

To test, on the MySQL console do this:

 SELECT variable_value FROM information_schema.global_variables WHERE variable_name = 'innodb_buffer_pool_size';

If that doesn’t work do this:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

How to Increase the Number of Connection for MariaDB/MySQL

In order to increase the number of connections, add the following setting:

max_connections = 501

As always, remember service mysql restart to implement the new settings.

If you have access to the MySQL console, you can also use these commands before and after to see how many connections are available:

> SHOW VARIABLES LIKE "max_connections";

References

See Also

MySQL stops working: InnoDB: Fatal error: cannot allocate memory for the buffer pool

Share this article

1 thought on “How to allocate more RAM to MariaDB/MySQL on a shared server and increase the connection count”

  1. It is much easier just to add this command to the docker compose file (assuming that MariaDB runs in a container):

    command: –innodb-buffer-pool-size=2G

Leave a Reply

Your email address will not be published. Required fields are marked *

Scroll to Top