From owner-freebsd-threads@FreeBSD.ORG Sun May 23 04:33:02 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E9D5D16A4CF for ; Sun, 23 May 2004 04:33:01 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id E87D643D2D for ; Sun, 23 May 2004 04:33:00 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (h91.vuokselantie10.fi [193.64.42.145]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4NBWe3v026661; Sun, 23 May 2004 14:32:41 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B08BD8.4030004@he.iki.fi> Date: Sun, 23 May 2004 14:32:40 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 11:33:02 -0000 JG wrote: > > While the benchmarks are running, gstat basically looks like this: > > amd64f# gstat > dT: 0.502 flag_I 500000us sizeof 288 i -1 > L(q) ops/s r/s kBps ms/r w/s kBps ms/w %busy Name > 0 0 0 0 0.0 0 0 0.0 0.0| ad0 > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1 > 0 0 0 0 0.0 0 0 0.0 0.0| acd0 > 0 0 0 0 0.0 0 0 0.0 0.0| amrd0 > 0 0 0 0 0.0 0 0 0.0 0.0| amrd1 > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1a > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1b > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1c > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1d > 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1e > 0 0 0 0 0.0 0 0 0.0 0.0| amrd0s1 > 0 0 0 0 0.0 0 0 0.0 0.0| amrd1s1 > > With the exception of a few split-second ticks to the amrd1 > drive (the mysql data drive) > Which means that either your dataset fits into the memory you have allocated for mysql or your are CPU bound or both. How does top -H look when running the test? Pete From owner-freebsd-threads@FreeBSD.ORG Sun May 23 07:50:33 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 10D5C16A4CE for ; Sun, 23 May 2004 07:50:32 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 674C743D48 for ; Sun, 23 May 2004 07:50:32 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4NEoN8Q013886; Sun, 23 May 2004 10:50:23 -0400 (EDT) Date: Sun, 23 May 2004 10:50:23 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: JG In-Reply-To: <5.2.0.9.2.20040522212217.01511190@mail.ojoink.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 14:50:33 -0000 On Sat, 22 May 2004, JG wrote: > > > >Along with the other benchmarks it's still a little apples > >and oranges. I've seen different versions of mysql run > >with different versions of FreeBSD. > > I'm going to stick with 4.0.20 from now for tests. > > Most of my tests were done on 4.0.20. > > > > I'd like to see the > >same mysql version run on the same hardware with libpthread > >and linuxthreads. > > I'll have to do that on the i386 install tomorrow, as Linuxthreads > is not available for FreeBSD/AMD64. > > > >This shows that -current (both SMP and UP) with libpthread is > >faster than linuxthreads (different versions of FreeBSD and > >mysql, though). > > For FreeBSD nothing (remote) has been faster than the i386 Linuxthreads > test. How does remote vs local affect the threads library? If we're trying to narrow down where the performance bottleneck is, looking at the local benchmarks doesn't seem to point the finger at the threads library. > I'll do everything local tomorrow. > > > >Also, if you use the default mysql config settings, then > >FreeBSD remote vs Linuxthreads remote also wins (2685.53 > >vs. 2091.10 from earlier post). > > I understand what you're saying, but those numbers are horrible. Perhaps, but this is threads@ and if we're trying to narrow down where the bottleneck is ... > >What modifications are you making to /etc/my.cnf and why > >do they turn the results around the other way? > > I used either my-huge.cnf or no config file at all (as noted in the > benchmarks). Yes, but what do the entries mean? From the looks of them, none of them should affect how the threads library performs. I think they affect shared memory sizes, correct me if I'm wrong. > Tell me all the FreeBSD tests you want to see & I'll do them if I can. I think the tests you ran are fine, but just run them with the same version of the OS and mysql. Try to use -current if possible, there were a couple of bugs in libpthread (libkse as it was installed in 5.2-release) that affected its mysql performance. I think comparing local vs remote just shows how our network stack compares with Linux. Another thing to try is to change the my-huge.cnf settings one by one and see how they affect FreeBSD local performance. -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Sun May 23 09:03:33 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A3F6A16A4CE for ; Sun, 23 May 2004 09:03:33 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9700643D45 for ; Sun, 23 May 2004 09:03:33 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 51995 invoked by uid 89); 23 May 2004 16:07:34 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 16:07:34 -0000 Message-Id: <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 09:03:05 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: References: <5.2.0.9.2.20040522212217.01511190@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 16:03:33 -0000 > > > Tell me all the FreeBSD tests you want to see & I'll do them if I can. > >I think the tests you ran are fine, but just run them with >the same version of the OS and mysql. Try to use -current >if possible, there were a couple of bugs in libpthread (libkse >as it was installed in 5.2-release) that affected its mysql >performance. MOST HAVE BEEN ON THE SAME VERSION OF MYSQL MOST HAVE BEEN USING FREEBSD-CURRENT. MOST HAVE BEEN ON THE SAME OS. There are about 20 different benchmark results out there that I have posted - with more on the way. Find the ones you want to compare and compare them. You'll see that with using even bleeding edge development code, as well as standard production code, with all the my.cnf tweaks you can muster -- that LINUX out of the box will beat it down without any tweaking. You'll also see that MULTI CPU FreeBSD systems don't do much better, and in some cases do WORSE than single CPU's when it comes to MySQL. >Another thing to try is to change the my-huge.cnf settings >one by one and see how they affect FreeBSD local performance. No. Everyone here wants to say "But you're comparing apples to oranges!" and now you're telling me to do just that. The Linux install uses default MySQL configuration settings. If you want me to start tweaking with the my.cnf, I'll have to do it on the linux machine as well, and then of course the performance gap between the OS's will be even worse. Why don't we compare apples to apples and use stock my.cnf settings for both? Anyway regarding various FreeBSD tweaking attempts.... Here is Chris Elsworth's recent post from the MySQL-General list: --------------------------------------------------------------------------------- "Since I'm a numbers freak, I've been running super-smack on it for the last few days to see how it stacks up. Tweaking various configs and kernel options, on any OS, obviously wins a few hundred/thousand queries per second, but I'm really quite surprised at one major difference. Optimisations and tweaking aside, FreeBSD 5.2.1-p6 on this hardware did well to achieve 17,000 queries per second, using super-smack's select-key.smack with the query cache turned on. Nothing I could do, and I spent days trying, got it much higher. Once I wiped this and tried Linux (both gentoo, with their patched-to-the-hilt 2.6.5 kernel, and Debian, with a stock 2.6.6 which had just been released by the time I installed) this figure jumped to 35,000 queries per second. Is FreeBSD really this crap for MySQL? I was quite horrified. FreeBSD 5 has a number of threading libraries, and I tried them all. LinuxThreads won (slightly, there wasn't much in it). I'm very much a FreeBSD fan and I'd quite like to keep FreeBSD on this machine before it goes live, but the performance pales in comparison to Linux. I had to do absolutely no tweaking to achieve 35,000 queries/sec in Linux. Has anyone else observed similar behaviour? Does anyone else have similar hardware with FreeBSD on? Have you fared any better?" --------------------------------------------------------------------------------- From owner-freebsd-threads@FreeBSD.ORG Sun May 23 09:56:38 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E8CB216A4CE for ; Sun, 23 May 2004 09:56:37 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id CAF8643D1D for ; Sun, 23 May 2004 09:56:37 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 53577 invoked by uid 89); 23 May 2004 17:00:49 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 17:00:49 -0000 Message-Id: <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 09:56:20 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <40B08BD8.4030004@he.iki.fi> References: <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 16:56:38 -0000 At 02:32 PM 5/23/2004 +0300, you wrote: >JG wrote: > >> >>While the benchmarks are running, gstat basically looks like this: >> >>amd64f# gstat >>dT: 0.502 flag_I 500000us sizeof 288 i -1 >> L(q) ops/s r/s kBps ms/r w/s kBps ms/w %busy Name >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0 >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1 >> 0 0 0 0 0.0 0 0 0.0 0.0| acd0 >> 0 0 0 0 0.0 0 0 0.0 0.0| amrd0 >> 0 0 0 0 0.0 0 0 0.0 0.0| amrd1 >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1a >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1b >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1c >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1d >> 0 0 0 0 0.0 0 0 0.0 0.0| ad0s1e >> 0 0 0 0 0.0 0 0 0.0 0.0| amrd0s1 >> 0 0 0 0 0.0 0 0 0.0 0.0| amrd1s1 >> >>With the exception of a few split-second ticks to the amrd1 >>drive (the mysql data drive) >Which means that either your dataset fits into the memory you have >allocated for mysql or your are CPU bound or both. >How does top -H look when running the test? This is on a DUAL CPU kernel using SCHED_4BSD: To make the test run longer to allow the CPU usage to build up and peak I ran super-smack with more rounds per client: amd64f# super-smack update-select.smack 30 1000000 Once it hit ~41% it just stayed there and bounced between 40 and 41 while the rest of the test ran.... last pid: 709; load averages: 2.48, 1.34, 0.59 up 0+00:22:40 08:08:21 63 processes: 1 running, 60 sleeping, 2 lock CPU states: 39.3% user, 0.0% nice, 25.9% system, 0.4% interrupt, 34.4% idle Mem: 65M Active, 6180K Inact, 59M Wired, 124K Cache, 25M Buf, 1848M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 582 mysql 132 0 63580K 50184K *Giant 1 3:13 41.85% 41.85% mysqld 582 mysql 126 0 63580K 50184K select 0 3:13 41.16% 41.16% mysqld 582 mysql 132 0 63580K 50184K *Giant 1 3:13 41.16% 41.16% mysqld 690 root 4 0 9316K 2108K sbwait 1 0:02 0.59% 0.59% super-smack 696 root 4 0 9316K 2108K sbwait 1 0:02 0.44% 0.44% super-smack 698 root 4 0 9316K 2108K sbwait 0 0:02 0.39% 0.39% super-smack 684 root 4 0 9316K 2108K sbwait 0 0:02 0.29% 0.29% super-smack 697 root 4 0 9316K 2108K sbwait 1 0:02 0.29% 0.29% super-smack 699 root 4 0 9316K 2108K sbwait 1 0:02 0.29% 0.29% super-smack 706 root 4 0 9316K 2108K sbwait 0 0:02 0.24% 0.24% super-smack 692 root 4 0 9316K 2108K sbwait 1 0:02 0.24% 0.24% super-smack 694 root 4 0 9316K 2108K sbwait 1 0:02 0.20% 0.20% super-smack 679 root 4 0 9316K 2108K sbwait 1 0:02 0.20% 0.20% super-smack 705 root 4 0 9316K 2108K sbwait 1 0:02 0.20% 0.20% super-smack 700 root 4 0 9316K 2108K sbwait 1 0:02 0.15% 0.15% super-smack 702 root 4 0 9316K 2108K sbwait 1 0:02 0.10% 0.10% super-smack 688 root 4 0 9316K 2108K sbwait 0 0:02 0.10% 0.10% super-smack 701 root 4 0 9316K 2108K sbwait 1 0:02 0.10% 0.10% super-smack 686 root 4 0 9316K 2108K sbwait 0 0:02 0.10% 0.10% super-smack 680 root 4 0 9316K 2108K sbwait 1 0:02 0.10% 0.10% super-smack 685 root 4 0 9316K 2108K sbwait 0 0:02 0.10% 0.10% super-smack 682 root 4 0 9316K 2108K sbwait 1 0:02 0.05% 0.05% super-smack 703 root 4 0 9316K 2108K sbwait 1 0:02 0.05% 0.05% super-smack 704 root 4 0 9316K 2108K sbwait 0 0:02 0.05% 0.05% super-smack 691 root 4 0 9316K 2108K sbwait 0 0:02 0.05% 0.05% super-smack 681 root 4 0 9316K 2108K sbwait 0 0:02 0.05% 0.05% super-smack 582 mysql 20 0 63580K 50184K ksesig 1 3:13 0.00% 0.00% mysqld 687 root 4 0 9316K 2108K sbwait 1 0:02 0.00% 0.00% super-smack 693 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 695 root 4 0 9316K 2108K sbwait 1 0:02 0.00% 0.00% super-smack 689 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 683 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 708 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 707 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack This is on a SINGLE CPU kernel using SCHED_4BSD: I also ran: amd64f# super-smack update-select.smack 30 1000000 to make the test last longer. On a single CPU kernel, it hits ~65 as a peak and backs off to bounce between 63% and 65%... last pid: 738; load averages: 1.30, 0.48, 0.19 up 0+00:38:10 08:55:19 62 processes: 2 running, 60 sleeping CPU states: 67.7% user, 0.0% nice, 31.5% system, 0.8% interrupt, 0.0% idle Mem: 65M Active, 6760K Inact, 58M Wired, 120K Cache, 25M Buf, 1848M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE TIME WCPU CPU COMMAND 581 mysql 121 0 63512K 50108K select 1:20 64.55% 64.55% mysqld 581 mysql 122 0 63512K 50108K RUN 1:20 64.55% 64.55% mysqld 700 root 4 0 9316K 2040K sbwait 0:01 0.34% 0.34% super-smack 712 root 4 0 9316K 2040K sbwait 0:01 0.29% 0.29% super-smack 718 root 4 0 9316K 2040K sbwait 0:01 0.29% 0.29% super-smack 713 root 4 0 9316K 2040K sbwait 0:01 0.29% 0.29% super-smack 698 root 4 0 9316K 2040K sbwait 0:01 0.24% 0.24% super-smack 722 root 4 0 9316K 2040K sbwait 0:01 0.20% 0.20% super-smack 719 root 4 0 9316K 2040K sbwait 0:01 0.20% 0.20% super-smack 707 root 4 0 9316K 2040K sbwait 0:01 0.20% 0.20% super-smack 710 root 4 0 9316K 2040K sbwait 0:01 0.20% 0.20% super-smack 705 root 4 0 9316K 2040K sbwait 0:01 0.15% 0.15% super-smack 704 root 4 0 9316K 2040K sbwait 0:01 0.15% 0.15% super-smack 715 root 4 0 9316K 2040K sbwait 0:01 0.15% 0.15% super-smack 693 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 699 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 721 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 694 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 703 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 706 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 714 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 716 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 717 root 4 0 9316K 2040K sbwait 0:01 0.10% 0.10% super-smack 709 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 720 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 701 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 708 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 695 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 696 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 711 root 4 0 9316K 2040K sbwait 0:01 0.05% 0.05% super-smack 581 mysql 20 0 63512K 50108K ksesig 1:20 0.00% 0.00% mysqld 702 root 4 0 9316K 2040K sbwait 0:01 0.00% 0.00% super-smack 697 root 4 0 9316K 2040K sbwait 0:01 0.00% 0.00% super-smack I am just a layman here, but what does this mean? From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:04:05 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0EFA316A4CE for ; Sun, 23 May 2004 10:04:05 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 795ED43D31 for ; Sun, 23 May 2004 10:04:04 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4NH3e8Q014159; Sun, 23 May 2004 13:03:40 -0400 (EDT) Date: Sun, 23 May 2004 13:03:40 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: JG In-Reply-To: <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:04:05 -0000 On Sun, 23 May 2004, JG wrote: > > > > > > Tell me all the FreeBSD tests you want to see & I'll do them if I can. > > > >I think the tests you ran are fine, but just run them with > >the same version of the OS and mysql. Try to use -current > >if possible, there were a couple of bugs in libpthread (libkse > >as it was installed in 5.2-release) that affected its mysql > >performance. > > MOST HAVE BEEN ON THE SAME VERSION OF MYSQL > > MOST HAVE BEEN USING FREEBSD-CURRENT. > > MOST HAVE BEEN ON THE SAME OS. > > There are about 20 different benchmark results out there that > I have posted - with more on the way. None consolidated in the same post with the same versions of FreeBSD and mysql. Like I said before, there are a ton of posts on amd64@ and some here on threads@. It would help much if you were to summarize them on a web page so that all of us don't have to go searching through all the archives trying to do it ourselves. > Find the ones you want to compare and compare them. > > You'll see that with using even bleeding edge development > code, as well as standard production code, with all the my.cnf > tweaks you can muster -- that LINUX out of the box will beat Yes, but isn't the point to find out where the bottleneck is, not to just say that some version of Linux is faster than -current? This is the threads@ mailing list, and from what I've seen so far it doesn't look like threading is the bottleneck. The default configuration with libpthread vs FreeBSD-linuxthreads seems to be similar. The big increase is when going to native Linux. This would suggest that it is kernel-related. The comparisons of libthr vs libpthread seem to be similar also. > You'll also see that MULTI CPU FreeBSD systems don't do > much better, and in some cases do WORSE than single CPU's > when it comes to MySQL. Yes, we know that the kernel needs some more work WRT locking, but that isn't a topic for threads@. > >Another thing to try is to change the my-huge.cnf settings > >one by one and see how they affect FreeBSD local performance. > > No. > > Everyone here wants to say "But you're comparing apples to oranges!" > and now you're telling me to do just that. No we're not. I want to know what aspects of those mysql conf settings affect FreeBSD performance. Perhaps it can help find one of the bottlenecks. > Why don't we compare apples to apples and use stock my.cnf settings > for both? That's fine. You were trying to run dual tests (stock and huge my.cnf settings) on each platform but mostly chose to look at huge cnf remote results. -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:10:11 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B8B1D16A4CF for ; Sun, 23 May 2004 10:10:11 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id B539E43D41 for ; Sun, 23 May 2004 10:10:10 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (h91.vuokselantie10.fi [193.64.42.145]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4NH9p3v027995; Sun, 23 May 2004 20:09:51 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B0DADE.1070605@he.iki.fi> Date: Sun, 23 May 2004 20:09:50 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:10:11 -0000 JG wrote: > > > > I am just a layman here, but what does this mean? > You seem to have a very small number of active threads. When I tried supersmack on fairly old machine (800MHz dual pentium) I get ~30 active mysql threads using CPU between 1.5% and 3.5%. I would suspect the test is limited by context switch and syscall overhead. With the old machine I see >70000 syscalls and >20000 context switches a second. So I suspect FreeBSD syscall overhead compared to linux must be higher. Mysql also asks for time repeatedly so make sure you are running ACPI timecounters. (donīt know if they are available on AMD64). With the old machine I got 1360 qps for the select test. (although itīs not idle, but doing light other work) Pete From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:23:56 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E4A3316A4CE for ; Sun, 23 May 2004 10:23:56 -0700 (PDT) Received: from mta06-svc.ntlworld.com (mta06-svc.ntlworld.com [62.253.162.46]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3D51843D54 for ; Sun, 23 May 2004 10:23:56 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta06-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040523172359.LCKN9422.mta06-svc.ntlworld.com@voi.aagh.net> for ; Sun, 23 May 2004 18:23:59 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BRwhW-000PUK-H7 for freebsd-threads@freebsd.org; Sun, 23 May 2004 18:23:54 +0100 Date: Sun, 23 May 2004 18:23:54 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040523172354.GA97724@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521215952.0166c368@mail.ojoink.com> <40AF4A8D.4020602@freebsd.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <40AF4A8D.4020602@freebsd.org> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:23:57 -0000 * David Xu (davidxu@freebsd.org) wrote: > I suggest you to do file system benchmark first, I suspect > -CURRENT file system's performance is far behind than Linux. The super-smack benchmarks we performed have minimal impact on filesystem; the tables are small enough to fit in cache, and systat shows clearly there's next to nothing touching the filesystem. We've tried the latest 5-RELEASE and -CURRENT as of a week or two ago; libkse (libpthread under current), libthr, libc_r and linuxthreads. System and process scope threads, linking explicitly at compile time to said libs and using libmap.conf. We've disabled HTT and tweaked everything we could think of with minimal change. I would say it *looks* like FreeBSD is only using 1 CPU, but threads are clearly distributed across them. Getting load >50% seems difficult, though. Given the number of threading libraries etc we've tried, I'm more inclined to point at scheduling or so. We did try SCHED_ULE once or twice with minimal difference. Maybe MySQL's just hitting a lot of locks *shrug*. (I'm Chris Elsworth's co-worker btw; the guy mentioned in the root of the thread :) -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:24:16 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5F4A816A4CE for ; Sun, 23 May 2004 10:24:16 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3C2E143D45 for ; Sun, 23 May 2004 10:24:16 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 54362 invoked by uid 89); 23 May 2004 17:28:46 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 17:28:46 -0000 Message-Id: <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 10:24:18 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: References: <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:24:16 -0000 > >None consolidated in the same post with the same versions >of FreeBSD and mysql. Like I said before, there are a ton >of posts on amd64@ and some here on threads@. It would >help much if you were to summarize them on a web page so >that all of us don't have to go searching through all >the archives trying to do it ourselves. Good idea, I'll do that now. > The default configuration with >libpthread vs FreeBSD-linuxthreads seems to be similar. I would disagree from what I've seen so far. Nothing came close to the remote tests using Linuxthreads. > > Everyone here wants to say "But you're comparing apples to oranges!" > > and now you're telling me to do just that. > >No we're not. I want to know what aspects of those >mysql conf settings affect FreeBSD performance. Perhaps >it can help find one of the bottlenecks. I'm just getting frustrated here having to go from list to list and repeat everything that has already been posted each time as each group wants to push the problem on another group... starting to remind me of the Healthcare system, but none of us can fix that problem :P > > Why don't we compare apples to apples and use stock my.cnf settings > > for both? > >That's fine. You were trying to run dual tests (stock and huge >my.cnf settings) on each platform but mostly chose to look at >huge cnf remote results. I only stressed the my-huge.cnf results because they (on i386, using Linuxthreads, remote) was the only thing that came anywhere near the Linux results. I'll try to put a page up that consolidates the results so far. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:33:43 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 6285716A4D0 for ; Sun, 23 May 2004 10:33:43 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4950843D31 for ; Sun, 23 May 2004 10:33:43 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 54675 invoked by uid 89); 23 May 2004 17:37:16 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 17:37:16 -0000 Message-Id: <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 10:32:47 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <40B0DADE.1070605@he.iki.fi> References: <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1"; format=flowed Content-Transfer-Encoding: quoted-printable Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:33:43 -0000 At 08:09 PM 5/23/2004 +0300, you wrote: >JG wrote: > >> >> >> >>I am just a layman here, but what does this mean? >You seem to have a very small number of active threads. When I tried=20 >supersmack on fairly old machine (800MHz dual pentium) I get ~30 active=20 >mysql threads using CPU between 1.5% and 3.5%. You mean where I have the 3 instances of mysqld running in top? You have=20 ~30 instances? Why would your box use more? Is there any configuration or flag in mysql=20 that would cause this? How was your mysql compiled? What version of FreeBSD are you running? >I would suspect the test is limited by context switch and syscall=20 >overhead. With the old machine I see >70000 syscalls and >20000 context=20 >switches a second. So I suspect FreeBSD syscall overhead compared to linux= =20 >must be higher. Mysql also asks for time repeatedly so make sure you are=20 >running ACPI timecounters. (don=B4t know if they are available on AMD64). I know that this FreeBSD/AMD64 reports that it is using ACPI... how do I=20 find out if its using timecounters, or using them properly? FWIW... amd64f# cat /var/log/dmesg.today | grep ACPI ACPI APIC Table: Timecounter "ACPI-fast" frequency 3579545 Hz quality 1000 cpu0: on acpi0 pcib0: port 0xcf8-0xcff on acpi0 pci0: on pcib0 pcib1: at device 6.0 on pci0 pci3: on pcib1 pcib2: at device 10.0 on pci0 pci2: on pcib2 pcib3: at device 11.0 on pci0 pci1: on pcib3 From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:35:32 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7A3F616A4CE for ; Sun, 23 May 2004 10:35:32 -0700 (PDT) Received: from mta03-svc.ntlworld.com (mta03-svc.ntlworld.com [62.253.162.43]) by mx1.FreeBSD.org (Postfix) with ESMTP id EC46243D1D for ; Sun, 23 May 2004 10:35:31 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta03-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040523173338.SJEO28581.mta03-svc.ntlworld.com@voi.aagh.net> for ; Sun, 23 May 2004 18:33:38 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BRwrr-000PXY-7S for freebsd-threads@freebsd.org; Sun, 23 May 2004 18:34:35 +0100 Date: Sun, 23 May 2004 18:34:35 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040523173435.GB97724@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:35:32 -0000 * JG (amd64list@jpgsworld.com) wrote: > ... But I wish I knew how he ran super-smack, those numbers are > insane. Nothing special: super-smack smacks/select-key.smack 4 1000 Or so; change the number of clients and iterations to taste; you'll note performance does improve slightly with more clients up to the number of CPU's and then drops slowly as you add more, with system load apparantly not increasing a whole lot; I'm not even sure it got past 50% most of the time, despite there being plenty of active clients. It'd be interesting to see this comparison with PgSQL; super-smack supports it, and as a preforking database server it might do better on FreeBSD. -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:42:19 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 66F6516A4CE for ; Sun, 23 May 2004 10:42:19 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4371143D46 for ; Sun, 23 May 2004 10:42:19 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 54983 invoked by uid 89); 23 May 2004 17:46:48 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 17:46:48 -0000 Message-Id: <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 10:42:19 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <20040523173435.GB97724@voi.aagh.net> References: <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:42:19 -0000 At 06:34 PM 5/23/2004 +0100, you wrote: >* JG (amd64list@jpgsworld.com) wrote: > > > ... But I wish I knew how he ran super-smack, those numbers are > > insane. > >Nothing special: > > super-smack smacks/select-key.smack 4 1000 I think I might have been talking about the 2cpu.com guy actually... but the numbers you guys had were pretty high (compared to mine) too. I just did a using 4 1000 and my numbers are much higher.. ~6500 with my current configuration. I use 30 10000 for everything because I feel it simulates a more realistic load on the server (But I could be wrong). It takes a good 2-4 minutes between tests though.. :( From owner-freebsd-threads@FreeBSD.ORG Sun May 23 10:47:12 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E6FC816A4CF for ; Sun, 23 May 2004 10:47:12 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4952C43D2F for ; Sun, 23 May 2004 10:47:12 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (h91.vuokselantie10.fi [193.64.42.145]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4NHkw3v028153; Sun, 23 May 2004 20:46:58 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B0E391.4000504@he.iki.fi> Date: Sun, 23 May 2004 20:46:57 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 17:47:13 -0000 JG wrote: > > You mean where I have the 3 instances of mysqld running in top? You > have ~30 instances? > Iīm running the supersmack with $1=30. > Why would your box use more? Is there any configuration or flag in > mysql that would cause this? > MySQL creates a thread per client. > How was your mysql compiled? What version of FreeBSD are you running? This is on -CURRENT from a few weeks ago. Cannot remember if I used optimized compilation. > > > I know that this FreeBSD/AMD64 reports that it is using ACPI... how do > I find out if its using timecounters, or using them properly? > kern.timecounter.hardware: ACPI-fast But I'm suspecting that the system call overhead between Linux and freebsd differs enough to cause the results. If anything can be done about it, I donīt know. Pete From owner-freebsd-threads@FreeBSD.ORG Sun May 23 11:04:41 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B776816A4CE for ; Sun, 23 May 2004 11:04:41 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id A9E8543D1F for ; Sun, 23 May 2004 11:04:41 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 55676 invoked by uid 89); 23 May 2004 18:09:12 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 18:09:12 -0000 Message-Id: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 11:04:42 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <40B0E391.4000504@he.iki.fi> References: <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1"; format=flowed Content-Transfer-Encoding: quoted-printable Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 18:04:41 -0000 At 08:46 PM 5/23/2004 +0300, you wrote: >JG wrote: > >> >>You mean where I have the 3 instances of mysqld running in top? You have= =20 >>~30 instances? >I=B4m running the supersmack with $1=3D30. (For those reading this thread who have not looked at the super-smack=20 source code, which I'm sure most have not, what Pete means by $1 is the number of=20 simultaneous supersmack client connections to run, even though it would be $2 from shell ie: super-smack smackfile-to-run clients rounds-per-client) Anyway Pete, I am running the supersmack with 30 clients as well. But that isn't what I asked you. I have 30 supersmack clients running in top -H when the test is running (if= =20 I ran it with 30 clients anyway). What I don't have is 30 mysqld instances (I had 3, as shown), and that is=20 what you made it sound like you had. You said: "You seem to have a very small number of active threads. When I tried=20 supersmack on fairly old machine (800MHz dual pentium) I get ~30 active=20 mysql threads using CPU between 1.5% and 3.5%." So if this is not the case, please clarify. Or better... post your top -H while tests are running as well as the=20 super-smack command you ran. >>I know that this FreeBSD/AMD64 reports that it is using ACPI... how do I= =20 >>find out if its using timecounters, or using them properly? >kern.timecounter.hardware: ACPI-fast > >But I'm suspecting that the system call overhead between Linux and freebsd= =20 >differs enough to cause the results. If anything can be done about it, I=20 >don=B4t know. $ sysctl -a | grep 'ACPI' kern.timecounter.hardware: ACPI-fast kern.timecounter.choice: TSC(800) ACPI-fast(1000) i8254(0) dummy(-1000000) Looks like I'm ok there (on FreeBSD/AMD64). - Jeremy From owner-freebsd-threads@FreeBSD.ORG Sun May 23 11:35:31 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 895BD16A4CE for ; Sun, 23 May 2004 11:35:31 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5A00543D4C for ; Sun, 23 May 2004 11:35:30 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (h91.vuokselantie10.fi [193.64.42.145]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4NIZ33v028349; Sun, 23 May 2004 21:35:03 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B0EED6.3040104@he.iki.fi> Date: Sun, 23 May 2004 21:35:02 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 18:35:31 -0000 JG wrote: > At 08:46 PM 5/23/2004 +0300, you wrote: > >> JG wrote: >> >>> >>> You mean where I have the 3 instances of mysqld running in top? You >>> have ~30 instances? >> >> Iīm running the supersmack with $1=30. > > > (For those reading this thread who have not looked at the super-smack > source code, > which I'm sure most have not, what Pete means by $1 is the number of > simultaneous > supersmack client connections to run, even though it would be $2 from > shell > ie: super-smack smackfile-to-run clients rounds-per-client) > > > Anyway Pete, I am running the supersmack with 30 clients as well. > > But that isn't what I asked you. > > I have 30 supersmack clients running in top -H when the test is > running (if I ran it with 30 clients anyway). > > What I don't have is 30 mysqld instances (I had 3, as shown), and that > is what you made it sound like you had. > > You said: > > "You seem to have a very small number of active threads. When I tried > supersmack on fairly old machine (800MHz dual pentium) I get ~30 > active mysql threads using CPU between 1.5% and 3.5%." > > So if this is not the case, please clarify. > > Or better... post your top -H while tests are running as well as the > super-smack command you ran. > last pid: 19842; load averages: 7.32, 1.70, 0.67 up 5+10:01:49 21:33:44 110 processes: 1 running, 99 sleeping, 10 lock CPU states: 55.6% user, 0.0% nice, 39.4% system, 0.8% interrupt, 4.2% idle Mem: 312M Active, 64M Inact, 103M Wired, 18M Cache, 60M Buf, 992K Free Swap: 3906M Total, 164K Used, 3906M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% mysqld 646 mysql 98 0 285M 282M *Giant 0 400:31 2.34% 2.34% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 2.25% 2.25% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 2.15% 2.15% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 2.15% 2.15% mysqld 646 mysql 98 0 285M 282M *Giant 0 400:31 2.15% 2.15% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 2.05% 2.05% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld 646 mysql 98 0 285M 282M *Giant 0 400:31 1.95% 1.95% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.90% 1.90% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.81% 1.81% mysqld 646 mysql 97 0 285M 282M *Giant 0 400:31 1.81% 1.81% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 1.71% 1.71% mysqld 646 mysql 98 0 285M 282M *Giant 0 400:31 1.71% 1.71% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% mysqld 646 mysql 97 0 285M 282M *Giant 0 400:31 1.56% 1.56% mysqld 646 mysql 4 0 285M 282M sbwait 0 400:31 1.46% 1.46% mysqld 646 mysql 20 0 285M 282M kserel 0 400:31 1.37% 1.37% mysqld 646 mysql 20 0 285M 282M kserel 1 400:31 1.22% 1.22% mysqld 646 mysql 4 0 285M 282M sbwait 0 400:31 1.22% 1.22% mysqld 19813 pete 4 0 2600K 1712K sbwait 0 0:00 1.19% 0.68% super-smack 19835 pete 4 0 2600K 1712K sbwait 0 0:00 1.11% 0.63% super-smack 19831 pete 96 0 2600K 1712K *Giant 0 0:00 1.11% 0.63% super-smack 19837 pete 4 0 2600K 1712K sbwait 1 0:00 1.11% 0.63% super-smack Pete From owner-freebsd-threads@FreeBSD.ORG Sun May 23 12:15:02 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0E15D16A4CE for ; Sun, 23 May 2004 12:15:02 -0700 (PDT) Received: from geri.cc.fer.hr (geri.cc.fer.hr [161.53.72.107]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2559F43D48 for ; Sun, 23 May 2004 12:15:01 -0700 (PDT) (envelope-from ivoras@fer.hr) Received: from [127.0.0.1] (root@geri.cc.fer.hr [161.53.72.107]) by geri.cc.fer.hr (8.12.9p2/8.12.8) with ESMTP id i4NJDqUh062087; Sun, 23 May 2004 21:13:52 +0200 (CEST) (envelope-from ivoras@fer.hr) Message-ID: <40B0F813.3010903@fer.hr> Date: Sun, 23 May 2004 21:14:27 +0200 From: Ivan Voras User-Agent: Mozilla Thunderbird 0.6 (Windows/20040502) X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> X-Enigmail-Version: 0.84.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="------------enig9429D9AEF482A7FB23F39E7E" cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 19:15:02 -0000 This is an OpenPGP/MIME signed message (RFC 2440 and 3156) --------------enig9429D9AEF482A7FB23F39E7E Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit JG wrote: > I'm just getting frustrated here having to go from list to list and > repeat everything that has already been posted each time as > each group wants to push the problem on another group... > starting to remind me of the Healthcare system, but none of us > can fix that problem :P The worst thing is, this is not a new problem - various users have reported performance troubles with libpthread and/or mysql, including at this thread in freebsd-current: From: Alex.Kovalenko@verizon.net (Alexandre Sunny Kovalenko) Subject: Is it possible to make -lpthread program to use 100% CPU? Message-ID: <20040429111355.7eb83170.Alex.Kovalenko@verizon.net> Date: 29 Apr 2004 15:13:55 GMT It appears similar to your problems: quote: "I have turned off hyperthreading and got result, which muddles me rather -- try as I might, with -lpthread I could not get past 50% CPU utilization (as reported by 'top') and number of computations per second matching that (number is very close to that for running single-threaded). Top report with thread view enabled shows that both KSE are bound to CPU 0 (if I am interpreting C column properly). " His application is custom, doing "heavy duty computation on relatively small amount of data". -- C isn't that hard: void (*(*f[])())() defines f as an array of unspecified size, of pointers to functions that return pointers to functions that return void. --------------enig9429D9AEF482A7FB23F39E7E Content-Type: application/pgp-signature; name="signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.2 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAsPgcldnAQVacBcgRAm9MAKDwlEujwYdk3NqsrHKiAYCM5a4VngCgw338 IvJ4koWZkjDlQfN2cFPbk4E= =/rXv -----END PGP SIGNATURE----- --------------enig9429D9AEF482A7FB23F39E7E-- From owner-freebsd-threads@FreeBSD.ORG Sun May 23 12:19:23 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 8722416A4CE for ; Sun, 23 May 2004 12:19:23 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7672A43D31 for ; Sun, 23 May 2004 12:19:23 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 57913 invoked by uid 89); 23 May 2004 19:23:22 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 19:23:22 -0000 Message-Id: <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 12:18:50 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <40B0EED6.3040104@he.iki.fi> References: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 19:19:23 -0000 >last pid: 19842; load averages: 7.32, 1.70, >0.67 up 5+10:01:49 21:33:44 >110 processes: 1 running, 99 sleeping, 10 lock >CPU states: 55.6% user, 0.0% nice, 39.4% system, 0.8% interrupt, 4.2% idle >Mem: 312M Active, 64M Inact, 103M Wired, 18M Cache, 60M Buf, 992K Free >Swap: 3906M Total, 164K Used, 3906M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND > 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% mysqld > 646 mysql 98 0 285M 282M *Giant 0 400:31 2.34% 2.34% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 2.25% 2.25% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 2.15% 2.15% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 2.15% 2.15% mysqld > 646 mysql 98 0 285M 282M *Giant 0 400:31 2.15% 2.15% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 2.05% 2.05% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% mysqld > 646 mysql 98 0 285M 282M *Giant 0 400:31 1.95% 1.95% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.90% 1.90% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.81% 1.81% mysqld > 646 mysql 97 0 285M 282M *Giant 0 400:31 1.81% 1.81% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 1.71% 1.71% mysqld > 646 mysql 98 0 285M 282M *Giant 0 400:31 1.71% 1.71% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% mysqld > 646 mysql 97 0 285M 282M *Giant 0 400:31 1.56% 1.56% mysqld > 646 mysql 4 0 285M 282M sbwait 0 400:31 1.46% 1.46% mysqld > 646 mysql 20 0 285M 282M kserel 0 400:31 1.37% 1.37% mysqld > 646 mysql 20 0 285M 282M kserel 1 400:31 1.22% 1.22% mysqld > 646 mysql 4 0 285M 282M sbwait 0 400:31 1.22% 1.22% mysqld >19813 pete 4 0 2600K 1712K sbwait 0 0:00 1.19% 0.68% >super-smack >19835 pete 4 0 2600K 1712K sbwait 0 0:00 1.11% 0.63% >super-smack >19831 pete 96 0 2600K 1712K *Giant 0 0:00 1.11% 0.63% >super-smack >19837 pete 4 0 2600K 1712K sbwait 1 0:00 1.11% 0.63% >super-smack > >Pete Ok. Well this is definitely a significant discovery. Maybe it's because I'm using SCHED_4BSD for the results I posted.. lets see.. /me switches kernels..... Using SMP enabled kernel with SCHED_ULE I get: last pid: 724; load averages: 1.43, 0.46, 0.17 up 0+00:18:28 11:11:23 64 processes: 4 running, 60 sleeping CPU states: 0.0% user, 0.0% nice, 50.2% system, 0.0% interrupt, 49.8% idle Mem: 66M Active, 6372K Inact, 59M Wired, 124K Cache, 25M Buf, 1846M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 581 mysql 139 0 63568K 50164K CPU0 0 1:41 49.22% 49.22% mysqld 581 mysql 139 0 63568K 50164K RUN 0 1:41 42.19% 42.19% mysqld 581 mysql 76 0 63568K 50164K select 0 1:41 0.00% 0.00% mysqld 581 mysql 20 0 63568K 50164K ksesig 0 1:41 0.00% 0.00% mysqld 681 root 8 0 9300K 2088K wait 0 0:00 0.00% 0.00% super-smack (only 4 myqld's up there while its running... the rest are super-smack in wait states) But then I ran it again, same command, doing nothing different.... And now I get: PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 581 mysql 77 0 63584K 50628K RUN 0 3:23 60.16% 60.16% mysqld 742 root 88 0 9316K 2108K RUN 0 0:07 12.50% 12.50% super-smack 732 root 4 0 9316K 2108K sbwait 1 0:07 8.59% 8.59% super-smack 734 root 90 0 9316K 2108K RUN 0 0:05 7.81% 7.81% super-smack 581 mysql 76 0 63584K 50628K select 1 3:23 0.00% 0.00% mysqld 581 mysql 20 0 63584K 50628K ksesig 0 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% mysqld ... many more mysqld's running in the beginning, but after a couple minutes the results flip around to look like this: last pid: 759; load averages: 3.91, 4.24, 2.10 up 0+00:23:41 11:16:36 64 processes: 3 running, 59 sleeping, 2 zombie CPU states: 1.8% user, 0.0% nice, 48.6% system, 0.6% interrupt, 49.0% idle Mem: 65M Active, 6336K Inact, 60M Wired, 124K Cache, 25M Buf, 1846M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 581 mysql 139 0 63584K 50180K RUN 0 6:49 46.88% 46.88% mysqld 581 mysql 139 0 63584K 50180K RUN 0 6:49 42.97% 42.97% mysqld 581 mysql 76 0 63584K 50180K select 1 6:49 0.00% 0.00% mysqld 581 mysql 20 0 63584K 50180K ksesig 0 6:49 0.00% 0.00% mysqld 732 root 4 0 9316K 2108K sbwait 1 0:16 0.00% 0.00% super-smack 756 root 4 0 9316K 2108K sbwait 1 0:10 0.00% 0.00% super-smack 739 root 4 0 9316K 2108K sbwait 1 0:06 0.00% 0.00% super-smack 737 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 729 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% super-smack 744 root 4 0 9316K 2108K sbwait 1 0:02 0.00% 0.00% super-smack 746 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% super-smack 733 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 731 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 730 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 735 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% super-smack 741 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% super-smack 750 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 755 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 747 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 751 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack 745 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% super-smack (+more super-smacks in sbwait state like above) When it's idle with no mysql load / benchmarking going on, it looks like this: last pid: 759; load averages: 1.40, 3.40, 1.98 up 0+00:24:59 11:17:54 33 processes: 1 running, 32 sleeping CPU states: 0.2% user, 0.0% nice, 0.0% system, 0.4% interrupt, 99.4% idle Mem: 60M Active, 5936K Inact, 58M Wired, 124K Cache, 25M Buf, 1854M Free Swap: 4096M Total, 4096M Free PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND 581 mysql 76 0 63568K 50164K select 1 7:27 0.00% 0.00% mysqld 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% mysqld 581 mysql 20 0 63568K 50164K ksesig 0 7:27 0.00% 0.00% mysqld 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% mysqld 677 root 76 0 6712K 1680K CPU0 0 0:00 0.00% 0.00% top Is this normal behavior? From owner-freebsd-threads@FreeBSD.ORG Sun May 23 12:22:15 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 13B0516A4CE for ; Sun, 23 May 2004 12:22:15 -0700 (PDT) Received: from mail.mikehost.net (lvs-1.voxel.net [207.99.115.72]) by mx1.FreeBSD.org (Postfix) with ESMTP id A951343D2F for ; Sun, 23 May 2004 12:22:14 -0700 (PDT) (envelope-from mike@mike2k.com) Received: by mail.mikehost.net (Postfix, from userid 502) id 8F9341BC7C; Sun, 23 May 2004 12:21:42 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by mail.mikehost.net (Postfix) with ESMTP id 8E4A61BC7A for ; Sun, 23 May 2004 12:21:42 -0700 (PDT) Date: Sun, 23 May 2004 12:21:42 -0700 (PDT) From: mike X-X-Sender: mike@sql01.internal.mikehost.net To: freebsd-threads@freebsd.org In-Reply-To: <40B0F813.3010903@fer.hr> Message-ID: References: <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> <40B0F813.3010903@fer.hr> mike: yes MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 19:22:15 -0000 On Sun, 23 May 2004, Ivan Voras wrote: > > The worst thing is, this is not a new problem - various users have > reported performance troubles with libpthread and/or mysql, including at > this thread in freebsd-current: > once again, here's the deal. i'm willing to PAY someone to own this issue and figure out how to resolve it. also give them access to my box to do whatever they feel like (it needs to stay AMD64 - but i don't care if you go up to 5.3, down to 5.2-current, 5.1, whatever) so i'm supplying resources and incentive. why is nobody stepping up to the plate to help figure out where the bottleneck is? it *sounds* like it's threading or the scheduler or a combination of both, i don't know. but i'm pledging at least $250 USD, and someone else will pledge another $100 - and that's just two of us. if someone owns it and can get a fix out in the next week or two (i don't care how or where) i could even give a bonus. once again - resources and incentive. let's figure this out so everyone can benefit. i just want to expedite this effort. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 12:34:41 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E560416A4CE for ; Sun, 23 May 2004 12:34:41 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id BC80443D2F for ; Sun, 23 May 2004 12:34:39 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 58385 invoked by uid 89); 23 May 2004 19:38:50 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 23 May 2004 19:38:50 -0000 Message-Id: <5.2.0.9.2.20040523122839.01597388@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 12:34:20 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: References: <40B0F813.3010903@fer.hr> <5.2.0.9.2.20040523084628.016296b0@mail.ojoink.com> <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> <40B0F813.3010903@fer.hr> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 19:34:42 -0000 > >once again, here's the deal. > >i'm willing to PAY someone to own this issue and figure out how to resolve >it. also give them access to my box to do whatever they feel like (it >needs to stay AMD64 - but i don't care if you go up to 5.3, down to >5.2-current, 5.1, whatever) > >so i'm supplying resources and incentive. why is nobody stepping up to the >plate to help figure out where the bottleneck is? it *sounds* like it's >threading or the scheduler or a combination of both, i don't know. > >but i'm pledging at least $250 USD, and someone else will pledge another >$100 - and that's just two of us. > >if someone owns it and can get a fix out in the next week or two (i don't >care how or where) i could even give a bonus. > >once again - resources and incentive. let's figure this out so everyone >can benefit. i just want to expedite this effort. I'm the other $100 sponsor on this offer.... Mike sort of jumped the gun here, but let me add that we want to see FreeBSD within +/- 10% performance Vs. "Stock" Linux results with a comparable or equivalent my.cnf files If it's possible, we'll pay to have it happen. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 12:56:29 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 747AF16A4CE for ; Sun, 23 May 2004 12:56:29 -0700 (PDT) Received: from sccrmhc11.comcast.net (sccrmhc11.comcast.net [204.127.202.55]) by mx1.FreeBSD.org (Postfix) with ESMTP id 6AFB343D1F for ; Sun, 23 May 2004 12:56:25 -0700 (PDT) (envelope-from julian@elischer.org) Received: from interjet.elischer.org ([24.7.73.28]) by comcast.net (sccrmhc11) with ESMTP id <2004052319562001100lhe4ae>; Sun, 23 May 2004 19:56:21 +0000 Received: from localhost (localhost.elischer.org [127.0.0.1]) by InterJet.elischer.org (8.9.1a/8.9.1) with ESMTP id MAA13983; Sun, 23 May 2004 12:56:18 -0700 (PDT) Date: Sun, 23 May 2004 12:56:17 -0700 (PDT) From: Julian Elischer To: JG In-Reply-To: <5.2.0.9.2.20040523122839.01597388@mail.ojoink.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 19:56:29 -0000 On Sun, 23 May 2004, JG wrote: > > > > >once again, here's the deal. > > > >i'm willing to PAY someone to own this issue and figure out how to resolve > >it. also give them access to my box to do whatever they feel like (it > >needs to stay AMD64 - but i don't care if you go up to 5.3, down to > >5.2-current, 5.1, whatever) > > > >so i'm supplying resources and incentive. why is nobody stepping up to the > >plate to help figure out where the bottleneck is? it *sounds* like it's > >threading or the scheduler or a combination of both, i don't know. > > > >but i'm pledging at least $250 USD, and someone else will pledge another > >$100 - and that's just two of us. > > > >if someone owns it and can get a fix out in the next week or two (i don't > >care how or where) i could even give a bonus. > > > >once again - resources and incentive. let's figure this out so everyone > >can benefit. i just want to expedite this effort. > > I'm the other $100 sponsor on this offer.... Mike sort of jumped the gun here, > but let me add that we want to see FreeBSD within +/- 10% performance Vs. > "Stock" > Linux results with a comparable or equivalent my.cnf files > > If it's possible, we'll pay to have it happen. There are two things that may help pinpoint the problem firstly, you might try run some work under truss or ktrace and get a feel for the times that various operations take. you might also lile to run a profiled app and a profiled kernel and use gprof and kgmon to get results. There is obviously a bottleneck, but it's very hard to tell what it is.. My guess is that the scheduler(s) are not doing a very good job. and the fact that GIANT is not removed from the kernel yet says that generally syscalls will be a bottleneck. ULE should be able to do a better job at scheduling with multiple CPUs but it is a work in progress. If threads all hit a GIANT based logjam, there is not a lot the scheduler can do about it.. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 13:10:10 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id C5C3916A4CE for ; Sun, 23 May 2004 13:10:10 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 54F9143D1F for ; Sun, 23 May 2004 13:10:10 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4NK9b8Q002486; Sun, 23 May 2004 16:09:37 -0400 (EDT) Date: Sun, 23 May 2004 16:09:37 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: JG In-Reply-To: <5.2.0.9.2.20040523100537.014613a8@mail.ojoink.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 20:10:10 -0000 On Sun, 23 May 2004, JG wrote: > > > >None consolidated in the same post with the same versions > >of FreeBSD and mysql. Like I said before, there are a ton > >of posts on amd64@ and some here on threads@. It would > >help much if you were to summarize them on a web page so > >that all of us don't have to go searching through all > >the archives trying to do it ourselves. > > Good idea, I'll do that now. > > > The default configuration with > >libpthread vs FreeBSD-linuxthreads seems to be similar. > > I would disagree from what I've seen so far. Nothing came > close to the remote tests using Linuxthreads. But I don't see how using network connections vs domain socket (is that what local uses?) affects a threading library. And if you compare FreeBSD linuxthreads to native linuxthreads, it still isn't close which still shows to me that it is the kernel. -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Sun May 23 13:11:47 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 48F8416A4CE for ; Sun, 23 May 2004 13:11:47 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 03D6643D1F for ; Sun, 23 May 2004 13:11:47 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4NKBM8Q002965; Sun, 23 May 2004 16:11:22 -0400 (EDT) Date: Sun, 23 May 2004 16:11:22 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: JG In-Reply-To: <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=X-UNKNOWN Content-Transfer-Encoding: QUOTED-PRINTABLE cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 20:11:47 -0000 On Sun, 23 May 2004, JG wrote: > At 08:09 PM 5/23/2004 +0300, you wrote: > >JG wrote: > > > >> > >> > >> > >>I am just a layman here, but what does this mean? > >You seem to have a very small number of active threads. When I tried=20 > >supersmack on fairly old machine (800MHz dual pentium) I get ~30 active= =20 > >mysql threads using CPU between 1.5% and 3.5%. >=20 > You mean where I have the 3 instances of mysqld running in top? You have= =20 > ~30 instances? >=20 > Why would your box use more? Is there any configuration or flag in mysql= =20 > that would cause this? >=20 > How was your mysql compiled? What version of FreeBSD are you running? >=20 > >I would suspect the test is limited by context switch and syscall=20 > >overhead. With the old machine I see >70000 syscalls and >20000 context= =20 > >switches a second. So I suspect FreeBSD syscall overhead compared to lin= ux=20 > >must be higher. Mysql also asks for time repeatedly so make sure you are= =20 > >running ACPI timecounters. (don=B4t know if they are available on AMD64)= =2E >=20 > I know that this FreeBSD/AMD64 reports that it is using ACPI... how do I= =20 > find out if its using timecounters, or using them properly? Doesn't Linux use HZ=3D1000 by default now and FreeBSD is still at HZ=3D100= ? Could that affect anything? --=20 Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Sun May 23 13:11:48 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A64D316A4CE for ; Sun, 23 May 2004 13:11:48 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id A0DAC43D2F for ; Sun, 23 May 2004 13:11:47 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (h91.vuokselantie10.fi [193.64.42.145]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4NKAd3v028733; Sun, 23 May 2004 23:10:40 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B1053F.6080604@he.iki.fi> Date: Sun, 23 May 2004 23:10:39 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Julian Elischer References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 20:11:48 -0000 Julian Elischer wrote: >you might also lile to run a profiled app and a profiled kernel >and use gprof and kgmon to get results. > > > I think profiling the application would be more useful. Is there a counter for syscalls per process? I know there is a context switch counter. >There is obviously a bottleneck, but it's very hard to tell what it is.. >My guess is that the scheduler(s) are not doing a very good job. and the >fact that GIANT is not removed from the kernel yet says that generally >syscalls will be a bottleneck. > > > While watching the top output, I saw a "logjam" to appear from time to time where all processes/threads were waiting for Giant. However I donīt feel that causes the large impact, it might contribute 10-20% but it does not feel frequent enough to cause 50% difference. >ULE should be able to do a better job at scheduling with >multiple CPUs but it is a work in progress. If threads all hit a GIANT >based logjam, there is not a lot the scheduler can do about it.. > > > I find it hard to believe that the threading stuff would be seriously broken since we do large processing with libkse and donīt have issues with the performance. However Iīm observing about 50000 context switches but only 5000 syscalls a second. (I know itīs a different application but also for 1500 queries a second 70000 syscalls sounds excessive). Pete From owner-freebsd-threads@FreeBSD.ORG Sun May 23 16:58:11 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id DABEA16A4CF for ; Sun, 23 May 2004 16:58:11 -0700 (PDT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id D12E143D2D; Sun, 23 May 2004 16:58:11 -0700 (PDT) (envelope-from davidxu@freebsd.org) Received: from freebsd.org (davidxu@localhost [127.0.0.1]) i4NNw3xv055037; Sun, 23 May 2004 16:58:04 -0700 (PDT) (envelope-from davidxu@freebsd.org) Message-ID: <40B13A30.50503@freebsd.org> Date: Mon, 24 May 2004 07:56:32 +0800 From: David Xu User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040522 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Thomas Hurst References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521215952.0166c368@mail.ojoink.com> <40AF4A8D.4020602@freebsd.org> <20040523172354.GA97724@voi.aagh.net> In-Reply-To: <20040523172354.GA97724@voi.aagh.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sun, 23 May 2004 23:58:12 -0000 Thomas Hurst wrote: >* David Xu (davidxu@freebsd.org) wrote: > > > >>I suggest you to do file system benchmark first, I suspect >>-CURRENT file system's performance is far behind than Linux. >> >> > >The super-smack benchmarks we performed have minimal impact on >filesystem; the tables are small enough to fit in cache, and systat >shows clearly there's next to nothing touching the filesystem. > >We've tried the latest 5-RELEASE and -CURRENT as of a week or two ago; >libkse (libpthread under current), libthr, libc_r and linuxthreads. >System and process scope threads, linking explicitly at compile time to >said libs and using libmap.conf. We've disabled HTT and tweaked >everything we could think of with minimal change. > >I would say it *looks* like FreeBSD is only using 1 CPU, but threads are >clearly distributed across them. Getting load >50% seems difficult, >though. > >Given the number of threading libraries etc we've tried, I'm more >inclined to point at scheduling or so. We did try SCHED_ULE once or >twice with minimal difference. Maybe MySQL's just hitting a lot of >locks *shrug*. > >(I'm Chris Elsworth's co-worker btw; the guy mentioned in the root of >the thread :) > > > I have a little knowledge of MySQL, the default mysql table type is mysql, when you update records on the table type, it uses Giant lock, it does not support concurrent update, so if you have SMP machine, it won't help you too much, when your table is for write-most, you should create other types of table, for example BDB or innodb etcs. I would expect 50%CPU usage when updating mysql table on SMP machine despite that memory is large enough --- number of G bytes. David Xu From owner-freebsd-threads@FreeBSD.ORG Sun May 23 17:04:39 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 35B2616A4CE for ; Sun, 23 May 2004 17:04:39 -0700 (PDT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2B82343D2F; Sun, 23 May 2004 17:04:39 -0700 (PDT) (envelope-from davidxu@freebsd.org) Received: from freebsd.org (davidxu@localhost [127.0.0.1]) i4O04UMM055247; Sun, 23 May 2004 17:04:31 -0700 (PDT) (envelope-from davidxu@freebsd.org) Message-ID: <40B13BB3.3030807@freebsd.org> Date: Mon, 24 May 2004 08:02:59 +0800 From: David Xu User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040522 X-Accept-Language: en-us, en MIME-Version: 1.0 To: JG References: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> In-Reply-To: <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 00:04:39 -0000 JG wrote: > >> last pid: 19842; load averages: 7.32, 1.70, >> 0.67 up 5+10:01:49 >> 21:33:44 >> 110 processes: 1 running, 99 sleeping, 10 lock >> CPU states: 55.6% user, 0.0% nice, 39.4% system, 0.8% interrupt, >> 4.2% idle >> Mem: 312M Active, 64M Inact, 103M Wired, 18M Cache, 60M Buf, 992K Free >> Swap: 3906M Total, 164K Used, 3906M Free >> >> PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU >> COMMAND >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.39% 2.39% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 2.34% 2.34% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 2.25% 2.25% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.15% 2.15% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 2.15% 2.15% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 2.15% 2.15% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.05% 2.05% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 2.00% 2.00% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 1.95% 1.95% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.90% 1.90% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.86% 1.86% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.81% 1.81% >> mysqld >> 646 mysql 97 0 285M 282M *Giant 0 400:31 1.81% 1.81% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.71% 1.71% >> mysqld >> 646 mysql 98 0 285M 282M *Giant 0 400:31 1.71% 1.71% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.66% 1.66% >> mysqld >> 646 mysql 97 0 285M 282M *Giant 0 400:31 1.56% 1.56% >> mysqld >> 646 mysql 4 0 285M 282M sbwait 0 400:31 1.46% 1.46% >> mysqld >> 646 mysql 20 0 285M 282M kserel 0 400:31 1.37% 1.37% >> mysqld >> 646 mysql 20 0 285M 282M kserel 1 400:31 1.22% 1.22% >> mysqld >> 646 mysql 4 0 285M 282M sbwait 0 400:31 1.22% 1.22% >> mysqld >> 19813 pete 4 0 2600K 1712K sbwait 0 0:00 1.19% 0.68% >> super-smack >> 19835 pete 4 0 2600K 1712K sbwait 0 0:00 1.11% 0.63% >> super-smack >> 19831 pete 96 0 2600K 1712K *Giant 0 0:00 1.11% 0.63% >> super-smack >> 19837 pete 4 0 2600K 1712K sbwait 1 0:00 1.11% 0.63% >> super-smack >> >> Pete > > > Ok. Well this is definitely a significant discovery. > > Maybe it's because I'm using SCHED_4BSD for the results I posted.. > lets see.. > > /me switches kernels..... > > Using SMP enabled kernel with SCHED_ULE I get: > > last pid: 724; load averages: 1.43, 0.46, 0.17 > up 0+00:18:28 11:11:23 > 64 processes: 4 running, 60 sleeping > CPU states: 0.0% user, 0.0% nice, 50.2% system, 0.0% interrupt, > 49.8% idle > Mem: 66M Active, 6372K Inact, 59M Wired, 124K Cache, 25M Buf, 1846M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 139 0 63568K 50164K CPU0 0 1:41 49.22% 49.22% > mysqld > 581 mysql 139 0 63568K 50164K RUN 0 1:41 42.19% 42.19% > mysqld > 581 mysql 76 0 63568K 50164K select 0 1:41 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K ksesig 0 1:41 0.00% 0.00% > mysqld > 681 root 8 0 9300K 2088K wait 0 0:00 0.00% 0.00% > super-smack > (only 4 myqld's up there while its running... the rest are > super-smack in wait states) > > > But then I ran it again, same command, doing nothing different.... > > And now I get: > > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 77 0 63584K 50628K RUN 0 3:23 60.16% 60.16% > mysqld > 742 root 88 0 9316K 2108K RUN 0 0:07 12.50% 12.50% > super-smack > 732 root 4 0 9316K 2108K sbwait 1 0:07 8.59% 8.59% > super-smack > 734 root 90 0 9316K 2108K RUN 0 0:05 7.81% 7.81% > super-smack > 581 mysql 76 0 63584K 50628K select 1 3:23 0.00% 0.00% > mysqld > 581 mysql 20 0 63584K 50628K ksesig 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 0 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > 581 mysql 4 0 63584K 50628K sbwait 1 3:23 0.00% 0.00% > mysqld > ... many more mysqld's running in the beginning, but after a couple > minutes > the results flip around to look like this: > > last pid: 759; load averages: 3.91, 4.24, 2.10 > up 0+00:23:41 11:16:36 > 64 processes: 3 running, 59 sleeping, 2 zombie > CPU states: 1.8% user, 0.0% nice, 48.6% system, 0.6% interrupt, > 49.0% idle > Mem: 65M Active, 6336K Inact, 60M Wired, 124K Cache, 25M Buf, 1846M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 139 0 63584K 50180K RUN 0 6:49 46.88% 46.88% > mysqld > 581 mysql 139 0 63584K 50180K RUN 0 6:49 42.97% 42.97% > mysqld > 581 mysql 76 0 63584K 50180K select 1 6:49 0.00% 0.00% > mysqld > 581 mysql 20 0 63584K 50180K ksesig 0 6:49 0.00% 0.00% > mysqld > 732 root 4 0 9316K 2108K sbwait 1 0:16 0.00% 0.00% > super-smack > 756 root 4 0 9316K 2108K sbwait 1 0:10 0.00% 0.00% > super-smack > 739 root 4 0 9316K 2108K sbwait 1 0:06 0.00% 0.00% > super-smack > 737 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% > super-smack > 729 root 4 0 9316K 2108K sbwait 0 0:02 0.00% 0.00% > super-smack > 744 root 4 0 9316K 2108K sbwait 1 0:02 0.00% 0.00% > super-smack > 746 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 733 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 731 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 730 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 735 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 741 root 4 0 9316K 2108K sbwait 0 0:01 0.00% 0.00% > super-smack > 750 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 755 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 747 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 751 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > 745 root 4 0 9316K 2108K sbwait 1 0:01 0.00% 0.00% > super-smack > (+more super-smacks in sbwait state like above) > > > When it's idle with no mysql load / benchmarking going on, it looks > like this: > > last pid: 759; load averages: 1.40, 3.40, 1.98 > up 0+00:24:59 11:17:54 > 33 processes: 1 running, 32 sleeping > CPU states: 0.2% user, 0.0% nice, 0.0% system, 0.4% interrupt, > 99.4% idle > Mem: 60M Active, 5936K Inact, 58M Wired, 124K Cache, 25M Buf, 1854M Free > Swap: 4096M Total, 4096M Free > > PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU > COMMAND > 581 mysql 76 0 63568K 50164K select 1 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K ksesig 0 7:27 0.00% 0.00% > mysqld > 581 mysql 20 0 63568K 50164K kserel 0 7:27 0.00% 0.00% > mysqld > 677 root 76 0 6712K 1680K CPU0 0 0:00 0.00% 0.00% top > > > Is this normal behavior? > > Note that default mysql table type is mysql, which uses Giant Lock when doing update on table, it does not support concurrent update, each thread wants to update the table will be serialized, fix me if I am wrong, but some years ago, mysql book tells me the fact, you might need to look other types, for example, BDB or innodb. 50% cpu usage on SMP machine is normal for MySQL server using mysql table type. David Xu From owner-freebsd-threads@FreeBSD.ORG Sun May 23 17:18:37 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9EE0B16A4CE for ; Sun, 23 May 2004 17:18:37 -0700 (PDT) Received: from mta05-svc.ntlworld.com (mta05-svc.ntlworld.com [62.253.162.45]) by mx1.FreeBSD.org (Postfix) with ESMTP id 19D9643D46 for ; Sun, 23 May 2004 17:18:37 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta05-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040524001642.NRFH2927.mta05-svc.ntlworld.com@voi.aagh.net> for ; Mon, 24 May 2004 01:16:42 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BS3AZ-0000lr-A7 for freebsd-threads@freebsd.org; Mon, 24 May 2004 01:18:19 +0100 Date: Mon, 24 May 2004 01:18:19 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040524001819.GA2713@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 00:18:37 -0000 * JG (amd64list@jpgsworld.com) wrote: > I use 30 10000 for everything because I feel it simulates a more > realistic load on the server (But I could be wrong). It takes a good > 2-4 minutes between tests though.. :( Well, super-smack isn't that effecient; with 30 clients you'll be getting 30 copies of it (perl I think) sucking down millions of records each; that saps a lot of power away from MySQL, so poor performance is to be expected. -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Sun May 23 17:35:59 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 501A616A4CE for ; Sun, 23 May 2004 17:35:59 -0700 (PDT) Received: from mta09-svc.ntlworld.com (mta09-svc.ntlworld.com [62.253.162.49]) by mx1.FreeBSD.org (Postfix) with ESMTP id C372C43D1D for ; Sun, 23 May 2004 17:35:58 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta09-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040524003537.MDUU20971.mta09-svc.ntlworld.com@voi.aagh.net> for ; Mon, 24 May 2004 01:35:37 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BS3Qn-0000oH-VU for freebsd-threads@freebsd.org; Mon, 24 May 2004 01:35:05 +0100 Date: Mon, 24 May 2004 01:35:05 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040524003505.GB2713@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> <40B13BB3.3030807@freebsd.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <40B13BB3.3030807@freebsd.org> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 00:35:59 -0000 * David Xu (davidxu@freebsd.org) wrote: > Note that default mysql table type is mysql, which uses Giant Lock when > doing update on table, it does not support concurrent update, each thread > wants to update the table will be serialized, fix me if I am wrong, but some > years ago, mysql book tells me the fact, you might need to look other types, > for example, BDB or innodb. 50% cpu usage on SMP machine is normal for > MySQL server using mysql table type. Only if you're only using one table. While this is true in this benchmark, it isn't really relevent because we're only testing selects, which are pure reads. There should be no table locking getting in the way; Linux's performance would seem to confirm this. -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Sun May 23 20:09:07 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5622C16A4CE for ; Sun, 23 May 2004 20:09:07 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0EAD543D31 for ; Sun, 23 May 2004 20:09:07 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 72308 invoked by uid 89); 24 May 2004 03:13:02 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 24 May 2004 03:13:02 -0000 Message-Id: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 20:08:29 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <20040524001819.GA2713@voi.aagh.net> References: <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 03:09:07 -0000 At 01:18 AM 5/24/2004 +0100, you wrote: >* JG (amd64list@jpgsworld.com) wrote: > > > I use 30 10000 for everything because I feel it simulates a more > > realistic load on the server (But I could be wrong). It takes a good > > 2-4 minutes between tests though.. :( > >Well, super-smack isn't that effecient; with 30 clients you'll be >getting 30 copies of it (perl I think) It's written in C++ ... you might want to minimally know what language an application is written in before you pass judgements on it or make statements of it's efficiency :-/ > sucking down millions of records Millions eh? What's 30x10000? >each; that saps a lot of power away from MySQL, so poor performance is >to be expected. No more help from you please. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 20:23:33 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id D09F516A4CE for ; Sun, 23 May 2004 20:23:33 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id A155043D1F for ; Sun, 23 May 2004 20:23:33 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 72812 invoked by uid 89); 24 May 2004 03:27:44 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 24 May 2004 03:27:44 -0000 Message-Id: <5.2.0.9.2.20040523201120.0158a100@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Sun, 23 May 2004 20:23:12 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> References: <20040524001819.GA2713@voi.aagh.net> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 03:23:33 -0000 I'm just getting frustrated here... Is this a threads problem? Is this a kernel problem? Is this a MySQL problem? Or some combination of all three... or more. Does anyone even know what the problem is? Or care to find out? Because if you don't, then the answer is simple: - for standalone MySQL servers run your favorite flavor of Linux, because it seems that any of them, with out of the box OS & MySQL install will slam FreeBSD performance wise. I understand that FreeBSD's big thing is pushing stability vs performance as Peter said... but is that to say that there is no stability, or an unacceptable amount of stability with Linux? Because I'm sure many of the Fortune 500 companies that have opt'd for Linux would disagree. And there is a point where a certain amount of loss of performance for increased stability just isn't acceptable anyway. I love using FreeBSD, but this performance issue is really starting to frustrate me. I still need to put a page together with all the benchmark results. I'll try to do that tomorrow. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 21:03:09 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A410916A4CE; Sun, 23 May 2004 21:03:09 -0700 (PDT) Received: from mail.mikehost.net (lvs-1.voxel.net [207.99.115.72]) by mx1.FreeBSD.org (Postfix) with ESMTP id 605C543D2F; Sun, 23 May 2004 21:03:09 -0700 (PDT) (envelope-from mike@mike2k.com) Received: by mail.mikehost.net (Postfix, from userid 502) id 8A0D71BC82; Sun, 23 May 2004 21:02:03 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by mail.mikehost.net (Postfix) with ESMTP id 888581BC7F; Sun, 23 May 2004 21:02:03 -0700 (PDT) Date: Sun, 23 May 2004 21:02:03 -0700 (PDT) From: mike X-X-Sender: mike@sql01.internal.mikehost.net To: David Xu In-Reply-To: <40B13BB3.3030807@freebsd.org> Message-ID: References: <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <40B13BB3.3030807@freebsd.org> mike: yes MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 04:03:09 -0000 > > > > > Note that default mysql table type is mysql, which uses Giant Lock when > doing update on table, it does not support concurrent update, each thread > wants to update the table will be serialized, fix me if I am wrong, but some > years ago, mysql book tells me the fact, you might need to look other types, > for example, BDB or innodb. 50% cpu usage on SMP machine is normal for > MySQL server using mysql table type. > > David Xu i just want to note - either way, running the same benchmarking utilities on linux shows linux outperforming freebsd hands down. this isn't a theoretical debate on why mysql performance might be slow. it works fine on linux - our question is why we can't get that type of performance out of freebsd. to reiterate for the nth time - i'm pledging $250 to whomever will own this/fix it. i believe two other parties will pledge another combined $250 as well. that's $500 to do something that [overall] benefits freebsd users, especially those wishing to run high performance mysql servers on freebsd. we want it to at least match linux performance, if not be able to exceed it. right now we're just going in loops with requests for more benchmarks and such. if it's worth $500, someone "in the know" step up and we'll give you access to one of our boxes, you can do whatever you want to get it to perform how we'd like. at least 2 or 3 of us have pretty similarly configured machines, so if you can get it to work good on one, that should be able to be applied to all, if it's not a permanent obvious fix in the freebsd kernel, scheduler, threading, or just a patch to get more out of mysql on freebsd 5.x. - mike From owner-freebsd-threads@FreeBSD.ORG Sun May 23 22:06:12 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 1B0F116A4CE; Sun, 23 May 2004 22:06:12 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9100F43D4C; Sun, 23 May 2004 22:06:11 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4O55T8Q022170; Mon, 24 May 2004 01:05:29 -0400 (EDT) Date: Mon, 24 May 2004 01:05:29 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: mike In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: David Xu cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 05:06:12 -0000 On Sun, 23 May 2004, mike wrote: > > > > > Note that default mysql table type is mysql, which uses Giant Lock when > > doing update on table, it does not support concurrent update, each thread > > wants to update the table will be serialized, fix me if I am wrong, but some > > years ago, mysql book tells me the fact, you might need to look other types, > > for example, BDB or innodb. 50% cpu usage on SMP machine is normal for > > MySQL server using mysql table type. > > > > David Xu > > i just want to note - either way, running the same benchmarking utilities > on linux shows linux outperforming freebsd hands down. > > this isn't a theoretical debate on why mysql performance might be slow. it > works fine on linux - our question is why we can't get that type of > performance out of freebsd. > > to reiterate for the nth time - i'm pledging $250 to whomever will own > this/fix it. i believe two other parties will pledge another combined $250 > as well. > > that's $500 to do something that [overall] benefits freebsd users, > especially those wishing to run high performance mysql servers on freebsd. > > we want it to at least match linux performance, if not be able to exceed > it. > > right now we're just going in loops with requests for more benchmarks and > such. if it's worth $500, someone "in the know" step up and we'll give you > access to one of our boxes, you can do whatever you want to get it to > perform how we'd like. at least 2 or 3 of us have pretty similarly > configured machines, so if you can get it to work good on one, that should > be able to be applied to all, if it's not a permanent obvious fix in the > freebsd kernel, scheduler, threading, or just a patch to get more out of > mysql on freebsd 5.x. There are people putting in a lot of time into locking down the kernel. We used to have people getting paid full-time to work on the kernel, but I don't think that's the case any longer (for certain, there are fewer of them). Unfortunately I don't think this is something you can throw a few hundred dollars at and get a 20 man-hour fix. From owner-freebsd-threads@FreeBSD.ORG Sun May 23 22:08:36 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 847CC16A4CE for ; Sun, 23 May 2004 22:08:36 -0700 (PDT) Received: from fledge.watson.org (fledge.watson.org [204.156.12.50]) by mx1.FreeBSD.org (Postfix) with ESMTP id D59CF43D45 for ; Sun, 23 May 2004 22:08:35 -0700 (PDT) (envelope-from robert@fledge.watson.org) Received: from fledge.watson.org (localhost [127.0.0.1]) by fledge.watson.org (8.12.11/8.12.11) with ESMTP id i4O57mO7044212; Mon, 24 May 2004 01:07:48 -0400 (EDT) (envelope-from robert@fledge.watson.org) Received: from localhost (robert@localhost)i4O57mwl044169; Mon, 24 May 2004 01:07:48 -0400 (EDT) (envelope-from robert@fledge.watson.org) Date: Mon, 24 May 2004 01:07:48 -0400 (EDT) From: Robert Watson X-Sender: robert@fledge.watson.org To: Petri Helenius In-Reply-To: <40B1053F.6080604@he.iki.fi> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: QUOTED-PRINTABLE cc: Julian Elischer cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 05:08:36 -0000 On Sun, 23 May 2004, Petri Helenius wrote: > >There is obviously a bottleneck, but it's very hard to tell what it is.. > >My guess is that the scheduler(s) are not doing a very good job. and the > >fact that GIANT is not removed from the kernel yet says that generally > >syscalls will be a bottleneck. > > > While watching the top output, I saw a "logjam" to appear from time to=20 > time where all processes/threads were waiting for Giant. However I don=B4= t=20 > feel that causes the large impact, it might contribute 10-20% but it=20 > does not feel frequent enough to cause 50% difference. top is a little misleading because it has to acquire Giant in order to check the status of the other processes. This increases the chance of Giant contention. There are at least a few things going on here. Among various results, I saw that switching to a UP kernel improved performance, but not nearly enough. This suggests lock contention is not the cause of the problem. If you want to investigate lock contention, there are a couple of things you might try: (1) Compile the kernel with MUTEX_PROFILING -- it has two contention measurement fields that can help track contention. Note that running with mutex profiling will dramatically hurt performance, but might still be quite informative.=20 (2) It might be interesting to run with the netperf patches, as they should greatly reduce contention for local UNIX domain socket I/O. I haven't tried any benchmarking with MySQL, but it might be worth a try. You can find information on the ongoing work at:=20 =09http://www.watson.org/~robert/freebsd/netperf/ The work is moving fairly fast, as I'm working on tracking down additional socket nits, but it could help. > >ULE should be able to do a better job at scheduling with > >multiple CPUs but it is a work in progress. If threads all hit a GIANT= =20 > >based logjam, there is not a lot the scheduler can do about it.. > > > I find it hard to believe that the threading stuff would be seriously=20 > broken since we do large processing with libkse and don=B4t have issues= =20 > with the performance. However I=B4m observing about 50000 context switche= s=20 > but only 5000 syscalls a second. (I know it=B4s a different application= =20 > but also for 1500 queries a second 70000 syscalls sounds excessive). ULE has some sort of known load balancing problem between multiple CPUs -- I've observed it on some local benchmarking with ubench, at least a month or so ago. It seemed to provide highly busy processes derived from the same process tree from migrating properly. SCHED_4BSD did not have this problem. Since we've seen results suggesting changing to SCHED_4BSD didn't help all that much easier, it's still likely not to be the cause. A few months ago I did some work to optimize system call cost a bit -- we had some extra mutex operations. It might be interesting to use ktrace or truss to generate a profile of the system call mix in use, perhaps that would give some informative results about things to look at. Robert N M Watson FreeBSD Core Team, TrustedBSD Projects robert@fledge.watson.org Senior Research Scientist, McAfee Research From owner-freebsd-threads@FreeBSD.ORG Sun May 23 22:16:12 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B27A016A4CE for ; Sun, 23 May 2004 22:16:12 -0700 (PDT) Received: from fledge.watson.org (fledge.watson.org [204.156.12.50]) by mx1.FreeBSD.org (Postfix) with ESMTP id 56FCF43D1D for ; Sun, 23 May 2004 22:16:12 -0700 (PDT) (envelope-from robert@fledge.watson.org) Received: from fledge.watson.org (localhost [127.0.0.1]) by fledge.watson.org (8.12.11/8.12.11) with ESMTP id i4O5FlDE045716; Mon, 24 May 2004 01:15:47 -0400 (EDT) (envelope-from robert@fledge.watson.org) Received: from localhost (robert@localhost)i4O5FlEm045713; Mon, 24 May 2004 01:15:47 -0400 (EDT) (envelope-from robert@fledge.watson.org) Date: Mon, 24 May 2004 01:15:47 -0400 (EDT) From: Robert Watson X-Sender: robert@fledge.watson.org To: Petri Helenius In-Reply-To: <40B1053F.6080604@he.iki.fi> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=ISO-8859-1 Content-Transfer-Encoding: QUOTED-PRINTABLE cc: Julian Elischer cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 05:16:12 -0000 On Sun, 23 May 2004, Petri Helenius wrote: > I find it hard to believe that the threading stuff would be seriously > broken since we do large processing with libkse and don=B4t have issues > with the performance. However I=B4m observing about 50000 context switche= s > but only 5000 syscalls a second. (I know it=B4s a different application > but also for 1500 queries a second 70000 syscalls sounds excessive).=20 Another thing that would be interesting to know is where the user processes are doing most of their waiting. Unfortunately, the mechanism I would use to derive this is a bit complicated. Not sure if you want to get into it or not, but... here's what I would do: I'd modify the KTR(4) tracing in msleep() to track the time slept and generate a log message indicating the wait channel, wait time, and process information. I'd set up a KTR and maybe ALQ trace to log a set of MySQL traces over a five second window during the benchmark. I'd dump the log records and then do a bit of statistical analysis to look at what the wait time distribution for various wait channels is. This might give a hint as to what in the kernel is being "waited" for. A nice pretty graph or the like for each wait channel. My recollection of previous posts in this thread is that the system is relatively idle, but did we determine what behavior was being seen on UP relating to CPU consumption? In particular, % user/system/interrupt/...=20 time for the CPU during a sustained benchmark run? Robert N M Watson FreeBSD Core Team, TrustedBSD Projects robert@fledge.watson.org Senior Research Scientist, McAfee Research From owner-freebsd-threads@FreeBSD.ORG Mon May 24 00:07:41 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 154FC16A4CE for ; Mon, 24 May 2004 00:07:41 -0700 (PDT) Received: from rms04.rommon.net (rms04.rommon.net [212.54.2.140]) by mx1.FreeBSD.org (Postfix) with ESMTP id EB54143D41 for ; Mon, 24 May 2004 00:07:39 -0700 (PDT) (envelope-from pete@he.iki.fi) Received: from he.iki.fi (i2-138.rommon.fi [195.163.185.138]) by rms04.rommon.net (8.12.10/8.12.9) with ESMTP id i4O77V3v031768; Mon, 24 May 2004 10:07:31 +0300 (EEST) (envelope-from pete@he.iki.fi) Message-ID: <40B19F2E.8010804@he.iki.fi> Date: Mon, 24 May 2004 10:07:26 +0300 From: Petri Helenius User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Thomas Hurst References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com> <5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com> <5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com> <5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com> <5.2.0.9.2.20040523104834.01465598@mail.ojoink.com> <5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com> <40B13BB3.3030807@freebsd.org> <20040524003505.GB2713@voi.aagh.net> In-Reply-To: <20040524003505.GB2713@voi.aagh.net> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 07:07:41 -0000 Thomas Hurst wrote: > >Only if you're only using one table. While this is true in this >benchmark, it isn't really relevent because we're only testing selects, >which are pure reads. There should be no table locking getting in the >way; Linux's performance would seem to confirm this. > > > I changed the test to use InnoDB table type and saw 50% performance improvement. Pete From owner-freebsd-threads@FreeBSD.ORG Mon May 24 07:14:27 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0CE9E16A4CE for ; Mon, 24 May 2004 07:14:27 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3342F43D1D for ; Mon, 24 May 2004 07:14:26 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4OEDa8Q004986; Mon, 24 May 2004 10:13:38 -0400 (EDT) Date: Mon, 24 May 2004 10:13:36 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Petri Helenius In-Reply-To: <40B19F2E.8010804@he.iki.fi> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 14:14:27 -0000 On Mon, 24 May 2004, Petri Helenius wrote: > Thomas Hurst wrote: > > > > >Only if you're only using one table. While this is true in this > >benchmark, it isn't really relevent because we're only testing selects, > >which are pure reads. There should be no table locking getting in the > >way; Linux's performance would seem to confirm this. > > > > I changed the test to use InnoDB table type and saw 50% performance > improvement. Are you able to run the test using InnoDB under Linux also (just to see if Linux also gets a ~50% improvement)? What did you do to change the test (got a diff)? -- Dan From owner-freebsd-threads@FreeBSD.ORG Mon May 24 07:37:09 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 861A316A4CE for ; Mon, 24 May 2004 07:37:09 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id 467B443D1F for ; Mon, 24 May 2004 07:37:09 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSGYp-0003TA-FR; Mon, 24 May 2004 15:36:15 +0100 Date: Mon, 24 May 2004 15:36:15 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524143615.GA12869@shagged.org> References: <40B19F2E.8010804@he.iki.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i Sender: Chris Elsworth X-SA-Exim-Mail-From: chris@clueful.shagged.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 14:37:09 -0000 On Mon, May 24, 2004 at 10:13:36AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Petri Helenius wrote: > > > I changed the test to use InnoDB table type and saw 50% performance > > improvement. > > Are you able to run the test using InnoDB under Linux also > (just to see if Linux also gets a ~50% improvement)? I've just done so. It doesn't make much difference. It should be noted, by the way, that the default super-smack tests use the query cache, which makes the numbers rather useless for testing different table types; all you're testing is how fast MySQL can fetch data from a cache. It won't be hitting the table at *all* after the first few queries, and thus tabletype will make *negligible* difference for the select-key test. I mean really, in the order of 0.0001 in the figures I quote below. One should also note that super-smack does not drop its test table after its done, so you need to drop it manually, or change the type, so that your tests are testing what you want. So, each of the smackfiles used below was changed to have sql_no_cache in the select. The _i smackfile simply has type=innodb in the create table statement. Here's the numbers: MyISAM: root@cluestick:~/super-smack-1.2# src/super-smack smacks/select-key.smack 4 100000 Query Barrel Report for client smacker1 connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 800000 0 0 22063.58 InnoDB: root@cluestick:~/super-smack-1.2# src/super-smack smacks/select-key_i.smack 4 100000 Query Barrel Report for client smacker1 connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 800000 0 0 22464.32 MyISAM: root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select.smack 4 100000 Query Barrel Report for client smacker connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 8296.32 update_index 400000 0 0 8296.32 InnoDB: root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 Query Barrel Report for client smacker connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 8141.49 update_index 400000 0 0 8141.49 For what it's worth, the select-key test numbers raise to approximately 36,000 with the query cache enabled. -- Chris From owner-freebsd-threads@FreeBSD.ORG Mon May 24 07:45:42 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 4DC2716A4CE for ; Mon, 24 May 2004 07:45:42 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id DDBDF43D31 for ; Mon, 24 May 2004 07:45:41 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4OEio8Q013149; Mon, 24 May 2004 10:44:50 -0400 (EDT) Date: Mon, 24 May 2004 10:44:50 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Chris Elsworth In-Reply-To: <20040524143615.GA12869@shagged.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 14:45:42 -0000 On Mon, 24 May 2004, Chris Elsworth wrote: > On Mon, May 24, 2004 at 10:13:36AM -0400, Daniel Eischen wrote: > > On Mon, 24 May 2004, Petri Helenius wrote: > > > > > I changed the test to use InnoDB table type and saw 50% performance > > > improvement. > > > > Are you able to run the test using InnoDB under Linux also > > (just to see if Linux also gets a ~50% improvement)? > > I've just done so. It doesn't make much difference. [ ... ] > So, each of the smackfiles used below was changed to have sql_no_cache > in the select. The _i smackfile simply has type=innodb in the create > table statement. > > Here's the numbers: And what kind of improvement do you see using FreeBSD (-current, libpthread)? -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Mon May 24 07:46:47 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 41E5E16A4CE for ; Mon, 24 May 2004 07:46:47 -0700 (PDT) Received: from mta08-svc.ntlworld.com (mta08-svc.ntlworld.com [62.253.162.48]) by mx1.FreeBSD.org (Postfix) with ESMTP id 89DFB43D2D for ; Mon, 24 May 2004 07:46:46 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta08-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040524144545.VTKK21846.mta08-svc.ntlworld.com@voi.aagh.net> for ; Mon, 24 May 2004 15:45:45 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BSGi1-0000S5-5s for freebsd-threads@freebsd.org; Mon, 24 May 2004 15:45:45 +0100 Date: Mon, 24 May 2004 15:45:45 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040524144545.GA1378@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 14:46:47 -0000 * JG (amd64list@jpgsworld.com) wrote: > At 01:18 AM 5/24/2004 +0100, you wrote: > > Well, super-smack isn't that effecient; with 30 clients you'll be > > getting 30 copies of it (perl I think) > > It's written in C++ ... you might want to minimally know what language > an application is written in before you pass judgements on it or make > statements of it's efficiency :-/ Hm... not sure where I got perl from; I even remember compiling it. It still sucks quite a bit of CPU, either way, although top could be misleading me. Still, if we're going to be comparing with results from Linux which are near the peak of it's performance curve we don't want to be running in the middle of our much lower one. > > sucking down millions of records > > Millions eh? What's 30x10000? Right; I was somehow under the impression it was selecting multiple records; I associated the relatively high CPU usage from super-smack with fetching the result set, when really it's got plenty to do just sending queries. > No more help from you please. No, at least not late at night after not having a caffeine fix for about 8 hours. It doesn't help that Debian's rotting my brain now *sulk* -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:03:52 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2E31516A4CE for ; Mon, 24 May 2004 08:03:52 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id EF44F43D1D for ; Mon, 24 May 2004 08:03:51 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSGz3-0003lF-Qq; Mon, 24 May 2004 16:03:21 +0100 Date: Mon, 24 May 2004 16:03:21 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524150321.GA13862@shagged.org> References: <40B19F2E.8010804@he.iki.fi> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i Sender: Chris Elsworth X-SA-Exim-Mail-From: chris@clueful.shagged.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:03:52 -0000 On Mon, May 24, 2004 at 10:13:36AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Petri Helenius wrote: > > > Thomas Hurst wrote: > > > > > > > >Only if you're only using one table. While this is true in this > > >benchmark, it isn't really relevent because we're only testing selects, > > >which are pure reads. There should be no table locking getting in the > > >way; Linux's performance would seem to confirm this. > > > > > > > I changed the test to use InnoDB table type and saw 50% performance > > improvement. > > Are you able to run the test using InnoDB under Linux also > (just to see if Linux also gets a ~50% improvement)? I have to aplogise unreservedly. Despite thoroughly testing the different table types for the last half an hour, I've only just realised that I had skip-innodb in my.cnf and thus the InnoDB tables were actually being created as MyISAM tables. Please consider all the InnoDB results just posted as void and replaced with the following: root@cluestick:~/super-smack-1.2# src/super-smack smacks/select-key_i.smack 4 100000 Query Barrel Report for client smacker1 connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 800000 0 0 22509.05 This one got a bit more interesting. Since InnoDB is transactional, it maintains a log of updates. Every update incurs a log write. With innodb_flush_log_at_trx_commit =1 .. root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 Query Barrel Report for client smacker connect: max=1ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 2517.41 update_index 400000 1 0 2517.41 However, with innodb_flush_log_at_trx_commit = 0, meaning InnoDB does *not* have to flush the log after every update: root@cluestick:~/super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 Query Barrel Report for client smacker connect: max=0ms min=0ms avg= 0ms from 4 clients Query_type num_queries max_time min_time q_per_s select_index 400000 0 0 8473.54 update_index 400000 0 0 8473.54 Obviously, the first update test is thoroughly disk bound; 2500 transactions per second to disk, at a rate of 13MB/s. Select performance is unchanged as to the setting of innodb_flush_log_at_trx_commit, as you might expect. -- Chris From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:27:39 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 5CBB716A4CE for ; Mon, 24 May 2004 08:27:39 -0700 (PDT) Received: from ojoink.com (center.ojoink.com [216.65.123.180]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3992843D2D for ; Mon, 24 May 2004 08:27:37 -0700 (PDT) (envelope-from amd64list@jpgsworld.com) Received: (qmail 90416 invoked by uid 89); 24 May 2004 15:31:44 -0000 Received: from unknown (HELO MAINBX.jpgsworld.com) (amd64list@jpgsworld.com@24.10.96.33) by center.ojoink.com with SMTP; 24 May 2004 15:31:44 -0000 Message-Id: <5.2.0.9.2.20040524081943.0343ec68@mail.ojoink.com> X-Sender: amd64list@jpgsworld.com@mail.ojoink.com X-Mailer: QUALCOMM Windows Eudora Version 5.2.0.9 Date: Mon, 24 May 2004 08:27:07 -0700 To: freebsd-threads@freebsd.org From: JG In-Reply-To: <20040524144545.GA1378@voi.aagh.net> References: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:27:39 -0000 At 03:45 PM 5/24/2004 +0100, you wrote: >* JG (amd64list@jpgsworld.com) wrote: > > > At 01:18 AM 5/24/2004 +0100, you wrote: > > > Well, super-smack isn't that effecient; with 30 clients you'll be > > > getting 30 copies of it (perl I think) > > > > It's written in C++ ... you might want to minimally know what language > > an application is written in before you pass judgements on it or make > > statements of it's efficiency :-/ > >Hm... not sure where I got perl from; I even remember compiling it. It >still sucks quite a bit of CPU, either way, although top could be >misleading me. The benchmarking util that JeremyZ originally wrote was written in Perl. Maybe you were thinking of that. >Still, if we're going to be comparing with results from Linux which are >near the peak of it's performance curve we don't want to be running in >the middle of our much lower one. Why would you assume Linux is near the peak of it's performance curve? Nobody that I have seen so far, including myself, has even tried to tweak Linux or MySQL on Linux beyond and out-of-the-box installation to get results that far exceed the performance of FreeBSD. So to the contrary, it is more likely to be the Linux results that are near the middle or lower end, and FreeBSD's which we are hitting peak performance (trying -CURRENT with every tweak and variation available). > > No more help from you please. > >No, at least not late at night after not having a caffeine fix for about >8 hours. It doesn't help that Debian's rotting my brain now *sulk* Yeah I'm sorry I flamed you there... I'm just very frustrated over here. From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:29:56 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 97FC816A4CE for ; Mon, 24 May 2004 08:29:56 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 3C3B543D48 for ; Mon, 24 May 2004 08:29:54 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4OFTZ8Q024668; Mon, 24 May 2004 11:29:35 -0400 (EDT) Date: Mon, 24 May 2004 11:29:35 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Chris Elsworth In-Reply-To: <20040524150321.GA13862@shagged.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:29:56 -0000 On Mon, 24 May 2004, Chris Elsworth wrote: > Please consider all the InnoDB results just posted as void and > replaced with the following: So to summarize in one place: Run: super-smack-1.2# src/super-smack smacks/select-key.smack 4 100000 DBType Query_type num_queries max_time min_time q_per_s ---------------------------------------------------------------- MyISAM select_index 800000 0 0 22063.58 InnoDB select_index 800000 0 0 22509.05 Run: super-smack-1.2# src/super-smack smacks/update-select_i.smack 4 100000 (innodb_flush_log_at_trx_commit = 0) DBType Query_type num_queries max_time min_time q_per_s ---------------------------------------------------------------- MyISAM select_index 400000 0 0 8296.32 update_index 400000 0 0 8296.32 InnoDB select_index 400000 0 0 8473.54 update_index 400000 0 0 8473.54 In otherwords you see very little gain when switching from MyISAM to InnoDB tables in (native) Linux. Any chance of doing the same on FreeBSD? Where is super-smack? Is it a port? -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:41:46 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id AE46816A4CE for ; Mon, 24 May 2004 08:41:46 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7A71543D3F for ; Mon, 24 May 2004 08:41:46 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSHa9-000A9W-Ry; Mon, 24 May 2004 16:41:41 +0100 Date: Mon, 24 May 2004 16:41:41 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524154141.GA38874@shagged.org> References: <20040524150321.GA13862@shagged.org> Mime-Version: 1.0 Content-Type: text/plain; charset=unknown-8bit Content-Disposition: inline Content-Transfer-Encoding: 8bit In-Reply-To: User-Agent: Mutt/1.5.6i Sender: Chris Elsworth X-SA-Exim-Mail-From: chris@clueful.shagged.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:41:46 -0000 On Mon, May 24, 2004 at 11:29:35AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Chris Elsworth wrote: > > In otherwords you see very little gain when switching from MyISAM > to InnoDB tables in (native) Linux. Yeah, pretty much. The extra couple of hundred on InnoDB are probably InnoDBs superior locking abilities, but any real gain it provides are nulled by the extra logging it has to do. Either way, not a 50% gain. > Any chance of doing the same on FreeBSD? Not on this Xeon I'm afraid, I'm out of testing time and it's being taken to a datacentre on Wednesday for live use. However, we are looking at purchasing an Opteron with an approximate spend of Ģ3500UKP, and I'm perfectly happy to devote a few weeks of testing time on that before it starts doing anything useful. > Where is super-smack? Is it a port? It's not a port - http://jeremy.zawodny.com/mysql/super-smack/ -- Chris From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:51:50 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id CA29B16A4CF for ; Mon, 24 May 2004 08:51:50 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id 5EE6543D41 for ; Mon, 24 May 2004 08:51:50 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4OFpS8Q029704; Mon, 24 May 2004 11:51:28 -0400 (EDT) Date: Mon, 24 May 2004 11:51:27 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Chris Elsworth In-Reply-To: <20040524143615.GA12869@shagged.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:51:50 -0000 On Mon, 24 May 2004, Chris Elsworth wrote: > > So, each of the smackfiles used below was changed to have sql_no_cache > in the select. The _i smackfile simply has type=innodb in the create > table statement. I'm not a DB person; can you post a diff to the smackfile? -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Mon May 24 08:57:41 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 9F58E16A4CE for ; Mon, 24 May 2004 08:57:41 -0700 (PDT) Received: from clueful.shagged.org (clueful.shagged.org [212.13.201.101]) by mx1.FreeBSD.org (Postfix) with ESMTP id 47D2443D2D for ; Mon, 24 May 2004 08:57:34 -0700 (PDT) (envelope-from chris@clueful.shagged.org) Received: from chris by clueful.shagged.org with local (Exim 4.30; FreeBSD) id 1BSHp3-000AIA-Nd; Mon, 24 May 2004 16:57:05 +0100 Date: Mon, 24 May 2004 16:57:05 +0100 From: Chris Elsworth To: Daniel Eischen Message-ID: <20040524155705.GA39441@shagged.org> References: <20040524143615.GA12869@shagged.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.6i Sender: Chris Elsworth X-SA-Exim-Mail-From: chris@clueful.shagged.org X-SA-Exim-Scanned: No; SAEximRunCond expanded to false cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 15:57:41 -0000 On Mon, May 24, 2004 at 11:51:27AM -0400, Daniel Eischen wrote: > On Mon, 24 May 2004, Chris Elsworth wrote: > > > > So, each of the smackfiles used below was changed to have sql_no_cache > > in the select. The _i smackfile simply has type=innodb in the create > > table statement. > > I'm not a DB person; can you post a diff to the smackfile? root@cluestick:~/super-smack-1.2/smacks# diff select-key.smack_orig select-key_i.smack 23c23 < )"; --- > ) type=innodb"; 50c50 < query "select * from http_auth where username = '$word'"; --- > query "select sql_no_cache * from http_auth where username = '$word'"; And: root@cluestick:~/super-smack-1.2/smacks# diff update-select.smack_orig update-select_i.smack 23c23 < )"; --- > ) type=innodb"; 47c47 < query "select * from http_auth where username = '$word'"; --- > query "select sql_no_cache * from http_auth where username = '$word'"; -- Chris From owner-freebsd-threads@FreeBSD.ORG Mon May 24 09:10:18 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id EA4A116A4CE for ; Mon, 24 May 2004 09:10:18 -0700 (PDT) Received: from mta07-svc.ntlworld.com (mta07-svc.ntlworld.com [62.253.162.47]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4664F43D2D for ; Mon, 24 May 2004 09:10:18 -0700 (PDT) (envelope-from tom.hurst@clara.net) Received: from voi.aagh.net ([81.104.55.176]) by mta07-svc.ntlworld.com (InterMail vM.4.01.03.37 201-229-121-137-20020806) with ESMTP id <20040524161024.JUKB5696.mta07-svc.ntlworld.com@voi.aagh.net> for ; Mon, 24 May 2004 17:10:24 +0100 Received: from freaky by voi.aagh.net with local (Exim 4.34; FreeBSD) id 1BSI1k-0000mV-2l for freebsd-threads@freebsd.org; Mon, 24 May 2004 17:10:12 +0100 Date: Mon, 24 May 2004 17:10:12 +0100 From: Thomas Hurst To: freebsd-threads@freebsd.org Message-ID: <20040524161012.GA2498@voi.aagh.net> Mail-Followup-To: freebsd-threads@freebsd.org References: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> <5.2.0.9.2.20040524081943.0343ec68@mail.ojoink.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <5.2.0.9.2.20040524081943.0343ec68@mail.ojoink.com> Organization: Not much. User-Agent: Mutt/1.5.6i Sender: Thomas Hurst Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 16:10:19 -0000 * JG (amd64list@jpgsworld.com) wrote: > At 03:45 PM 5/24/2004 +0100, you wrote: > > Still, if we're going to be comparing with results from Linux which > > are near the peak of it's performance curve we don't want to be > > running in the middle of our much lower one. > > Why would you assume Linux is near the peak of it's performance curve? I don't mean generally (the curve of super-smack results across various tunings); I mean the curve you plot with a single set of tunings as you increase the number of clients; performance peaks around $numcpu clients and then drops off as you increase load -- the values being quoted for Linux seem to be around $numcpu level clients. IIRC (heh, pinch of salt time ;) at 30 clients Linux got about 55-65% the performance it got at 4 (numcpu for a HTT dual Xeon), but don't take my word for it (I'll check later if necessary, although without similar hardware to compare against I doubt it's going to be very helpful). > Nobody that I have seen so far, including myself, has even tried to tweak > Linux or MySQL on Linux beyond and out-of-the-box installation to get > results that far exceed the performance of FreeBSD. I think the most we did was use my-huge.cnf, which is the bundled config suggested for >1GB systems. You're right that there's not a lot of need to tune to get damn good results on Linux though; at least on this relatively small benchmark. > Yeah I'm sorry I flamed you there... I'm just very frustrated over here. After recompiling MySQL about 20 times in a row, I fully understand :/ -- Thomas 'Freaky' Hurst - freaky@aagh.net - http://www.aagh.net/ From owner-freebsd-threads@FreeBSD.ORG Mon May 24 10:21:11 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id EFBC916A4CE for ; Mon, 24 May 2004 10:21:11 -0700 (PDT) Received: from mail.mikehost.net (lvs-1.voxel.net [207.99.115.72]) by mx1.FreeBSD.org (Postfix) with ESMTP id C054E43D1F for ; Mon, 24 May 2004 10:21:09 -0700 (PDT) (envelope-from mike@mike2k.com) Received: by mail.mikehost.net (Postfix, from userid 502) id 727D41BC9B; Mon, 24 May 2004 10:20:38 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by mail.mikehost.net (Postfix) with ESMTP id 711181BC94 for ; Mon, 24 May 2004 10:20:38 -0700 (PDT) Date: Mon, 24 May 2004 10:20:38 -0700 (PDT) From: mike X-X-Sender: mike@sql01.internal.mikehost.net To: freebsd-threads@freebsd.org In-Reply-To: <20040524161012.GA2498@voi.aagh.net> Message-ID: References: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040521154458.01627688@127.0.0.1> <5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com> <5.2.0.9.2.20040523103738.01563ed0@mail.ojoink.com> <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> <20040524161012.GA2498@voi.aagh.net> mike: yes MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 17:21:12 -0000 On Mon, 24 May 2004, Thomas Hurst wrote: > [...] i will say i still trust and rely on the "run-all-tests" perl script that is inside of the sql-bench dir on a mysql installation. i think it runs the widest amount of tests that can reliably be adapted cross platform. for some reason i don't trust super-smack fully. it requires perl, DBI, and DBD::mysql - but it comes with the mysql distrubition so it's received their stamp of "approval" basically for something to benchmark the server against. - mike From owner-freebsd-threads@FreeBSD.ORG Mon May 24 11:01:56 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id D134816A4CE for ; Mon, 24 May 2004 11:01:56 -0700 (PDT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id B1C6A43D31 for ; Mon, 24 May 2004 11:01:56 -0700 (PDT) (envelope-from owner-bugmaster@freebsd.org) Received: from freefall.freebsd.org (peter@localhost [127.0.0.1]) i4OI1tS3076675 for ; Mon, 24 May 2004 11:01:55 -0700 (PDT) (envelope-from owner-bugmaster@freebsd.org) Received: (from peter@localhost) by freefall.freebsd.org (8.12.11/8.12.11/Submit) id i4OI1tZE076669 for freebsd-threads@freebsd.org; Mon, 24 May 2004 11:01:55 -0700 (PDT) (envelope-from owner-bugmaster@freebsd.org) Date: Mon, 24 May 2004 11:01:55 -0700 (PDT) Message-Id: <200405241801.i4OI1tZE076669@freefall.freebsd.org> X-Authentication-Warning: freefall.freebsd.org: peter set sender to owner-bugmaster@freebsd.org using -f From: FreeBSD bugmaster To: freebsd-threads@FreeBSD.org Subject: Current problem reports assigned to you X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 18:01:57 -0000 Current FreeBSD problem reports Critical problems S Submitted Tracker Resp. Description ------------------------------------------------------------------------------- o [2000/06/13] kern/19247 threads uthread_sigaction.c does not do anything s [2004/03/15] kern/64313 threads FreeBSD (OpenBSD) pthread implicit set/un o [2004/04/22] threads/65883threads libkse's sigwait does not work after fork 3 problems total. Serious problems S Submitted Tracker Resp. Description ------------------------------------------------------------------------------- o [2000/07/18] kern/20016 threads pthreads: Cannot set scheduling timer/Can o [2000/08/26] misc/20861 threads libc_r does not honor socket timeouts o [2001/01/19] bin/24472 threads libc_r does not honor SO_SNDTIMEO/SO_RCVT o [2001/01/25] bin/24632 threads libc_r delicate deviation from libc in ha o [2001/01/25] misc/24641 threads pthread_rwlock_rdlock can deadlock o [2001/11/26] bin/32295 threads pthread dont dequeue signals o [2002/02/01] i386/34536 threads accept() blocks other threads o [2002/05/25] kern/38549 threads the procces compiled whith pthread stoppe o [2002/06/27] bin/39922 threads [PATCH?] Threaded applications executed w o [2002/08/04] misc/41331 threads Pthread library open sets O_NONBLOCK flag o [2003/03/02] bin/48856 threads Setting SIGCHLD to SIG_IGN still leaves z o [2003/03/10] bin/49087 threads Signals lost in programs linked with libc a [2003/04/08] bin/50733 threads buildworld won't build, because of linkin o [2003/05/07] bin/51949 threads thread in accept cannot be cancelled 14 problems total. Non-critical problems S Submitted Tracker Resp. Description ------------------------------------------------------------------------------- o [2000/05/25] misc/18824 threads gethostbyname is not thread safe o [2000/10/21] misc/22190 threads A threaded read(2) from a socketpair(2) f o [2001/09/09] bin/30464 threads pthread mutex attributes -- pshared o [2002/05/02] bin/37676 threads libc_r: msgsnd(), msgrcv(), pread(), pwri s [2002/07/16] misc/40671 threads pthread_cancel doesn't remove thread from 5 problems total. From owner-freebsd-threads@FreeBSD.ORG Mon May 24 11:10:48 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 50D6416A4CE for ; Mon, 24 May 2004 11:10:48 -0700 (PDT) Received: from mail1.mail.iol.ie (mail1.mail.iol.ie [193.120.142.151]) by mx1.FreeBSD.org (Postfix) with ESMTP id AD5F943D31 for ; Mon, 24 May 2004 11:10:47 -0700 (PDT) (envelope-from s_sourceforge@nedprod.com) Received: from dialup130.ts521.cwt.esat.net ([194.165.162.130] helo=kate) by mail1.mail.iol.ie with esmtp (Exim 3.36 #9) id 1BSJtn-0003wu-00 for freebsd-threads@freebsd.org; Mon, 24 May 2004 19:10:07 +0100 From: "Niall Douglas" To: JG Date: Mon, 24 May 2004 18:45:02 +0100 MIME-Version: 1.0 Message-ID: <40B242AE.16121.2FAF11E3@localhost> Priority: normal In-reply-to: <5.2.0.9.2.20040523201120.0158a100@mail.ojoink.com> References: <5.2.0.9.2.20040523200223.01583468@mail.ojoink.com> X-PM-Encryptor: IDWPGP-PM32, 4 X-mailer: Pegasus Mail for Windows (v4.12a) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 18:10:48 -0000 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 23 May 2004 at 20:23, JG wrote: > Is this a threads problem? > > Is this a kernel problem? > > Is this a MySQL problem? > > Or some combination of all three... or more. > > Does anyone even know what the problem is? Or care to find out? > [snip] > I love using FreeBSD, but this performance issue is really starting to > frustrate me. I've kept out of this so far but maybe now we're at straw clutching time my experiences might be useful. I've personally found that in general the same applications run faster on FreeBSD than Linux. KDE is noticeably more responsive and I can happily use 256Mb RAM for FreeBSD whereas Linux needs 384Mb RAM in VMWare. However when it comes to threads, things stop working properly. I never got my code to work with libc_r at all. With kse threads, pipes ran fine but I never got sockets working (v5.2.1). There appears to be something odd with sockets in threads, they appear to hang momentarily for no reason. I don't know if that's fixed yet. Perhaps MySQL is seeing pauses during threaded socket i/o though I'd have thought it would use pipes when it can. It's worth checking which your build of MySQL is using and whichever it is, try the other one to see what difference it makes. Cheers, Niall -----BEGIN PGP SIGNATURE----- Version: idw's PGP-Frontend 4.9.6.1 / 9-2003 + PGP 8.0.2 iQA/AwUBQLI0n8EcvDLFGKbPEQKNfwCbBFc4BUh6l5PabzQWk0EY9EBJrDEAn3Wl BEAu0FkWOeepTJYszTp5jHYY =YVcD -----END PGP SIGNATURE----- From owner-freebsd-threads@FreeBSD.ORG Mon May 24 13:20:47 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 87AF316A4CE for ; Mon, 24 May 2004 13:20:47 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id DE4BA43D2D for ; Mon, 24 May 2004 13:20:46 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4OKKf8Q013321; Mon, 24 May 2004 16:20:41 -0400 (EDT) Date: Mon, 24 May 2004 16:20:41 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Niall Douglas In-Reply-To: <40B242AE.16121.2FAF11E3@localhost> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 24 May 2004 20:20:47 -0000 On Mon, 24 May 2004, Niall Douglas wrote: > > However when it comes to threads, things stop working properly. I > never got my code to work with libc_r at all. With kse threads, pipes > ran fine but I never got sockets working (v5.2.1). There appears to > be something odd with sockets in threads, they appear to hang > momentarily for no reason. That was fixed after 5.2.1. -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Tue May 25 21:27:56 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 2B87A16A4CE for ; Tue, 25 May 2004 21:27:56 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id B7AC243D41 for ; Tue, 25 May 2004 21:27:55 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4Q4Rk8Q009670; Wed, 26 May 2004 00:27:47 -0400 (EDT) Date: Wed, 26 May 2004 00:27:46 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Chris Elsworth In-Reply-To: <20040524155705.GA39441@shagged.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 May 2004 04:27:56 -0000 On Mon, 24 May 2004, Chris Elsworth wrote: > On Mon, May 24, 2004 at 11:51:27AM -0400, Daniel Eischen wrote: > > On Mon, 24 May 2004, Chris Elsworth wrote: > > > > > > So, each of the smackfiles used below was changed to have sql_no_cache > > > in the select. The _i smackfile simply has type=innodb in the create > > > table statement. > > > > I'm not a DB person; can you post a diff to the smackfile? Thanks. It wasn't clean; I had to also make mods to the smack files to point to /tmp/mysql.sock instead of /var/lib/mysql/mysql.sock. The source also needed tweaking (s/__FreeBSD/__FreeBSD__/ in src/client.cc). You also have to create /var/smack-data/. Here's what I get on a dual 450MHz Xeon PIII (options WITNESS and WITNESS_SKIPSPIN): Run: super-smack ../smacks/select-key.smack 4 10000 DB_type Query_type num_queries max_time min_time q_per_s ----------------------------------------------------------------- myisam select_index 80000 4 2 348.56 innodb select_index 80000 29 2 296.63 Run: super-smack ../smacks/update-select.smack 4 10000 DB_type Query_type num_queries max_time min_time q_per_s ----------------------------------------------------------------- myisam select_index 40000 24 2 169.30 update_index 40000 8 3 169.30 innodb select_index 40000 5 2 164.64 update_index 40000 11 1 164.64 I don't see much of a difference between myisam and innodb; the former even seems to be faster for the select-key.smack test. I'm using my-large.cnf settings in /etc/my.cnf (I have 512MB RAM): [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M thread_concurrency = 8 log-bin server-id = 1 innodb_flush_log_at_trx_commit = 0 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- Dan Eischen From owner-freebsd-threads@FreeBSD.ORG Wed May 26 06:14:20 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 228B916A4CE for ; Wed, 26 May 2004 06:14:20 -0700 (PDT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0184843D1D; Wed, 26 May 2004 06:14:20 -0700 (PDT) (envelope-from davidxu@freebsd.org) Received: from freebsd.org (davidxu@localhost [127.0.0.1]) i4QDDdYQ056079; Wed, 26 May 2004 06:13:41 -0700 (PDT) (envelope-from davidxu@freebsd.org) Message-ID: <40B497A6.6040307@freebsd.org> Date: Wed, 26 May 2004 21:12:06 +0800 From: David Xu User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040522 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Daniel Eischen References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 May 2004 13:14:20 -0000 Daniel Eischen wrote: >On Mon, 24 May 2004, Chris Elsworth wrote: > > > >>On Mon, May 24, 2004 at 11:51:27AM -0400, Daniel Eischen wrote: >> >> >>>On Mon, 24 May 2004, Chris Elsworth wrote: >>> >>> >>>>So, each of the smackfiles used below was changed to have sql_no_cache >>>>in the select. The _i smackfile simply has type=innodb in the create >>>>table statement. >>>> >>>> >>>I'm not a DB person; can you post a diff to the smackfile? >>> >>> > >Thanks. It wasn't clean; I had to also make mods to the smack >files to point to /tmp/mysql.sock instead of /var/lib/mysql/mysql.sock. >The source also needed tweaking (s/__FreeBSD/__FreeBSD__/ in >src/client.cc). You also have to create /var/smack-data/. > >Here's what I get on a dual 450MHz Xeon PIII (options >WITNESS and WITNESS_SKIPSPIN): > >Run: super-smack ../smacks/select-key.smack 4 10000 > >DB_type Query_type num_queries max_time min_time q_per_s >----------------------------------------------------------------- >myisam select_index 80000 4 2 348.56 >innodb select_index 80000 29 2 296.63 > >Run: super-smack ../smacks/update-select.smack 4 10000 > >DB_type Query_type num_queries max_time min_time q_per_s >----------------------------------------------------------------- >myisam select_index 40000 24 2 169.30 > update_index 40000 8 3 169.30 >innodb select_index 40000 5 2 164.64 > update_index 40000 11 1 164.64 > >I don't see much of a difference between myisam and innodb; the >former even seems to be faster for the select-key.smack test. > > Innodb still wins big because it supports transaction and logs everything. it is a bit out of topic... >I'm using my-large.cnf settings in /etc/my.cnf (I have 512MB RAM): > >[client] >port = 3306 >socket = /tmp/mysql.sock > >[mysqld] >port = 3306 >socket = /tmp/mysql.sock >skip-locking >key_buffer = 256M >max_allowed_packet = 1M >table_cache = 256 >sort_buffer_size = 1M >read_buffer_size = 1M >myisam_sort_buffer_size = 64M >thread_cache = 8 >query_cache_size= 16M >thread_concurrency = 8 >log-bin >server-id = 1 >innodb_flush_log_at_trx_commit = 0 > >[mysqldump] >quick >max_allowed_packet = 16M > >[mysql] >no-auto-rehash > >[isamchk] >key_buffer = 128M >sort_buffer_size = 128M >read_buffer = 2M >write_buffer = 2M > >[myisamchk] >key_buffer = 128M >sort_buffer_size = 128M >read_buffer = 2M >write_buffer = 2M > >[mysqlhotcopy] >interactive-timeout > > > From owner-freebsd-threads@FreeBSD.ORG Thu May 27 09:09:54 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 7FF1D16A4CE for ; Thu, 27 May 2004 09:09:54 -0700 (PDT) Received: from miramanee.icarz.com (miramanee.icarz.com [207.99.22.55]) by mx1.FreeBSD.org (Postfix) with ESMTP id 4B3A643D1D for ; Thu, 27 May 2004 09:09:53 -0700 (PDT) (envelope-from kenfreebsd@icarz.com) Received: from deanna.icarz.com (deanna.icarz.com [207.99.22.19]) by miramanee.icarz.com (8.12.10/8.12.10) with ESMTP id i4OEd5xh051421; Mon, 24 May 2004 10:39:05 -0400 (EDT) (envelope-from kenfreebsd@icarz.com) Received: from kenxp (netb-138.icarz.com [209.123.219.138]) by deanna.icarz.com (8.12.9p2/8.12.9) with SMTP id i4OEd311082176; Mon, 24 May 2004 10:39:03 -0400 (EDT) (envelope-from kenfreebsd@icarz.com) Message-ID: <105b01c4419c$dc7a9060$8adb7bd1@icarz.com> From: "Ken Menzel" To: "Petri Helenius" , "Thomas Hurst" References: <5.2.0.9.2.20040521154458.01627688@127.0.0.1><5.2.0.9.2.20040521154458.01627688@127.0.0.1><5.2.0.9.2.20040522052606.0156fd70@mail.ojoink.com><5.2.0.9.2.20040522100318.01598f50@mail.ojoink.com><5.2.0.9.2.20040522135338.0158cc50@mail.ojoink.com><5.2.0.9.2.20040523090659.01628af8@mail.ojoink.com><5.2.0.9.2.20040523102747.015557e8@mail.ojoink.com><5.2.0.9.2.20040523104834.01465598@mail.ojoink.com><5.2.0.9.2.20040523114544.014d8150@mail.ojoink.com><40B13BB3.3030807@freebsd.org> <20040524003505.GB2713@voi.aagh.net> <40B19F2E.8010804@he.iki.fi> Date: Mon, 24 May 2004 10:39:03 -0400 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1409 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409 X-Spam-Score: -100.001 () BAYES_44,USER_IN_WHITELIST X-Scanned-By: MIMEDefang 2.39 cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list Reply-To: Ken Menzel List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 27 May 2004 16:09:54 -0000 Also note that InnoDB tables use a clustered index for the primary key. If you are using the primary key the data will be read at the same time as the index when the correct node is located (Re: High performance MySQL by Jeremy Zawodney and Derek Balling). MyISAM tables use btree's in a separate file scanning the index then starting a read of the data file at the appropriate disk location. Primay key lookups with innodb should be faster and use less disk seeking. Hope this helps, Ken ----- Original Message ----- From: "Petri Helenius" To: "Thomas Hurst" Cc: Sent: Monday, May 24, 2004 3:07 AM Subject: Re: Why is MySQL nearly twice as fast on Linux? > Thomas Hurst wrote: > > > > >Only if you're only using one table. While this is true in this > >benchmark, it isn't really relevent because we're only testing selects, > >which are pure reads. There should be no table locking getting in the > >way; Linux's performance would seem to confirm this. > > > > > > > I changed the test to use InnoDB table type and saw 50% performance > improvement. > > Pete > > > _______________________________________________ > freebsd-threads@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-threads > To unsubscribe, send any mail to "freebsd-threads-unsubscribe@freebsd.org" >