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

[jols]

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!

 

[PlatinumServerM]

You can set this in /etc/my.cnf

max_connections=100
max_user_connections=25

 

[cPanelKenneth]

You might find the documentation useful for this:

MySQL :: MySQL 5.0 Reference Manual :: 5.5.4 Limiting Account Resources

 

[jols]

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.

 

[cPanelKenneth]

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.

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?

 

[jols]

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.

 

[wizzy420]

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.

 

[cPanelKenneth]

Actually, max_user_connections is enforced by the SQL server. It will stop PHP, Perl, etc from passing that number.

Correct. The original post was about doing this for specific users rather than server-wide.

 

[electric]

Would be nice if cpanel were able to limit per-account resource usage by ram/cpu/mysql threads, instead of just disk/bw.

 

[Spiral]

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

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:

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.

 

[jols]

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.