The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Silly simple mysql question - Limit connections on a per account basis?

Discussion in 'General Discussion' started by jols, Jun 3, 2009.

  1. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    With the cPanel system, is there a away to limit the number of hits to MySQL that a specific hosting account can place on the system.

    For example, I imagine this could be done by putting up a special my.cnf file in the vsites /etc directory with just this line in it:

    max_connections = 25

    Then that vsite's my.cnf file and any statements therein would override the global, server-wide my.cnf file. Is this true? Am I on the right track here?

    Thanks much!
     
  2. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
  3. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    Thanks cpanelkenneth, the link you sent does indeed address my question. I was just hoping it would have been as simple as poping a custom my.cnf file in the vsites own /etc directory.
     
    #4 jols, Jun 3, 2009
    Last edited: Jun 3, 2009
  4. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    my.cnf files only work automatically for tools provided by MySQL ( e.g. the mysql command line client ). Other tools ( e.g. Perl scritps using DBD::MySQL, PHP scripts using any of the MySQL extensions, etc ) will not use settings in a my.cnf unless explicitly instructed to.

    What might be interesting here is a periodic cron job that checks various statistics gathered by MySQL. The job could compare current state versus last state ( stats for NOW compared to last time the job ran ) and automatically update the appropriate quota setting. This is just pure speculation. However I would guess someone has written something like this.

    Have you tried googling for 'mysql quota' ? or a variation thereof?
     
  5. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    Thanks for the suggestions and further input.

    What this request comes out of is a need to limit the hit that one particular (large) WordPress built site has on one of our servers. Regular "human" usage during regular peak hours is fine, but every once and a while they get hit by a kazillion web bots, or one extremely persistent bot that ends up killing the server, usually taking the load average up beyond 70 until we either start firewalling individual IP addresses or reboot.

    I've tried using mod_bandwidth, taking limit down really low, as well as various mod_security bot blocking rules but nothing seems to help. During one of the big load hits, that is if I can get into shell at all to do this, I view the mysql process list and it's page and page after page of listed processes for this account, usually occurring very late at night, and again, it is apparent that these are search engine and other bots doing this, apparent that is from what is showing up in their account apache access log after such an event.

    So my idea was to limit the number of possible simultaneous MySQL processes just to keep the server up, but doing the MySQL limit just for this one account.
     
  6. wizzy420

    wizzy420 Well-Known Member

    Joined:
    Nov 13, 2007
    Messages:
    125
    Likes Received:
    2
    Trophy Points:
    18
    Actually, max_user_connections is enforced by the SQL server. It will stop PHP, Perl, etc from passing that number.

    Keep in mind if you sat it to say "10 connection" it is enforced per SQL username. So if you have a user with a login "user1" and they have five SQL usernames (user1_bob, user1_jane, user1_harry, etc.) they could potentially use up to 50 connections.

    But for the most part, this setting works very well at stopping the run away website.
     
  7. cPanelKenneth

    cPanelKenneth cPanel Development
    Staff Member

    Joined:
    Apr 7, 2006
    Messages:
    4,458
    Likes Received:
    22
    Trophy Points:
    38
    cPanel Access Level:
    Root Administrator
    Correct. The original post was about doing this for specific users rather than server-wide.
     
  8. electric

    electric Well-Known Member

    Joined:
    Nov 5, 2001
    Messages:
    697
    Likes Received:
    1
    Trophy Points:
    18
    Would be nice if cpanel were able to limit per-account resource usage by ram/cpu/mysql threads, instead of just disk/bw.
     
  9. Spiral

    Spiral BANNED

    Joined:
    Jun 24, 2005
    Messages:
    2,023
    Likes Received:
    7
    Trophy Points:
    0
    Have you tried "Mod_Evasive"?

    (If not, it's probably exactly what you are looking for!)

    Now as far as the web bots go, those are a bit easier to kill through
    either Apache AUTH security or using Mod_security rules targeting
    those connections specifically (yes, will help if setup correctly).

    Most bots also often originate from a specific IP or a small set of IPs
    and you should be able to isolate those and drop those connections:
    Code:
    iptables -A INPUT -s (ip address or cidr range) -j DROP
    
    As for MySQL, I think you are looking at the effect instead of the source
    and though it might be good to try to limit connections for other reasons,
    I don't think that is your real problem at hand here.

    Being a Wordpress site, you might also be dealing with the site being DIGGed
    and there are a few neat tricks you can employ regarding handling that
    sort of traffic as well including caching redirects, etc.

    If you want some help locking this issue down, contact me.
     
    #10 Spiral, Jun 15, 2009
    Last edited: Jun 15, 2009
  10. jols

    jols Well-Known Member

    Joined:
    Mar 13, 2004
    Messages:
    1,111
    Likes Received:
    2
    Trophy Points:
    38
    Regarding mod_evasive. Yes, but we really have not found this to be a very useful tool. For one thing, this only looks at the entire server as opposed to the ability to set up different settings for individual accounts (correct me if I'm wrong here.)

    Also, I've noticed in the past, that by the time I get the mod_evasive settings down to something that would control massive bot hits, people start complaining that not all of their graphics are loading, i.e. on web pages that sport dozens of thumbnails, etc.

    --------------

    Yes, I have plenty of mod_security rules set up to fend off the bots, the problem is that there are new, formerly unheard of bots raking the server all the time. Also, for some bizzare reason some bots like the Twiceler bot seem to be able to avoid being impeeded by any mod_security rule we put up.

    --------------

    And yes, we sit there and block IP addresses in the firewall when all else fails, but this is a tedeous cure (if even that) rather than any sort of prevention.

    --------------

    What you said in the last paragraph is probably the most likely about being DIGGed.

    Thanks for your response.
     
Loading...

Share This Page