Could you set the tracelevel to 5 and send the exact imap commands mail.app is generating? This way we might be able to speed things up a bit.
Attached as text.
I've also noticed that after every inserted message, there is one query per folder on my mailbox to determine the total size of my account. Setting aside the fact that I don't have a quota on my account, there's no possible need run 20 queries when one join would do. e.g. select sum(messages.rfcSize) from messages inner join mailboxes using (mailbox_idnr) where mailbox.owner_idnr='me'. It would still be a table scan, but at least it would just be one table scan. (see db_check_sizelimit)Well, this problem is being addressed in 2.0 - it uses a new field in the user table which keeps track of the current amount of mail in all the folders for a certain user. This enables us to determine the current mailbox size with one real fast query. The reason we currently use a number of queries instead of one JOINed is performance; we have encountered numerous cases where looping simple queries performs way better than having the sql backend joining tables (though i'm not completely sure this is such a case ;-)
I'm seeing about 20% better performance with one query, but that may just be a combination of measurement error and my particular mailbox size distribution. Scratch that, I'm seeing performance all over the map, 100% variation on one query, even given the same plan. Must be that I'm testing on my live server.
A couple of notes on this though, I think pgsql will be happier if the frequently updated rows are in their own table, e.g. last login date, sum(mailboxsize) rather than thrown in the user table. If not, then you're going to need very frequent vacuums on those tables as they're going to fill with superceeded tuples. That probably goes for mailbox flags as well.
Is it possible to just not calculate quotas for people who have maxmail set to 0? (or rather, it is easy?) Is it really this easy?
in dbpgsql.c:
from:
u64_t db_check_sizelimit (u64_t addblocksize UNUSED, u64_t messageidnr,
u64_t *useridnr)
{
/* returns -1 when a block cannot be inserted due to dbase failure
* 1 when a block cannot be inserted due to quotum exceed
* -2 when a block cannot be inserted due to quotum exceed
and a dbase failure occured
* also does a complete rollback when this occurs
* returns 0 when situation is ok
*/
u64_t currmail_size = 0, maxmail_size = 0, j, n;
*useridnr = db_get_useridnr (messageidnr);
/* checking current size */
to:
u64_t db_check_sizelimit (u64_t addblocksize UNUSED, u64_t messageidnr,
u64_t *useridnr)
{
/* returns -1 when a block cannot be inserted due to dbase failure
* 1 when a block cannot be inserted due to quotum exceed
* -2 when a block cannot be inserted due to quotum exceed
and a dbase failure occured
* also does a complete rollback when this occurs
* returns 0 when situation is ok
*/
u64_t currmail_size = 0, maxmail_size = 0, j, n;
*useridnr = db_get_useridnr (messageidnr);
/* now check the maxsize for this user */
maxmail_size = auth_getmaxmailsize(*useridnr);
if (maxmail_size ==0 )
{
return 0;
}
/* checking current size */
eric
dbmail-trace
Description: application/applefile
dbmail-trace
Description: Binary data
