SQL Logging problem

[R Wahyudi]

Ryan Sweat wrote:
> I'm having a problem with the SQL logging functionality in MailScanner 
> and hopefully someone can help me figure it out.  After MailScanner 
> starts, it works perfectly for a period of time, usually 30 minutes to 
> an hour, then it stops logging incoming email to the MySQL database.  
> The log shows "Logging message to SQL" but it never finishes with the 
> usual "Logged message to SQL.  When this happens, the queue builds 
> incredibly fast and there are huge delays in delivering email -- often 
> many hours of delay.  Currently the MySQL database is local, but I had 
> the same problem logging to a remote database where an older version 
> of MailScanner is currently running (and logging) without problems.   
> I put some custom debugging in the CustomFunctions/MailWatch.pm and I 
> can tell that the connection to the database is failing here and never 
> seems to recover.
>
> # Failed to connect - kick off new child, wait, and try again
> InitMailWatchLogging();
>
> I ran strace on the MailWatch SQL process until the problem occurred 
> and it doesn't really show any good info, other than it eventually 
> gets stuck waiting for data.
>
> 24731 write(3, "<22>Dec 24 15:15:20 MailScanner[24731]: 
> lBOGSJ16013580: Logged to MailWatch SQL\n\0", 81) = 81
> 24731 accept(7, {sa_family=AF_INET, sin_port=htons(57527), 
> sin_addr=inet_addr(" 127.0.0.1 <http://127.0.0.1>")}, 
> [13249590112313933840]) = 10
> 24731 close(9)                          = 0
> 24731 ioctl(10, SNDCTL_TMR_TIMEBASE or TCGETS, 0x7fffaedc7350) = -1 
> EINVAL (Invalid argument)
> 24731 lseek(10, 0, SEEK_CUR)            = -1 ESPIPE (Illegal seek)
> 24731 ioctl(10, SNDCTL_TMR_TIMEBASE or TCGETS, 0x7fffaedc7350) = -1 
> EINVAL (Invalid argument)
> 24731 lseek(10, 0, SEEK_CUR)            = -1 ESPIPE (Illegal seek)
> 24731 fcntl(10, F_SETFD, FD_CLOEXEC)    = 0
> 24731 alarm(9600)                       = 9600
> 24731 read(10,
>
> # ps auwx | grep Mail
> root     22372  0.0  0.0 152796 31408 ?        Ss   14:58   0:00 
> MailScanner: master waiting for children, sleeping
> root     22373  8.7  0.3 237232 104848 ?       S    14:58   3:24 
> MailScanner: finishing batch
> root     22387  7.8  0.3 242212 107484 ?       S    14:58   3:02 
> MailScanner: finishing batch
> root     22396  8.4  0.3 237400 104764 ?       S    14:58   3:17 
> MailScanner: finishing batch
> root     22409  3.9  0.3 234432 103984 ?       S    14:58   1:33 
> MailScanner: finishing batch
>
> Strace on these MailScanner processes all show the same thing...
>
> connect(8, {sa_family=AF_INET, sin_port=htons(11553), 
> sin_addr=inet_addr("127.0.0.1 <http://127.0.0.1>")}, 16
>
> I check the port 11553 and it is indeed listening.  Restarting 
> MailScanner fixes the problem (for about 30 minutes).  I installed 
> MailScanner using the rpm packages/installation scripts on the 
> download page " Version 4.65.3-1 for RedHat, Fedora and Mandrake Linux 
> <http://www.mailscanner.info/files/4/rpm/MailScanner-4.65.3-1.rpm.tar.gz>".  
> The OS is Linux RHEL 5 64-bit, 16 processors and 32 GB ram.  Has 
> anyone experienced this before or have any advice?
>
> Thanks,
>
> Ryan
How big is your maillog table ?
How often did you prune your maillog table  ?
Did you optimize the table after you prune it ?

I found out that if the table get locked ... mailwatch stopped writing 
to it until you restart mailscanner !!!
I couldn't find any explanation on this but I definitely can recreate 
the problem.

MySQL seems to executes a select and then starts inserting.
The bigger the database, Insert,select, update, and delete operation 
will take more time and increase the chances of table get locked.

To get around this you can have a look at my roll over table patch which 
available at : 
http://mailwatch.sourceforge.net/doku.php?id=mailwatch:tipandtricks:rollover_table
The idea is to partition maillog table into smaller chunks, therefore 
one table will have less data.

Since you have been blessed with good amount of memory, try increase 
key_buffer_size.
A better work around is to make MailWatch to write to temporary table 
using memory (heap) engine and periodically flush it to the real table.



Regards,
Rianto Wahyudi


-- adela tirta belek




-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20071228/4b4cc0ff/attachment.html