Mailing List Archive: DBMail: users

dbmail3: hughe performance problem

1 2

View All

Index | Next | Previous | View Threaded

h.reindl at thelounge

Feb 1, 2012, 10:04 AM

Post #1 of 27 (547 views)
Permalink

dbmail3: hughe performance problem

uhm this query is a killer!

happens after first horde-wbemail login on the snapshoted test-machine
with full transition done in offline mode

better said: it happens while the first login because timeout finally

(around 67.000 messages with 10 GB)
__________________________

| 135 | dbmail | localhost | dbmail | Execute | 453 | Copying to tmp table | SELECT DISTINCT m.message_idnr FROM
dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_i
__________________________

dbmail_mimeparts | CREATE TABLE `dbmail_mimeparts` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`hash` char(128) NOT NULL,
`data` longblob NOT NULL,
`size` bigint(20) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `hash` (`hash`)
) ENGINE=InnoDB AUTO_INCREMENT=239624 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED

dbmail_partlists | CREATE TABLE `dbmail_partlists` (
`physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_header` tinyint(1) NOT NULL DEFAULT '0',
`part_key` smallint(6) NOT NULL DEFAULT '0',
`part_depth` smallint(6) NOT NULL DEFAULT '0',
`part_order` smallint(6) NOT NULL DEFAULT '0',
`part_id` bigint(20) unsigned NOT NULL DEFAULT '0',
UNIQUE KEY `message_parts` (`physmessage_id`,`part_key`,`part_depth`,`part_order`),
KEY `physmessage_id` (`physmessage_id`),
KEY `part_id` (`part_id`),
CONSTRAINT `dbmail_partlists_ibfk_1` FOREIGN KEY (`physmessage_id`) REFERENCES `dbmail_physmessage` (`id`) ON
DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `dbmail_partlists_ibfk_2` FOREIGN KEY (`part_id`) REFERENCES `dbmail_mimeparts` (`id`) ON DELETE
CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
--

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 1, 2012, 11:57 AM

Post #2 of 27 (534 views)
Permalink

Re: dbmail3: hughe performance problem [In reply to]

On 02/01/2012 07:04 PM, Reindl Harald wrote:
> uhm this query is a killer!
>
> happens after first horde-wbemail login on the snapshoted test-machine
> with full transition done in offline mode
>
> better said: it happens while the first login because timeout finally
>
> (around 67.000 messages with 10 GB)
> __________________________
>
> | 135 | dbmail | localhost | dbmail | Execute | 453 | Copying to tmp table | SELECT DISTINCT m.message_idnr FROM
> dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_i

That query looks truncated. Could you please fetch the full query and
run it with 'explain'?

Actually, if you use file_logging_level=511 you can obtain both the full
query and the IMAP command that triggers it.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 1, 2012, 12:06 PM

Post #3 of 27 (533 views)
Permalink

Re: dbmail3: hughe performance problem [In reply to]

Am 01.02.2012 20:57, schrieb Paul J Stevens:
> On 02/01/2012 07:04 PM, Reindl Harald wrote:
>> uhm this query is a killer!
>>
>> happens after first horde-wbemail login on the snapshoted test-machine
>> with full transition done in offline mode
>>
>> better said: it happens while the first login because timeout finally
>>
>> (around 67.000 messages with 10 GB)
>> __________________________
>>
>> | 135 | dbmail | localhost | dbmail | Execute | 453 | Copying to tmp table | SELECT DISTINCT m.message_idnr FROM
>> dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_i
>
> That query looks truncated. Could you please fetch the full query and
> run it with 'explain'?
>
> Actually, if you use file_logging_level=511 you can obtain both the full
> query and the IMAP command that triggers it.

sorry for the dumb question but how can i get the untruncated?
this is from "show processlist" and had also the feeling that it
may be truncated but phpMyAdmin showed the same

"part_i" should be "part_id" -> C&P mistake

hopefully i have later at night or tomorrow energy/time to
switch the snapshot for analyze, currently the VM is running
again as replication/backup-slave - i love VMware :-)

Attachments:

signature.asc (0.26 KB)

admin at fullspace

Feb 1, 2012, 10:04 PM

Post #4 of 27 (535 views)
Permalink

Re: dbmail3: hughe performance problem [In reply to]

On 02.02.2012 00:06, Reindl Harald wrote:
>
> Am 01.02.2012 20:57, schrieb Paul J Stevens:
>> On 02/01/2012 07:04 PM, Reindl Harald wrote:
>>> uhm this query is a killer!
>>>
>>> happens after first horde-wbemail login on the snapshoted test-machine
>>> with full transition done in offline mode
>>>
>>> better said: it happens while the first login because timeout finally
>>>
>>> (around 67.000 messages with 10 GB)
>>> __________________________
>>>
>>> | 135 | dbmail | localhost | dbmail | Execute | 453 | Copying to tmp table | SELECT DISTINCT m.message_idnr FROM
>>> dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_i
>> That query looks truncated. Could you please fetch the full query and
>> run it with 'explain'?
>>
>> Actually, if you use file_logging_level=511 you can obtain both the full
>> query and the IMAP command that triggers it.
> sorry for the dumb question but how can i get the untruncated?
> this is from "show processlist" and had also the feeling that it
> may be truncated but phpMyAdmin showed the same
Via ssh it is:
# mysql -e "show full processlist"

or, as Paul said - just set log level to 511.
>
> "part_i" should be "part_id" -> C&P mistake
>
> hopefully i have later at night or tomorrow energy/time to
> switch the snapshot for analyze, currently the VM is running
> again as replication/backup-slave - i love VMware :-)
>
>
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Feb 2, 2012, 12:14 AM

Post #5 of 27 (535 views)
Permalink

Re: dbmail3: hughe performance problem [In reply to]

On 02/01/2012 09:06 PM, Reindl Harald wrote:

> sorry for the dumb question but how can i get the untruncated?
> this is from "show processlist" and had also the feeling that it
> may be truncated but phpMyAdmin showed the same

Like I said: in dbmail.conf set file_logging_level=511
this will create a massive logfile in the location defined by the
logfile option.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 2, 2012, 1:29 AM

Post #6 of 27 (529 views)
Permalink

Re: dbmail3: hughe performance problem [In reply to]

Am 02.02.2012 09:14, schrieb Paul J Stevens:
> On 02/01/2012 09:06 PM, Reindl Harald wrote:
>
>> sorry for the dumb question but how can i get the untruncated?
>> this is from "show processlist" and had also the feeling that it
>> may be truncated but phpMyAdmin showed the same
>
> Like I said: in dbmail.conf set file_logging_level=511
> this will create a massive logfile in the location defined by the
> logfile option.

OK, i will clone a Test-Server with our full live-data as vm
on my devel machine and give feedback ASAP

thank you!

Attachments:

signature.asc (0.26 KB)

h.reindl at thelounge

Feb 2, 2012, 5:32 AM

Post #7 of 27 (531 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

Am 02.02.2012 07:04, schrieb fp:
>> sorry for the dumb question but how can i get the untruncated?
>> this is from "show processlist" and had also the feeling that it
>> may be truncated but phpMyAdmin showed the same
>
> Via ssh it is:
> # mysql -e "show full processlist"

well, everytime i learn new things even after 10 years in business
did not know "show full" before in this context
below the explain and the full killer-query

SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
'%charsettest%' ORDER BY m.message_idnr;

mysql> explain SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON
k.id=l.part_id LEFT JOIN dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON
h.physmessage_id=p.id LEFT JOIN dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON
m.physmessage_id=p.id WHERE m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR
k.data LIKE BINARY '%charsettest%' ORDER BY m.message_idnr;
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
| id | select_type | table | type | possible_keys
| key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
| 1 | SIMPLE | k | ALL | NULL
| NULL | NULL | NULL | 1712428 | Using temporary; Using filesort |
| 1 | SIMPLE | l | ref | part_id
| part_id | 8 | dbmail.k.id | 1 | Using index |
| 1 | SIMPLE | p | eq_ref | PRIMARY
| PRIMARY | 8 | dbmail.l.physmessage_id | 1 | Using index |
| 1 | SIMPLE | h | ref |
PRIMARY,physmessage_id,physmessage_id_headername_id,physmessage_id_headervalue_id | physmessage_id | 8
| dbmail.p.id | 8 | Using index |
| 1 | SIMPLE | v | eq_ref | PRIMARY
| PRIMARY | 8 | dbmail.h.headervalue_id | 1 | Using where |
| 1 | SIMPLE | m | ref | physmessage_id_index
| physmessage_id_index | 8 | dbmail.p.id | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------------------------------------------------------+----------------------+---------+-------------------------+---------+---------------------------------+
6 rows in set (0.00 sec)

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 2, 2012, 10:33 AM

Post #8 of 27 (530 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

On 02/02/2012 02:32 PM, Reindl Harald wrote:

> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
> '%charsettest%' ORDER BY m.message_idnr;

Dude, that's a full mailbody search. Horde does that just to login??? I
can't believe that. And no, this does not sound like a regression in 3.0.

Full body searches will always trigger table scans since mail content is
*not* indexed. And won't be until we somehow implement full-text search
- which has all kinds of other issues.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 2, 2012, 10:57 AM

Post #9 of 27 (528 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

Am 02.02.2012 19:33, schrieb Paul J Stevens:
> On 02/02/2012 02:32 PM, Reindl Harald wrote:
>
>> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
>> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
>> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
>> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
>> '%charsettest%' ORDER BY m.message_idnr;
>
> Dude, that's a full mailbody search. Horde does that just to login??? I
> can't believe that.

repeatable, everytime i touch a login with horde3
using IMAP as authentication this happens

> And no, this does not sound like a regression in 3.0.

but it is

same data, same machine, same horde with 2.2 no problem since years

> Full body searches will always trigger table scans since mail content is
> *not* indexed. And won't be until we somehow implement full-text search
> - which has all kinds of other issues.

there is a hughe difference between table scan and "copy to tempoary table"
seems to be triggered by some sort/distinct

doing a full search in thunderbird on content and activate "search on server"
does not trigger this

--

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 2, 2012, 11:32 AM

Post #10 of 27 (531 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

On 02/02/2012 07:57 PM, Reindl Harald wrote:
> Am 02.02.2012 19:33, schrieb Paul J Stevens:
>> On 02/02/2012 02:32 PM, Reindl Harald wrote:
>>
>>> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
>>> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
>>> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
>>> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
>>> '%charsettest%' ORDER BY m.message_idnr;
>>
>> Dude, that's a full mailbody search. Horde does that just to login??? I
>> can't believe that.
>
> repeatable, everytime i touch a login with horde3
> using IMAP as authentication this happens

I really need to see the IMAP commands leading up to this query. I don't
like guessing.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 2, 2012, 12:07 PM

Post #11 of 27 (529 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Am 02.02.2012 20:32, schrieb Paul J Stevens:
> On 02/02/2012 07:57 PM, Reindl Harald wrote:
>> Am 02.02.2012 19:33, schrieb Paul J Stevens:
>>> On 02/02/2012 02:32 PM, Reindl Harald wrote:
>>>
>>>> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
>>>> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
>>>> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
>>>> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
>>>> '%charsettest%' ORDER BY m.message_idnr;
>>>
>>> Dude, that's a full mailbody search. Horde does that just to login??? I
>>> can't believe that.
>>
>> repeatable, everytime i touch a login with horde3
>> using IMAP as authentication this happens
>
> I really need to see the IMAP commands leading up to this query. I don't
> like guessing.

no problem, as developer i understand this well

attached the whole maillog from login on horde3 until dovecot
confirms the succesfull login but never responds to horde and
only my password is obfuscated

at this momemt the query above is running, the hard-disk is
screaming and "show processlist" displays "Copying to tmp table"
for some minutes, horde will never respond and stay on the login-screen

in the scenario where i saw this first horde was running on another
machine connecting to our "backup-dbmail" and from the moment on
i revert to the dbmail-2.2 snapshot all is fine, reproduceable as
often i do it

many thanks for your time and work
Harry

Attachments:

maillog.txt (27.8 KB)

signature.asc (0.26 KB)

gordan at bobich

Feb 2, 2012, 1:00 PM

Post #12 of 27 (528 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

On 02/02/2012 06:33 PM, Paul J Stevens wrote:
> On 02/02/2012 02:32 PM, Reindl Harald wrote:
>
>> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
>> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
>> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
>> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
>> '%charsettest%' ORDER BY m.message_idnr;
>
> Dude, that's a full mailbody search. Horde does that just to login??? I
> can't believe that. And no, this does not sound like a regression in 3.0.
>
> Full body searches will always trigger table scans since mail content is
> *not* indexed. And won't be until we somehow implement full-text search
> - which has all kinds of other issues.

What's wrong with MySQL's FTS for this functionality?

Gordan
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 2, 2012, 1:22 PM

Post #13 of 27 (529 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

Am 02.02.2012 22:00, schrieb Gordan Bobic:
> On 02/02/2012 06:33 PM, Paul J Stevens wrote:
>> On 02/02/2012 02:32 PM, Reindl Harald wrote:
>>
>>> SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts k LEFT JOIN dbmail_partlists l ON k.id=l.part_id LEFT JOIN
>>> dbmail_physmessage p ON l.physmessage_id=p.id LEFT JOIN dbmail_header h ON h.physmessage_id=p.id LEFT JOIN
>>> dbmail_headervalue v ON h.headervalue_id=v.id LEFT JOIN dbmail_messages m ON m.physmessage_id=p.id WHERE
>>> m.mailbox_idnr = 1406 AND m.status IN (0,1) AND v.headervalue LIKE '%charsettest%' OR k.data LIKE BINARY
>>> '%charsettest%' ORDER BY m.message_idnr;
>>
>> Dude, that's a full mailbody search. Horde does that just to login??? I
>> can't believe that. And no, this does not sound like a regression in 3.0.
>>
>> Full body searches will always trigger table scans since mail content is
>> *not* indexed. And won't be until we somehow implement full-text search
>> - which has all kinds of other issues.
>
> What's wrong with MySQL's FTS for this functionality?

it does simply not exist currently
you cannot use MyISAM for a mailserver
InnoDB will support FTS in the upcoming 5.6 release

however, i guess the "select distinct" is the problem
MySQL is really weak with distinct/order by
in my php apps i use normaly array_unique() and
sort-functions of php because they are much faster
as mysql most of the time and as additional benefit
the application will use mysql query cache better

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 2, 2012, 11:40 PM

Post #14 of 27 (524 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

On 02/02/2012 10:00 PM, Gordan Bobic wrote:
> What's wrong with MySQL's FTS for this functionality?

IMAP search expects substring matching. Something FTS doesn't do, afaik.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Feb 2, 2012, 11:49 PM

Post #15 of 27 (522 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

On 02/02/2012 09:07 PM, Reindl Harald wrote:

> attached the whole maillog from login on horde3 until dovecot
> confirms the succesfull login but never responds to horde and
> only my password is obfuscated

Harry,

Please don't use syslog_logging_level, but file_logging_level.

Syslog will truncated lines, and may cache stuff without flushing it to
disk.

There is nothing in your logfile that would explain the behaviour you
encounter, so I'm guessing there are some lines missing at the end.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 3, 2012, 2:33 AM

Post #16 of 27 (521 views)
Permalink

Re: dbmail3: hughe performance problem >full + explain [In reply to]

Am 03.02.2012 08:40, schrieb Paul J Stevens:
> On 02/02/2012 10:00 PM, Gordan Bobic wrote:
>> What's wrong with MySQL's FTS for this functionality?
>
> IMAP search expects substring matching. Something FTS doesn't do, afaik.

it think it does
http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

but as said, this will be a topic with MySQL 5.6 i expect this
year to get GA and will bring additional performance optimizings
for InnoDB

Attachments:

signature.asc (0.26 KB)

h.reindl at thelounge

Feb 3, 2012, 3:04 AM

Post #17 of 27 (523 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Am 03.02.2012 08:49, schrieb Paul J Stevens:
> On 02/02/2012 09:07 PM, Reindl Harald wrote:
>
>> attached the whole maillog from login on horde3 until dovecot
>> confirms the succesfull login but never responds to horde and
>> only my password is obfuscated
>
> Harry,
>
> Please don't use syslog_logging_level, but file_logging_level.

done, while "syslog_logging_levelS" seems to be important

* log level set
* restarted all mail services
* > /var/log/dbmail.err
* horde login
* waited until the hammer-query was finished + 30 seconds

the only touch to the file was replacing my password
________________________

below also the whole mysqld configuration

the buffers are not too big since it is a backup-machine
but as said, dbmail-2.2 with the same config/mail-data and horde
does not trigger this problem and god beware more than one client
will ever trigger copy to temp table at the same time

[root [at] dbmail-tes:~]$ rpm -qa | grep mysql-server
mysql-server-5.5.20-2.fc15.20120111.rh.x86_64
________________________

[mysqld]
socket = /var/lib/mysql/mysql.sock
datadir = /Volumes/dune/mysql_data
pid-file = /Volumes/dune/mysql_data/mysql.pid
tmpdir = /tmp
character-set-server = utf8
collation-server = utf8_general_ci
default-time-zone = "Europe/Vienna"
default-storage-engine = innodb
lower_case_table_names = 1
log-error = /Volumes/dune/www-servers/_logs/mysql_error.log
slow_query_log = 0
slow_query_log_file = /Volumes/dune/www-servers/_logs/mysql_slow_query.log
general_log = 0
general_log_file = /Volumes/dune/www-servers/_logs/mysql_query.log
port = 3306
old_passwords = 0
local-infile = 0
thread_concurrency = 16

ssl-ca = /etc/mysql-ssl/ca.crt
ssl-cert = /etc/mysql-ssl/server.pem
ssl-key = /etc/mysql-ssl/server.pem

delay-key-write = ALL
concurrent_insert = 2
open-files-limit = 1000
myisam-recover = "FORCE"
myisam_use_mmap = 1

wait_timeout = 604800
interactive_timeout = 604800

max_allowed_packet = 250M
max_connections = 50
max_tmp_tables = 150
max_connect_errors = 200
max_delayed_threads = 32

flush_time = 0

query_cache_limit = 512K
query_cache_min_res_unit = 1K
query_cache_size = 15M
query_cache_type = 1

table_cache = 4000
thread_cache = 200
table_definition_cache = 768

tmp_table_size = 512M
max_heap_table_size = 256M

key_buffer_size = 150M
sort_buffer_size = 128K
read_rnd_buffer_size = 128K
join_buffer_size = 128K
read_buffer_size = 128K
preload_buffer_size = 128K
myisam_sort_buffer_size = 5M

innodb_buffer_pool_size = 2048M
innodb_buffer_pool_instances = 4
innodb_purge_threads = 1
innodb_max_purge_lag = 200000
innodb_max_dirty_pages_pct = 60
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 512M
innodb_log_buffer_size = 32M
innodb_thread_concurrency = 32
innodb_thread_sleep_delay = 10
innodb_flush_log_at_trx_commit = 2
innodb_support_xa = 1
innodb_lock_wait_timeout = 50
innodb_table_locks = 0
innodb_checksums = 1
innodb_file_format = barracuda
innodb_file_per_table = 1
innodb_open_files = 600
innodb_io_capacity = 300
innodb_read_io_threads = 6
innodb_write_io_threads = 6
transaction-isolation = READ-COMMITTED

low-priority-updates
safe-user-create
skip-federated
skip-name-resolve
skip-networking
skip-partition
skip-archive
skip-blackhole
skip-symbolic-links
slave_compressed_protocol
large-pages

[client]
socket = /var/lib/mysql/mysql.sock
port = 3306
user = root
ssl-ca = /etc/mysql-ssl/ca.crt
ssl-cert = /etc/mysql-ssl/client.pem
ssl-key = /etc/mysql-ssl/client.pem

[mysqladmin]
socket = /var/lib/mysql/mysql.sock
port = 3306
user = root

[mysqld_safe]
socket = /var/lib/mysql/mysql.sock
port = 3306
log-error = /Volumes/dune/www-servers/_logs/mysql_error.log

Attachments:

log.tar.gz (20.0 KB)

signature.asc (0.26 KB)

paul at nfg

Feb 4, 2012, 1:57 AM

Post #18 of 27 (518 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Located the problem. An old fixme/too.

Not so easy to fix though :-(

The strategy currently used for searches is correct, but sub-optimal.

Searches can be very complex or very simple.

a simple flat query:

x search charset utf-8 text "charsettest" 1

means:

search for substring "charsettest"
AND
search for message id 1

The current strategy is to handle all search-keys separately and combine
the results afterwards. This approach scales - in terms of correctness -
very well even for very complex search trees (a AND b or C or D not E
and F) but leads to very poor performance for simple statements like above.

In the above example this leads to:

search *all* messages in the mailbox for the substring
search for the message with the specified message-id
merge both results.

Which is very suboptimal to say the least.

It would be much better to re-order the search-keys on the same level
where possible (left-hand and right-hand of AND operators). Scan for
cheap searches like sequence sets (like message ids or UIDS), and use
those to speed up the expensive search-keys (like body searches) through
a feed-forward mechanism.

Introducing this for left-hand/right-hand arguments of AND operators
seems safe and if done right, and without side-effects.

Let's see if I can come up with a solution.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

paul at nfg

Feb 4, 2012, 3:21 AM

Post #19 of 27 (518 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

I believe the last git-master updates fix this

http://git.dbmail.eu/paul/dbmail/log/

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 4, 2012, 4:38 AM

Post #20 of 27 (522 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Am 04.02.2012 12:21, schrieb Paul J Stevens:
>
> I believe the last git-master updates fix this
>
> http://git.dbmail.eu/paul/dbmail/log/

great - confirmed!
horde-login works again

search from horde seems also to work but something
i do not understand in the logs :-(

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Error:[mailbox] mailbox_search(+1323): SQLException:
_____________________________

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[iconv] dbmail_iconv_once(+62): Initialize
DB encoding surface [UTF-8..utf8]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[iconv] dbmail_iconv_once(+67): Initialize
DB decoding surface [utf8..UTF-8]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[iconv] dbmail_iconv_once(+72): Initialize
default MSG decoding surface [utf8..UTF-8]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox] append_search(+475):
[0x7fcbd400a5e0] leaf [1] type [10] field [] search [domainservices] at depth [1]

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox]
dbmail_mailbox_build_imap_search(+1034): done [0] at idx [5]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[misc] g_tree_merge(+1096):
(0x7fcbdc007260) (0x7fcbdc007200): a[0] [OR] b[65] -> a[65]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[misc] g_tree_merge(+1096):
(0x7fcbdc001e30) (0x7fcbdc007260): a[65] [AND] b[65] -> a[65]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox] _prescan_search(+1537):
[0x7fcbd4002600] depth [1] type [1] rows [65]

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Database:[db] db_con_get(+256): [0x23d24c0]
connection from pool
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Database:[db] db_stmt_prepare(+400): [0x23d24c0]
[.SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts p LEFT JOIN dbmail_partlists l ON p.id=l.part_id LEFT JOIN
dbmail_physmessage s ON l.physmessage_id=s.id LEFT JOIN dbmail_messages m ON m.physmessage_id=s.id LEFT JOIN
dbmail_mailboxes b ON m.mailbox_idnr = b.mailbox_idnr WHERE b.mailbox_idnr=? AND m.status IN (?,?) AND
m.message_idnr IN
(205250,205252,205254,205256,205258,205260,205262,205264,205266,245358,618547,831526,1973989,1973992,1973993,1973994,1973995,1973996,1973997,1973998,1973999,1974000,1974001,1974002,1974003,2579675,2579676,2579677,2717922,2717923,2717924,2717925,2717926,2717927,2718035,2734033,2904386,3046725,3294383,3550713,3898198,3898199,4132270,4132276,4277423,4277424,4277425,4277426,4277427,4277428,4277429,4277430,4277431,4277432,4277433,4277434,4277435,4277436,4277441,4277442,4313789,4454485,4763789,4763790,5008065)AND
(l.part_key > 1 OR l.is_header=0) AND p.data LIKE BINARY ?ORDER BY m.message_idnr]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Error:[mailbox] mailbox_search(+1323): SQLException:
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Database:[db] db_con_close(+274): [0x23d24c0]
connection to pool
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox] _do_search(+1593):
[0x7fcbd400a5e0] depth [2] type [10] rows [0]

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox] _merge_search(+1670):
[0x7fcbd400a5e0] leaf [1] depth [2] type [10] found [65]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x2407140] Debug:[mailbox] dbmail_mailbox_search(+1713):
found [65] ids

Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x23a7280] Debug:[imap] _ic_cb_leave(+547): handling imap
session [0x24df800]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x23a7280] Debug:[clientbase] ci_uncork(+212): [0x7fcc040de010]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x23a7280] Debug:[imap] imap_handle_exit(+262): [0x24df800]
state [3] command_status [1] [SEARCH] returned with status [0]
Feb 04 13:31:39 dbmail-test.thel Æ dbmail-imapd[5772]: [0x23a7280] Info:[clientbase] ci_write(+312):
[0x7fcc040de010] S > [552/552:* SEARCH 205250 205252 205254 205256 205258 205260 205262 205264 205266 245358 618547
831526 1973989 1973992 1973993 1973994 1973995 1973996 1973997 1973998 1973999 1974000 1974001 1974002 1974003
2579675 2579676 2579677 2717922 2717923 2717924 2717925 2717926 2717927 2718035 2734033 2904386 3046725 3294383
3550713 3898198 3898199 4132270 4132276 4277423 4277424 4277425 4277426 4277427 4277428 4277429 4277430 4277431
4277432 4277433 4277434 4277435 4277436 4277441 4277442 4313789 4454485 4763789 4763790 5008065
00000004 OK UID SEARCH completed

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 4, 2012, 4:51 AM

Post #21 of 27 (516 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

On 02/04/2012 01:38 PM, Reindl Harald wrote:
>
>
> Am 04.02.2012 12:21, schrieb Paul J Stevens:
>>
>> I believe the last git-master updates fix this
>>
>> http://git.dbmail.eu/paul/dbmail/log/
>
> great - confirmed!
> horde-login works again
>
> search from horde seems also to work but something
> i do not understand in the logs :-(

Missing whitespace in query. Fixed in git master's HEAD.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 4, 2012, 6:09 AM

Post #22 of 27 (518 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Am 04.02.2012 13:51, schrieb Paul J Stevens:
> On 02/04/2012 01:38 PM, Reindl Harald wrote:
>>
>>
>> Am 04.02.2012 12:21, schrieb Paul J Stevens:
>>>
>>> I believe the last git-master updates fix this
>>>
>>> http://git.dbmail.eu/paul/dbmail/log/
>>
>> great - confirmed!
>> horde-login works again
>>
>> search from horde seems also to work but something
>> i do not understand in the logs :-(
>
> Missing whitespace in query. Fixed in git master's HEAD

sorry, but not confirmed, see below
dbmail-3.0.0-4.fc15.20120204.rh.dd6f58a67974f542aa96106fbd1a4296fa1119db.x86_64

hopefully i can soon leave you fuck in peace :-)

i will report you some other (maybe small) issues that we can
possibly track down in seperated threads, my plan is to upgrade
liveservers next or the following weekend and maybe we are one
of the the first running 3.0 in production

but i must be sure that things are running smooth since people
trust in my reputation of sucessful transitions which is the
key to allow me x.0 or even RCx upgrades in production whenever
i think it is a good idea
__________________________

Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] _handle_search_args(+953): using
charset [ISO-8859-1] for searching
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] append_search(+475):
[0x7fbb400128c0] leaf [1] type [10] field [] search [domainservices] at depth [1]

Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox]
dbmail_mailbox_build_imap_search(+1034): done [0] at idx [5]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[misc] g_tree_merge(+1096):
(0x7fbb10008410) (0x7fbb10008380): a[0] [OR] b[1] -> a[1]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[misc] g_tree_merge(+1096):
(0x7fbb10008470) (0x7fbb10008410): a[1] [AND] b[1] -> a[1]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] _prescan_search(+1537):
[0x7fbb40010800] depth [1] type [1] rows [1]

Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Database:[db] db_con_get(+256): [0x1ad34c0]
connection from pool
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Database:[db] db_stmt_prepare(+400): [0x1ad34c0]
[.SELECT DISTINCT m.message_idnr FROM dbmail_mimeparts p LEFT JOIN dbmail_partlists l ON p.id=l.part_id LEFT JOIN
dbmail_physmessage s ON l.physmessage_id=s.id LEFT JOIN dbmail_messages m ON m.physmessage_id=s.id LEFT JOIN
dbmail_mailboxes b ON m.mailbox_idnr = b.mailbox_idnr WHERE b.mailbox_idnr=? AND m.status IN (?,?) AND
m.message_idnr IN (5007644) AND (l.part_key > 1 OR l.is_header=0) AND p.data LIKE BINARY ?ORDER BY m.message_idnr]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Error:[mailbox] mailbox_search(+1323): SQLException:
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Database:[db] db_con_close(+274): [0x1ad34c0]
connection to pool
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] _do_search(+1593):
[0x7fbb400128c0] depth [2] type [10] rows [0]

Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] _merge_search(+1670):
[0x7fbb400128c0] leaf [1] depth [2] type [10] found [1]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1b09800] Debug:[mailbox] dbmail_mailbox_search(+1713):
found [1] ids

Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1aa8280] Debug:[imap] _ic_cb_leave(+547): handling imap
session [0x1c65000]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1aa8280] Debug:[clientbase] ci_uncork(+212): [0x1c68c50]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1aa8280] Debug:[imap] imap_handle_exit(+262): [0x1c65000]
state [3] command_status [1] [SEARCH] returned with status [0]
Feb 04 14:59:52 dbmail-test.thelÀ dbmail-imapd[6626]: [0x1aa8280] Info:[clientbase] ci_write(+312): [0x1c68c50] S
> [52/52:* SEARCH 5007644
00000004 OK UID SEARCH completed

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 4, 2012, 6:17 AM

Post #23 of 27 (517 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

On 02/04/2012 03:09 PM, Reindl Harald wrote:

>> Missing whitespace in query. Fixed in git master's HEAD
>
> sorry, but not confirmed, see below

another missing whitespace.

> hopefully i can soon leave you fuck in peace :-)

excuse me??

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

h.reindl at thelounge

Feb 4, 2012, 6:18 AM

Post #24 of 27 (516 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

Am 04.02.2012 15:17, schrieb Paul J Stevens:
> On 02/04/2012 03:09 PM, Reindl Harald wrote:
>
>>> Missing whitespace in query. Fixed in git master's HEAD
>>
>> sorry, but not confirmed, see below
>
> another missing whitespace.
>
>> hopefully i can soon leave you fuck in peace :-)
>
> excuse me??

did mean i really do not like to waste your time :-(

Attachments:

signature.asc (0.26 KB)

paul at nfg

Feb 4, 2012, 6:26 AM

Post #25 of 27 (509 views)
Permalink

Re: dbmail3: hughe performance problem > debuglog [In reply to]

On 02/04/2012 03:18 PM, Reindl Harald wrote:
>
>
> Am 04.02.2012 15:17, schrieb Paul J Stevens:
>> On 02/04/2012 03:09 PM, Reindl Harald wrote:
>>
>>>> Missing whitespace in query. Fixed in git master's HEAD
>>>
>>> sorry, but not confirmed, see below
>>
>> another missing whitespace.
>>
>>> hopefully i can soon leave you fuck in peace :-)
>>
>> excuse me??
>
> did mean i really do not like to waste your time :-(

You're not. Too bad you couldn't test the RCs, but I'm glad to see
you're helping out now.

The search optimization really was overdue.

--
________________________________________________________________
Paul J Stevens pjstevns @ gmail, twitter, skype, linkedin

* Premium Hosting Services and Web Application Consultancy *

www.nfg.nl/info [at] nfg/+31.85.877.99.97
________________________________________________________________
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

1 2

View All

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads