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 = 42949672964294967296÷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 = 501As 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
- https://tableplus.com/blog/2018/11/error-1040-too-many-connection-increase-max-connections-mysql.html
- Our good friend Trith who assisted us in isolating an issue
- https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
See Also
MySQL stops working: InnoDB: Fatal error: cannot allocate memory for the buffer pool
1 thought on “How to allocate more RAM to MariaDB/MySQL on a shared server and increase the connection count”
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