MySQL 4.1 SIGBUS failiure with FreeBSD 6.0

[Glenn Nielsen]

MySQL 4.1 SIGBUS failiure with FreeBSD 6.0

We recently put four FreeBSD 6.0 servers with MySQL 4.1 into
production. MySQL on three of these servers is moderately busy.
Each requiring between 60-200 threads to handle anywhere from 6-40
queries per second during business hours. One is a Master DB,
the other two are RO Slaves. The fourth where we haven't seen
any problems is a hotbackup slave.

Since going into production three days ago the Master DB has
failed twice. The RO Slaves are failing 1-4 times per hour.
In all cases the failure is a signal 10 for a SIGBUS error.
The mysqld_safe script successfully restarts mysqld after each
failure.

I am posting on this list first to see if anyone has any ideas
of how to resolve this problem before submitting either a
FreeBSD or MySQL bug report.

I enabled core files with the "--core-file" argument to the
start script.

I have examined six core files now. All look very similar.

Core was generated by `mysqld'.
Program terminated with signal 10, Bus error.

(gdb) bt
#0  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
#1  0x285190a2 in sigaction () from /usr/lib/libpthread.so.2
#2  0x2851318d in pthread_kill () from /usr/lib/libpthread.so.2
#3  0x0815a1bf in ?? ()
#4  0x0841b000 in ?? ()
#5  0x0000000a in ?? ()
#6  0xbfbfdee8 in ?? ()
#7  0x0000000a in ?? ()
#8  0xbfbfe230 in ?? ()
#9  0x0000000a in ?? ()
#10 0xbfbfdef8 in ?? ()
#11 0x080aaf95 in ?? ()
#12 0x0000000a in ?? ()
#13 0x0835b8da in ?? ()
#14 0x000403fb in ?? ()
#15 0x00000000 in ?? ()
#16 0xbfbfdf30 in ?? ()
#17 0x2852c4b4 in ?? () from /usr/lib/libpthread.so.2
#18 0xbfbfdf28 in ?? ()
#19 0x28517252 in sigaction () from /usr/lib/libpthread.so.2

(gdb) info threads
* 166 Thread 0x841b000 (LWP 100330)  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
  165 Thread 0x841bc00 (LWP 100188)  0x28529277 in pthread_testcancel () from /usr/lib/libpthread.so.2
  164 Thread 0x841be00 (LWP 100206)  0x28529337 in pthread_testcancel () from /usr/lib/libpthread.so.2
  163 Thread 0xb22a400 (LWP 100205)  0x28573833 in read () from /lib/libc.so.6
  162 Thread 0xb22a600 (LWP 100175)  0x28529277 in pthread_testcancel () from /usr/lib/libpthread.so.2
  161 Thread 0xb22a800 (LWP 100211)  0x28529277 in pthread_testcancel () from /usr/lib/libpthread.so.2
  160 Thread 0xb22aa00 (LWP 100204)  0x28573833 in read () from /lib/libc.so.6
  159 Thread 0xb22ac00 (LWP 100257)  0x28573833 in read () from /lib/libc.so.6
  158 Thread 0xb22ae00 (LWP 100258)  0x28573833 in read () from /lib/libc.so.6
  157 Thread 0xb2eb000 (LWP 100259)  0x28573833 in read () from /lib/libc.so.6
  ...

In every case the SIGBUS error is occurring when mysql is killing off
some threads.

MySQL has a varible for setting the thread_cache_size. By default it is 0,
meaning mysql creates threads as needed and kills off threads which are no
longer needed almost immediately.

We configured the thread_cache_size=50 so that mysql would maintain a pool
of threads to reduce the frequency at which it did a thread kill. This has
reduced the frequency of failures down to less than once an hour.

We are using mysql-server-4.1.18_2 from ports with native threads.

Here is some information on the system hardware and kernel config.

vai and blackmore:

Dell PowerEdge 1850
2x2.8 Ghz P4
1024 MB
2 x 36 GB disks (RAID1)

FreeBSD vai.kinetic.more.net 6.0-RELEASE FreeBSD 6.0-RELEASE #0: Mon Jan 30 15:40:24 CST 2006
FreeBSD blackmore.kinetic.more.net 6.0-RELEASE-p5 FreeBSD 6.0-RELEASE-p5 #1: Tue Mar 21 13:44:15 CST 2006

hendrix and satriani:

Dell PowerEdge 2650
2 x 2.8 Ghz P4
2048 MB
2 x 36 GB disks (RAID1)

FreeBSD satriani.kinetic.more.net 6.0-RELEASE-p4 FreeBSD 6.0-RELEASE-p4 #0: Tue Mar  7 14:32:43 CST 2006
FreeBSD hendrix.kinetic.more.net 6.0-RELEASE-p5 FreeBSD 6.0-RELEASE-p5 #0: Wed Mar 15 10:54:10 CST 2006

The kernel on both platforms is the same (we include GENERIC and add      
a couple of options):

# QUOTAS_SMP

include GENERIC

ident           MOREnet-SMP

maxusers        0

options        IPFILTER                #ipfilter support
options        IPFILTER_LOG            #ipfilter logging

# To make an SMP kernel, the next line is needed
options         SMP                     # Symmetric MultiProcessor
Kernel

options        QUOTA

Hyper Threading is disabled in the BIOS on all four servers.

Thanks,

Glenn Nielsen

----------------------------------------------------------------------
Glenn Nielsen             [hidden email] | /* Spelin donut madder    |
MOREnet System Programming               |  * if iz ina coment.      |
Missouri Research and Education Network  |  */                       |
----------------------------------------------------------------------
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"

[Kurt Jaeger-3]

Hi!

On Wed, Mar 22, 2006 at 03:51:10PM -0600, Glenn Nielsen wrote:
> MySQL 4.1 SIGBUS failiure with FreeBSD 6.0
[...]
> (gdb) bt
> #0  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
> #1  0x285190a2 in sigaction () from /usr/lib/libpthread.so.2
> #2  0x2851318d in pthread_kill () from /usr/lib/libpthread.so.2
[...]
> We are using mysql-server-4.1.18_2 from ports with native threads.

There were some discussions on -current and -stable about this topic.

See

http://bugs.mysql.com/bug.php?id=12251

for some details. Greg seems to work on it somehow.

See also

http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html

and have a look into

http://lists.freebsd.org/mailman/htdig/freebsd-stable/2005-March/013241.html

There was some discussion on compiling it with linuxthreads instead
of pthreads.

--
MfG/Best regards, Kurt Jaeger                                  14 years to go !
LF.net GmbH        fon [phone]  [hidden email]  
Ruppmannstr. 27    fax [phone]
D-70565 Stuttgart  mob [phone]
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"

[Justin Bastedo]

There was also this thread on ther freebsd-database list a while back.

http://www.daemonnews.org/mailinglists/FreeBSD/freebsd-database/msg01178.html

couldn't search through the actual freebsd site for some reason but
google found it.

There the author said dropping back down to 4.11 seemed to solve the
problem. I am running a MySQL 5 server on a dual, dualcore amd 64
system with a max queries persecond i think we hit was in the 200 -
300 range. If I remember back to the conversations then, I think the
problem started to surface at about 500 or so queries persecond. I
haven't had any problems with MySQL 5 and I really think its worth the
step up. It seems some people at MySQL looked into the problem for
that thread I linked to but i don't know if any type of solution was
found on the MySQL side. It just seems the 4.x doesn't like the high
loads on FreeBSD for some reason.



On 3/22/06, Kurt Jaeger <[hidden email]> wrote:
> Hi!

--
Justin Bastedo At Gmail Dot Com
--------------------------------------------------
http://www.thebastedo.com

>
> On Wed, Mar 22, 2006 at 03:51:10PM -0600, Glenn Nielsen wrote:
> > MySQL 4.1 SIGBUS failiure with FreeBSD 6.0
> [...]
> > (gdb) bt
> > #0  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
> > #1  0x285190a2 in sigaction () from /usr/lib/libpthread.so.2
> > #2  0x2851318d in pthread_kill () from /usr/lib/libpthread.so.2
> [...]
> > We are using mysql-server-4.1.18_2 from ports with native threads.
>

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"

[Glenn Nielsen]

Thanks for the reply.

I had already found the below in a Google search.

Today I am going to try and reproduce the bug on test box.
If I can reproduce the bug I will first try building mysql
with linux threads to see if that solves the problem.

Glenn

On Thu, Mar 23, 2006 at 12:19:07AM -0800, Justin Bastedo wrote:

> There was also this thread on ther freebsd-database list a while back.
>
> http://www.daemonnews.org/mailinglists/FreeBSD/freebsd-database/msg01178.html
>
> couldn't search through the actual freebsd site for some reason but
> google found it.
>
> There the author said dropping back down to 4.11 seemed to solve the
> problem. I am running a MySQL 5 server on a dual, dualcore amd 64
> system with a max queries persecond i think we hit was in the 200 -
> 300 range. If I remember back to the conversations then, I think the
> problem started to surface at about 500 or so queries persecond. I
> haven't had any problems with MySQL 5 and I really think its worth the
> step up. It seems some people at MySQL looked into the problem for
> that thread I linked to but i don't know if any type of solution was
> found on the MySQL side. It just seems the 4.x doesn't like the high
> loads on FreeBSD for some reason.
>
>
>
> On 3/22/06, Kurt Jaeger <[hidden email]> wrote:
> > Hi!
>
> --
> Justin Bastedo At Gmail Dot Com
> --------------------------------------------------
> http://www.thebastedo.com
>
>
> >
> > On Wed, Mar 22, 2006 at 03:51:10PM -0600, Glenn Nielsen wrote:
> > > MySQL 4.1 SIGBUS failiure with FreeBSD 6.0
> > [...]
> > > (gdb) bt
> > > #0  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
> > > #1  0x285190a2 in sigaction () from /usr/lib/libpthread.so.2
> > > #2  0x2851318d in pthread_kill () from /usr/lib/libpthread.so.2
> > [...]
> > > We are using mysql-server-4.1.18_2 from ports with native threads.
> >

----------------------------------------------------------------------
Glenn Nielsen             [hidden email] | /* Spelin donut madder    |
MOREnet System Programming               |  * if iz ina coment.      |
Missouri Research and Education Network  |  */                       |
----------------------------------------------------------------------
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"

[Glenn Nielsen]

Thanks for the reply.

I had run across most of your links below in my Google search.

Today I am going to try and reproduce the bug on a test box.
If I can reproduce the bug the first thing I will try is building
mysql with linux threads to see if that solves the problem.

Glenn

On Thu, Mar 23, 2006 at 06:48:31AM +0100, Kurt Jaeger wrote:

> Hi!
>
> On Wed, Mar 22, 2006 at 03:51:10PM -0600, Glenn Nielsen wrote:
> > MySQL 4.1 SIGBUS failiure with FreeBSD 6.0
> [...]
> > (gdb) bt
> > #0  0x285292b7 in pthread_testcancel () from /usr/lib/libpthread.so.2
> > #1  0x285190a2 in sigaction () from /usr/lib/libpthread.so.2
> > #2  0x2851318d in pthread_kill () from /usr/lib/libpthread.so.2
> [...]
> > We are using mysql-server-4.1.18_2 from ports with native threads.
>
> There were some discussions on -current and -stable about this topic.
>
> See
>
> http://bugs.mysql.com/bug.php?id=12251
>
> for some details. Greg seems to work on it somehow.
>
> See also
>
> http://www.krellis.org/unix-stuff/mysql-freebsd-threads.html
>
> and have a look into
>
> http://lists.freebsd.org/mailman/htdig/freebsd-stable/2005-March/013241.html
>
> There was some discussion on compiling it with linuxthreads instead
> of pthreads.
>
> --
> MfG/Best regards, Kurt Jaeger                                  14 years to go !
> LF.net GmbH        fon [phone]  [hidden email]  
> Ruppmannstr. 27    fax [phone]
> D-70565 Stuttgart  mob [phone]

----------------------------------------------------------------------
Glenn Nielsen             [hidden email] | /* Spelin donut madder    |
MOREnet System Programming               |  * if iz ina coment.      |
Missouri Research and Education Network  |  */                       |
----------------------------------------------------------------------
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-database
To unsubscribe, send any mail to "[hidden email]"