gmane.mail.imap.dbmail

Re: Indices in MySQL Schema

Pavlo Lavrenenko — 2013-05-21T14:55:15

Sorry, UID SORT IMAP request

Re: Indices in MySQL Schema

Pavlo Lavrenenko — 2013-05-21T12:10:29

On 05/15/2013 04:38 PM, Jure Pečar wrote:

Hi. The problem is in a `ORDER BY' statement. Basically when the results 
are ordered by `message_idnr' field only mysql hits the index both in 
`WHERE' and `ORDER BY' statements any query works smooth. With complex 
`ORDER BY' statements, even if dbmail_headervalue.sortfield would have 
an index, query will remain slow.

You can find out more at 
http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html :

 > In some cases, MySQL cannot use indexes to resolve the ORDER BY,
 > although it still uses indexes to find the rows that match the WHERE
 > clause. These cases include the following:
 >
 >    You use ORDER BY on different keys:
 >
 >    SELECT * FROM t1 ORDER BY key1, key2;

I would also note that this performance issue will reappear every time 
someone sends SEARCH IMAP request with sort parameter or uses
dbmail-export utility (see src/dbmail-mailbox.c::_handle_sort_args() 
function). And I don't see how this could be overcomed.

Re: Indices in MySQL Schema

Pavlo Lavrenenko — 2013-05-20T15:44:59

On 05/16/2013 12:58 AM, Jure Pečar wrote:

Still could you please dump your dbmail mysql scheme (no data needed, 
structure only)? Because dbmail_messages.sortfield is confusing and 
fails being executed manually:

ERROR 1054 (42S22): Unknown column 'sortfield' in 'order clause'.

$ mysqldump --create-options --no-data --add-drop-table --comments 
--default-character-set=utf8 dbmail-database

should be of help.

500 Sender already received. Use RSET to clear.

Jack Bates — 2013-05-17T15:18:46

I'm running DBMail from Git (e10143b6804824bffc20752251578de734a01df8) 
and I'm getting the following error whenever I try to deliver a message 
with LMTP. Can you spot what I'm doing wrong, or can I do anything to 
help fix this? Would any more details be helpful?

    ubuntu< at >ip-10-242-78-228:~$ telnet localhost 24
    Trying 127.0.0.1...
    Connected to localhost.
    Escape character is '^]'.
    220 ip-10-242-78-228 LMTP
    LHLO example.com
    250-ip-10-242-78-228
    250-PIPELINING
    250-ENHANCEDSTATUSCODES
    250 SIZE
    MAIL FROM:<alice< at >example.com>
    500 Sender already received. Use RSET to clear.

Re: DBMail wiki spam?

Thomas Raschbacher — 2013-05-17T09:17:15

ah good point thanks for reminding me .. I actually posted about that 
too then .. ;)

Re: DBMail wiki spam?

Robert C. Sheets — 2013-05-17T08:39:22

There was a recent thread about this same topic and replacing the wiki
software. I think Paul was considering Confluence.

Did anything ever come of that?

DBMail wiki spam?

Thomas Raschbacher — 2013-05-17T06:31:12

Hi.

I just was going to look at the wiki and see if the dbmail upgrade 
instructions for 2.2 -> 3.0 need to be improved, but I found this on the 
wiki start page:

Floor lamps will be the latest bright idea in design with fun new 
models. Whatever lighting solution you have to enhance your indoor or 
outdoor living, choose fashionable lamps any room can be illuminated by 
you with a touch of charm. Floor lamps

Looking at http://www.dbmail.org/dokuwiki/doku.php/start?do=revisions 
it seems there's been quite a bit of this spam recently.

Maybe remove those users and put some extra checks in place ? (and/or 
only allow certain users/groups to edit things like the start page too 
...) -- or make changes to existing - and important - pages to be 
moderated? (I could help out here if needed)

Regards

Re: Upgrade from 2.2.1 to 3.0.2

Curtis Maurand — 2013-05-16T19:46:48

Thank you.  I will do that.  I have a machine to test on now that I have 
gotten to the point where i can get it to build.  It's also a pretty 
large database at this point.

--Curtis

Re: Error message from dbmail-util (invalid byt esequence for encoding)

Thomas Raschbacher — 2013-05-16T17:01:53

Hi.

so I got around to look this up now finally. here's what I found in the
attachment.

Please let me know if you need anything else.

Regards

<quote who="Paul J Stevens">

Re: Upgrade from 2.2.1 to 3.0.2

Thomas Raschbacher — 2013-05-16T08:44:02

All I did when I upgrade from 2.2.x to 3.0 was run the 2_2-3_0 upgrade.

all 2.3 release were developer releases therefor you have no reason to 
apply those.

As always try this on another machine with a copy of the db first ;)

On 2013-05-15 18:58, Curtis Maurand wrote:

Re: Indices in MySQL Schema

Jure Pečar — 2013-05-15T21:58:42

On Wed, 15 May 2013 17:47:25 +0300
Pavlo Lavrenenko <santa< at >portaone.com> wrote:


I have, but it's not really suitable for performance tests.
 

3.0.2 + some fixes from master for roundcube sort ordering. And no, I don't
see dbmail_messages.sortfield either.


Yes, I was looking at that (mail client will break lines; some
reformatting needed):

+----+-------------+-------+--------+----------------------------------------+-------------------+---------+-------------------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                          | key               | key_len | ref                                 | rows | Extra                                        |
+----+-------------+-------+--------+----------------------------------------+-------------------+---------+-------------------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | m     | range  | mailbox_status

Upgrade from 2.2.1 to 3.0.2

Curtis Maurand — 2013-05-15T16:58:47

I'm working on an upgrade from 2.2.1 to 3.0.2  I know, I should have 
done this a while ago, but I've been running Ubuntu and have not been 
able to get it to compile, nor are there any version out there in a 
repository (at least not a repository that I know about.).

Anyway, the wiki documentation is non existent on upgrade instructions.  
Is there a document out there about how to upgrade the database?  Do I 
need to run the scripts incrementally.  beyond the database upgrade the 
rest of it should be "make install"

Thanks in advance,
Curtis

Re: Ubuntu 12.04

Curtis Maurand — 2013-05-15T16:40:31

That worked like a charm.  I finally got it to compile.

Thanks again,
--Curtis

Re: Ubuntu 12.04

Curtis Maurand — 2013-05-15T16:08:43

Thank you.  I'll install those.

--Curtis

On 5/15/2013 2:19 AM, Pavlo Lavrenenko wrote:

Re: Indices in MySQL Schema

Pavlo Lavrenenko — 2013-05-15T14:47:25

On 05/15/2013 04:38 PM, Jure Pečar wrote:

Oh, you should have a staging system for such changes to be tested first.

Also I don't see see dbmail_messages.sortfield field in a schema I am 
using. Neighter do I see that field in dbmail-3.0.2 src/dbmail-mailbox.c.

What version of dbmail do you use? Can you attach your mysql db schema? 
Can you provide the output of:

DESCRIBE SELECT message_idnr FROM dbmail_messages m LEFT JOIN 
dbmail_physmessage p ON m.physmessage_id=p.id LEFT JOIN dbmail_datefield 
ON m.physmessage_id=dbmail_datefield.physmessage_id WHERE m.mailbox_idnr 
= 3733 AND m.status IN (0,1) ORDER BY sortfield,message_idnr;

query?

Re: Indices in MySQL Schema

Jure Pečar — 2013-05-15T13:38:21

On Mon, 25 Mar 2013 17:41:42 +0200
Pavlo Lavrenenko <santa< at >portaone.com> wrote:


I applied these to our production mysql (and dropped those you recomended in the attached files) and immediately noticed about 10x drop in Innodb row operations.

However looking at slow query log I still see some queries pop up often. This one is the most common:

SELECT message_idnr FROM dbmail_messages m LEFT JOIN dbmail_physmessage p ON m.physmessage_id=p.id LEFT JOIN dbmail_datefield ON m.physmessage_id=dbmail_datefield.physmessage_id WHERE m.mailbox_idnr = 3733 AND m.status IN (0,1) ORDER BY sortfield,message_idnr;

These take anywhere from 2 to 15 seconds to complete. 

Can you recommend additional indexing statements to speedup these?

Re: Ubuntu 12.04

Pavlo Lavrenenko — 2013-05-15T06:19:40

Is that a configure script tries to pull in libzdb-dev? I am using 12.04 
and I don't have any *zdb* package available in repository.

I suppose you are trying to use http://debian.nfgd.net/debian repository
Please remove that repo and try using libzdb available at launchpad 
https://launchpad.net/+search?field.text=libzdb

Ubuntu 12.04

Curtis Maurand — 2013-05-14T18:57:40

Has anyone gotten dbmail to successfully build in Ubuntu 12.04.  I keep 
getting stupid stuff like:

And I'm still trying to get "configure" to finish.

sudo ./configure --prefix=/usr

The following packages have unmet dependencies:
  libzdb-dev : Depends: libzdb7 (= 2.9-1) but it is not going to be 
installed
E: Unable to correct problems, you have held broken packages.
cmaurand< at >canon:~/dbmail-3.0.2$ sudo apt-get install libzdb-dev libzdb7
Reading package lists... Done
Building dependency tree
Reading state information... Done
Some packages could not be installed. This may mean that you have
requested an impossible situation or if you are using the unstable
distribution that some required packages have not yet been created
or been moved out of Incoming.
The following information may help to resolve the situation:

The following packages have unmet dependencies:
  libzdb7 : Depends: libmysqlclient16 (>= 5.1.50-1) but it is not 
installable
E: Unable to correct problems, you have held broken packages.

Any he

Debian Wheezy Packages

Joel van Velden — 2013-05-08T01:51:07

Hi,

Now Debian 7 "Wheezy" is out, please could wheezy packages be made 
available on http://debian.nfgd.net/ ?

-Joel van Velden

Re: Error message from dbmail-util (invalid byt esequence forencoding)

Thomas Raschbacher — 2013-05-06T18:10:21

Hi.

I am using dbmail 3.0.2 and gmime 2.4.32.

currently trying to figure out how to get the message id. How can I 
best find it ?

Regards

On 2013-05-06 11:24, Paul J Stevens wrote:
_______________________________________________
DBmail mailing list
DBmail< at >dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Re: Error message from dbmail-util (invalid byt esequence forencoding)

Paul J Stevens — 2013-05-06T09:24:27

Thomas,

Please provide if you can:

- the raw Message-ID for the message involved.
- your dbmail and gmime versions.

DBMail already tries very hard to always provide the database with a
correct encoded string. It does rely on GMime however to provide UTF8
encoded representations of any message fed. Maybe a bug in GMime is
exposed here.


On 05/05/2013 10:39 PM, Thomas Raschbacher wrote: