You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@spamassassin.apache.org by Dan <ka...@gmail.com> on 2006/08/03 18:23:52 UTC
postres bayes db and high load
Over the past few weeks, my company's mail server has been experiencing high
loads that result in SA skipping emails. I use a postgres database to
manage bayes, awl and userprefs. I am pretty sure that it is the bayes db
that is causing the high load and resultant skipping, but I have no idea how
to fix the problem. I installed the SA DBI plugin in hopes this would
decrease the load, but it hasn't. I have also tried increasing spamd's
max-children parameter from 8 up to 27.
It's appears that if all of the spamd's children become busy SA skips the
message all together. Or spamd stops working on a message when bayes times
out. If the latter is the case, is there a way to tell spamd to continue
processing the message without bayes?
I have included some details below. Any suggestions would be very helpful.
The mail servers stats:
~3500 email/day
2GHz Intel Celeron
768M ram
SA v3.1.0
postgreSQL v8.0.4
database size: 333M
bayes_seen: 378275 rows
bayes_token: 172484 rows
a snippet of maillog when the disruption began:
Aug 2 14:47:59 mail spamd[32613]: prefork: child states:
BBBBBBBBBBBBBBBBBBBBBBBBBBB
Aug 2 14:47:59 mail spamd[32613]: prefork: server reached --max-clients
setting, consider raising it
Aug 2 14:47:59 mail spamd[3577]: spamd: connection from
localhost.localdomain [127.0.0.1] at port 49872
Aug 2 14:47:59 mail spamd[3577]: spamd: processing message
<44D0EAEB.8040004@photoresearchers.c
om> for steve:0
Aug 2 14:48:16 mail spamd[3675]: bayes: child processing timeout at
/usr/bin/spamd line 1088.
Aug 2 14:48:19 mail spamd[3675]: spamd: identified spam ( 25.9/5.0) for
bug:0 in 5525.1 seconds, 2163 bytes.
Aug 2 14:48:19 mail spamd[3675]: spamd: result: Y 25 -
BAYES_99,MY_ALL_CAPS,MY_CASINO,MY_OFFER,
MY_URI_2CHAR,MY_URI_ALPHNM,MY_URI_CHARNUM,RAZOR2_CF_RANGE_51_100,RAZOR2_CF_RANGE_E8_51_100,RAZOR
2_CHECK,URIBL_BLACK,URIBL_JP_SURBL,URIBL_OB_SURBL,URIBL_SC_SURBL,URIBL_WS_SURBL
scantime= 5525.1,size=2163,user=bug,uid=0,required_score=5.0,rhost=
localhost.localdomain,raddr=127.0.0.1,rport=48946, mid=<
78127206270184.401EAB9F00@ZR4ZJR7>,bayes=1,autolearn=failed
Aug 2 14:48:35 mail spamd[3675]: __alarm__
Aug 2 14:48:35 mail spamd[3675]: __alarm__
Aug 2 14:48:36 mail spamd[32613]: prefork: child states:
BBBBBBBBBBBBBBBBBBBBBBBBBBB
Aug 2 14:48:36 mail spamd[32613]: prefork: server reached --max-clients
setting, consider raising it
Aug 2 14:48:36 mail spamd[3675]: spamd: connection from
localhost.localdomain [127.0.0.1] at port 49881
Aug 2 14:48:40 mail spamd[3675]: spamd: processing message
<63...@photoresearchers.com>
for harriet:0
Aug 2 14:50:06 mail spamd[3835]: bayes: child processing timeout at
/usr/bin/spamd line 1088, <GEN1402> line 59.
Re: postres bayes db and high load
Posted by Mark Martinec <Ma...@ijs.si>.
Dan,
> Over the past few weeks, my company's mail server has been experiencing
> high loads that result in SA skipping emails. I use a postgres database to
> manage bayes, awl and userprefs. I am pretty sure that it is the bayes db
> that is causing the high load ...
Are you using a general-purpose SQL module:
bayes_store_module Mail::SpamAssassin::BayesStore::SQL
or a dedicated and optimized:
bayes_store_module Mail::SpamAssassin::BayesStore::PgSQL
?
See file sql/README.bayes in the SA distribution.
Mark
Re: postres bayes db and high load
Posted by Dan <ka...@gmail.com>.
I do use BayesStore::PgSQL and although I don't have autovacuum turned on, I
do vacuum the database nightly (as well as a weekly vacuum full).
I didn't know that 8.1 offered was that much faster, but it's worth a shot.
I'll also have to check out amavisd-new 2.4. The pen-pal whitelisting
sounds good.
Thanks,
Dan
On 8/4/06, Mark Martinec <Ma...@ijs.si> wrote:
>
> > >postgreSQL v8.0.4
>
> SM writes:
> > Upgrade to Postgresql 8.1.4 if you can. Turn on autovacuum.
> > Use BayesStore::PgSQL.
>
> Very good advice.
>
> As an interesting side-information, I can say that
> when using pen-pals whitelisting with amavisd-new,
> SQL database maintenance operations (purging old records)
> is about an order of magnitude faster with PostgreSQL 8.1.4
> compared to MySQL 4.1.20.
>
> Mark
>
Re: postres bayes db and high load
Posted by Mark Martinec <Ma...@ijs.si>.
> >postgreSQL v8.0.4
SM writes:
> Upgrade to Postgresql 8.1.4 if you can. Turn on autovacuum.
> Use BayesStore::PgSQL.
Very good advice.
As an interesting side-information, I can say that
when using pen-pals whitelisting with amavisd-new,
SQL database maintenance operations (purging old records)
is about an order of magnitude faster with PostgreSQL 8.1.4
compared to MySQL 4.1.20.
Mark
Re: postres bayes db and high load
Posted by SM <sm...@resistor.net>.
At 09:23 03-08-2006, Dan wrote:
>Over the past few weeks, my company's mail server has been
>experiencing high loads that result in SA skipping emails. I use a
>postgres database to manage bayes, awl and userprefs. I am pretty
>sure that it is the bayes db that is causing the high load and
>resultant skipping, but I have no idea how to fix the problem. I
>installed the SA DBI
[snip]
>postgreSQL v8.0.4
Upgrade to Postgresql 8.1.4 if you can. Turn on autovacuum. Use
BayesStore::PgSQL.
Regards,
-sm
Re: postres bayes db and high load
Posted by Davin Flatten <fl...@ecs.umass.edu>.
Dan-
Make sure you are vacuuming your database. I have seen similar
postgresql slow downs with a large database that has not been vacuumed.
For a permanent solution I would suggest migrating to mysql instead. I
love postgresql but it has a lot of overhead designed to make it a
transactional database that really the bayes database, awl, etc.. does
not really need. I am running my bayes database now out of mysql for
15K-30K messages a day with a bayes_token table of 50 million rows.
Runs like a charm.
-Davin
Re: postres bayes db and high load
Posted by "John D. Hardin" <jh...@impsec.org>.
On Thu, 3 Aug 2006, Dan wrote:
> The mail servers stats:
> ~3500 email/day
> 2GHz Intel Celeron
> 768M ram
Throw some more memory at it, if the motherboard supports it.
> Aug 2 14:48:19 mail spamd[3675]: spamd: identified spam ( 25.9/5.0) for
> bug:0 in 5525.1 seconds, 2163 bytes.
OUCH! 5500 seconds? It should *never* take more than a couple of
minutes (~120 sec) to score a message.
That *really* sounds like you're swap-thrashing.
What are your memory stats? (on Linux, "procinfo" or "cat
/proc/meminfo" and look for "swap total" vs. "swap free")
--
John Hardin KA7OHZ ICQ#15735746 http://www.impsec.org/~jhardin/
jhardin@impsec.org FALaholic #11174 pgpk -a jhardin@impsec.org
key: 0xB8732E79 - 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
A weapons registration phase ... 4) allows for a degree of control
to be exercised during the collection phase; 5) assists in the
planning of the collection phase; ...
-- the UN, who "doesn't want to confiscate guns"
-----------------------------------------------------------------------