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.

 

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.

 

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

 

I've added this as a feature request to our bugtracker.

 

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.

 

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.

 

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.

 

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