3

I'm using MySQL 5.7.29 on Ubuntu 16.04. I'd like to increase the number of connections allowed by the MySQL server. This involves setting configuration for both MySQL and Ubuntu.

MySQL

The primary config parameter is MySQL's max_connections. I've edited /etc/mysql/mysql.conf.d/mysqld.cnf to have

max_connections = 800

(previously it was commented out and set to 100). I also need to make sure that table-open-cache is high enough; for our application that's about 2 x max_connections plus a little extra. $ mysqld --verbose --help reports

table-open-cache                2000

which should be high enough.

Ubuntu

For every open connection, MySQL opens a placeholder file for it. Thus, in addition to increasing MySQL's max_connections, I need to make sure that the OS allows the MySQL process to open a sufficient number of files.

Existing answers for doing this divide into systemd and non-systemd approaches. This system uses systemd.

non-systemd

The non-systemd approach involves editing /etc/security/limits.conf. I followed instructions for doing this before I learned that systemd ignores this file for this case. The original version of this question concerned that effort; I've edited it to remove what I now know is irrelevant. I'm happy to restore information about what I did if anyone thinks it's relevant. Long story short is that I successfully increased the output of $ ulimit -n from 1024 to 16384.

systemd

Raising the number of files MySQL can open on systemd requires editing the MySQL service file.

The systemd MySQL service file on this system is /lib/systemd/system/mysql.service. Rather than editing the file directly, which I understand is bad practice, I created the directory /etc/systemd/system/mysql.service.d/ and the file override.conf within it. To override.conf, I added

[Service]
LimitNOFILE=65536
LimitMEMLOCK=infinity

I also added fs.file-max = 65536 to /etc/sysctl.conf, though I'm still not clear on what relation that file has to systemd or whether it has any effect on a systemd system.

I reloaded systemd config ($ sudo systemctl daemon-reload) and MySQL ($ sudo service mysql restart).

Checking to see if it worked, I note that $ service mysql status indicates that the new override.conf file is being recognized:

$ sudo service mysql status
● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled
  Drop-In: /etc/systemd/system/mysql.service.d
           └─override.conf
...

However, MySQL continues to report a value of 151 for max_connections:

$ sudo mysqld --verbose --help | grep max-connections
  --max-connections=# The number of simultaneous clients allowed
max-connections                                              151

(I get the same result checking mysql> SHOW VARIABLES LIKE 'max_connections' from within the interactive MySQL shell).

What else do I need to do to permanently increase max_connections?

0

Welcome.

As per MySQL Documentation, your MySQL server does not have enough resources.

Also go through this

The table_open_cache and max_connections system variables affect the maximum number of files the server keeps open. If you increase one or both of these values, you may run up against a limit imposed by your operating system on the per-process number of open file descriptors.

3
  • 1
    Yes. You've essentially restated the premise of my question. Mar 7 '20 at 16:58
  • Also, please note that you shall look at OS settings for per-process number of open file descriptions. Once that is increased, you shall see higher value here too. Mar 8 '20 at 13:04
  • The accepted answer to this question: unix.stackexchange.com/questions/55319/… indicates that the limits.conf settings I've already (successfully) increased are per-process. Do you have a more detailed idea in mind that you might share? Mar 8 '20 at 15:46
0

First, let's verify that it is looking at that file. Run

mysqld --help --verbose | more

and look for something like

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

Then I will question the need for 800. Do you have hundreds of clients that want to stay connected? Hopefully, they are willing to reconnect; after all, network glitches do happen. Is the wait_timeout set high? Perhaps too high.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.