Crashing repeatedly: 6.2-RELEASE-p5 and MySQL 5.0.41

[Jeremy Chadwick]

On Mon, Feb 04, 2008 at 12:50:32PM +0000, Primeroz lists wrote:
> we are experiencing repeated crash on a Dell PowerEdge 2950 (rev 1 or 2).
> 
> FBSD release is 6.2-RELEASE-p5 , AMD64. 2xXeon QuadCore and 8G of Ram.
> MySQL Version is 5.0.41 with following configuration settings:
> {snip}

There's additional information needed to help with this:

1) Contents of /boot/loader.conf
2) What scheduler you're using in your kernel configuration
3) Your kernel configuration in its entirity, if possible :-)
4) What options you compiled/built mysql50-server with

Chances are your problem is related to process size limits.  The most
important part is how you've tuned /boot/loader.conf -- I'm betting you
haven't.  In my experience, mysqld will either segfault or in some cases
cause the entire box to panic when kern.maxdsiz, kern.dfldsiz, and
kern.maxssiz are not adjusted in loader.conf.

This is taken from loader.conf on our production SQL server running
RELENG_6, i386, with 2GB RAM:


# Increase maximum allocatable memory on a process to 1536MB.
# We don't choose 2GB (our amount of RAM) since that would
# exhaust all memory, and result in a kernel panic.  Maximum
# stack size is still set to 128MB.
#
# dfldsiz = Initial data size limit (bytes)
# maxdsiz = Maximum data size limit (bytes)
# dflssiz = Initial stack size limit (bytes)
# maxssiz = Maximum stack size limit (bytes)
#
kern.maxdsiz="1536M"
kern.dfldsiz="1536M"
kern.maxssiz="128M"


Some other comments:

> set-variable    = key_buffer=768M
> set-variable    = table_cache=800
> set-variable    = sort_buffer=24M
> set-variable    = myisam_sort_buffer_size=256M
> set-variable    = record_buffer=16M
> set-variable    = max_allowed_packet=10M
> set-variable    = thread_stack=128K
> set-variable    = join_buffer=512M
> set-variable    = max_heap_table_size=256M
> set-variable    = max_connections=300
> set-variable    = tmp_table_size=384M
> set-variable    = query_cache_size=402653184
> set-variable    = query_cache_limit=134217728
> set-variable    = read_rnd_buffer_size=10M
> set-variable    = ft_min_word_len=1
> pid-file        = /var/db/mysqld.pid
> tmpdir          = /var/tmp
> ft_stopword_file = ''
> set-variable    = thread_cache_size=80
> set-variable    = myisam_stats_method=nulls_equal

These tunings seem fairly "random", in that they almost look like
someone just picked arbitrary values rather than reading how they all
work together and how exactly they impact the system.  This is a
very common (and bad) habit people have when "tuning" mysql; they
just fiddle around.

For comparison, using the same box of ours mentioned above:

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


Also, please remove pid-file from your my.cnf -- it serves zero purpose,
and if placed in a location which isn't where rc.d/mysql expects it to
be, could lead to problems when shutting down/starting up the server.
The rc.d/mysql script takes care of this for you, so don't override it.

Finally, I will take a moment to urge you to upgrade that box to
RELENG_7.  SCHED_ULE was re-written and specifically tested with mysqld
in mind, and are quite impressive.  The fact you're using a pair of quad
core CPUs would be reason enough to upgrade.  RELENG_6 will soon be on
its way out the door, so it's an inevitable upgrade anyways.

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