Adding a MariaDB index
In order to speed up some common Carbonio operations that occur frequently, it is feasible to add an index to the MariaDB database that saves emails when their volume reaches a certain threshold.
In fact, the Coherency Check feature of Carbonio Backup and the doCheckBlobs feature of Carbonio Storages both check to see whether the hash of the files matches the one stored in the database and issue a warning if they don’t match, with the option to change the incorrect one for Carbonio Backup if necessary.
Both jobs work on a subset of data and restrict the amount of items altered by filtering the data by mailbox_id and locator of the mail_item table in order to avoid and minimise any data loss in the event that a problem develops.
As a result, these two procedures might become database-intensive and extremely sluggish, to the point that they affect the entire inbox and are noticeable to all users.
Since Carbonio 23.1.0, you may use a script that is supplied with Carbonio to add an index to the locator field, enhancing efficiency. Just execute the script, and you’re done. To add index to a location, follow these steps:
- Join
zextrasas a user.# su - zextras - Head over to the scripts directory.
zextras$ cd /opt/zextras/libexec/scripts - Implement the script:
zextras$ perl -I. migrate20221110-AddIndexLocatorOnMailItem.pl
You can confirm that the mail_item table now has the new index from the MariaDB prompt after you’re finished.
MariaDB> SHOW INDEXES FROM mail_item;
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+
| ... | .. | .. | .. | ... | . | .. | .... | ... | ... | ..... |
| mail_item | 1 | i_locator | 2 | locator | A | 2 | 255 | NULL | YES | BTREE |
+-----------+------------+------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+