Geek Answers Handbook

Max_connections in MySQL 5.7

I ran into a problem, the max_connction value in MySQL is 214 after I set it to 1000 by editing my.cnf as shown below:

hadoop@node1 :~$ mysql -V mysql Ver 14.14 Distrib 5.7.15, for Linux (x86_64) using EditLine wrapper

MySQL Version: 5.7

OS Version: ubuntu 16.04LTS

 mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)

As we can see, the variable max_connections value is 151. Then I edit the MySQL configuration file.

 yang2@node1 :~$ sudo vi /etc/mysql/my.cnf [mysqld] character-set-server=utf8 collation-server=utf8_general_ci max_connections=1000

Restart the MySQL service after saving the configuration.

 yang2@node1 :~$ service mysql restart ==== AUTHENTICATING FOR org.freedesktop.systemd1.manage-units === Authentication is required to restart 'mysql.service'. Multiple identities can be used for authentication: 1. yangqiang,,, (yang2) 2. ,,, (hadoop) Choose identity to authenticate as (1-2): 1 Password: ==== AUTHENTICATION COMPLETE === yang2@node1 :~$

Now we assume that max_connection is 1000, really?

 mysql> show variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 214 | +-----------------+-------+ 1 row in set (0.00 sec)

This is 214. I really do not understand this result, who can help me? THX!

+6
source share
3 answers

The MySQL documentation on max_connections states:

Increasing this value increases the number of file descriptors that mysqld requires. If the required number of descriptors is not available, the server decreases the value of max_connections.

This means that perhaps your MySQL server does not have sufficient resources to support the required number of descriptors.

The MySQL documentation on How MySQL Opens and Closes Tables makes it clear that:

The system variables table_open_cache and max_connections affect the maximum number of files opened by the server. If you increase one or both of these values, you may encounter a limit imposed by your operating system for each process on open file descriptors. Many operating systems can increase the limit of open files, although this method varies widely from system to system. Consult your operating system documentation to determine if it is possible to increase the limit and how to do it.

+6
source

You can set the value manually, for example

 set global max_connections=500;

however, after rebooting MySQL, the value is reset to 214.

The decision depends on the OS (version) and version of MySQL. With Ubuntu 16.04 and MySQL> = 5.7.7, the following works:

systemctl edit mysql

Enter

 [Service] LimitNOFILE=8000

save, this will create a new file

/etc/systemd/system/mysql.service.d/override.conf

and restart the server:

 systemctl daemon-reload systemctl restart mysql

In other environments: Unable to increase max_open_files for mysql max connections in Ubuntu 15

+3
source

Add session required pam_limits.so to /etc/pam.d/common-session (usually not by default).

In /etc/security/limits.conf you can add some restrictions:

 * hard nofile 8192 * soft nofile 4096

Also check with ulimit -a limit of open files. This can be increased with ulimit -n 4096

Make sure you reboot at the end.

+1
source

More articles:


All Articles