• Register
    • Login
    • Search
    • Recent
    • Unsolved
    • Tags
    • Popular
    • Users
    • Groups
    • Search

    Solved Storage nodes opening multiple mySQL connections to Master DB

    FOG Problems
    3
    10
    1189
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • D
      dsloan.ethra last edited by

      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.

      1 Reply Last reply Reply Quote 0
      • george1421
        george1421 Moderator @dsloan.ethra last edited by 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
        
        1. Reload systemd processor
          systemctl daemon-reload
        2. Reload the mysql service
          systemctl restart mysqld
        3. Login to mysql as root and run the following command.
          mysql> show variables like 'max_connections';
        4. The response should be something like
        +-----------------+-------+
        | Variable_name | Value |
        +-----------------+-------+
        | max_connections | 8000 |
        +-----------------+-------+
        1 row in set (0.01 sec)
        
        1. 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/

        Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!

        1 Reply Last reply Reply Quote 1
        • george1421
          george1421 Moderator @dsloan.ethra last edited by 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
          
          1. Reload systemd processor
            systemctl daemon-reload
          2. Reload the mysql service
            systemctl restart mysqld
          3. Login to mysql as root and run the following command.
            mysql> show variables like 'max_connections';
          4. The response should be something like
          +-----------------+-------+
          | Variable_name | Value |
          +-----------------+-------+
          | max_connections | 8000 |
          +-----------------+-------+
          1 row in set (0.01 sec)
          
          1. 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/

          Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!

          1 Reply Last reply Reply Quote 1
          • D
            dsloan.ethra @Sebastian Roth last edited by

            @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 1 Reply Last reply Reply Quote 0
            • S
              Sebastian Roth Moderator last edited by 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)

              Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

              Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

              D 1 Reply Last reply Reply Quote 1
              • george1421
                george1421 Moderator @dsloan.ethra last edited by

                @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.

                Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!

                1 Reply Last reply Reply Quote 0
                • D
                  dsloan.ethra @george1421 last edited by

                  @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 1 Reply Last reply Reply Quote 0
                  • george1421
                    george1421 Moderator last edited by

                    @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?

                    Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!

                    D 1 Reply Last reply Reply Quote 0
                    • D
                      dsloan.ethra last edited by

                      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.

                      1 Reply Last reply Reply Quote 0
                      • S
                        Sebastian Roth Moderator last edited by 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     | 
                        +----------------------------------+-------+
                        

                        Web GUI issue? Please check apache error (debian/ubuntu: /var/log/apache2/error.log, centos/fedora/rhel: /var/log/httpd/error_log) and php-fpm log (/var/log/php*-fpm.log)

                        Please support FOG if you like it: https://wiki.fogproject.org/wiki/index.php/Support_FOG

                        1 Reply Last reply Reply Quote 1
                        • D
                          dsloan.ethra last edited by

                          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?

                          1 Reply Last reply Reply Quote 0
                          • 1 / 1
                          • First post
                            Last post

                          62
                          Online

                          10.4k
                          Users

                          16.4k
                          Topics

                          150.7k
                          Posts

                          Copyright © 2012-2023 FOG Project