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
