From owner-freebsd-hackers@FreeBSD.ORG Wed Mar 6 18:30:37 2013 Return-Path: Delivered-To: freebsd-hackers@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:1900:2254:206a::19:1]) by hub.freebsd.org (Postfix) with ESMTP id 2C3B75A4; Wed, 6 Mar 2013 18:30:37 +0000 (UTC) (envelope-from nevans@talkpoint.com) Received: from mailbox.talkpoint.com (mailbox.talkpoint.com [204.141.10.74]) by mx1.freebsd.org (Postfix) with ESMTP id F1E14DE1; Wed, 6 Mar 2013 18:30:36 +0000 (UTC) Received: from localhost (localhost [127.0.0.1]) by mailbox.talkpoint.com (Postfix) with ESMTP id 04EFAF66006; Wed, 6 Mar 2013 13:30:30 -0500 (EST) X-Virus-Scanned: amavisd-new at talkpoint.com Received: from mailbox.talkpoint.com ([127.0.0.1]) by localhost (mailbox.talkpoint.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id PaZbCIxsonOB; Wed, 6 Mar 2013 13:30:25 -0500 (EST) Received: from pleiades.nextvenue.com (pleiades.nextvenue.com [204.141.15.194]) by mailbox.talkpoint.com (Postfix) with ESMTP id 325A0F66001; Wed, 6 Mar 2013 13:30:25 -0500 (EST) Date: Wed, 6 Mar 2013 13:30:24 -0500 From: Nick Evans To: Ivan Voras Subject: Re: MySQL Data_free = 0 Slowness (appending to files slow?) Message-ID: <20130306133024.24fcacd4@pleiades.nextvenue.com> In-Reply-To: References: <20130304155020.77e470da@pleiades.nextvenue.com> X-Mailer: Claws Mail 3.8.0 (GTK+ 2.24.6; amd64-portbld-freebsd9.0) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Cc: freebsd-hackers@freebsd.org X-BeenThere: freebsd-hackers@freebsd.org X-Mailman-Version: 2.1.14 Precedence: list List-Id: Technical Discussions relating to FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 06 Mar 2013 18:30:37 -0000 On Tue, 05 Mar 2013 18:12:19 +0100 Ivan Voras wrote: > I don't know if you've noticed, but: > > 1) In your slow case, the load is almost entirely in userspace (USR), > i.e. mysql, while in the fast case, the kernel load (SYS) is 5x-6x > greater. Unless the first case actually excercises your disks more > (unlikely since you have almost no IO load at all) it means that it's > mysql that is slow, not the kernel. > > 2) Ordinary iostat doesn't show the disk busy-time estimation. You > need to use "-x" (e.g. "iostat -x 1") to see the service time and busy > percentage stats. > > 3) you seem to have bursts of traffic, probably writing, which may be > caused by the file system IFF mysql is not doing anything to fsync the > data sooner (i.e. you have everything in a single transaction), but > the burst in the "fast" case is 388 MB while the burst in the "slow" > case is 100 MB > > 4) your benchmark seems to be single-threaded > > 5) You have a lot of 32 KB transactions, which probably means you have > created the file system in 9.x with the default options, leading to 32 > KB blocks. This is probably not the source of your current problems, > but in the future you might want to match the block size to innodb's > block size (16 KiB). You should also do that on the RAID array level > and match the block alignments. > > 6) Are you using innodb_file_per_table? You probably should. > 1) I had not noticed that. Thanks. 2) Here's iostat -x 1 output for both cases. slow: http://pleiades.nextvenue.com/mysql/iostat_x_slow.txt fast: http://pleiades.nextvenue.com/mysql/iostat_x_fast.txt 3) The load generators are constant, so I'm guessing this is file system related, but I can't see into MySQL very much to tell for sure. Would the ktraces show mysql doing fsync? These tables are all MyISAM so no transactions as such, and each super-smack agent is running insert queries individually. 4) Technically 2 threads. I am only running a single super-smack on each load server of which there are two. I tried to keep the test as simple as possible to start. 5) I'll trash the array and change the stripe to 16k as well as the block size and see if it helps. 6) As I said before, these are MyISAM tables, so they are already separate table files. Unfortunately, MySQL doesn't seem to have a reserve x space per table for MyISAM tables. I can manually do this for my high access tables by inserting bogus rows and then deleting them (which I may do) as a temporary fix.