mysql frequently crash on 6.2

[Jeremy Chadwick]

On Tue, May 01, 2007 at 09:11:28AM +0800, Ken Chen wrote:
>  After moving the database from Linux 2.4 to FreeBSD 6.2, the mysqld crash
>  very frequently! I think the problem is on FreeBSD when myusql is heaving
>  loading.
> 
>  I have another same machine with lower loading, the mysql is stable.

I can confirm this problem.  Normally sig11 is an indication that you
have hardware-related problems, but in this particular case (at least in
my experience), it can also be caused by some lack-of loader.conf
tunables permitting mysqld to allocate the amount of memory you're
claiming in my.cnf.

My loader.conf comments may not be absolutely correct (folks who know
the innards of the VM will probably correct me in my claims), but I can
confirm that increasing kern.maxdsiz/dfldsiz/maxssiz relieved all
bizarre sig11 issues we were seeing.

I'll use our production SQL server as an example:

# dmesg | grep ' memory'
real memory  = 1073676288 (1023 MB)
avail memory = 1041801216 (993 MB)

# top -d 1 -U mysql
Mem: 207M Active, 598M Inact, 138M Wired, 42M Cache, 111M Buf, 11M Free
Swap: 8192M Total, 16K Used, 8192M Free

  PID USERNAME  THR PRI NICE   SIZE    RES STATE    TIME   WCPU COMMAND
  914 mysql       6  20    0   754M   173M kserel  31:54  0.00% mysqld
  877 mysql       1   8    0  1812K   900K wait     0:00  0.00% sh

# pkg_info | grep ^mysql
mysql-client-5.0.27 Multithreaded SQL database (client)
mysql-server-5.0.27 Multithreaded SQL database (server)

(The mysql ports were not build with any special options; all were
literally their defaults.)

# mysqladmin version | grep 'Server version'
Server version          5.0.27


/boot/loader.conf :

# Increase maximum allocatable memory on a process to 768MB.
# (We don't choose 1GB (our max RAM) since that would exhaust
# all memory, and result in a kernel panic.)
# Set default memory size as 768MB.
# Maximum stack size is 128MB.
#
kern.maxdsiz="768M"
kern.dfldsiz="768M"
kern.maxssiz="128M"


my.cnf :

set-variable    = tmp_table_size=64M
set-variable    = max_allowed_packet=32M
set-variable    = table_cache=256
set-variable    = key_buffer_size=64M
set-variable    = join_buffer_size=8M
set-variable    = sort_buffer_size=8M
set-variable    = read_buffer_size=8M
set-variable    = query_cache_size=64M
set-variable    = query_cache_limit=32M

set-variable    = innodb_buffer_pool_size=512M
set-variable    = innodb_additional_mem_pool_size=20M
set-variable    = innodb_log_file_size=128M
set-variable    = innodb_log_buffer_size=8M


The my.cnf settings you see above were chosen somewhat carefully.
Initially I had all the *_buffer_size variables set to something
much higher, but after coming across some tuning documentation
on MySQL's site, I read that there's actually a "sweet spot" as
far as what you set the sizes to vs. how much memory you have.

Anyways, I hope this helps.  If not, I would definitely recommend
running memtest86 (not a 100% failsafe way to test memory, but it
usually catches obvious things) and/or swapping out some hardware.

-- 
| 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 |