SQL Logging problem

[Ugo Bellavance]

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/MailWat ch.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?

16 procs and 32GB ram ?  What is your daily volume?  I strongly suggest 
having your MySQL server on a separte server, with the fastest disks or 
array that you can find and maybe do your selects on a slave, while 
inserting on the master.

Ugo