Announcement

Announcement Module
Collapse
No announcement yet.

Reserve connections to MySQL root

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Reserve connections to MySQL root

    More often than not in recent times I have had to bump off the mysql instance to regain normalcy, because it exhausted the max_connections value allotted to it. In scenarios such as this am unable to check the exact number of the existing connections and also troubleshoot the reason for the same.

    My query was to know, if there was any possibility of allotting some connections to the MySQL root user from the max_connections set?

    Any help will be appreciated.

  • #2
    Isn't there none who can assist, refer, suggest, help ( ?

    Comment


    • #3
      check if you have the SUPER privilege. There is one more connection reserved for such users. See http://dev.mysql.com/doc/refman/5.1/en/too-many-connections. html

      Comment


      • #4
        I thought root had the SUPER privilege by default!! confused:

        Comment


        • #5
          Also, one of my users, 'SYSDBA' does have SUPER privileges assigned to it but inspite of that during the "Too many connections" scenario its still unable to connect.

          mysql> show grants for 'SYSDBA';
          +----------------------------------------------------------- ----------------------------+
          | Grants for SYSDBA@%

          +----------------------------------------------------------- ----------------------------+
          | GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER ON *.* TO 'SYSDBA'@'%' IDENTIFIED BY PASSWORD 'xxxxxxxxxxxx' |
          +----------------------------------------------------------- ----------------------------+
          1 row in set (0.01 sec)

          Comment


          • #6
            So I assume that the user, who abuses too much connections, has also the SUPER privilege. Check all the users with SUPER privilege and consider if they really need it.

            Comment


            • #7
              Does that mean if this 'SYSDBA' user who has the SUPER privilege exhausts all the max_connections number then mysql would not be able to reserve that one extra connection to another SUPER privilege user? Interesting...

              Comment


              • #8
                I really don't know, I don't see you app )

                Watch the processlist, look for sleeping connections.
                If it's a web app, issue SET SESSION wait_timeout=30; right after connecting to the DB. This prevents sleeping connections to fill the max_connections limit.
                Another thing is max_user_connections - set it *lower* than the max_connections.

                Comment

                Working...
                X