Date: Mon, 30 Apr 2007 19:07:21 -0700 From: Jeremy Chadwick <koitsu@FreeBSD.org> To: Ken Chen <ken73.chen@gmail.com> Cc: freebsd-stable@freebsd.org Subject: Re: mysql frequently crash on 6.2 Message-ID: <20070501020721.GA44472@icarus.home.lan> In-Reply-To: <d018a9bd0704301811n47a34e7ax457f527f083e49e0@mail.gmail.com> References: <d018a9bd0704301811n47a34e7ax457f527f083e49e0@mail.gmail.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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 |
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20070501020721.GA44472>