Re: Bayes innodb problems

>>> 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 " 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.

> Actually this bayes DB has been around for a few months, and has been
> built up over time.

I experienced a problem with a custom innodb-stored database, where at one point a certain query suddenly took minutes instead of microseconds. Using EXPLAIN, I saw that indexes were used not as intended any more. I changed the query to include a FORCE INDEX clause, but later detected that an OPTIMIZE TABLE remedied the situation also. I saw this on two different databases that were built up over time from totally empty to many million records.
OPTIMIZE TABLE on innodb tables only defragments the index and updates the index statistics.

> This does make me wonder what regular DB maintenance tasks should be
> performed on the bayes DB. It sounds like some people let the code
> auto-expire, while some run cron jobs to expire data?

I added a TIMESTAMP column to the bayes_seen table, so I can expire this table by date. The other tables are maintained internally by SA, so don't do anything with them.

> Should I be running an optimize table every so often?

No, I don't think so. In my above mentioned databases, the problem never came back, and I didn't run optimize table since then (more than 1 year ago).

> mysql> explain SELECT count(*) FROM bayes_token WHERE id = '4' AND
> ('1190846660' - atime) > '345600';
> +----+-------------+-------------+------+--------------------------+------------------+---------+-------+--------+--------------------------+
> | id | select_type | table | type | possible_keys | key
> | key_len | ref | rows | Extra |
> +----+-------------+-------------+------+--------------------------+------------------+---------+-------+--------+--------------------------+
> | 1 | SIMPLE | bayes_token | ref | PRIMARY,bayes_token_idx2 |
> bayes_token_idx2 | 2 | const | 229946 | Using where; Using index |
> +----+-------------+-------------+------+--------------------------+------------------+---------+-------+--------+--------------------------+

Do you need help?
In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related spamassassin.apache.org Links users Request more information about Pantek

The "2" in the key_len column makes me wonder. That means only 2 bytes of the index are used. The index at my system includes id (INTEGER=4 bytes) and atime (INTEGER=4 bytes). 2 Bytes would be half of the id field, which is impossible. The key_len field always shows 4 at my system, so it uses the id part (the first half) of the index for lookup. Have you changed id to smallint? Then you have altered the table which has the same (side-)effect as optimize table. I don't think truncating the field to 2 bytes did the speedup, I think the side-effect of recreating the index did it. Received on Fri Sep 28 11:51:57 2007