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
# [EMAIL PROTECTED]: spamass[spamass] @ [10.0.2.4]
# 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.
innodb_flush_log_at_trx_commit=0
