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

Storage nodes opening multiple mySQL connections to Master DB

Scheduled Pinned Locked Moved Solved
FOG Problems
3
10
1.6k
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 Jan 1, 2018, 2:51 PM

    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
    • G
      george1421 Moderator @dsloan.ethra
      last edited by george1421 Jan 2, 2018, 2:23 PM Jan 2, 2018, 4:42 PM

      @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
        last edited by Jan 1, 2018, 3:43 PM

        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
        • S
          Sebastian Roth Moderator
          last edited by Sebastian Roth Jan 1, 2018, 12:33 PM Jan 1, 2018, 6:31 PM

          @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 Jan 1, 2018, 9:12 PM

            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
            • G
              george1421 Moderator
              last edited by Jan 1, 2018, 11:15 PM

              @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 Jan 1, 2018, 11:47 PM Reply Quote 0
              • D
                dsloan.ethra @george1421
                last edited by Jan 1, 2018, 11:47 PM

                @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
                
                G 1 Reply Last reply Jan 2, 2018, 3:08 AM Reply Quote 0
                • G
                  george1421 Moderator @dsloan.ethra
                  last edited by Jan 2, 2018, 3:08 AM

                  @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
                  • S
                    Sebastian Roth Moderator
                    last edited by Sebastian Roth Jan 2, 2018, 2:56 AM Jan 2, 2018, 8:54 AM

                    @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 Jan 2, 2018, 3:50 PM Reply Quote 1
                    • D
                      dsloan.ethra @Sebastian Roth
                      last edited by Jan 2, 2018, 3:50 PM

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

                      G 1 Reply Last reply Jan 2, 2018, 4:42 PM Reply Quote 0
                      • G
                        george1421 Moderator @dsloan.ethra
                        last edited by george1421 Jan 2, 2018, 2:23 PM Jan 2, 2018, 4:42 PM

                        @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
                        • 1 / 1
                        1 / 1
                        • First post
                          1/10
                          Last post

                        192

                        Online

                        12.0k

                        Users

                        17.3k

                        Topics

                        155.2k

                        Posts
                        Copyright © 2012-2024 FOG Project