I didn't notice this until we upgraded to 5.1 in anticipation of using
partition tables, but I have a penpals query suggestion that took a 4
hour SINGLE EMAIL LOOKUP to 90 seconds.
Currently using mysql 5.1.x , amavisd 2.5.4, innodb tables.
Having major delays on email with penpals enabled on sites with a large
maddr table.
I never noticed this till we went to 5.1. and I don't have any 5.0 or
4.x installations available to test this on.
A query like this will take, sometimes several hours!!! (on a fast dual
quad machine, scsi mirrored drives, lots of ram, swap fine)
if you want to try this inhouse, do a select * from maddr limit 50; and
select a 'sid' from outside your domain, and a 'rid' from inside your
domain.
SELECT msgs.time_num, msgs.mail_id, subject FROM msgs JOIN msgrcpt ON
msgs.mail_id=msgrcpt.mail_id WHERE sid='16' AND rid='2' AND content!='V'
AND ds='P' ORDER BY msgs.time_num DESC LIMIT 1
explain shows things like this: (320K rows, 'Using where') and it takes
4 hours to run!
explain SELECT msgs.time_num, msgs.mail_id, subject FROM msgs JOIN
msgrcpt ON msgs.mail_id=msgrcpt.mail_id WHERE sid='16' AND rid='2' AND
content!='V' AND ds='P' ORDER BY msgs.time_num DESC LIMIT 1;
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
| 1 | SIMPLE | msgs | index |
PRIMARY,msgs_idx_sid | msgs_idx_time_num | 4 |
NULL | 319783 | Using where |
| 1 | SIMPLE | msgrcpt | ref |
msgrcpt_idx_mail_id,msgrcpt_idx_rid | msgrcpt_idx_mail_id | 14 |
mail.msgs.mail_id | 1 | Using where |
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
2 rows in set (0.01 sec)
I previously posted about this, and I suppose I didn't explain it well
enough.
Three currious things make the query faster:.
1) drop the ORDER BY in 'ORDER BY msgs.time_num DESC LIMIT 1'
this will probally not select the latest sent message, thereby
breaking penpals.
2) drop the LIMIT 1. I don't know if this will break penpals, Mark
would need to tell us this
(maybe 2.6.2 could eliminate the limit 1? and penpals could just
grab the first response?
3) force an index, as in
setting this in amavisd.conf:
$sql_clause{sel_penpals}="Select msgs.time_num, msgs.mail_id, subject
FROM msgs force index (msgs_idx_sid) JOIN
msgrcpt ON msgs.mail_id=msgrcpt.mail_id WHERE sid=? AND rid=? AND
content!='V' AND ds='P' ORDER BY msgs.time_num
LIMIT 1";
both EXPLAIN in mysql, and timed tests confirm that either of the three
options takes a 4 HOUR QUERY into a 90 second query (yes, 90 seconds is
way too long, and yes, the maddr table is too big, and, yes, mysql 5.1,
amavisd 2.6 and partitions will help)
results:
original query,
original: 319783 rows, might be using msgs_idx_time_num key
tests,
1) no order by: 2018 rows, using msgs_idx_sid key
either case, if forcing an index helps, should not this be a good
edition to the default?
2) no limit 1: 2018 rows, using msgs_idx_sid key
3) force index, 2018 rows.
documentation on explains:
original: 319783 rows, no key used, 'Using where'
explain SELECT msgs.time_num, msgs.mail_id, subject FROM msgs JOIN
msgrcpt ON msgs.mail_id=msgrcpt.mail_id WHERE sid='16' AND rid='2' AND
content!='V' AND ds='P' ORDER BY msgs.time_num DESC LIMIT 1;
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
| 1 | SIMPLE | msgs | index |
PRIMARY,msgs_idx_sid | msgs_idx_time_num | 4 |
NULL | 319783 | Using where |
| 1 | SIMPLE | msgrcpt | ref |
msgrcpt_idx_mail_id,msgrcpt_idx_rid | msgrcpt_idx_mail_id | 14 |
mail.msgs.mail_id | 1 | Using where |
+----+-------------+---------+-------+-------------------------------------+---------------------+---------+-------------------+--------+-------------+
any of #1, 2 or 3 above:
explain SELECT msgs.time_num, msgs.mail_id, subject FROM msgs force
index (msgs_idx_sid) JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id WHERE
sid='16' AND rid='2' AND content!='V' AND ds='P' ORDER BY msgs.time_num
DESC LIMIT 1;
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------------+------+-----------------------------+
| id | select_type | table | type |
possible_keys | key | key_len |
ref | rows | Extra |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------------+------+-----------------------------+
| 1 | SIMPLE | msgs | ref |
msgs_idx_sid | msgs_idx_sid | 8 |
const | 2018 | Using where; Using filesort |
| 1 | SIMPLE | msgrcpt | ref |
msgrcpt_idx_mail_id,msgrcpt_idx_rid | msgrcpt_idx_mail_id | 14 |
mail.msgs.mail_id | 1 | Using where |
+----+-------------+---------+------+-------------------------------------+---------------------+---------+-------------------+------+-----------------------------+
table creates:
Table: msgs
Create Table: CREATE TABLE `msgs` (
`mail_id` varbinary(12) NOT NULL,
`secret_id` varbinary(12) DEFAULT '',
`am_id` varchar(20) NOT NULL DEFAULT '',
`time_num` int(10) unsigned NOT NULL DEFAULT '0',
`time_iso` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
`sid` bigint(20) unsigned NOT NULL,
`policy` varchar(255) DEFAULT '',
`client_addr` varchar(255) DEFAULT '',
`size` int(10) unsigned NOT NULL DEFAULT '0',
`content` binary(1) DEFAULT NULL,
`quar_type` binary(1) DEFAULT NULL,
`dsn_sent` char(1) DEFAULT NULL,
`spam_level` float DEFAULT NULL,
`message_id` varchar(255) DEFAULT '',
`from_addr` varchar(255) DEFAULT '',
`subject` varchar(255) DEFAULT '',
`host` varchar(255) NOT NULL DEFAULT '',
`quar_loc` varbinary(255) DEFAULT '',
PRIMARY KEY (`mail_id`),
KEY `msgs_idx_sid` (`sid`),
KEY `msgs_idx_mess_id` (`message_id`),
KEY `msgs_idx_time_num` (`time_num`),
KEY `msgs_idx_reports` (`time_num`,`content`,`quar_loc`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table: msgrcpt
Create Table: CREATE TABLE `msgrcpt` (
`mail_id` varbinary(12) NOT NULL,
`rid` bigint(20) unsigned NOT NULL,
`ds` char(1) NOT NULL DEFAULT '',
`rs` binary(1) NOT NULL,
`bl` char(1) DEFAULT '',
`wl` char(1) DEFAULT '',
`bspam_level` float DEFAULT NULL,
`smtp_resp` varchar(255) DEFAULT '',
KEY `msgrcpt_idx_mail_id` (`mail_id`),
KEY `msgrcpt_idx_rid` (`rid`),
KEY `idx_resp` (`smtp_resp`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
--
Michael Scheidell, CTO
Phone: 561-999-5000, x 1259
> *| *SECNAP Network Security Corporation
* Certified SNORT Integrator
* King of Spam Filters, SC Magazine 2008
* Information Security Award 2008, Info Security Products Guide
* CRN Magazine Top 40 Emerging Security Vendors
_________________________________________________________________________
This email has been scanned and certified safe by SpammerTrap(r).
For Information please see http://www.spammertrap.com
_________________________________________________________________________
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
