#1  
Old 30th March 2011, 11:57
NdK NdK is offline
Member
 
Join Date: Jul 2009
Location: Bologna, ITA
Posts: 41
Thanks: 1
Thanked 3 Times in 1 Post
Default MySQL quotas

Hello.

It could be useful to have some way to limit MySQL accesses, like many providers do.
There could be both space and queries limits (often used limits are 100MB and 10K queries/h ).

http://lrem.net/software/mysql-quota-daemon.xhtml could be an interesting starting point, at least for "size" issues.
Reply With Quote
Sponsored Links
  #2  
Old 30th March 2011, 13:55
theWeird theWeird is offline
Member
 
Join Date: Nov 2009
Posts: 70
Thanks: 11
Thanked 6 Times in 5 Posts
Default

Maybe there is already a feature request in the bugtracker:
http://bugtracker.ispconfig.org/inde...s&task_id=1345

If this request does not fit your idea, feel free to add a new request to the tracker.
Reply With Quote
  #3  
Old 30th March 2011, 15:20
NdK NdK is offline
Member
 
Join Date: Jul 2009
Location: Bologna, ITA
Posts: 41
Thanks: 1
Thanked 3 Times in 1 Post
Default

No. Using "real" (OS) quota is BAD!
Apart the fact that usually web space and db space have very different "price", the real bad thing in using OS quota is that if a user hits his limit, the whole MySQL server dies (IIUC many articles found googling around).
Since in ISPConfig a single MySQL instance is handling queries for all the sites, the net result is that that when ONE user hits his limit, NOBODY ELSE can use SQL! Not good...
Reply With Quote
  #4  
Old 31st March 2011, 14:05
falko falko is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 41,701
Thanks: 1,900
Thanked 2,752 Times in 2,582 Posts
Default

I've added this as a feature request to our bugtracker.
__________________
Falko
--
Download the ISPConfig 3 Manual! | Check out the ISPConfig 3 Billing Module!

FB: http://www.facebook.com/howtoforge

nginx-Webhosting: Timme Hosting | Follow me on:
Reply With Quote
  #5  
Old 31st March 2011, 15:15
till till is offline
Super Moderator
 
Join Date: Apr 2005
Location: Lüneburg, Germany
Posts: 37,022
Thanks: 840
Thanked 5,655 Times in 4,464 Posts
Default

The mysql quota daemon project seems to be dead. The website is offline and the last update that I was able to find is from 2006.
__________________
Till Brehm
--
Get ISPConfig support and the ISPConfig 3 manual from ispconfig.org.
Reply With Quote
  #6  
Old 1st April 2011, 13:39
NdK NdK is offline
Member
 
Join Date: Jul 2009
Location: Bologna, ITA
Posts: 41
Thanks: 1
Thanked 3 Times in 1 Post
Default

Should be easily integrable w/ the rest of the monitoring: just use a query like
Code:
select table_catalog,table_schema,sum(data_length+index_length) as size
    from information_schema.partitions
    where table_schema not in ('dbispconfig','information_schema','mysql')
    group by(table_schema);
Probably table_catalog is not needed, but I'm a MySQL newbie and could be wrong...

If you add "max_size" to dbispconfig.web_database, you can get all "over quota" DBs with a simple
Code:
select * from (
    select database_user as user,
        database_name as db,
        sum(data_length+index_length) as size,
        max_size
    from information_schema.partitions, dbispconfig.web_database
    where database_name=table_schema
    group by(table_schema)
    ) tmp
    where size>max_size;
To extend it, "over quota" could trigger an event (to revoke insert/update, send a mail, force repacking, etc.).
If saved as a view, you can even use it to limit "overall db size" for a user. With a slightly more complex query and 2 extra columns in web_database it's possible to handle soft/hard quota limits.

Last edited by NdK; 1st April 2011 at 13:47. Reason: Wrong query, corrected.
Reply With Quote
The Following 3 Users Say Thank You to NdK For This Useful Post:
falko (3rd April 2011), i-chat (8th April 2011), till (1st April 2011)
  #7  
Old 8th April 2011, 17:27
i-chat i-chat is offline
Member
 
Join Date: Jan 2011
Posts: 31
Thanks: 3
Thanked 0 Times in 0 Posts
Default

running such a php based quota script from something like cron wouldn't be my favorite way of doing things (fearing for huge load if you get to many databases) but i would certainly prefer it over OS-based quota (that could bring your server down in total), or no quota at all.

i think that 1 table could be added to the master config, where a per database quota could be added. also one could opt to a few alternate ways to run a quota script.

i for one would opt to run sutch a script only once every few houres,
even though that this would allow databased to grow bigger than thair limits, ( i would rather opt for a little more space than a strict monitoring and a verry big draggdown on the server.

if you where running a 'deamon' in some compiled language like c you could probably query the databases every 10seconds, but i would not do this with php or perl.
Reply With Quote
  #8  
Old 8th April 2011, 19:06
NdK NdK is offline
Member
 
Join Date: Jul 2009
Location: Bologna, ITA
Posts: 41
Thanks: 1
Thanked 3 Times in 1 Post
Default

Running such a query places load only on DB, and that load doesn't depend on the method used to send it.
There are surely many ways to optimize it. In PostGreSQL I'd use a stored procedure.

Its complexity should be nearly linear in the number of total tables. But I haven't any big DB handy to test.

If yours is, you can try to run it in phpMyAdmin's console, or via mysql client. But I don't think it gets too heavy, at least in a scenario where you have 100dbs w/ about 100tables each.

What then depends on the language used to submit query is the result handling, but there should be only a few queries. I think a single uncached Drupal query is heavier.
Reply With Quote
  #9  
Old 8th April 2011, 21:37
i-chat i-chat is offline
Member
 
Join Date: Jan 2011
Posts: 31
Thanks: 3
Thanked 0 Times in 0 Posts
Default

meaning that you could do somehing like one php file,

that: 1 get the db size per db, on the server,
2 check the quota's table,
3 compare the 2
4 if dbsize is bigger than for example 80% of the quota send a warning to the database owner (ie the client that the db belongs to...).
5 if dbsize is bigger than the quota warn again and remove priveledges of that database.

and running it from a cronjob every so mutch time, is it realy that simple?
because that would probably only require 2 php files, 1 for the cronjob and another one to create a management interface in the pannel?
Reply With Quote
  #10  
Old 8th April 2011, 21:49
NdK NdK is offline
Member
 
Join Date: Jul 2009
Location: Bologna, ITA
Posts: 41
Thanks: 1
Thanked 3 Times in 1 Post
 
Default

Quote:
Originally Posted by i-chat View Post
meaning that you could do somehing like one php file,

that: 1 get the db size per db, on the server,
2 check the quota's table,
3 compare the 2
4 if dbsize is bigger than for example 80% of the quota send a warning to the database owner (ie the client that the db belongs to...).
5 if dbsize is bigger than the quota warn again and remove priveledges of that database.

and running it from a cronjob every so mutch time, is it realy that simple?
because that would probably only require 2 php files, 1 for the cronjob and another one to create a management interface in the pannel?
Yes. At the cost of a slight increase in query complexity you could get only one "kind" of over-quota users. Or, if you prefer to put more logic in the client, then you could select just the total db size per user and the two extra columns: warn limit (soft quota: triggers a mail) and hard quota (suspends insert/update rights).
Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CPU load locks up box. Apache or MYSQL related. crypted General 61 29th October 2010 23:16
Squirrelmail login failure Cracklefish Installation/Configuration 9 30th June 2010 21:38
ISPConfig3 Mail Warn Errors reason8 General 3 25th November 2009 14:58
Management/system config/settings & /server/settings not working!! dactor Installation/Configuration 9 6th February 2008 10:11
Mandriva 10.2 Perfect Setup Install Problems... ctroyp Installation/Configuration 12 30th December 2005 17:04


All times are GMT +2. The time now is 04:24.


Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.