List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:March 4 2001 1:55pm
Subject:Re: Once again... quotas...]
View as plain text  

>>>>> "Sasha" == Sasha Pachev <sasha@stripped> writes:

Sasha> On Wednesday 21 February 2001 03:46, Tristan 'Minty' Colgate wrote:
>> Hi There,
>> I have recently been asked to implement per database quotas 
>> for mysql for a large scale shared webhosting environmnet,
>> from the lists it seems pretty obvious that mysql cant achieve
>> this at the moment (without using filesystem quotas), file
>> system quotaing is pretty awkward with our environment.
>> Ive been asked to investigate the possibility of implementing
>> this and my boss has agreed that we can release the code back
>> to the community if we decide to do it.
>> I found one posting that mentioned that someone at MySQL
>> had some ideas on how you would like this implemented, if
>> this is still the case could they post them to me, if it
>> can fit in our timeframe Id be more than happy to do it.
>> It really would be a nice feature.

Sasha> I would just add a separate table in mysql database for quotas, load it into 
Sasha> a hash on startup, update/check the size info on insert, give error if there 
Sasha> is a problem. Monty may have a better idea.

I agree that the above is probably the best approach.  We would also
need a thread handler that flushes periodically any changes to the
database to the quota table, but this shouldn't be that hard.

Sasha> You may also consider sponsoring the feature instead of developing it 
Sasha> yourself - it might save you some time. Not sure about the exact cost, Monty 
Sasha> is the one to determine that, but it might very well be covered by extended 
Sasha> login support.

I would estimate that this is about 5x8 hours of work, which is a
little more than what is covered by extended login support, but if
Tristan doesn't have time to do this, we can of course try to create a
formal proposal for this.

What needs to be done:

- Add a quota table to the mysql database.
- Add to sql/ to read the quota table to memory.
- Add to sql/ some code to periodically flush changes to
  the quota table.
- When doing a drop or re-generation of a table, release the freed
  disk space in the quota. This can be done in sql/ and
- When doing an ALTER TABLE update the quote after the new table is
  generated. (sql/
- When doing a CREATE TABLE, update the quota.
- When doing a INSERT, UPDATE OR LOAD DATA INFILE update the quota.
  in this case one would probably need to this this on the handler
  level, after the write is done.
  Checking the quota would be easy to do in myisam/mi_write.c and
  myisam/mi_update.c;  INSERT is easy to fix, but UPDATE is a bit
  tricky to do completely right as you have to calculate the
  difference in size between the old row and the new row if you want
  to get this 100 % right;  You can get the last part done easier by
  storing the size of the original row when it's read.

Instead of storing the used quota to disk, it would probably be much
easier to on start of mysqld go through the databases and calculate
the file sizes and just keep the total in RAM per database.  This
would make a mysqld start a little slower, but could be worth it as it
would reduce complexity and would ensure that the quota calculation
will never go out of sync even if you get crash.

Once again... quotas...]Tristan 'Minty' Colgate21 Feb
  • Re: Once again... quotas...]Sasha Pachev21 Feb
    • Re: Once again... quotas...]Michael Widenius5 Mar