From owner-freebsd-current@FreeBSD.ORG Fri Apr 30 13:14:42 2010 Return-Path: Delivered-To: freebsd-current@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [69.147.83.52]) by hub.freebsd.org (Postfix) with ESMTP id B554C106566C for ; Fri, 30 Apr 2010 13:14:42 +0000 (UTC) (envelope-from jasonjwwilliams@gmail.com) Received: from mail-pw0-f54.google.com (mail-pw0-f54.google.com [209.85.160.54]) by mx1.freebsd.org (Postfix) with ESMTP id 84B5E8FC12 for ; Fri, 30 Apr 2010 13:14:42 +0000 (UTC) Received: by pwi9 with SMTP id 9so106661pwi.13 for ; Fri, 30 Apr 2010 06:14:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:references:message-id:from:to :in-reply-to:content-type:content-transfer-encoding:x-mailer :mime-version:subject:date:cc; bh=0344Gq++/RziXzdpnT9DOK/86TI3dyzMRwWPkoFbJMo=; b=TKdh6IwUpLu1Ium3cmbVlyGhy9vnRWU9RCcEml9weX0fU6gZOoKZE0yodbuIvz1A/9 NxoBB5rAvcuek011eoiBtK1Q+oLbO4FsMyLNX9I5CDisEXW2XoK3OcMMxFZoVrbiBpEJ 2BHY04D2fQ7tS/czZ7zyGCHrT+Sagu93rrRx0= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=references:message-id:from:to:in-reply-to:content-type :content-transfer-encoding:x-mailer:mime-version:subject:date:cc; b=rFLeQwP9Wc6NqH7Rao2DYQ/vc9LUq2ZHLZiDs9x3Td0ro2m7/BSoDlI4qLj44qA3P0 /S2IJwjMp+5azGAgd+PsG3w9wS7ydXhi+kdUeXS9fSIux9ds0zqe6IXIGpkXuTqXAopZ FjgQwsnUA/ceYULAbvE+D7XRb/cwRjirhYm/k= Received: by 10.142.149.36 with SMTP id w36mr899812wfd.228.1272633274365; Fri, 30 Apr 2010 06:14:34 -0700 (PDT) Received: from [192.168.100.56] (71-209-52-18.bois.qwest.net [71.209.52.18]) by mx.google.com with ESMTPS id 23sm1865000pzk.10.2010.04.30.06.14.28 (version=TLSv1/SSLv3 cipher=RC4-MD5); Fri, 30 Apr 2010 06:14:29 -0700 (PDT) References: Message-Id: <80219502-EDAD-4745-A89B-813601D1C15E@gmail.com> From: "Jason J. W. Williams" To: Pierre Massat In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed; delsp=yes Content-Transfer-Encoding: 7bit X-Mailer: iPhone Mail (7E18) Mime-Version: 1.0 (iPhone Mail 7E18) Date: Fri, 30 Apr 2010 07:14:26 -0600 Cc: "freebsd-current@freebsd.org" Subject: Re: ZFS Stability & MySQL (Comments Requested) X-BeenThere: freebsd-current@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Discussions about the use of FreeBSD-current List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 30 Apr 2010 13:14:42 -0000 Hi Pierre, What was the iowait when the update hangs occurred? Also, what was the box's uptime approx when it happened? Have you surpassed that amount of uptime since? Were you able to DTrace MySQL to see what func was hanging? Thank you so much! -J Sent via iPhone Is your e-mail Premiere? On Apr 30, 2010, at 2:04, Pierre Massat wrote: > Hi everybody, > > On Thu, Apr 29, 2010 at 4:31 PM, Jason J. W. Williams > wrote: >> I've written before that we're considering moving to FreeBSD 8 from >> OpenSolaris and are heavily reliant on ZFS. Has anyone used FreeBSDs >> ZFS implementation for a high reliability environment like a >> database? >> If so, what are your experiences? > > I'm running MySQL 5.5 (Server version: 5.5.1-m2-log FreeBSD port: > mysql-server-5.5.1) on FreeBSD 8.0 with a quite recent kernel (FreeBSD > 8.0-STABLE (GENERIC) #0: Tue Feb 23 12:38:58 UTC 2010). I've done some > tweaks to ZFS, like limiting ARC size, followed advices like > recordsize matching InnoDB page size (i'm using InnoDB on my 4Go > database). We have 11% of reads and 89% of writes and we make around > 300 queries per second. > > The thing is it runs well except that once, I had a very big slowdown > on MySQL : UPDATE queries were taking 30 seconds (which is actually > the timeout in my.cnf) to complete. I can't tell if it was a MySQL > problem (as i'm using something which is not release yet i think) or > if it's due to ZFS. However, after a restart, everything was back to > normal and things are running well. We have a lot of memory, 32 Go > (yes, the box is actually oversize for what we're doing right now), > and we don't stress the server so much (it has 2x XeonQuand X55xx), > and that might be a reason why it runs well (we don't push ZFS and the > server to the limits). > > I hope it's useful for you. If you need anything more, please ask. > > -- > Pierre Massat