On Nov 5, 2003, at 8:10 PM, Matthew T. O'Connor wrote:
Eric Soroos wrote:
Maybe it's just my setup, but I've found that synching a 18k item
mailbox gets me a message or two a seconds. Smaller mailboxes are
damn fast.
I'm on debian/stable, updated as of yesterday, running postgresql.
The client is mail.app.
Have you added all the recommended indexes? I use RH9, dbmail 1.1,
postgresql 7.3 and am quite fast with 25k+ messages per mailbox.
Yes, I have. And some interesting functional ones too. But they don't
help in this case. There are really 3 problems:
1) It's hitting db_getmailbox for every 2nd email message.
2) db_getmailbox runs a query that sorts all items in a mailbox by
message_idnr and returns 18k rows from pg to dbmail.
3) For some combinations of mailbox size vs overall message store size,
this is going to be a table scan.
Now, I'm suspecting that there are cases when you want a list of all of
the message_idnrs in a folder. However, I doubt that responding to a [#
uid fetch [headers]] command is one of them. This is the core problem,
since if you only run this query once, it only takes 800msec. It's
just that 800msec * 18k/2 is a big number.
As for 3, if your mailbox is more than 1/4-1/5 of the total # of
messages, you're going to get a table scan since it's faster to do the
disk accesses in order rather than one for the index, one for the row.
I managed to speed it up quite a bit by bumping up my sort_mem to 4096
and putting the buffers up to 2048 in postgresql.conf, (I hadn't
adjusted when I added a stick of memory) but now I'm redlining the proc
when I do this. It's possible that since all of this fits in cache, I
should just try disabling sequential scans for this query and see how
it works, but that's not really a good solution in the long run.
(the query of interest is select message_idnr, readflag, unreadflag,
from messages where uniqueid!='' and mailbox=# and status<2 order by
message_idnr asc)
I think I'd better figure out how different I am from 1.2.1, then patch
and run from there, since I think I'm at a snapshot as of 9/3/03.
eric
