Account mailbox database structure

Revision as of 18:05, 12 July 2007 by Bobby (talk | contribs) (New page: Category: Troubleshooting Category: mySQL Category: Database Each zimbra account is associated with a mailbox group, which is based on the mailbox id number. You can determine...)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)


Each zimbra account is associated with a mailbox group, which is based on the mailbox id number. You can determine the mailbox id for an account (this example account is named "b@test.test") like this:

$ zmprov getMailboxInfo b@test.test
mailboxId: 5247
quotaUsed: 1951021

Mailbox users are members of mailbox groups on a rotation up to a maximum of 100 mailbox groups; you can determine the mailbox group by modulo division of the mailbox id by 100 (the remainder of dividing the mailbox id by 100, i.e only the last two digits).

$ expr 5247 % 100
47

Take a look at the mail_item table in the mailbox group database, particularly the date, size, sender, and subject columns.

$ mysql mboxgroup47
mysql> describe mail_item;
+--------------+---------------------+------+-----+---------+-------+
| Field        | Type                | Null | Key | Default | Extra |
+--------------+---------------------+------+-----+---------+-------+
| mailbox_id   | int(10) unsigned    | NO   | PRI |         |       |
| id           | int(10) unsigned    | NO   | PRI |         |       |
| type         | tinyint(4)          | NO   |     |         |       |
| parent_id    | int(10) unsigned    | YES  |     | NULL    |       |
| folder_id    | int(10) unsigned    | YES  |     | NULL    |       |
| index_id     | int(10) unsigned    | YES  |     | NULL    |       |
| imap_id      | int(10) unsigned    | YES  |     | NULL    |       |
| date         | int(10) unsigned    | NO   |     |         |       |
| size         | int(10) unsigned    | NO   |     |         |       |
| volume_id    | tinyint(3) unsigned | YES  | MUL | NULL    |       |
| blob_digest  | varchar(28)         | YES  |     | NULL    |       |
| unread       | int(10) unsigned    | YES  |     | NULL    |       |
| flags        | int(11)             | NO   |     | 0       |       |
| tags         | bigint(20)          | NO   |     | 0       |       |
| sender       | varchar(128)        | YES  |     | NULL    |       |
| subject      | text                | YES  |     | NULL    |       |
| name         | varchar(128)        | YES  |     | NULL    |       |
| metadata     | text                | YES  |     | NULL    |       |
| mod_metadata | int(10) unsigned    | NO   |     |         |       |
| change_date  | int(10) unsigned    | YES  |     | NULL    |       |
| mod_content  | int(10) unsigned    | NO   |     |         |       |
+--------------+---------------------+------+-----+---------+-------+

Let's examine a single mail item.

mysql> select * from mail_item where id>26000 and mailbox_id=5247 limit 1 \G
*************************** 1. row ***************************
  mailbox_id: 5247
          id: 26001
        type: 5
   parent_id: NULL
   folder_id: 2
    index_id: 26001
     imap_id: 26001
        date: 1182380903
        size: 95842
   volume_id: 1
 blob_digest: rbrw+fj0tvyvTPt2haxssued7,A=
      unread: 1
       flags: 2
        tags: 0
      sender: sender@domain.com
     subject: Message subject
        name: NULL
    metadata: d1:f147:This message...1:p8:Re: SF: 1:s21:sender@domain.com1:vi10ee
mod_metadata: 30102
 change_date: 1182380905
 mod_content: 30102
1 row in set (0.00 sec)

With the volume_id, mailbox_id, id, and mod_content fields, you can determine the location of the message "blob" file. Volume 1 is the default message store:

mysql> select * from zimbra.volume;
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
| id | type | name     | path              | file_bits | file_group_bits | mailbox_bits | mailbox_group_bits | compress_blobs | compression_threshold |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+
|  1 |    1 | message1 | /opt/zimbra/store |        12 |               8 |           12 |                  8 |              0 |                  4096 |
|  2 |   10 | index1   | /opt/zimbra/index |        12 |               8 |           12 |                  8 |              0 |                  4096 |
+----+------+----------+-------------------+-----------+-----------------+--------------+--------------------+----------------+-----------------------+

On the filesystem, the user directories and the message directories within each user directory are split so that there are a maximum of 4096 (that is, 212) files in each directory. To determine the appropriate directory "hash" numbers, bitshift the mailbox_id and the mail item id to the right by 12 bits (i.e. divide by 212, rounding down). It is significant to note that if the id is less than 4096, the hash number will be 0.

$ perl -e 'print 5247 >> 12 ; print "\n"'
1
$ perl -e 'print 26001 >> 12 ; print "\n"'
6

The mod_content field is used to keep track of message blob file revisions. If a blob file is ever updated (for example, if a calendar appointment is modified), the filename and this field are updated. So, if the user with mailbox_id 5427 has a message on volume 1 with id 26001 and mod_content 30102, we'll see it like this:

$ ls -l /opt/zimbra/store/1/5247/msg/6/26001-30102.msg
-rw-r-----  1 zimbra zimbra 95842 Jun 20 16:08 /opt/zimbra/store/1/5247/msg/6/26001-30102.msg
Jump to: navigation, search