>>>>> "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/sql_acc.cc to read the quota table to memory.
- Add to sql/sql_manager.cc 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/sql_delete.cc and
- When doing an ALTER TABLE update the quote after the new table is
- 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.