Crashing repeatedly: 6.2-RELEASE-p5 and MySQL 5.0.41

[Jeremy Chadwick]

On Mon, Feb 04, 2008 at 02:19:05PM +0000, Primeroz lists wrote:
> We using the 4bsd Scheduler (ULE is commented out in kernel conf)

That's good, as it confirms you're not hitting ULE bugs.

> > 3) Your kernel configuration in its entirity, if possible :-)
> 
> attached

I don't see anything in your kernel configuration that could cause this
sort of situation.  I think your kernel config is fine.

> > 4) What options you compiled/built mysql50-server with
> 
> As from Tinderbox log:
> 
> configure: running /bin/sh './configure' --prefix=/usr/local
> '--localstatedir=/var/db/mysql' '--without-debug' '--without-readline'
> '--without-libedit' '--without-bench' '--without-extra-tools'
> '--with-libwrap' '--with-mysqlfs' '--with-low-memory'
> '--with-comment=FreeBSD port: mysql-server-5.0.41'
> '--enable-thread-safe-client' '--with-named-thread-libs=-pthread'
> '--prefix=/usr/local' '--build=amd64-portbld-freebsd6.2' 'CC=cc'
> 'CFLAGS=-O2 -fno-strict-aliasing -pipe ' 'CXXFLAGS=-O2
> -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe
> -felide-constructors -fno-rtti -fno-exceptions' 'CXX=c++'
> 'build_alias=amd64-portbld-freebsd6.2' CFLAGS=' -DDBUG_OFF -O2
> -fno-strict-aliasing -pipe ' CXXFLAGS=' -DDBUG_OFF -O2
> -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe
> -felide-constructors -fno-rtti -fno-exceptions -fno-implicit-templates
> -fno-exceptions -fno-rtti -DMYSQLD_NET_RETRY_COUNT=1000000'
> --cache-file=/dev/null --srcdir=.

Okay, so no custom make variables for the port; this is the same
as what we use on our box.  You're also using pthreads, which should be
fine (we use it as well, and not LinuxThreads).

We use mysql-server-5.0.45 (fairly old, since newest is 5.0.51a); yours
is a bit older.  I'll have to look at the ChangeLog between 5.0.41 and
5.0.51a to see if there's any relevant changes which might give hints to
what's causing it.

I don't see any changes relevant between 5.0.41 and 5.0.45, 5.0.51,
nor 5.0.51a which could cause what you're seeing.

> I did not setup anything in the /boot/loader.conf so i guess i'm using
> default values for all of those settings,

See my other mail; amd64 might be doing something different with those
values (calculating them in a more appropriate manner?).

> Yes, configuration need fixing especially about InnoDB that is not
> configured/tuned at all.
> The one that take my attention more then others options is:
> 
> set-variable    = innodb_buffer_pool_size=512M
> 
> I'm sure i need to increase that value, i'm using the default that surely is
> not giving me the expected resaults ... but i wonder if that can lead to
> such a crash and not just a performance problem as i read on some sources on
> internet about mysql optimization.

It's very possible.  A lot of the tuning variables in mysqld have added
side-effects in FreeBSD from what I've seen -- that is to say, if the
FreeBSD box isn't configured with things like a large default data size
(confirmed by limits(1)), crashes can happen.  In our case it was
usually mysqld segfaulting with absolutely no details as to why, but in
other cases we'd seen kernel panics.  Ultimately once we tuned
loader.conf all of these went away, and we've had nothing but stability.

> We are thinking about an OS Upgrade but what i would really like is to
> understand what in mysql is triggering this crash before upgrading to new
> OS/Mysql and maybe mask the problem for long time.

Understood, but you need to keep in mind that if this is a FreeBSD bug
which has already been fixed, the fix itself may only exist in RELENG_7
and may not be backported.  I'm getting ahead of myself saying that, but
it is something to keep in mind.

A few more questions:

1) Shortly before the kernel panics, do you get any odd messages in
/var/log/messages or on the console itself?  (dmesg -a could help here).
I'm left wondering if maybe the problem is something else, like a disk
issue or other oddity and is manifesting itself in an odd way.

2) Are you remapping any libraries using /etc/libmap.conf, such as
transparently remapping pthread to kse?

3) How big are all the InnoDB tables?  If you're not sure, how big do
the ibdata* and ib_logfile* files grow to?

-- 
| Jeremy Chadwick                                    jdc at parodius.com |
| Parodius Networking                           http://www.parodius.com/ |
| UNIX Systems Administrator                      Mountain View, CA, USA |
| Making life hard for others since 1977.                  PGP: 4BD6C0CB |