18

I met a problem, the value of max_connction in MySQL is 214 after I set it 1000 via edit the my.cnf, just like 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 value of max_connections is 151. Then , I edit the configuration file of MySQL.

yang2@node1:~$ sudo vi /etc/mysql/my.cnf
[mysqld]

character-set-server=utf8
collation-server=utf8_general_ci
max_connections=1000

Restart MySQL service after save the configraion.

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 guess the max_connection is 1000, really?

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

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

StrongYoung
  • 762
  • 1
  • 7
  • 17

5 Answers5

15

You may set the value manually, e.g.

set global max_connections=500;

however, after a restart of MySQL the value is reset to 214.

The solution depends on the (version of) OS and the MySQL version. With Ubuntu 16.04 and MySQL >= 5.7.7 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

For other environments: Can not increase max_open_files for Mysql max-connections in Ubuntu 15

Community
  • 1
  • 1
WernerE
  • 161
  • 1
  • 7
11

As MySQL documentation on max_connections setting says:

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

This means that probably your MySQL server does not have enough resources to maintain the required number of descriptors.

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

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. Many operating systems permit you to increase the open-files limit, although the method varies widely from system to system. Consult your operating system documentation to determine whether it is possible to increase the limit and how to do so.

Shadow
  • 33,525
  • 10
  • 51
  • 64
  • Thank you so much, and can you tell me the detail resources that MySQL server required, My computer has 16GB memory and 8 cores. So I don't know what is the "enough resources" . – StrongYoung Oct 13 '16 at 10:13
  • 1
    The resource is the max number of file descriptors controlled by the OS, not your memory and processor capacity. – Shadow Oct 13 '16 at 10:18
  • I just want to update the number of file descriptions, but I can get the value of the file description now , it is 1024 in my OS, is it too small ?thanks again! – StrongYoung Oct 13 '16 at 10:39
  • The documentation on How MySQL Opens and Closes Tables (linked in the answer) explains how to estimate the number of file descriptors required for your operation. 1024 is a way too low, if you want support 1000 concurrent connections. – Shadow Oct 13 '16 at 10:48
5

Follow the following steps:

cp /lib/systemd/system/mysql.service /etc/systemd/system/
echo -e "\r\nLimitNOFILE=infinity" >> /etc/systemd/system/mysql.service
echo "LimitMEMLOCK=infinity" >> /etc/systemd/system/mysql.service
sudo systemctl daemon-reload
sudo service mysql restart

And change or add he following line into file /etc/mysql/mysql.conf.d/mysqld.cnf :

[mysqld]
max_connections=110

Just this! @Mahdi_Mohammadi

Mohsen Abasi
  • 2,050
  • 28
  • 30
3

1.Edit mysql service file 

#sudo cat /etc/systemd/system/multi-user.target.wants/mysql.service
# MySQL systemd service file

[Unit]
Description=MySQL Community Server
After=network.target

[Install]
WantedBy=multi-user.target

[Service]
Type=forking
User=mysql
Group=mysql
PIDFile=/run/mysqld/mysqld.pid
PermissionsStartOnly=true
ExecStartPre=/usr/share/mysql/mysql-systemd-start pre
ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
TimeoutSec=600
Restart=on-failure
RuntimeDirectory=mysqld
RuntimeDirectoryMode=755
##this bellow for tuneup 
LimitNOFILE=infinity
LimitMEMLOCK=infinity

2.edit /etc/mysql/mysql.conf.d/mysqld.cnf max_connections = 99999

sonjaya sonjaya
  • 31
  • 1
2

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

The in /etc/security/limits.conf you can add some limits:

*   hard    nofile  8192
*   soft    nofile  4096

Also check using ulimit -a the open files limit. This you can increase with ulimit -n 4096

Make sure you reboot at the end.

Dragos
  • 138
  • 4