found a client with a slowdown that started at 7am this am (maint window
is 3am to 4am).
nothing changed (tm).
2gb ram, 4cpu, dual scsi raid, freebsd mysql 5.1.26.
penpals is enabled, and mytop showed it hung up doing the penpals query:
823578 amavis localhost mail 2782 Query SELECT
msgs.time_num, msgs.mail_id, subject FROM msgs JOIN msgrcpt ON
msgs.mail_id=msgrcpt.mail_id WHERE sid='261' AND rid='141' AND content!='V
823573 amavis localhost mail 2789 Query SELECT
msgs.time_num, msgs.mail_id, subject FROM msgs JOIN msgrcpt ON
msgs.mail_id=msgrcpt.mail_id WHERE sid='311' AND rid='364' AND content!='V
(mytop does n show all, but fullprocesslist confirmed this was query
running:
'sel_penpals' => # no message-id references list
"SELECT msgs.time_num, msgs.mail_id, subject".
" FROM msgs JOIN msgrcpt ON msgs.mail_id=msgrcpt.mail_id".
" WHERE sid=? AND rid=? AND content!='V' AND ds='P'".
" ORDER BY msgs.time_num DESC LIMIT 1",
manual testing showed some very strange behaviour (so I suspect
something happened to mysql install). I rebuild (create table new from
old; insert into new select * from old; rename old to delete,new to
old;drop table table delete) on msgs qnd msgrcpt;
no foreign keys, using innodb, VERY LIGHT ACTIVITY ON HOST.
above query takes 60 seconds.
even twice (after letting cache settle in).
I took the 'limit 1' off, and its less then 1 second.
I look the 'order by msgs.time_num' off and its less than 1 second.
desc shows valid keys on time_num, mail_id, all, according to everything
I have already in place at sites with 10x the volume.
(so, yes, suspect a sql problem somewhere).
Q: I assume order by msgs.time_num desc is to find the newest entry,
limit 1 keeps only one.
does amavis have to use the limit 1? or will it barf?
only way I could fix this till I figure out what is wrong with mysql is
to modify amavisd.conf with a custom penpals without the order by.
I guess I could disable pen pals, since without the order, I suppose its
worthless anyway.
not running out of ram, lots of cpu left, hd isn't being hammered. and
As I said, I manually rebuild the msgs and msgrcpt table.
--
Michael Scheidell, CTO
Phone: 561-999-5000, x 1259
> *| *SECNAP Network Security Corporation
* Certified SNORT Integrator
* King of Spam Filters, SC Magazine 2008
* Information Security Award 2008, Info Security Products Guide
* CRN Magazine Top 40 Emerging Security Vendors
_________________________________________________________________________
This email has been scanned and certified safe by SpammerTrap(r).
For Information please see http://www.spammertrap.com
_________________________________________________________________________
-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
AMaViS-user mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/amavis-user
AMaViS-FAQ:http://www.amavis.org/amavis-faq.php3
AMaViS-HowTos:http://www.amavis.org/howto/
