spamassassin-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Alex Woick <>
Subject Re: Bayes innodb problems
Date Thu, 27 Sep 2007 09:14:15 GMT
Micah Anderson schrieb am 27.09.2007 02:20:

> processing has ground down to really slow. I'm seeing some incredibly
> long queries now in my slow-query log, such as:

Try an "optimize table <tabname>" for each of the sa tables. You just 
filled the database from scratch, so perhaps the counters/statistics do 
not reflect the actual value distribution yet.

> # Time: 070926 17:10:53
> # User@Host: spamass[spamass] @  []
> # Query_time: 758  Lock_time: 0  Rows_sent: 1  Rows_examined: 2205327
> SELECT count(*)
>                FROM bayes_token
>               WHERE id = '4'
>                 AND ('1190846660' - atime) > '345600';

More than 10 minutes for counting 2 mio rows is a bit long. You can try 
to look what Mysql is doing all the time. Execute a "show full 
processlist" from a mysql command line while the above query is running 
and look at the "State" column. If a SA-initiated query is waiting for a 
lock and actually doing nothing, you should see it there. You also see 
all the other queries that are currently running at this point and may 
be hogging the database server.

The database design and query design of Spamassassin is ok, even the 
appearently non-indexable term "('1190846660' - atime) > '345600'", 
since Mysql would not use the index on an optimized term anyway. Try an 
EXPLAIN of this statement - Mysql will always use only the first half 
for lookup (4 bytes) of the index, which covers only the id part.

> innodb_flush_log_at_trx_commit=1

Use value 0 for more performance and a small sacrifice of safety. See 
the comment in the default *.ini file:

# If set to 1, InnoDB will flush (fsync) the transaction logs to the
# disk at each commit, which offers full ACID behavior. If you are
# willing to compromise this safety, and you are running small
# transactions, you may set this to 0 or 2 to reduce disk I/O to the
# logs. Value 0 means that the log is only written to the log file and
# the log file flushed to disk approximately once per second. Value 2
# means the log is written to the log file at each commit, but the log
# file is only flushed to disk approximately once per second.

View raw message