Dominic Benson <domi...@lenny.cus.org> writes:
> On 19 Oct 2010, at 17:05, Micah Anderson wrote:
>
>>
>> Hello,
>>
>> I'm running a busy mail server. We've got a bayes database on its own
>> server, with InnoDB tables.
>
> What is your total DB size / server RAM? Could you include a snapshot of the
> output of top from the DB server? I would guess that your problem is
> indexing/tuning or server capacity MySQL side rather than in SA, but without
> more data it is just a guess.
The databsae size is 2.74gig.
$ free
total used free shared buffers cached
Mem: 8055876 6872740 1183136 0 584032 5403916
-/+ buffers/cache: 884792 7171084
Swap: 1959912 569432 1390480
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
10765 stunnel4 20 0 123m 109m 1416 S 2 1.4 179:38.73 stunnel4
1 root 20 0 1984 636 548 S 0 0.0 2:40.15 init
397 bind 20 0 82856 23m 2632 S 0 0.3 0:46.72 named
1812 root 20 0 3120 1176 772 S 0 0.0 0:15.04 syslog-ng
3551 messageb 20 0 2488 648 488 S 0 0.0 0:00.00 dbus-daemon
3610 nobody 20 0 6368 2668 888 S 0 0.0 0:11.94 nagios-statd
4828 root 20 0 5484 1824 1476 S 0 0.0 0:09.44 master
10707 root 20 0 3784 1276 1076 S 0 0.0 0:00.02 mysqld_safe
10745 root 20 0 2892 608 532 S 0 0.0 0:00.00 logger
10760 stunnel4 20 0 3836 688 348 S 0 0.0 1:25.14 stunnel4
10761 stunnel4 20 0 3836 692 352 S 0 0.0 1:16.94 stunnel4
10762 stunnel4 20 0 3836 692 352 S 0 0.0 1:16.24 stunnel4
10763 stunnel4 20 0 3836 692 352 S 0 0.0 1:16.45 stunnel4
10764 stunnel4 20 0 3836 692 352 S 0 0.0 1:20.77 stunnel4
11311 root 20 0 2044 888 704 S 0 0.0 0:09.02 cron
15444 postfix 20 0 5496 1788 1452 S 0 0.0 0:00.00 pickup
I'm averaging around 150 mysql threads, with peaks during peak mail
times.
>> 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)
Note, this might be related to the post I made today about sa-learn
--expire taking hours...
micah
