threads/71725: Mysql Crashes frequently giving Sock Error

[Birju Shah]

>Number:         71725
>Category:       threads
>Synopsis:       Mysql Crashes frequently giving Sock Error
>Confidential:   no
>Severity:       serious
>Priority:       high
>Responsible:    freebsd-threads
>State:          open
>Quarter:        
>Keywords:       
>Date-Required:
>Class:          sw-bug
>Submitter-Id:   current-users
>Arrival-Date:   Tue Sep 14 09:50:06 GMT 2004
>Closed-Date:
>Last-Modified:
>Originator:     Birju Shah
>Release:        4.8
>Organization:
>Environment:
FreeBSD hecht.ai.net 4.8-RELEASE FreeBSD 4.8-RELEASE #1: Sun Jun 22 05:42:39 EDT 2003     ainet at mercury.ai.net:/usr/src/sys/compile/www  i386
>Description:
------------------OUR PROBLEM--------------------------------------------------

We have a autoresponder script(backend mysql) which maintains 100% double optin email lists, All those who submit their site to our search engine are sent a email which states that please confirm your email address, the users are sent a link where they can visit and enter their email and name and confirm their email address. 
Now the visitors visit the confirmation page and confirm their email address with us. This confirmation is handled by the autoresponder perl script and the backendis mysql.

We have kept a monitoring system on the confirmation script and there are times when the autoresponder script gives the timedout error( its a perl script). And when we notice the running process that time on the server, it shows around 50-80 people are simultaneously executing the confirmation script we discussed above. And immediately the mysql crashed and gives a mysql sock error. The only remedy remains is to reboot the server to restart the mysql. We tried restarting mysql without rebooting the server, but nothing happens, it doesnt restart. Once we reboot the server, everything works fine, till again we receive a timedout error and it crashes again.

We tried upgrading our RAM from 256 MB to 768MB, on our dedicated server, but the problem remains the same, absolutely no change. Very frankly speaking I am not a technical savy and I am not aware of the mysql techical issue, I have been reading a lot of forums for finding a solution to this issue, but in vain, I am hoping a lot out of you guys as you are the experts in this field.

Conclusion:
What I have noticed is that initially the mysql server gets overloaded and as soon as it gets overloaded, it starts giving mysql sock error and the only remedy remains is to reboot the server. It seems like a bug in pthread libc_r as i have gone through different forums but i am not sure.

My server configuration is
PIII 700MHz 
256M ram + 512MB ( Recently added)
1X9GB hard drive (SCSI) 
------------------------------------------------------------------------ 
 
When we check the hostname error log in mysql it shows this,
 
 
40903  5:53:06  InnoDB: Starting shutdown...
040903  5:53:08  InnoDB: Shutdown completed
040903  5:53:08  /usr/local/libexec/mysqld: Shutdown Complete
 
040903 05:53:08  mysqld ended
 
040903 06:00:10  mysqld started
040903  6:00:10  Warning: setrlimit returned ok, but didn't change limits. Max open files is 14781 (request: 81930)
040903  6:00:10  Warning: Changed limits: max_connections: 14771  table_cache: 64
040903  6:00:10  InnoDB: Started
/usr/local/libexec/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/tmp/mysql.sock'  port: 3306
Fatal error '_pq_remove: Not in priority queue' at line ? in file /usr/src/lib/libc_r/uthread/uthread_priority_queue.c (errno = ?)
 
Number of processes running now: 0
040903 08:10:22  mysqld restarted
040903  8:10:22  Warning: setrlimit returned ok, but didn't change limits. Max open files is 14781 (request: 81930)
040903  8:10:22  Warning: Changed limits: max_connections: 14771  table_cache: 64
040903  8:10:23  InnoDB: Started
/usr/local/libexec/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/tmp/mysql.sock'  port: 3306
040903 13:01:38  mysqld started
040903 13:01:38  Warning: setrlimit returned ok, but didn't change limits. Max open files is 14781 (request: 81930)
040903 13:01:38  Warning: Changed limits: max_connections: 14771  table_cache: 64
040903 13:01:40  InnoDB: Started
/usr/local/libexec/mysqld: ready for connections.
Version: '4.0.18-log'  socket: '/tmp/mysql.sock'  port: 3306
040903 14:57:09  /usr/local/libexec/mysqld: Normal shutdown
 
 
>How-To-Repeat:
No fix time, sometimes crashes once in a day and sometimes crashes once in 4 days. 
>Fix:
      
>Release-Note:
>Audit-Trail:
>Unformatted: