The following issue has been RESOLVED.
======================================================================
http://www.dbmail.org/mantis/view.php?id=539
======================================================================
Reported By: paul
Assigned To: paul
======================================================================
Project: DBMail
Issue ID: 539
Category: IMAP daemon
Reproducibility: always
Severity: minor
Priority: normal
Status: resolved
target:
Resolution: fixed
Fixed in Version: 2.2.5
======================================================================
Date Submitted: 17-Mar-07 16:42 CET
Last Modified: 23-Mar-07 13:53 CET
======================================================================
Summary: Mailclient times out opening large mailboxes
Description:
When a mailclient like Thunderbird opens a large mailfolder with many
unread messages an error is returned to the user saying that opening the
folder takes too long.
======================================================================
----------------------------------------------------------------------
cmayo - 19-Mar-07 20:27
----------------------------------------------------------------------
I see the same timeouts with 2.2.4. Analysing the Postgresql 8.1.8 logs
with pgfouine updating the recent flags with:
update dbmail_messages set recent_flag = 0 where message_idnr in
is taking a lot of time.
----------------------------------------------------------------------
paul - 19-Mar-07 22:11
----------------------------------------------------------------------
Interesting.
As part of this bug I'm actually working on avoiding long-running queries
against the envelope and header caches. Such queries shouldn't scale 1/n
with the number of messages in a mailbox before returning some data to the
client.
At first glance however, your case smells like a different beast.
Why is postgres taking so long to run the update query that flushes recent
flags?
I assume that postgres doesn't suffer from locking issues due to mvcc.
Right?
Are you sure this is the query that is triggering timeouts in the client?
----------------------------------------------------------------------
cmayo - 19-Mar-07 23:20
----------------------------------------------------------------------
Sorry, looks like it was my database getting stale. It did appear to be the
updates activity that were slowing the connection - I could see them by
doing tail -f on the log file, and they would continue for a while even
after the client had timed out.
However, I'd done a vacuum analyze and a vacuum full on my database and it
seems the solution was:
reindex table dbmail_messages
----------------------------------------------------------------------
cmayo - 21-Mar-07 20:34
----------------------------------------------------------------------
Well, performance drops off again and another reindex speeds it up for
while and then it slows. May well be a problem with my database, which I
will keep looking for.
I ended up taking drastic action and patching dbmail-imapsession.c:
@@ -1764,7 +1764,7 @@
int dbmail_imap_session_mailbox_update_recent(struct ImapSession *self)
{
- imap_session_update_recent(self->recent);
+ /* imap_session_update_recent(self->recent); */
g_list_destroy(self->recent);
self->recent = NULL;
Hopefully this hasn't broken anything significant.
----------------------------------------------------------------------
paul - 21-Mar-07 21:44
----------------------------------------------------------------------
The plot thickens then. Removing that call will break rfc compliance since
the recent flag for a message must (not should) be cleared upon first
accessing a message (fetch) in a read/write selected mailbox.
Makes /me/ wonder about why postgres needs reindexing at all?? I would
wager that simply updating a int2 field shouldn't require such manual
intervention. Don't you have pg_autovacuum running?
I can, and should in fact wrap the whole sequence of update queries in a
transaction, which will most likely improve performance, but I doubt it
will influence the key autoindexing.
Perhaps some input from an avid postgres user is warrented.
----------------------------------------------------------------------
cmayo - 22-Mar-07 22:43
----------------------------------------------------------------------
An interesting post at:
http://postgis.refractions.net/pipermail/postgis-users/2006-December/014073.html
says that when Postgresql UPDATES a row it duplicates it (for MVCC to
work).
I did some experimentation using:
select pg_total_relation_size( 'messages_pkey' );
to monitor the size of the index and the updates are causing it to
increase in size rapidly. vacuum doesn't actually seem to shrink it by
this measure (vacuum full actually increases it)
So, updating thousands of rows in one go doesn't seem like a good idea for
performance. It would be nice not to call update at all for rows that
already have recent_flag = 0 but just adding:
and recent_flag <> 0
to the update in dbmail-imapsession.c speeds things up massively for me.
----------------------------------------------------------------------
paul - 23-Mar-07 13:53
----------------------------------------------------------------------
fixed in rev 2474
Issue History
Date Modified Username Field Change
======================================================================
17-Mar-07 16:42 paul New Issue
17-Mar-07 16:42 paul Status new => assigned
17-Mar-07 16:42 paul Assigned To => paul
19-Mar-07 20:27 cmayo Note Added: 0001927
19-Mar-07 20:28 cmayo Issue Monitored: cmayo
19-Mar-07 22:11 paul Note Added: 0001928
19-Mar-07 23:20 cmayo Note Added: 0001929
21-Mar-07 20:34 cmayo Note Added: 0001933
21-Mar-07 21:44 paul Note Added: 0001934
22-Mar-07 22:43 cmayo Note Added: 0001938
22-Mar-07 22:44 cmayo File Added: reset_recent_check_not_zero.patch
23-Mar-07 13:53 paul Note Added: 0001952
23-Mar-07 13:53 paul Status assigned => resolved
23-Mar-07 13:53 paul Resolution open => fixed
23-Mar-07 13:53 paul Fixed in Version => 2.2.5
23-Mar-07 13:53 paul Additional Information Updated
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://twister.fastxs.net/mailman/listinfo/dbmail-dev
