Mailing List Archive: DBMail: users

Performance, IMAP backend, etc.

Index | Next | Previous | View Threaded

ken at jots

Jan 21, 2009, 7:22 AM

Post #1 of 13 (2297 views)
Permalink

Performance, IMAP backend, etc.

Hi, all. I've got a folder with over 100K messages in it. I'm using
Squirrelmail as the front-end (with server-side searching enabled) and --
obviously -- dbmail as the backend. When I search for (say) a message
with "foo" in the subject, it takes about 30 seconds to return the hits.
However, if I go into mysql, and do a

select * from dbmail_subjectfield where subjectfield like "foo";

it takes just over a third of a second. I understand that there are
probably joins, etc., going on in the background, but it still strikes me
that that probably doesn't explain a two-orders-of-magnitude disparity.
Suggestions on where (or how) to begin troubleshooting?

Thanks!

-Ken

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

jake at vapourforge

Jan 21, 2009, 8:19 AM

Post #2 of 13 (2210 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Ken D'Ambrosio wrote:
> Hi, all. I've got a folder with over 100K messages in it. I'm using
> Squirrelmail as the front-end (with server-side searching enabled) and --
> obviously -- dbmail as the backend. When I search for (say) a message
> with "foo" in the subject, it takes about 30 seconds to return the hits.
> However, if I go into mysql, and do a
>
> select * from dbmail_subjectfield where subjectfield like "foo";
>
> it takes just over a third of a second. I understand that there are
> probably joins, etc., going on in the background, but it still strikes me
> that that probably doesn't explain a two-orders-of-magnitude disparity.
> Suggestions on where (or how) to begin troubleshooting?
>
> Thanks!
>
> -Ken
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
turn on the slow query log in mysql and see what turns up
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

shane at time-travellers

Jan 21, 2009, 8:42 AM

Post #3 of 13 (2211 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Ken,

On Wed, 2009-01-21 at 10:22 -0500, Ken D'Ambrosio wrote:
> Hi, all. I've got a folder with over 100K messages in it. I'm using
> Squirrelmail as the front-end (with server-side searching enabled) and --
> obviously -- dbmail as the backend. When I search for (say) a message
> with "foo" in the subject, it takes about 30 seconds to return the hits.
> However, if I go into mysql, and do a
>
> select * from dbmail_subjectfield where subjectfield like "foo";
>
> it takes just over a third of a second. I understand that there are
> probably joins, etc., going on in the background, but it still strikes me
> that that probably doesn't explain a two-orders-of-magnitude disparity.
> Suggestions on where (or how) to begin troubleshooting?

Perhaps try:

select * from dbmail_subjectfield where subjectfield like "%foo%";

The query you did is a simple check for equality, not to see if "foo" is
present in the subject.

Cheers,

--
Shane

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

ken at jots

Jan 21, 2009, 9:23 AM

Post #4 of 13 (2210 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

> select * from dbmail_subjectfield where subjectfield like "%foo%";
>
> The query you did is a simple check for equality, not to see if "foo" is
> present in the subject.

Yeah, I realized my mistake after (of course) I sent the e-mail, but using
wildcards only doubled the time, so it's still well under a second. An
interesting datapoint, though, is that I've removed Squirrelmail from the
equation -- issued the IMAP commands directly. Still takes a while --
about ten seconds -- but shaves an easy 20+ seconds off the time the
client took, so now, at least, we're almost in the same ballpark.

Which, I guess, means that I'm now wondering if there are clients out
there that are better at this. I'm looking for a client that:
1) Does *not* cache IMAP information locally
2) Preferably is web-based, but Linux and (sigh) Windows would work.

I've tried kmail, Outlook, and Evolution, and they all cache the damn
messages (or, at least, the headers) locally. I want the client to
leverage dbmail, here, and it's irksome to have the client think it knows
better. Anyone know a client that doesn't cache (or, at least, has the
option not to)?

Thanks,

-Ken

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

aleksander at krediidiinfo

Jan 21, 2009, 11:43 AM

Post #5 of 13 (2213 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Ken D'Ambrosio wrote:
> I've tried kmail, Outlook, and Evolution, and they all cache the damn
> messages (or, at least, the headers) locally. I want the client to
> leverage dbmail, here, and it's irksome to have the client think it knows
> better.

Why do you think so? What's wrong with local caching? Or is this for
testing the mail server?

Regards,

--

Aleksander Kamenik
System Administrator
Krediidiinfo AS
an Experian Company
Phone: +372 665 9649
Email: aleksander [at] krediidiinfo

http://www.krediidiinfo.ee/
http://www.experiangroup.com/
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

ken at jots

Jan 21, 2009, 12:56 PM

Post #6 of 13 (2206 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

> Ken D'Ambrosio wrote:
>> I've tried kmail, Outlook, and Evolution, and they all cache the damn
>> messages (or, at least, the headers) locally. I want the client to
>> leverage dbmail, here, and it's irksome to have the client think it
>> knows better.
>
> Why do you think so? What's wrong with local caching? Or is this for
> testing the mail server?

Well, if it's caching locally, presumably it's doing this because it
thinks it can (among other things) search more quickly if it does it
against locally cached data. Aside from bringing my local system to its
knees, it totally ignores the advantages of having a database backend for
your mail server. On top of this, I flit from machine to machine -- I'd
prefer not to have a heavy-weight client have to follow me. While I'd
really prefer a web-based client (about as lightweight as it gets), I'll
take a locally-installed one if need be... but 100K+ messages (or message
headers) is pretty much the diametric opposite of "lightweight."

So that, in a nutsehll, is the answer. ;-) Make more sense now?

I mean, maybe I'm missing something here, but it seems to me that a huge
part of the reason for dbmail to even exist is to take advantage of the
fact that it can do the heavy lifting for you. Not just storage, which is
good, but also having an actual database engine capable of doing searches
through hundreds of thousands of messages in the blink of an eye. With
power like that, all you really need locally is a front-end. No?

-Ken

> Regards,
>
> --
>
> Aleksander Kamenik
> System Administrator
> Krediidiinfo AS
> an Experian Company
> Phone: +372 665 9649
> Email: aleksander [at] krediidiinfo
>
> http://www.krediidiinfo.ee/
> http://www.experiangroup.com/
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

jake at vapourforge

Jan 21, 2009, 5:19 PM

Post #7 of 13 (2204 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Ken D'Ambrosio wrote:
>> Ken D'Ambrosio wrote:
>>
>>> I've tried kmail, Outlook, and Evolution, and they all cache the damn
>>> messages (or, at least, the headers) locally. I want the client to
>>> leverage dbmail, here, and it's irksome to have the client think it
>>> knows better.
>>>
>> Why do you think so? What's wrong with local caching? Or is this for
>> testing the mail server?
>>
>
> Well, if it's caching locally, presumably it's doing this because it
> thinks it can (among other things) search more quickly if it does it
> against locally cached data. Aside from bringing my local system to its
> knees, it totally ignores the advantages of having a database backend for
> your mail server. On top of this, I flit from machine to machine -- I'd
> prefer not to have a heavy-weight client have to follow me. While I'd
> really prefer a web-based client (about as lightweight as it gets), I'll
> take a locally-installed one if need be... but 100K+ messages (or message
> headers) is pretty much the diametric opposite of "lightweight."
>
> So that, in a nutsehll, is the answer. ;-) Make more sense now?
>
> I mean, maybe I'm missing something here, but it seems to me that a huge
> part of the reason for dbmail to even exist is to take advantage of the
> fact that it can do the heavy lifting for you. Not just storage, which is
> good, but also having an actual database engine capable of doing searches
> through hundreds of thousands of messages in the blink of an eye. With
> power like that, all you really need locally is a front-end. No?
>
> -Ken
>
>
>
squirrelmail?

marc at electronics-design

Jan 22, 2009, 12:24 AM

Post #8 of 13 (2192 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

> I've tried kmail, Outlook, and Evolution, and they all cache the damn
> messages (or, at least, the headers) locally. I want the client to
> leverage dbmail, here, and it's irksome to have the client think it
> knows better.

Have you tried Mutt?
>
> So that, in a nutsehll, is the answer. ;-) Make more sense now?

The caching is intentional. IMAP RFC3501 (as I understand it) encourages
caching of messages localy. That's what UIDVALIDITY and UID are for.

Marc
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

arnt at gulbrandsen

Jan 22, 2009, 3:59 AM

Post #9 of 13 (2188 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Marc Dirix writes:
> Have you tried Mutt?

Mutt caches a lot of header fields. As soon as it opens a mailbox, it
reads all that stuff, no matter whether it'll need it. Opening my inbox
with mutt downloads about half a megabyte of header fields, and usually
just a few k of that is ever displayed or used in any other way.

The original poster might try pine or a webmail program.

Arnt
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

dan at entropy

Jan 22, 2009, 12:33 PM

Post #10 of 13 (2186 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

On Wed, 21 Jan 2009 15:56:11 -0500 (EST), Ken D'Ambrosio <ken [at] jots>
wrote:

> With power like that, all you really need locally is a front-end. No?

I use RoundCube ( http://roundcube.net/ ) . It caches things in a database
- either MySQL or Postgres. But it also has an option to disable caching (
which I haven't investigated ). Searching is relatively fast, though I
haven't really tested it against anything else. RoundCube is also one of
the best looking email clients I've seen.

Dan

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

dan at entropy

Jan 22, 2009, 4:07 PM

Post #11 of 13 (2184 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

On Fri, 23 Jan 2009 01:35:32 +0100, Andrea Brancatelli wrote:

> I have big problems using roundcube with DBMail. Haven't you ever
experienced hangs in apache?

Only when using the Zend PHP engine ( had to check it out for a customer ).
Zend was doing some pretty strange things, including hanging in roundcube,
crashing, and screwing with my MySQL setup. I removed it and everything's
fine again ... apart from my mysql socket file is still moved and
everything refuses to look in the correct place for it.

The only issue I have with roundcube is when people send me HTML email (
yours for example ). Sometimes it works well; others not. I haven't updated
to the latest version yet, so this might be fixed somewhat.

Dan

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

andrea at brancatelli

Jan 22, 2009, 4:35 PM

Post #12 of 13 (2193 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

Il giorno ven, 23/01/2009 alle 07.33 +1100, Dan ha scritto:

> > With power like that, all you really need locally is a front-end. No?
> I use RoundCube ( http://roundcube.net/ ) . It caches things in a database

I have big problems using roundcube with DBMail. Haven't you ever
experienced hangs in apache?

aaron at serendipity

Jan 22, 2009, 9:53 PM

Post #13 of 13 (2188 views)
Permalink

Re: Performance, IMAP backend, etc. [In reply to]

On Fri, 2009-01-23 at 01:35 +0100, Andrea Brancatelli wrote:
> Il giorno ven, 23/01/2009 alle 07.33 +1100, Dan ha scritto:
>
> > > With power like that, all you really need locally is a front-end. No?
> > I use RoundCube ( http://roundcube.net/ ) . It caches things in a database
>
> I have big problems using roundcube with DBMail. Haven't you ever
> experienced hangs in apache?

I found RC 0.1 to be unusable with DBMail 2.2, but RC 0.2 works well for
me.

Aaron

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads