The following issue has been RESOLVED. ====================================================================== http://www.dbmail.org/mantis/view.php?id=836 ====================================================================== Reported By: waza123 Assigned To: paul ====================================================================== Project: DBMail Issue ID: 836 Category: IMAP daemon Reproducibility: always Severity: minor Priority: normal Status: resolved target: Resolution: fixed Fixed in Version: 2.3.7 ====================================================================== Date Submitted: 28-Jan-10 00:24 CET Last Modified: 29-Jan-10 10:56 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://www.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://www.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://www.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://www.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://www.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://www.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://www.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://www.dbmail.org/mantis/view.php?id=836#c3015 ---------------------------------------------------------------------- Apart from the fact that 0 rows are returned I fail to see the problem here. ---------------------------------------------------------------------- (0003016) waza123 (reporter) - 29-Jan-10 10:54 http://www.dbmail.org/mantis/view.php?id=836#c3016 ---------------------------------------------------------------------- just change the JOIN to LEFT JOIN and close this bugreport. 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 29-Jan-10 10:54 waza123 Note Added: 0003016 29-Jan-10 10:56 paul Assigned To => paul 29-Jan-10 10:56 paul Status new => resolved 29-Jan-10 10:56 paul Resolution open => fixed 29-Jan-10 10:56 paul Fixed in Version => 2.3.7 ====================================================================== _______________________________________________ Dbmail-dev mailing list Dbmail-dev@dbmail.org http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail-dev