Results 1 to 11 of 11
  1. #1

    Limit MySQL Usage

    Hi,

    Some of my clients have forums with high mysql usage.

    These forums create high server load at times, making problem for other users.

    How do i limit MySQL usage for these users ?

    I set max_connections for the MySQL user to 80, at times this will make error like

    Warning: mysql_connect(): User 'mysql_user_name' has exceeded the 'max_connections' resource (current value: 80)
    What is the recommended value for "max_connections" in shared hosting ?

    Regards,

    Yujin

  2. #2
    There no right answer, ou just have to play around until you get a nice configuration.

    Try these basic changes in the my.cnf:

    code:
    [mysqld]
    max_connections = 250
    wait_timeout = 30
    connect_timeout = 10
    interactive_timeout = 30
    query_cache_type = 1

    and change your httpd.conf to set keepalive=off
    OSHS Ltd
    OSHS Services - DNS Clusters | R1Soft Licenses | Remote Backup Storage | R1Soft CDP Storage | Cheap Dedicated Servers
    EconDC.com - Enterprise UK Server Colo & Rack Space at Lowest Prices

  3. #3
    Join Date
    Feb 2002
    Location
    Vestal, NY
    Posts
    1,378
    I do not believe there is a way to limit connections on a per-user basis. What you could do is run a seperate MySQL server for them and then set the connection limit so only their site will display the error message when the limit is reached. It may be time that you ask your client to upgrade to a dedicated server.
    H4Y Technologies LLC Check out our new website!
    "Smarter, Cheaper, Faster" - SMB, Reseller, VDS, Dedicated, Colo hosting done right.

    ZERO PACKETLOSS, ZERO DOWNTIME Dedicated and Colo - USA: IA, CA, NC, OR, NV
    **http://h4y.us**
    Voice: (866)435-5642. *** Email: askus at host4yourself d0t com

  4. #4
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    11,686
    I do not believe there is a way to limit connections on a per-user basis.
    Sure there is, it's been posted above
    as far as the recommended value for max_user_connections:
    I typically leave mine around 20-30
    There really is no need for 80 user connections if the software is designed properly. In fact, being as you're running a "shared" host, then there's really no need for more than 20.. remember, every connection you have from any user will cause more problems to the server in the long run, which is why it's advisable to keep those persistent connections on whenever possible, and to close connections when not being used.

    Properly designed software will actually fix the problem you're having. Most likely this is not just "forums" but "forums with hacks" and those "hacks" add sql connections and problems to the forums and db's, thusly adding load to the server. It's a bad thing, but some don't seem to understand how to do sql design properly, sadly ;(
    WHMCS Guru - WHMCS addons, management, support and more.
    WHMCS Notifications Extended - Add slack, hipchat, SMS, pushover to WHMCS !!
    Always looking for Linux, WHMCS, Support Desk work. PM for details

  5. #5
    Join Date
    Feb 2002
    Location
    Vestal, NY
    Posts
    1,378
    I was under the impression that max_connections and max_user_connections was simply a global value (max_connections definitely is, which is the only setting that was mentioned above) and would not pertain to specific users. I had thought that when the max_user_connections limit is reached, the entire MySQL server stops accepting connections. Maybe I am wrong, but it seems to support my thoughts here:

    http://dev.mysql.com/doc/mysql/en/user-resources.html

    "From MySQL 5.0.3 on, it is also possible to limit the number of simultaneous connection to the server on a per-account basis. " MySQL 5 is beta of course. The wording on that page is a bit confusing though.

    It looks like versions of MySQL 4.02 may introduce more controls on a per-account basis. I have never experimented with this, but looks like it is worth a shot.
    H4Y Technologies LLC Check out our new website!
    "Smarter, Cheaper, Faster" - SMB, Reseller, VDS, Dedicated, Colo hosting done right.

    ZERO PACKETLOSS, ZERO DOWNTIME Dedicated and Colo - USA: IA, CA, NC, OR, NV
    **http://h4y.us**
    Voice: (866)435-5642. *** Email: askus at host4yourself d0t com

  6. #6
    Join Date
    Sep 2002
    Location
    Top Secret
    Posts
    11,686
    I had thought that when the max_user_connections limit is reached, the entire MySQL server stops accepting connections.
    Nope, that specific USER will stop accepting connections, but not the server, otherwise there'd be no sense in a separate max_connections and max_user_connections variable.
    WHMCS Guru - WHMCS addons, management, support and more.
    WHMCS Notifications Extended - Add slack, hipchat, SMS, pushover to WHMCS !!
    Always looking for Linux, WHMCS, Support Desk work. PM for details

  7. #7
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Originally posted by John[H4Y]
    http://dev.mysql.com/doc/mysql/en/user-resources.html

    It looks like versions of MySQL 4.02 may introduce more controls on a per-account basis. I have never experimented with this, but looks like it is worth a shot.
    With 4.0.2+ You can do per user restrictions

    As a prerequisite for using this feature, the user table in the mysql database must contain the resource-related columns. Resource limits are stored in the max_questions, max_updates, max_connections, and max_user_connections columns. If your user table doesn't have these columns, it must be upgraded; see Section 2.10.7, “Upgrading the Grant Tables”. http://dev.mysql.com/doc/mysql/en/up...nt-tables.html

    To set resource limits with a GRANT statement, use a WITH clause that names each resource to be limited and a per-hour count indicating the limit value. For example, to create a new account that can access the customer database, but only in a limited fashion, issue this statement:

    mysql> GRANT ALL ON customer.* TO 'francis'@'localhost'
    -> IDENTIFIED BY 'frank'
    -> WITH MAX_QUERIES_PER_HOUR 20
    -> MAX_UPDATES_PER_HOUR 10
    -> MAX_CONNECTIONS_PER_HOUR 5
    -> MAX_USER_CONNECTIONS 2;

    What you want is something like:

    mysql> GRANT USAGE ON *.* TO 'francis'@'localhost'
    -> WITH MAX_USER_CONNECTIONS 20;

    assuming [email protected] is the user you wish to restrict.

  8. #8
    Join Date
    Apr 2003
    Location
    San Jose, CA.
    Posts
    1,622
    Originally posted by linux-tech
    Sure there is, it's been posted above
    as far as the recommended value for max_user_connections:
    I typically leave mine around 20-30
    max_user_connections=#
    in my.cnf

    That's not limiting on a per-user basis.

    That's limiting every user to # connections.

  9. #9
    I used to set limit for mysql user by

    # mysql
    mysql> use mysql;
    mysql> update user set max_connections='80' where User='mysql_user';
    But it seems the site getting down and not coming up.

    Is this variable set maximum connections at a time or total connections allowed ?

    What i want is to limit the MySQL usage of this user when the site is getting too much visitors, so that others on the server won't be affected.

  10. #10
    Join Date
    Sep 2000
    Location
    Alberta, Canada
    Posts
    3,109
    Originally posted by linux-tech
    There really is no need for 80 user connections if the software is designed properly.
    Now there's a statement that can really add fuel to the fire.

    Our Servers use: max_connections = 3000 & keep alive = on, and run just fine.


    flashwebhost, "max_connections" is total connections for the Server as mentioned earlier. Which is why "max_user_connections" applies to individual accounts and should always be lower than "max_connections" -- by a lot.
    PotentProducts.com - for all your Hosting needs
    Helping people Host, Create and Maintain their Web Site
    ServerAdmin Services also available

  11. #11
    In mysql data base, table user, there is a field "max_connections" for each user, that is what i set.

    This field is for that user only as in my case, only the site i set the limit goes down saying max_connections exceeded. What i want to know is it simultaneous connections or not.

    max_connections = 3000 will work fine if you have enough memory, cpu or there is no sites with heavy mysql usage.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •