Storage nodes opening multiple mySQL connections to Master DB

[dsloan.ethra]

I recently completed a deployment of 48 storage nodes to our local and remote locations on Friday. We have been testing FOG 1.4.4 for over 3 months, adding the storage nodes over this time. Starting sometime this weekend, the nodes have just hammered our primary FOG mysql server, opening multiple connections to it. I set the max connections to the mysql server 2500 but that didn’t seem to help, the connections from the nodes just started growing further… Any suggestions/assistance would be gratefully accepted.

[george1421]

@dsloan-ethra So just to recap what was done here to adjust Debian 9 (where the referenced article also implies it is an issue for any Debian variant like Ubuntu) for others that might find the thread:

1. Edit /etc/mysql/mysql.conf.d/mysqld.cnf
   vi /etc/mysql/mysql.conf.d/mysqld.cnf
2. Insert the following line in the [mysqld] section
   max_connections = 8000
3. Copy the systemd service descriptor file so you can customize it
   cp /lib/systemd/system/mysql.service /etc/systemd/system/
4. Append the following lines to this file: /etc/systemd/system/mysql.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

5. Reload systemd processor
   systemctl daemon-reload
6. Reload the mysql service
   systemctl restart mysqld
7. Login to mysql as root and run the following command.
   mysql> show variables like 'max_connections';
8. The response should be something like

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 8000 |
+-----------------+-------+
1 row in set (0.01 sec)

9. Done.

These are not my words, only restated from the accepted solution here that Sebastian found and referenced: https://www.rfc3092.net/2017/06/mysql-max_connections-limited-to-214-on-ubuntu-foo/

[dsloan.ethra]

Ok, so I misread the “Time” of the connection as the Number of connections. That is not the issue. The Nodes are only hitting the server with 2-3 connections each. I had this issue early on and upped the connections to 500:

sudo mysql -u root -p
set global max_connections = 500;

as well as set in /etc/mysql/my.cnf
[mysqld]
max_connections = 500

However, I don’t believe the my.cnf option is taking effect. I Did restart my main FOG DB server Friday, which would explain why this is happening all over again. What am I doing wrong to keep mysql from lowering the connections?

[Sebastian Roth]

@dsloan-ethra Try the following mysql queries to see if max_connection is properly set after a reboot:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+

… and connection limit (Connection_error_max_connections) is being hit at all:

mysql> show status like '%onn%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| Aborted_connects                 | 0     | 
| Connections                      | 8     | 
| Max_used_connections             | 4     | 
| Connection_error_max_connections | 1421  |
| Ssl_client_connects              | 0     | 
| Ssl_connect_renegotiates         | 0     | 
| Ssl_finished_connects            | 0     | 
| Threads_connected                | 4     | 
+----------------------------------+-------+

[dsloan.ethra]

Since posting this, I have restarted mysql several times.

I finally got the “Too many connections” error to stop (shortly before I posted my update) after restarting and quickly getting into the mysql console and manually doing a set global max_connections = 1000;. Currently, there are 0 errors. But as I was saying the max_connections = 500 in the /etc/mysql/my.cnf does not seem to be taking effect upon restart. When I restart the service now, it reports that the max_connections variable is set to 214.

+-----------------------------------------------+---------------------+
| Variable_name                                 | Value               |
+-----------------------------------------------+---------------------+
| Aborted_connects                              | 19                  |
| Connection_errors_accept                      | 0                   |
| Connection_errors_internal                    | 0                   |
| Connection_errors_max_connections             | 0                   |
| Connection_errors_peer_address                | 0                   |
| Connection_errors_select                      | 0                   |
| Connection_errors_tcpwrap                     | 0                   |
| Connections                                   | 151324              |
| Locked_connects                               | 0                   |
| Max_used_connections                          | 385                 |
| Max_used_connections_time                     | 2018-01-01 11:30:20 |
| Performance_schema_session_connect_attrs_lost | 0                   |
| Ssl_client_connects                           | 0                   |
| Ssl_connect_renegotiates                      | 0                   |
| Ssl_finished_connects                         | 0                   |
| Threads_connected                             | 313                 |
+-----------------------------------------------+---------------------+```

Thank you for responding.

[george1421]

@dsloan-ethra said in Storage nodes opening multiple mySQL connections to Master DB:

max_connections

I guess lets start with what does your /etc/my.cnf file look like?

[dsloan.ethra]

@george1421 said in Storage nodes opening multiple mySQL connections to Master DB:

@dsloan-ethra said in Storage nodes opening multiple mySQL connections to Master DB:

max_connections

I guess lets start with what does your /etc/my.cnf file look like?

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]
max_connections = 500

[george1421]

@dsloan-ethra

Sorry for the delay. You might need to check the other directories to see if one of them are overriding your max connections.

You could search them quickly with
grep -R -e "max_connections" /etc/mysql

The newer versions of mysql don’t populate the my.cnf file, but use the .d directories to hold the configs. My bet is that mysql is finding what it needs in the .d directories and then just ignores your 500 value.

[Sebastian Roth]

@dsloan.ethra Take a look at this: https://www.rfc3092.net/2017/06/mysql-max_connections-limited-to-214-on-ubuntu-foo/ and/or https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied and/or https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/ (depending on which distro/version you use)

[dsloan.ethra]

@sebastian-roth said in Storage nodes opening multiple mySQL connections to Master DB:

@dsloan.ethra Take a look at this: https://www.rfc3092.net/2017/06/mysql-max_connections-limited-to-214-on-ubuntu-foo/ and/or https://support.plesk.com/hc/en-us/articles/213393029-MySQL-values-open-files-limit-and-max-connections-are-not-applied and/or https://codepoets.co.uk/2015/mysql-max_connections-stuck-on-214/ (depending on which distro/version you use)

www.rfc3092.net held the answer! Worked great and now I can set the number of connections and have it stick! Thank you both for helping!

[george1421]

@dsloan-ethra So just to recap what was done here to adjust Debian 9 (where the referenced article also implies it is an issue for any Debian variant like Ubuntu) for others that might find the thread:

1. Edit /etc/mysql/mysql.conf.d/mysqld.cnf
   vi /etc/mysql/mysql.conf.d/mysqld.cnf
2. Insert the following line in the [mysqld] section
   max_connections = 8000
3. Copy the systemd service descriptor file so you can customize it
   cp /lib/systemd/system/mysql.service /etc/systemd/system/
4. Append the following lines to this file: /etc/systemd/system/mysql.service

LimitNOFILE=infinity
LimitMEMLOCK=infinity

5. Reload systemd processor
   systemctl daemon-reload
6. Reload the mysql service
   systemctl restart mysqld
7. Login to mysql as root and run the following command.
   mysql> show variables like 'max_connections';
8. The response should be something like

+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 8000 |
+-----------------+-------+
1 row in set (0.01 sec)

9. Done.

These are not my words, only restated from the accepted solution here that Sebastian found and referenced: https://www.rfc3092.net/2017/06/mysql-max_connections-limited-to-214-on-ubuntu-foo/