MySQL quotas

Discussion in 'Feature Requests' started by NdK, Mar 30, 2011.

  1. NdK

    NdK Member


    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 ). could be an interesting starting point, at least for "size" issues.
  2. theWeird

    theWeird Member

  3. NdK

    NdK Member

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

    falko Super Moderator ISPConfig Developer

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

    till Super Moderator Staff Member ISPConfig Developer

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

    NdK Member

    Should be easily integrable w/ the rest of the monitoring: just use a query like
    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
    select * from (
        select database_user as user,
            database_name as db,
            sum(data_length+index_length) as 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: Apr 1, 2011
  7. i-chat

    i-chat New Member

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

    NdK Member

    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.
  9. i-chat

    i-chat New Member

    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?
  10. NdK

    NdK Member

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

Share This Page