<p dir="ltr">Ah, that explains it! In reality, you work with a set of rather small database files, which of course has a lot of impact on timeebased (and indeed most!:-) queries... A very sensible design (that wasn't possible for Steve F at the inseption even of MaiLWatch 1.0:-) and probably not that much work implementing in my old setup (I confess, I've been... slow... in adapting to the latest/greatest:-). <br>
If/when time permits experimentation...:-) </p>
<p dir="ltr">Cheers!<br>
-- <br>
-- Glenn</p>
<div class="gmail_quote">Den 5 aug 2014 17:58 skrev "Jerry Benton" <<a href="mailto:jerry.benton@mailborder.com">jerry.benton@mailborder.com</a>>:<br type="attribution"><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div style="word-wrap:break-word">Caveat: You should partition the database by time. This is the Mailborder cp_maillog, which is slightly different than MailWatch, but the bit near the end is what you are looking for. You can adapt it for your table with an alter statement. <div>
<br><div><br></div><div><div>CREATE TABLE IF NOT EXISTS `cp_maillog` (</div><div> `db_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,</div><div> `timestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,</div>
<div> `id` varchar(30) NOT NULL,</div><div> `size` bigint(20) DEFAULT '0',</div><div> `from_address` varchar(255) DEFAULT NULL,</div><div> `from_domain` varchar(255) DEFAULT NULL,</div><div> `to_address` varchar(255) DEFAULT NULL,</div>
<div> `to_domain` varchar(255) DEFAULT NULL,</div><div> `subject` text CHARACTER SET utf8 COLLATE utf8_unicode_ci,</div><div> `clientip` varchar(15) DEFAULT NULL,</div><div> `archive` varchar(100) DEFAULT NULL,</div><div>
`isspam` tinyint(1) DEFAULT '0',</div><div> `ishighspam` tinyint(1) DEFAULT '0',</div><div> `issaspam` tinyint(1) DEFAULT '0',</div><div> `isrblspam` tinyint(1) DEFAULT '0',</div><div>
`spamwhitelisted` tinyint(1) DEFAULT '0',</div><div> `spamblacklisted` tinyint(1) DEFAULT '0',</div><div> `sascore` decimal(7,2) DEFAULT '0.00',</div><div> `spamreport` text,</div><div> `virusinfected` tinyint(1) DEFAULT '0',</div>
<div> `nameinfected` tinyint(1) DEFAULT '0',</div><div> `sizeinfected` tinyint(1) DEFAULT '0',</div><div> `otherinfected` tinyint(1) DEFAULT '0',</div><div> `report` text,</div><div> `ismcp` tinyint(1) DEFAULT '0',</div>
<div> `ishighmcp` tinyint(1) DEFAULT '0',</div><div> `issamcp` tinyint(1) DEFAULT '0',</div><div> `mcpwhitelisted` tinyint(1) DEFAULT '0',</div><div> `mcpblacklisted` tinyint(1) DEFAULT '0',</div>
<div> `mcpsascore` decimal(7,2) DEFAULT '0.00',</div><div> `mcpreport` text,</div><div> `hostname` varchar(100) DEFAULT NULL,</div><div> `date` date NOT NULL DEFAULT '0000-00-00',</div><div> `time` time DEFAULT NULL,</div>
<div> `headers` text,</div><div> `quarantined` tinyint(1) DEFAULT '0',</div><div> `released` tinyint(1) DEFAULT '0',</div><div> `guid` varchar(40) NOT NULL,</div><div> PRIMARY KEY (`db_id`,`date`),</div>
<div> KEY `id` (`id`),</div><div> KEY `timestamp` (`timestamp`),</div><div> KEY `from_address` (`from_address`),</div><div> KEY `from_domain` (`from_domain`),</div><div> KEY `to_address` (`to_address`),</div><div> KEY `to_domain` (`to_domain`),</div>
<div> KEY `guid` (`guid`),</div><div> KEY `isspam` (`isspam`),</div><div> KEY `ishighspam` (`ishighspam`),</div><div> KEY `issaspam` (`issaspam`),</div><div> KEY `isrblspam` (`isrblspam`),</div><div> KEY `spamwhitelisted` (`spamwhitelisted`),</div>
<div> KEY `spamblacklisted` (`spamblacklisted`),</div><div> KEY `virusinfected` (`virusinfected`),</div><div> KEY `nameinfected` (`nameinfected`),</div><div> KEY `otherinfected` (`otherinfected`),</div><div> KEY `quarantined` (`quarantined`),</div>
<div> KEY `sizeinfected` (`sizeinfected`),</div><div> KEY `ismcp` (`ismcp`),</div><div> KEY `ishighmcp` (`ishighmcp`),</div><div> KEY `issamcp` (`issamcp`),</div><div> KEY `mcpwhitelisted` (`mcpwhitelisted`),</div><div>
KEY `mcpblacklisted` (`mcpblacklisted`),</div><div> KEY `released` (`released`),</div><div> KEY `size` (`size`)</div><div>) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY HASH (( YEAR(`date`) + MONTH(`date`) )) PARTITIONS 70;</div>
<div><br></div><div>
<div style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
<br>-</div><div style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
Jerry Benton</div><div style="color:rgb(0,0,0);font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
<a href="http://www.mailborder.com" target="_blank">www.mailborder.com</a></div>
</div>
<br><div><div>On Aug 5, 2014, at 11:16 AM, Jerry Benton <<a href="mailto:jerry.benton@mailborder.com" target="_blank">jerry.benton@mailborder.com</a>> wrote:</div><br><blockquote type="cite"><div style="word-wrap:break-word">
Based on Mailborder design and testing, which the DB structure of Mailwatch is very similar, MyISAM has better performance when you start hitting millions of records.<br><div>
<div style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
<br>-</div><div style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
Jerry Benton</div><div style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
<a href="http://www.mailborder.com/" target="_blank">www.mailborder.com</a></div>
</div>
<br><div><div>On Aug 5, 2014, at 10:23 AM, Randal, Phil <<a href="mailto:phil.randal@hoopleltd.co.uk" target="_blank">phil.randal@hoopleltd.co.uk</a>> wrote:</div><br><blockquote type="cite"><div lang="EN-GB" link="blue" vlink="purple" style="font-family:Helvetica;font-size:12px;font-style:normal;font-variant:normal;font-weight:normal;letter-spacing:normal;line-height:normal;text-align:start;text-indent:0px;text-transform:none;white-space:normal;word-spacing:0px">
<div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Does converting the MailWatch databases to InnoDB make a big difference in MailWatch performance?<u></u><u></u></span></div>
<div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
<span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Just curious.<u></u><u></u></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div>
<div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)">Phil<u></u><u></u></span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
<span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="font-size:11pt;font-family:Calibri,sans-serif;color:rgb(31,73,125)"> </span></div>
<div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><b><span lang="EN-US" style="font-size:10pt;font-family:Tahoma,sans-serif">From:</span></b><span lang="EN-US" style="font-size:10pt;font-family:Tahoma,sans-serif"><span> </span><a href="mailto:mailscanner-bounces@lists.mailscanner.info" style="color:purple;text-decoration:underline" target="_blank">mailscanner-bounces@lists.mailscanner.info</a><span> </span>[<a href="mailto:mailscanner-bounces@lists.mailscanner.info" style="color:purple;text-decoration:underline" target="_blank">mailto:mailscanner-bounces@lists.mailscanner.info</a>]<span> </span><b>On Behalf Of<span> </span></b>Glenn Steen<br>
<b>Sent:</b><span> </span>05 August 2014 14:51<br><b>To:</b><span> </span>MailScanner discussion<br><b>Subject:</b><span> </span>Re: MailScanner Deficiency: Multi-Ruleset Processing per Email Recipient<u></u><u></u></span></div>
<div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><u></u> <u></u></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">Can only agree with Martin and Alex, there is no way around either splitting mails per recipient (very feasible), or som major rework of both the MailScanner and mailWatch code (very infeasible).<u></u><u></u></div>
<div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">But I also have to agree that the increase in hardware seem quite excessive... i suppose you arrived at that figure by analysing the number of recipients per mail (and frequency of multi-recipient emails)? Well, the number isnät everything:-)<u></u><u></u></div>
</div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">Provided you use the normal caching-dns-thingy and also use "Cache SpamAssassin Results = yes", the actual processing time and resource use will be minimized (not to mention that the normal batch-processing style of MailScanner will ... help...:-).<u></u><u></u></div>
</div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">Introducing a "splitting MX" between the internet and your regular MailScanner hosts should be rather simple, as well as adjusting which Received: lines your MailScanner hosts should ignore (since they otherwise will perceive all messages as originating from the "splitting MX" host)... So why not try that, with the gear you have ATM, and see where that leads you? Depending on what mailstore hosts you eventually deliver to, the storage impact should be minimal or even non-existant, since even M-Sexchange has abandioned "single store" since ... way back... so every recipient would eventually have their own copy in their own mailbox anyway;-).<u></u><u></u></div>
</div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><u></u> <u></u></div></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
As Alex says, we know nothing about your actual mail volume, but my money is on there being much less of a problem than you think, even if you do have ... serious traffic... (more than a few thousand mails/hour). the likeliest problem point/bottleneck is likely your MailWatch database so... keep an eye on that one, make sure you run it as InnoDB etc.<u></u><u></u></div>
</div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><u></u> <u></u></div></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
Cheers!<u></u><u></u></div></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">-- <u></u><u></u></div></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
-- Glenn<u></u><u></u></div></div></div><div><p class="MsoNormal" style="margin:0cm 0cm 12pt;font-size:12pt;font-family:'Times New Roman',serif"><u></u> <u></u></p><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
On 11 July 2014 15:49, Martin Hepworth <<a href="mailto:maxsec@gmail.com" style="color:purple;text-decoration:underline" target="_blank">maxsec@gmail.com</a>> wrote:<u></u><u></u></div><div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
Might want to also consider having a more flexible approach as Alex had mentioned.<u></u><u></u></div></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">Will also help with some of the hardware requirements as you can also reject non-valid recipients at MTA as well as splitting the emails up, so the core MailScanner farm has less to do.<u></u><u></u></div>
</div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><span style="color:rgb(136,136,136)"><br clear="all"><span><u></u><u></u></span></span></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
<span style="color:rgb(136,136,136)">--<span> </span><br>Martin Hepworth, CISSP<br>Oxford, UK</span><u></u><u></u></div></div><div><p class="MsoNormal" style="margin:0cm 0cm 12pt;font-size:12pt;font-family:'Times New Roman',serif">
<u></u> <u></u></p><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">On 11 July 2014 09:51, Sam Gelbart <<a href="mailto:samg@synaq.com" style="color:purple;text-decoration:underline" target="_blank">samg@synaq.com</a>> wrote:<u></u><u></u></div>
<div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">Hi All,<br><br>We at SYNAQ use and have used Mailscanner for many years. As an Email Hygiene provider MailScanner has served us very well.<br>
However, as we have grown (very rapidly in the past 6 months, to many more customer domains) we have noticed some deficiencies in MailScanner.<br><br>Below is a brief description covering our problem areas:<br><br>Overview<br>
The issue has arisen due to SYNAQ's ever growing client base and the fact that we're provisioning more and more customers (and email domains) on our hygiene platform, and that more than one of these customer recipients/domains (and their applicable rulesets) are being addressed in the same email.<br>
<br>Problem 1<br>1)<span> </span><a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>and<span> </span><a href="http://xyz.co.za/" style="color:purple;text-decoration:underline" target="_blank">xyz.co.za</a><span> </span>are both provisioned on our platform.<br>
2)<span> </span><a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>has quarantining of SPAM configured, while<span> </span><a href="http://xyz.co.za/" style="color:purple;text-decoration:underline" target="_blank">xyz.co.za</a><span> </span>does not.<br>
3) Mailscanner accepts the message for processing but "chooses"<span> </span><a href="mailto:user@abc.co.za" style="color:purple;text-decoration:underline" target="_blank">user@abc.co.za</a><span> </span>and<a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>as the Message's "to_address" and "to_domain".<br>
4) MailScanner determines that the message is SPAM and because it has "chosen" @<a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>as the email domain it deletes the message as the configured spam action for @abc.coz.a is to delete.<br>
5) However the rule for<span> </span><a href="http://xyz.co.za/" style="color:purple;text-decoration:underline" target="_blank">xyz.co.za</a><span> </span>is to store/quarantine spam. This does not happen because of the actions above and data is also never logged via MailWatch.<br>
6) The example above is a based on very simple scenario, and as you are aware this applies to many more complex rulesets (size, File Type etc) across the system.<br><br>Problem 2<br>1)<span> </span><a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>and<span> </span><a href="http://xyz.co.za/" style="color:purple;text-decoration:underline" target="_blank">xyz.co.za</a><span> </span>are both provisioned on our platform.<br>
2) A third party emails both<span> </span><a href="mailto:user@abc.co.za" style="color:purple;text-decoration:underline" target="_blank">user@abc.co.za</a><span> </span>and<span> </span><a href="mailto:user@xyz.co.za" style="color:purple;text-decoration:underline" target="_blank">user@xyz.co.za</a><span> </span>in a single email message.<br>
3) Mailscanner accepts the message for processing but "chooses"<span> </span><a href="mailto:user@abc.co.za" style="color:purple;text-decoration:underline" target="_blank">user@abc.co.za</a><span> </span>and<a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a><span> </span>as the Message's "to_address" and "to_domain".<br>
4) When the message is processed, the MailWatch.pm script receives a message object for SQL logging with data only for<span> </span><a href="mailto:user@abc.co.za" style="color:purple;text-decoration:underline" target="_blank">user@abc.co.za</a><span> </span>and<span> </span><a href="http://abc.co.za/" style="color:purple;text-decoration:underline" target="_blank">abc.co.za</a>;<span> </span><a href="http://xyz.co.za/" style="color:purple;text-decoration:underline" target="_blank">xyz.co.za</a><span> </span>is never logged.<br>
<br>Finally we have considered splitting incoming messages by recipient at an MTA level to address this problem, but our calculations show that it would require 3.5x more hardware to process this increased mail load. So for us a MailsScanner solution is ideal.<br>
<br>Based on the above, could you tell me if there is anything that can be done from a MailScanner community point of view to help develop MailScanner functionality to address these issues?<br>We'd be very happy to give a nice donation for a fix or patch.<br>
<br>Also if the community has any ideas on other ways we can remedy this problem we welcome your feedback.<br><br>Thanks and regards,<br><br>Sam Gelbart<br>SYNAQ<br><span style="color:rgb(136,136,136)"><br><br>--<br>MailScanner mailing list<br>
<a href="mailto:mailscanner@lists.mailscanner.info" style="color:purple;text-decoration:underline" target="_blank">mailscanner@lists.mailscanner.info</a><br><a href="http://lists.mailscanner.info/mailman/listinfo/mailscanner" style="color:purple;text-decoration:underline" target="_blank">http://lists.mailscanner.info/mailman/listinfo/mailscanner</a><br>
<br>Before posting, read<span> </span><a href="http://wiki.mailscanner.info/posting" style="color:purple;text-decoration:underline" target="_blank">http://wiki.mailscanner.info/posting</a><br><br>Support MailScanner development - buy the book off the website!</span><u></u><u></u></div>
</div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><u></u> <u></u></div></div></div><p class="MsoNormal" style="margin:0cm 0cm 12pt;font-size:12pt;font-family:'Times New Roman',serif">
<br>--<br>MailScanner mailing list<br><a href="mailto:mailscanner@lists.mailscanner.info" style="color:purple;text-decoration:underline" target="_blank">mailscanner@lists.mailscanner.info</a><br><a href="http://lists.mailscanner.info/mailman/listinfo/mailscanner" style="color:purple;text-decoration:underline" target="_blank">http://lists.mailscanner.info/mailman/listinfo/mailscanner</a><br>
<br>Before posting, read<span> </span><a href="http://wiki.mailscanner.info/posting" style="color:purple;text-decoration:underline" target="_blank">http://wiki.mailscanner.info/posting</a><br><br>Support MailScanner development - buy the book off the website!<u></u><u></u></p>
</div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif"><br><br clear="all"><u></u><u></u></div><div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">
<u></u> <u></u></div></div><div style="margin:0cm 0cm 0.0001pt;font-size:12pt;font-family:'Times New Roman',serif">--<span> </span><br>-- Glenn<br>email: glenn < dot > steen < at > gmail < dot > com<br>
work: glenn < dot > steen < at > ap1 < dot > se<u></u><u></u></div></div></div><font face="Arial, sans-serif" color="#00829b">Hoople Ltd, Registered in England and Wales No. 7556595<br>Registered office: Plough Lane, Hereford, HR4 0LE<br>
<br><font face="Arial, sans-serif">"Any opinion expressed in this e-mail or any attached files are those of the individual and not necessarily those of Hoople Ltd. You should be aware that Hoople Ltd. monitors its email service. This e-mail and any attached files are confidential and intended solely for the use of the addressee. This communication may contain material protected by law from being passed on. If you are not the intended recipient and have received this e-mail in error, you are advised that any use, dissemination, forwarding, printing or copying of this e-mail is strictly prohibited. If you have received this e-mail in error please contact the sender immediately and destroy all copies of it."</font></font><span> </span>--<span> </span><br>
MailScanner mailing list<br><a href="mailto:mailscanner@lists.mailscanner.info" style="color:purple;text-decoration:underline" target="_blank">mailscanner@lists.mailscanner.info</a><br><a href="http://lists.mailscanner.info/mailman/listinfo/mailscanner" style="color:purple;text-decoration:underline" target="_blank">http://lists.mailscanner.info/mailman/listinfo/mailscanner</a><br>
<br>Before posting, read<span> </span><a href="http://wiki.mailscanner.info/posting" style="color:purple;text-decoration:underline" target="_blank">http://wiki.mailscanner.info/posting</a><br><br>Support MailScanner development - buy the book off the website!<span> </span><br>
</div></blockquote></div><br></div></blockquote></div><br></div></div></div><br>--<br>
MailScanner mailing list<br>
<a href="mailto:mailscanner@lists.mailscanner.info">mailscanner@lists.mailscanner.info</a><br>
<a href="http://lists.mailscanner.info/mailman/listinfo/mailscanner" target="_blank">http://lists.mailscanner.info/mailman/listinfo/mailscanner</a><br>
<br>
Before posting, read <a href="http://wiki.mailscanner.info/posting" target="_blank">http://wiki.mailscanner.info/posting</a><br>
<br>
Support MailScanner development - buy the book off the website!<br>
<br></blockquote></div>