GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Implementing Quota Management for MYSQL

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Implementing Quota Management for MYSQL

    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

  • #2
    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

    Comment


    • #3
      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.

      Comment


      • #4
        davidktw wrote on Sun, 06 March 2011 19:38
        If UPDATE privilege is not revoked, then the user can continue to use UPDATE privilege to consume more data.
        How much more data can you actually consume by using UPDATE?
        Yes you can update a field with a string twice as long but if the application is built in a certain way then storing a twice as long string in a field won't make you any happier if you aren't allowed to insert new records.

        davidktw wrote on Sun, 06 March 2011 19:38
        Hence unless there are better solution, I will only consider this solution as one of my last resort.
        That's about as good as it gets, anything that has to do with file space quota is not a good solution since you:
        1. block writes for the application
        2. it's hard for a user to reclaim space by deleting records (unless you allow your users to perform ALTER TABLE which I don't recommend). At which point you have to check the information_schema anyway to find out how much data that is actually used.


        And as Justin said depriving a process to write to disk is not a good solution if you want a stable environment.
        Applications tend to be a bit picky about you pulling the rug out from under them during operation.

        Comment


        • #5
          It shouldn't have crashed It should have rolled back with "table full" error. But I would not be surprised to find bugs in this area of the code, because of course running the filesystem out of space is a less frequent occurrence.

          Comment


          • #6
            Dear xaprb,

            I'm not using the shared innodb tablespace, but rather using the feature of 1 tablespace file per table, which cannot be configured with a fixed size.

            Would this therefore be the problem ? Do the "table full" error only shows on the shared innodb tablespace without the autoextent configured ?

            I'm glad the normal behaviour should be a table full error instead of crashing.

            Comment


            • #7
              davidktw wrote on Sat, 12 March 2011 08:39
              Would this therefore be the problem ? Do the "table full" error only shows on the shared innodb tablespace without the autoextent configured ?

              I'm glad the normal behaviour should be a table full error instead of crashing.
              No it _should_ report it in all cases, both when you have a fixed tablespace, or an autoextending one, regardless of if it is a file-per-table or a shared tablespace.

              But the emphasis is on the word _should_ and if you choose to use file system quotas to limit the databases size you _will_ get a headache.

              What should work and what actually works in a production environment are two different things.

              Comment

              Working...
              X