PDA

View Full Version : MySQL quotas


NdK
30th March 2011, 11:57
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.

theWeird
30th March 2011, 13:55
Maybe there is already a feature request in the bugtracker:
http://bugtracker.ispconfig.org/index.php?do=details&task_id=1345

If this request does not fit your idea, feel free to add a new request to the tracker.

NdK
30th March 2011, 15:20
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...

falko
31st March 2011, 14:05
I've added this as a feature request to our bugtracker.

till
31st March 2011, 15:15
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.

NdK
1st April 2011, 13:39
Should be easily integrable w/ the rest of the monitoring: just use a query like
select table_catalog,table_schema,sum(data_length+index_l ength) 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

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.

i-chat
8th April 2011, 17:27
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.

NdK
8th April 2011, 19:06
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.

i-chat
8th April 2011, 21:37
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?

NdK
8th April 2011, 21:49
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).