Improving and simplifying email mailbox quota management in ISPConfig

Discussion in 'Installation/Configuration' started by cbj4074, Jun 10, 2013.

  1. cbj4074

    cbj4074 Member

    I'd like to open a dialog about how mailbox quotas are handled in ISPConfig. Let's walk through a common use-case.

    I set a 10000MB quota in a client template, named "Basic Web Hosting". I apply this template to a client.

    I create 10 new mailboxes for the client, and do not assign an explict quota to any mailbox. The 10 users share the 10000MB; the quota is not "divided evenly", which means that one user is able to consume 9000MB, leaving the other 9 users to share 1000MB.

    I realize that I can "hard-code" each user's quota to be, for example, 1000MB. But then, resources may be "wasted" if one user hardly uses email, but 1000MB are being "reserved" for him, and one user requires a lot of large attachments but is limited to 1000MB. Also, this approach requires me to recalculate all users' quotas for the client in question, manually, each time a new mailbox is created (or whenever the client template quota is changed).

    Ultimately, I'm looking for some type of "dynamic quota renogotiation" behavior that would allow for at least two different quota management schemes:

    1.) All users share the client quota. (This "mode" is possible already.)

    Benefits: Simple setup and limited or no maintenance; adding mailboxes or changing the client template poses no real risks.
    Risks: There is nothing to stop an individual user from consuming the entire quota, thereby causing other users to go over-quota and not receive mail.

    2.) All users split the client quota evenly; a 10000MB quota for 10 users means that each user will be guaranteed 1000MB. When new mailbox users are created, or the client template quota is changed, each user's quota is recalculated (divided evenly).

    Benefits: Provides guaranteed resources for each user.
    Risks: Users with lower storage requirements may "waste" disk space that another user under the same client could better-utilize. Also, a potential problem arises when a new mailbox is added or the client template quota is changed; if any user is near his quota, the quota recalculation could cause him to be over-quota instantly. (I have no suggestion for addressing this problem at the minute.)

    Are there additional schemes that might be useful?

    Has this issue been discussed internally (or externally) already? Has someone elucidated clearer, more complete plans to implement something like what I describe?

    Last edited: Jun 10, 2013
  2. till

    till Super Moderator Staff Member ISPConfig Developer

    What you described above is not the way quota works in ispconfig. Each mailbox has its own quota and only the quota of the mailbox is applied to incoming emails. The quota value that you set in the client limits the client to add mailboxes with a higher overall space in ispconfig. So a mailbox user can nevre consume any resources from another mailbox user as you described above.
  3. cbj4074

    cbj4074 Member

    Thanks for responding, Till. I sure do appreciate your time.

    Okay, I see. That's great to know.

    So, what happens if I don't specify a quota for any mailboxes under a given client, but the client template that's applied specifies 10000MB?

    Does this mean that there is simply no quota applied to the mailboxes? And by extension, that the client is able to add any number of new mailboxes -- even if the current total of all this client's mailboxes exceeds 10000MB?

    Or am I misunderstanding?
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    You cant do that as a client. A administrator can override quotas of course, but a client cant. Try it out yourself, set a limit of 1000MB for the client in the client limits, then login as this client and try to create a 500mb plus a 600mb mailbox, you will get a error like this:

    "The max space for mailboxes is reached. The max. available space in MB is: 1000"

    when you try to create the second mailbox. if the second mailbox is smaller then 500mb so it fits in the client quota, you will be able to create it.

    Same happens when you try to create a unlimited mailbox as this client as unlimited exceeds the allowed quota size.
  5. cbj4074

    cbj4074 Member

    Okay, very good. That behavior makes sense and I wish it were better-described in the manual. (Maybe it is and I missed it; my manual is old, too, purchased 2012.01.30.)

    Given everything you've stated, I suppose that my only "wish" is to be able to "re-allocate quota" for all mailboxes for a given client.

    The problem I see arising occurs when we need to increase a client's quota. Suppose that the client's quota is set to 10000MB. Suppose also that the client has set each mailbox's quota to 1000MB (and there are 10 mailboxes).

    If we increase the client's quota to 20000MB, wouldn't the client have to adjust all of his mailbox's quotas, one at a time, in order to give each mailbox a 2000MB quota?

    Basically, I would like to be able to click a button, "Re-allocate quota by dividing available client quota across all client mailboxes". Does this make sense? Or am I still missing something?

    Thanks again, Till.
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    YEs, he would have to reallocate the space aat it might also be that he wants to increase the psca only for some mailboxes or that he wishes to add additional mailboxes which require the space.

    In my opinion, that might be a function for the tools module.
  7. cbj4074

    cbj4074 Member

    That's an excellent point, and that's where the topic of quotas becomes tricky. I'll attempt to brainstorm different ways that this challenge might be approached.

    In the meantime, I'm going to document some of the queries that may be useful and relevant once I (or anyone else) gets around to building a Tools module to handle quotas in more advanced ways.

    "Get all clients whose combined mailbox quotas exceed the client mail quota." This query might be useful in determining which clients' quotas need to be "reigned-in". This is the situation I am facing myself, and it doesn't seem like an uncommon scenario (basic use-case is when an Admin creates a client's mailboxes with unlimited quotas and then a client quota is introduced later).
        `mail_user`.`sys_groupid` AS `groupId`,
        `client`.`client_id` AS `clientId`,
        FORMAT(`client`.`limit_mailquota`, 2) AS `mailQuota`,
        FORMAT(SUM(`quota`)/1024/1024, 2) AS `groupQuota`
    FROM `mail_user`
    INNER JOIN `sys_group` ON `mail_user`.`sys_groupid` = `sys_group`.`groupid`
    INNER JOIN `client` ON `sys_group`.`client_id` = `client`.`client_id`
    GROUP BY `client`.`client_id`
        `limit_mailquota` > -1
        `groupQuota` > 0
        SUM(`quota`)/1024/1024 > (SELECT `limit_mailquota` FROM `client` WHERE `client_id` = `clientId`)
    "Update all mailbox quotas for client with ID %d such that the available client quota is divided evenly across all client mailboxes."
    UPDATE `mail_user`
    INNER JOIN `sys_group` ON `mail_user`.`sys_groupid` = `sys_group`.`groupid`
    SET `quota` = FLOOR((
        FROM (
                (SELECT COUNT(*) FROM `mail_user` WHERE `sys_groupid` = `groupid`) AS `numClientMailboxes`,
                (`limit_mailquota`/ (SELECT COUNT(*) FROM `mail_user` WHERE `sys_groupid` = `groupid`)) AS `updatedUserQuota`
            FROM `client`
            INNER JOIN `sys_group` ON `client`.`client_id` = `sys_group`.`client_id`
            INNER JOIN `mail_user` ON `sys_group`.`groupid` = `mail_user`.`sys_groupid`
                `limit_mailquota` > -1
                AND `mail_user`.`sys_groupid` = %d
            GROUP BY `numClientMailboxes`
                `numClientMailboxes` > 0
        ) AS `derived`
        `mail_user`.`sys_groupid` = %d
    I'm sure that I am missing a couple of crucial pieces, but at least this is a start, and for my own records, if no one else's.

    Happy to receive any feedback, as this is my first attempt at working with ISPC's DB tables in a development capacity.
    Last edited: Jun 12, 2013
  8. till

    till Super Moderator Staff Member ISPConfig Developer

    Please not that data updates have to be done trigh the ispconfig datalog function. If you you update records in the db directly with a mysql query, then th changes wont get written to disk (config files) and dont get propagated to other servers in a multiserver setup.
  9. cbj4074

    cbj4074 Member

    I learned this very early on with ISPConfig, and the reason for it makes perfect sense. Thanks for the tip!

Share This Page