From owner-freebsd-performance@FreeBSD.ORG Fri Jun 17 14:57:52 2005 Return-Path: X-Original-To: performance@freebsd.org Delivered-To: freebsd-performance@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 81BFB16A41C for ; Fri, 17 Jun 2005 14:57:52 +0000 (GMT) (envelope-from steve@lonres.com) Received: from anchor-post-32.mail.demon.net (anchor-post-32.mail.demon.net [194.217.242.90]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2445043D1D for ; Fri, 17 Jun 2005 14:57:52 +0000 (GMT) (envelope-from steve@lonres.com) Received: from mail.lonres.com ([194.70.153.187]) by anchor-post-32.mail.demon.net with esmtp (Exim 4.42) id 1DjII2-0008th-7E; Fri, 17 Jun 2005 14:57:50 +0000 Received: from bibipentium.lonres.com (bibipentium.lonres.com [10.10.10.225]) by mail.lonres.com (Postfix) with SMTP id F242E2E06C; Fri, 17 Jun 2005 15:57:49 +0100 (BST) Received: by bibipentium.lonres.com (sSMTP sendmail emulation); Fri, 17 Jun 2005 15:57:57 +0100 Date: Fri, 17 Jun 2005 15:57:57 +0100 From: Steve Roome To: David Sze Message-ID: <20050617145757.GD34777@bibipentium.lonres.com> References: <746fd037f6ca8131a8fb8938f1e346e9@lonres.com> <20050610170537.GA67849@bibipentium.lonres.com> <20050611085604.J75625@fledge.watson.org> <20050616161506.GB28794@bibipentium.lonres.com> <6.2.1.2.2.20050617091736.05949298@mail.distrust.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6.2.1.2.2.20050617091736.05949298@mail.distrust.net> User-Agent: Mutt/1.4.2.1i X-Mailman-Approved-At: Sat, 18 Jun 2005 12:37:27 +0000 Cc: performance@freebsd.org Subject: Re: FreeBSD MySQL still WAY slower than Linux X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 17 Jun 2005 14:57:52 -0000 I moved this thread to -performance as that's entirely what it's about now and we seem not to care which version of freebsd we'll try out. Thanks for your comments on this, rather than write you an essay (I just deleted that) here's the results I've just got from trying this with vmstat and iostat running at the same time. They're all running at the same time so it's a pain to read, but it looks to me like there's only very minimal filesystem activity either at the end or start of the query run. If that is the slow part then surely it must be VERY VERY slow for us to get twice the figures on Linux. (i.e. there's only disk access in the last second, and the rest of the query takes 4+ seconds to run.) It could be an issue, it could even be THE issue we have, but I'm not sure that it looks like it could be responsible for all of it. Also, note that the CPU is at 100% usage pretty much all the way through, and Linux still goes twice as fast ? There's got to be something else wrong. Or hopefully I'm just interpreting the figures (below) wrong. So, still getting about 15k qps. Anyway, I'll try and get this all configured properly async, but mounting async,noatime just sets noatime for me (async is noneffective and doesn't appear in the mount list) and that's how it was mounted for the test results below. root@lithium 21 130 # mount /dev/amrd0s1a on / (ufs, local) devfs on /dev (devfs, local) /dev/amrd0s1g on /data (ufs, local, noatime, soft-updates) /dev/amrd0s1e on /usr (ufs, local, soft-updates) /dev/amrd0s1f on /var (ufs, local, soft-updates) Here's even more results then: vmstat 1 & iostat 1 & foreach f (1 2 3 4 5) {/data/supersmack-1.3/bin/super-smack select-key.smack 50 1000|grep select_index} procs memory page disks faults cpu r b w avm fre flt re pi po fr sr am0 pa0 in sy cs us sy id tty amrd0 pass0 cpu 2 1 0 1499072 2699820 6 0 0 0 6 0 0 0 328 494 420 0 0 100 tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 0 1 10.16 0 0.00 0.14 0 0.00 0 0 0 0 100 0 262 0.00 0 0.00 0.00 0 0.00 46 0 51 0 3 3 1 0 1539380 2692080 2091 0 0 0 166 0 0 0 342 426991 59315 45 52 3 0 143 0.00 0 0.00 0.00 0 0.00 42 0 57 0 0 3 1 0 1539380 2692080 0 0 0 0 0 0 0 0 338 461227 64672 43 57 0 0 143 0.00 0 0.00 0.00 0 0.00 46 0 53 0 1 4 1 0 1539380 2692080 0 0 0 0 0 0 0 0 335 449944 62900 45 54 1 0 143 0.00 0 0.00 0.00 0 0.00 43 0 56 0 1 4 1 0 1539380 2692080 1 0 0 0 0 0 0 0 334 456473 64005 43 56 1 0 143 0.00 0 0.00 0.00 0 0.00 40 0 57 0 3 3 1 0 1539380 2692080 0 0 0 0 0 0 0 0 334 456581 63700 40 57 3 0 143 0.00 0 0.00 0.00 0 0.00 39 0 60 0 1 3 1 0 1539380 2692080 0 0 0 0 0 0 0 0 335 456716 63878 39 60 1 0 143 0.00 0 0.00 0.00 0 0.00 42 0 56 0 2 3 1 0 1530740 2693724 166 0 0 0 469 0 0 0 334 454858 63687 41 57 2 select_index 100000 27 1 14175.41 0 177 0.00 0 0.00 0.00 0 0.00 47 0 50 0 3 3 1 0 1539380 2692080 2754 0 0 0 2457 0 0 0 347 470893 43658 47 50 3 0 144 0.00 0 0.00 0.00 0 0.00 44 0 53 0 3 4 1 0 1539380 2692080 0 0 0 0 0 0 0 0 336 451858 62920 44 53 3 0 143 0.00 0 0.00 0.00 0 0.00 45 0 53 0 1 3 1 0 1539380 2692080 1 0 0 0 0 0 0 0 335 457440 63747 45 54 1 0 143 0.00 0 0.00 0.00 0 0.00 45 0 53 0 2 3 1 0 1539380 2692080 0 0 0 0 0 0 0 0 334 457705 63865 45 53 2 0 143 0.00 0 0.00 0.00 0 0.00 47 0 52 0 2 4 1 0 1539380 2692080 0 0 0 0 0 0 0 0 335 457634 63799 47 51 2 0 143 0.00 0 0.00 0.00 0 0.00 51 0 47 0 2 3 1 0 1539380 2692080 0 0 0 0 0 0 0 0 338 462670 64419 51 48 2 select_index 100000 10 3 14441.46 0 177 16.00 5 0.08 0.00 0 0.00 42 0 55 0 3 51 1 0 1537044 2692708 2758 0 0 0 2926 0 5 0 341 415206 57104 42 55 3 0 144 0.00 0 0.00 0.00 0 0.00 53 0 46 0 1 50 1 0 1539380 2692064 162 0 0 0 0 0 0 0 335 564638 33688 54 46 0 0 143 0.00 0 0.00 0.00 0 0.00 55 0 45 0 0 50 1 0 1539380 2692064 0 0 0 0 0 0 0 0 335 565593 33753 55 45 0 0 143 0.00 0 0.00 0.00 0 0.00 43 0 56 0 2 3 1 0 1539380 2692064 0 0 0 0 0 0 0 0 337 498784 51893 42 56 2 0 143 0.00 0 0.00 0.00 0 0.00 42 0 56 0 2 3 1 0 1539380 2692064 0 0 0 0 0 0 0 0 334 457060 63578 42 56 2 0 143 0.00 0 0.00 0.00 0 0.00 45 0 54 0 0 3 1 0 1539380 2692064 0 0 0 0 0 0 0 0 336 457381 63928 46 53 0 tty amrd0 pass0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id 0 142 0.00 0 0.00 0.00 0 0.00 44 0 55 0 2 4 1 0 1539380 2692064 0 0 0 0 0 0 0 0 338 456654 63909 41 57 2 select_index 100000 10 0 14852.39 0 296 0.00 0 0.00 0.00 0 0.00 41 0 55 0 5 50 1 0 1539380 2692080 2920 0 0 0 2930 0 0 0 337 444283 48341 42 54 4 0 144 0.00 0 0.00 0.00 0 0.00 52 0 48 0 0 50 1 0 1539380 2692080 0 0 0 0 0 0 0 0 336 565342 33734 52 48 0 0 143 0.00 0 0.00 0.00 0 0.00 50 0 50 0 0 50 1 0 1539380 2692080 0 0 0 0 0 0 0 0 335 564739 33736 50 50 0 0 143 0.00 0 0.00 0.00 0 0.00 50 0 50 0 0 50 1 0 1539380 2692080 0 0 0 0 0 0 0 0 334 565227 33760 50 50 0 0 143 0.00 0 0.00 0.00 0 0.00 59 0 41 0 0 50 1 0 1539380 2692080 0 0 0 0 0 0 0 0 334 564694 33663 61 39 0 0 143 0.00 0 0.00 0.00 0 0.00 52 0 48 0 0 50 1 0 1539380 2692080 0 0 0 0 0 0 0 0 338 570659 34101 51 49 0 0 143 0.00 0 0.00 0.00 0 0.00 51 0 49 0 0 select_index 100000 6 0 15800.56 40 1 0 1518496 2696768 1694 0 0 0 2916 0 0 0 339 524302 31026 49 48 3 0 177 0.00 0 0.00 0.00 0 0.00 46 0 51 0 3 51 1 0 1539380 2692064 1226 0 0 0 10 0 0 0 336 556488 33533 47 53 0 0 143 0.00 0 0.00 0.00 0 0.00 50 0 50 0 0 50 1 0 1539380 2692064 0 0 0 0 0 0 0 0 335 566623 33760 50 50 0 0 143 0.00 0 0.00 0.00 0 0.00 52 0 48 0 0 50 1 0 1539380 2692064 0 0 0 0 0 0 0 0 334 569522 33964 52 48 0 0 143 0.00 0 0.00 0.00 0 0.00 50 0 50 0 0 50 1 0 1539380 2692064 0 0 0 0 0 0 0 0 336 568597 33894 51 49 0 0 143 16.00 2 0.03 0.00 0 0.00 49 0 51 0 0 50 1 0 1539380 2692064 0 0 0 0 0 0 2 0 336 567234 33929 48 52 0 0 143 0.00 0 0.00 0.00 0 0.00 50 0 50 0 0 19 1 0 1517060 2696428 528 0 0 0 1619 0 0 0 334 570948 34320 49 51 0 select_index 100000 3 0 15928.08 root@lithium 20 0 # 0 226 0.00 0 0.00 0.00 0 0.00 13 0 17 0 71 0 0 0 1498948 2699796 371 0 0 0 1173 0 0 0 339 114112 7520 9 13 78 0 143 0.00 0 0.00 0.00 0 0.00 0 0 0 0 100 Ta, Steve Roome