spamassassin-users October 2010 archive
Main Archive Page > Month Archives  > spamassassin-users archives
spamassassin-users: Re: Bayes timeouts and database handle being

Re: Bayes timeouts and database handle being DESTROY'd without explicit disconnect

From: Dominic Benson <dominic_at_nospam>
Date: Tue Oct 26 2010 - 15:13:25 GMT
To: users@spamassassin.apache.org

On 26/10/10 15:38, Micah Anderson wrote:
> The databsae size is 2.74gig.
>
>
> top - 07:26:39 up 10 days, 20:37, 1 user, load average: 9.24, 6.80, 6.15
> Tasks: 24 total, 2 running, 22 sleeping, 0 stopped, 0 zombie
> Cpu(s): 83.3%us, 16.2%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.5%si, 0.0%st
> Mem: 8055876k total, 6890032k used, 1165844k free, 584364k buffers
> Swap: 1959912k total, 569432k used, 1390480k free, 5405264k cached
>
> PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
> 10744 mysql 20 0 655m 110m 5500 S 190 1.4 9296:14 mysqld
>
>
> I'm averaging around 150 mysql threads, with peaks during peak mail
> times.
>
>

The thing that jumps out at me from this is that MySQL is only using
~112MB of memory; sure your FS cache is a respectable size, but I would
expect MySQL itself to want to use some of the 1GB free memory. Which
would suggest that my.cnf needs tuning somewhat.

Have a look at SHOW STATUS; - a few particular things come to mind:
Innodb_buffer_pool_read_requests/Innodb_buffer_pool_reads
Innodb_buffer_pool_wait_free
Innodb_log_waits
Handler_read_%
Created_tmp_%
Sort_%
>>> and a few of these, although not that many:
>>>
>>> Oct 17 12:02:29 spamd3 spamd[6367]: prepare_cached(SELECT max(runtime) from bayes_expire WHERE id = ?) statement handle DBI::st=HASH(0xadbb060)still Active at /usr/share/perl5/Mail/SpamAssassin/BayesStore/SQL.pm line 722
>>>
>>
>> Try an EXPLAIN SELECT max(runtime) from bayes_expire WHERE id =<some value>; as you know it to be slow it might give a clue where to look to improve performance. Or try turning the general query log on for a while and see what queries are taking up time. MonYog is quite a nice frontend to this, but you can do it by hand fairly simply.
>>
> mysql> EXPLAIN SELECT max(runtime) from bayes_expire WHERE id = 5;
> +----+-------------+--------------+------+-------------------+-------------------+---------+-------+------+-------+
> | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
> +----+-------------+--------------+------+-------------------+-------------------+---------+-------+------+-------+
> | 1 | SIMPLE | bayes_expire | ref | bayes_expire_idx1 | bayes_expire_idx1 | 2 | const | 198 | |
> +----+-------------+--------------+------+-------------------+-------------------+---------+-------+------+-------+
> 1 row in set (0.00 sec)
>

This looks well indexed. I can only see it taking a long time if there
are locking issues with other queries.
> Note, this might be related to the post I made today about sa-learn
> --expire taking hours...
>

Very probably. Try SHOW FULL PROCESSLIST to see the whole query.
bayes_token looks like a problem table - how many rows are in it?
> micah
>
>
Dominic