Implementing Quota Management for MYSQL

[davidktw]

Dear Supporters,

I am meanwhile looking for solutions to quota management for Mysql. I do understand that quota management is not natively available in Mysql at the moment. Hence my rough approach to this problem is implementing native filesystem limit on block device size.

The implemention is to be done on Amazon EC2 and hence it is relatively inexpensive and easy to create as many EBS necessary and allocate each device to individual users. As each user would have only 1 database at most, hence quota control would be straight forward.

My first thought would be to use InnoDB for the tables as I understand these tables are stored in tablespaces which are preallocated as files, but unfortunately all InnoDB tables will be stored under one big shared tablespace unless “innodb_file_per_table” is used where autoextend ibd files are created in their own dedicated database directory instead. Hence the file will grow and eventually reach the limit of the device that is dedicated to it.

The problem while testing out this concept, the file grew until no space is available and eventually the database just crashed.

Is this the normal behaviour ? Should commit just rollback in the event there is no space available to add rows into the table ? I’m currently testing out on MYSQL DB “5.1.49-1ubuntu8.1”

Can anyone please advice ?

Thanks

[justin.swanhart]

Since InnoDB writes data back to disk asynchronously, it is not possible to simply limit the per-database size in the manner you are attempting.

If ‘total consumed space’ is your primary concern, then I would suggest tracking the amount of space consumed per user using INFORMATION_SCHEMA.tables (since there is a 1:1 mapping between users and databases) to examine how much space each user is consuming.

Just run a job once an hour (or more frequently depending on your needs) and REVOKE privileges from users that exceed their quota.

Regards,

–Justin

[davidktw]

Dear Justin,

I do came across articles on using privileges as a external management approach. But after some thoughts, I identify that UPDATE privilege is a loophole in this solution.

True that if a soft quota is exceeded, the user can be revoked of CREATE and INSERT privileges, but should UPDATE privileges be revoked too. If so, then it took away the possibility and could very well be the only available privilege to cut down the DB usage should DELETE is not an option. If UPDATE privilege is not revoked, then the user can continue to use UPDATE privilege to consume more data.

Hence unless there are better solution, I will only consider this solution as one of my last resort.

Anyone else who can help out on this ? I sincerely welcome your contribution.