On Wed, Jul 02, 2014 at 10:00:52AM +0200, Marco wrote:
> Hello,
> I have a slow performance on my dspam-mysql conf. The purge nightly
> script take 7-12 hours to complete. During this time I see a lot of
> locks
>
> [07/02/2014 09:28:35] 32082: Lock wait timeout exceeded; try
> restarting transaction: UPDATE dspam_token_data SET
> last_hit=CURRENT_DATE(),innocent_hits=innocent_hits+1 WHERE uid=715
> AND token IN
> ('5719708924817070673','15748594722301702351','12523852096982625759','15747193994780569807','7072984635321818198','8566666067201058025','3214617105374076928','667817407838676578','2746530540300936818','7855920995652397412','5465147151345906752','5252602293815960809','12797608577848435519','7966374547679573945','12661687167322745101','1476367451104814719','15905813578389094591','7592741750694013197','15748943523967104959',
>
> During these locks DSPAM fails to analize the mails:
>
> 2014-07-02T09:28:14.901941+02:00 02ucas amavis[32751]: (32751-06)
> spam-tag, <OMISSIS@OMISSIS> -> <OMISSIS@OMISSIS>, No, score=-1.275
> tagged_above=-999 required=4.5 tests=[DSPAM_ERROR=0.1,
> FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_LOW=-0.7,
> RCVD_IN_MSPIKE_H2=-0.676, SPF_PASS=-0.001] autolearn=disabled
> 2014-07-02T09:28:30.486044+02:00 01ucas amavis[21712]: (21712-18)
> spam-tag, <OMISSIS@OMISSIS> -> <OMISSIS@OMISSIS>, No, score=-4.93
> tagged_above=-999 required=4.5 tests=[DSPAM_ERROR=0.1,
> HEADER_FROM_DIFFERENT_DOMAINS=0.001, RCVD_IN_DNSWL_HI=-5,
> RCVD_IN_MSPIKE_H3=-0.01, RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001,
> T_RP_MATCHES_RCVD=-0.01] autolearn=disabled
> 2014-07-02T09:28:39.428388+02:00 01ucas amavis[28266]: (28266-03)
> spam-tag, <OMISSIS@OMISSIS> -> <OMISSIS@OMISSIS>, No, score=-0.617
> tagged_above=-999 required=4.5 tests=[DSPAM_ERROR=0.1,
> FREEMAIL_FORGED_FROMDOMAIN=0.001, FREEMAIL_FROM=0.001,
> HEADER_FROM_DIFFERENT_DOMAINS=0.001, HTML_MESSAGE=0.001,
> RCVD_IN_DNSWL_LOW=-0.7, RCVD_IN_MSPIKE_H3=-0.01,
> RCVD_IN_MSPIKE_WL=-0.01, SPF_PASS=-0.001] autolearn=disabled
> 2014-07-02T09:28:44.233983+02:00 03ucas amavis[25593]: (25593-09)
> spam-tag, <OMISSIS@OMISSIS> -> <OMISSIS@OMISSIS>, No, score=2.8
> tagged_above=-999 required=4.5 tests=[DSPAM_ERROR=0.1,
> HEADER_FROM_DIFFERENT_DOMAINS=0.001, HTML_IMAGE_ONLY_28=0.726,
> HTML_IMAGE_RATIO_02=0.805, HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.105,
> RCVD_IN_DNSWL_NONE=-0.0001, RCVD_IN_MSPIKE_H5=-1,
> RCVD_IN_MSPIKE_WL=-0.01, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001,
> T_RP_MATCHES_RCVD=-0.01, URIBL_GREY=1.084] autolearn=disabled
> 2014-07-02T09:28:45.563818+02:00 03ucas amavis[25462]: (25462-11)
> spam-tag, <OMISSIS@OMISSIS> -> <OMISSIS@OMISSIS>, No, score=-0.814
> tagged_above=-999 required=4.5 tests=[DSPAM_ERROR=0.1,
> MISSING_DATE=1.396, RCVD_IN_DNSWL_MED=-2.3, T_RP_MATCHES_RCVD=-0.01]
> autolearn=disabled
>
Hi Marco,
Your best bet is to increase the granularity of the SQL statements that
you are running to make the time taken per piece short enough to prevent
scanning problems. We currently process by first letter of the account
to break it into 26 smaller pieces. This is with DSPAM 3.6.8 w/ MySQL and
MYISM tables. We are moving to DSPAM 3.10.2 w/ PostgreSQL and a partitioned
set of backend tables to allow us to process each piece independently. You
may be able to use partitioning with MySQL, but I am not familiar enough
with it to be certain. Breaking the query up will definitely work since we
do that here for the same reason.
Regards,
Ken
------------------------------------------------------------------------------
Open source business process management suite built on Java and Eclipse
Turn processes into business applications with Bonita BPM Community Edition
Quickly connect people, data, and systems into organized workflows
Winner of BOSSIE, CODIE, OW2 and Gartner awards
http://p.sf.net/sfu/Bonitasoft
_______________________________________________
Dspam-user mailing list
Dspam-user@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/dspam-user
