Database tips

Installing and Upgrading

DBMail automatically creates and updates all required tables.

If you run a large installation you may prefer to test and update the schema manually. You can find the create table and upgrade scripts in the sql/database/upgrade folder on GitHub for each database, for example the Postgres scripts can be found here.

The character set used for tables and DBMail must be the same. The default of utf-8 is probably suitable for most installations

[DBMAIL]
encoding = utf8

Monitoring

DBMail works out of the box for most installations. Each site has it's own usage patterns and there are differences between databases. To help monitor how long queries take to run there are a few setting in dbmail.conf for generating log entries for the time to execute long queries:

[DBMAIL]
query_time_info    = 10
query_time_notice  = 20
query_time_warning = 30

The indexes created by DBMail are suitable for most installations. There are differences between databases, for example PostgreSQL uses the primary key in the the following sql whereas MySQL does not.

SELECT max(message_idnr)+1 FROM dbmail_messages WHERE mailbox_idnr=71

Adding an index might work, but tests have so far proved inconclusive, YMMV.

Using sql explain analyse on queries flagged up with log entries should help resolve any inefficiencies.

MySQL Table type

Although you can use any table type, to ensure data integrity it's preferable to use one that enforces constraints such as innodb.

MySQL Attachment Size Tuning

DBMail sends updates to the server in one go and the limit is defined by the configuration maximum_allowed_packet=64M or command line --max_allowed_packet=128M. Recent defaults are usually okay, you can check with the following sql:

show variables like 'max_allowed_packet';

If you get an ER_NET_PACKET_TOO_LARGE the attachment will not have been saved.

MySQL Reclaiming space

As emails are added and deleted there can be table fragmentation. It's recommended to set innodb_file_per_table so that space in tables can be reclaimed more easily.