A NOTE has been added to this issue.
======================================================================
http://dbmail.org/mantis/view.php?id=836
======================================================================
Reported By: waza123
Assigned To:
======================================================================
Project: DBMail
Issue ID: 836
Category: IMAP daemon
Reproducibility: always
Severity: minor
Priority: normal
Status: new
target:
======================================================================
Date Submitted: 28-Jan-10 00:24 CET
Last Modified: 29-Jan-10 10:41 CET
======================================================================
Summary: Slow query..
Description:
And again, IMAPD is too slow..
Here is logs from mysql
# Time: 100127 11:48:47
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 133 Lock_time: 0 Rows_sent: 0 Rows_examined: 16077033
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 5969 AND m.message_idnr
BETWEEN 886017 AND 889972 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100127 11:50:04
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 97 Lock_time: 0 Rows_sent: 0 Rows_examined: 16077033
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 5969 AND m.message_idnr
BETWEEN 886017 AND 889972 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100127 20:42:34
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 106 Lock_time: 0 Rows_sent: 0 Rows_examined: 16132997
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 8762 AND m.message_idnr
BETWEEN 847922 AND 889477 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
# Time: 100128 1:15:52
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 103 Lock_time: 0 Rows_sent: 0 Rows_examined: 16150734
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id JOIN
dbmail_headername n ON h.headername_id=n.id JOIN dbmail_headervalue v ON
h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr BETWEEN
317320 AND 320980 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
latest git. Server no load 1:20am at night.
A bit info about mailbox:
It's my stress test mailbox, it's receive a large amount of spam.
Unread inbox messages: 707
Unread spam messages: 13793
Mailbox size: 209 MB
What i do is:
Select spam box, then go to Page: 900 of 1380
And here you go, very slow query.
Advice ? Fix ?
Thanks.
======================================================================
----------------------------------------------------------------------
(0003005) waza123 (reporter) - 28-Jan-10 00:27
http://dbmail.org/mantis/view.php?id=836#c3005
----------------------------------------------------------------------
And about tables:
dbmail_header = 7,974,522 rows
dbmail_messages = 315,591 rows
dbmail_headervalue = 2,285,067 rows
----------------------------------------------------------------------
(0003009) paul (administrator) - 28-Jan-10 14:12
http://dbmail.org/mantis/view.php?id=836#c3009
----------------------------------------------------------------------
Could you please test if using explicit LEFT JOIN would improve the
response time?
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id LEFT JOIN
dbmail_headername n ON h.headername_id=n.id LEFT JOIN dbmail_headervalue v
ON h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr
BETWEEN 317320 AND 320980 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
----------------------------------------------------------------------
(0003010) waza123 (reporter) - 28-Jan-10 15:28
http://dbmail.org/mantis/view.php?id=836#c3010
----------------------------------------------------------------------
Tell where to change it, in which cpp file and which line ?
----------------------------------------------------------------------
(0003011) waza123 (reporter) - 29-Jan-10 00:21
http://dbmail.org/mantis/view.php?id=836#c3011
----------------------------------------------------------------------
found , dbmail-imapsession.c , str: 648
will test now..
----------------------------------------------------------------------
(0003012) waza123 (reporter) - 29-Jan-10 00:25
http://dbmail.org/mantis/view.php?id=836#c3012
----------------------------------------------------------------------
ok, works.. it's now faster, but not enough
# Time: 100129 1:22:20
# u...@host: dbmail[dbmail] @ localhost []
# Query_time: 3 Lock_time: 0 Rows_sent: 0 Rows_examined: 21966
SELECT m.message_idnr, n.headername, v.headervalue FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id=m.physmessage_id LEFT JOIN
dbmail_headername n ON h.headername_id=n.id LEFT JOIN dbmail_headervalue v
ON h.headervalue_id=v.id WHERE m.mailbox_idnr = 24 AND m.message_idnr
BETWEEN 441428 AND 446398 AND lower(n.headername) IN
('newsgroups','content-md5','content-disposition','content-language','content-location','followup-to','references');
----------------------------------------------------------------------
(0003013) paul (administrator) - 29-Jan-10 09:13
http://dbmail.org/mantis/view.php?id=836#c3013
----------------------------------------------------------------------
Query time dropped from around 100 seconds to just 3 seconds! Sounds like a
massive improvement to me, especially given the size of the mailbox. If you
need more speedups you will most likely need to tweak your my.cnf. What
kind of hardware are you running on (disks, ram, cpu).
I'm kind of surprised to see 0 rows were returned in all cases. It's
possible of course, especially since those are spam messages. What happens
when you run that query manually, and perhaps add 'to','subject' and 'from'
to the list of headers?
----------------------------------------------------------------------
(0003014) waza123 (reporter) - 29-Jan-10 10:29
http://dbmail.org/mantis/view.php?id=836#c3014
----------------------------------------------------------------------
ok tested it in phpmyadmin
Showing rows 0 - 29 (252 total, Query took 0.3592 sec)
SELECT m.message_idnr, n.headername, v.headervalue
FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id = m.physmessage_id
LEFT JOIN dbmail_headername n ON h.headername_id = n.id
LEFT JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE m.mailbox_idnr =24
AND m.message_idnr
BETWEEN 441428
AND 446398
AND lower( n.headername )
IN (
'newsgroups', 'content-md5', 'content-disposition', 'content-language',
'content-location', 'followup-to', 'references', 'to', 'subject', 'from'
)
LIMIT 0 , 30;
Showing rows 0 - 0 (1 total, Query took 0.1501 sec)
SELECT m.message_idnr, n.headername, v.headervalue
FROM dbmail_header h
LEFT JOIN dbmail_messages m ON h.physmessage_id = m.physmessage_id
LEFT JOIN dbmail_headername n ON h.headername_id = n.id
LEFT JOIN dbmail_headervalue v ON h.headervalue_id = v.id
WHERE m.mailbox_idnr =24
AND m.message_idnr
BETWEEN 317320
AND 320980
AND lower( n.headername )
IN (
'newsgroups', 'content-md5', 'content-disposition', 'content-language',
'content-location', 'followup-to', 'references'
)
LIMIT 0 , 30;
/etc/my.cnf
[mysqld]
log-error=/backup/mysql_db/error.log
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 100M
table_cache = 512
sort_buffer_size = 36M
read_buffer_size = 36M
read_rnd_buffer_size = 36M
myisam_sort_buffer_size = 64M
thread_cache_size = 36
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
[mysqldump]
quick
max_allowed_packet = 36M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 35M
write_buffer = 35M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 35M
write_buffer = 35M
[mysqlhotcopy]
interactive-timeout
----------------------------------------------------------------------
(0003015) paul (administrator) - 29-Jan-10 10:41
http://dbmail.org/mantis/view.php?id=836#c3015
----------------------------------------------------------------------
Apart from the fact that 0 rows are returned I fail to see the problem
here.
Issue History
Date Modified Username Field Change
======================================================================
28-Jan-10 00:24 waza123 New Issue
28-Jan-10 00:25 waza123 Issue Monitored: waza123
28-Jan-10 00:25 waza123 Issue End Monitor: waza123
28-Jan-10 00:27 waza123 Note Added: 0003005
28-Jan-10 14:12 paul Note Added: 0003009
28-Jan-10 15:28 waza123 Note Added: 0003010
29-Jan-10 00:21 waza123 Note Added: 0003011
29-Jan-10 00:25 waza123 Note Added: 0003012
29-Jan-10 09:13 paul Note Added: 0003013
29-Jan-10 10:29 waza123 Note Added: 0003014
29-Jan-10 10:41 paul Note Added: 0003015
======================================================================
_______________________________________________
Dbmail-dev mailing list
Dbmail-dev@dbmail.org
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev
