From owner-freebsd-stable@FreeBSD.ORG Tue May 1 02:07:22 2007 Return-Path: X-Original-To: freebsd-stable@freebsd.org Delivered-To: freebsd-stable@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id 5448E16A408 for ; Tue, 1 May 2007 02:07:22 +0000 (UTC) (envelope-from jdc@koitsu.dyndns.org) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [204.127.192.82]) by mx1.freebsd.org (Postfix) with ESMTP id 3E5DD13C43E for ; Tue, 1 May 2007 02:07:22 +0000 (UTC) (envelope-from jdc@koitsu.dyndns.org) Received: from icarus.home.lan (c-71-198-0-135.hsd1.ca.comcast.net[71.198.0.135]) by comcast.net (rwcrmhc12) with ESMTP id <20070501020721m1200ch9q7e>; Tue, 1 May 2007 02:07:21 +0000 Received: by icarus.home.lan (Postfix, from userid 1000) id 694B21FA01D; Mon, 30 Apr 2007 19:07:21 -0700 (PDT) Date: Mon, 30 Apr 2007 19:07:21 -0700 From: Jeremy Chadwick To: Ken Chen Message-ID: <20070501020721.GA44472@icarus.home.lan> Mail-Followup-To: Ken Chen , freebsd-stable@freebsd.org References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.15 (2007-04-06) Cc: freebsd-stable@freebsd.org Subject: Re: mysql frequently crash on 6.2 X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 01 May 2007 02:07:22 -0000 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 |