spamassassin-users mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Micah Anderson <mi...@riseup.net>
Subject Re: Bayes timeouts and database handle being DESTROY'd without explicit disconnect
Date Tue, 26 Oct 2010 14:38:13 GMT
Dominic Benson <dominic@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


Mime
View raw message