From owner-freebsd-current@FreeBSD.ORG Fri Oct 28 09:24:37 2005 Return-Path: X-Original-To: current@freebsd.org Delivered-To: freebsd-current@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id F358C16A434 for ; Fri, 28 Oct 2005 09:24:36 +0000 (GMT) (envelope-from mv@roq.com) Received: from p4.roq.com (ns1.ecoms.com [207.44.130.137]) by mx1.FreeBSD.org (Postfix) with ESMTP id 9687043D48 for ; Fri, 28 Oct 2005 09:24:34 +0000 (GMT) (envelope-from mv@roq.com) Received: from p4.roq.com (localhost.roq.com [127.0.0.1]) by p4.roq.com (Postfix) with ESMTP id DFF804CE9D; Fri, 28 Oct 2005 09:24:44 +0000 (GMT) Received: from [192.168.0.3] (ppp157-158.static.internode.on.net [150.101.157.158]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by p4.roq.com (Postfix) with ESMTP id 98E634CE98; Fri, 28 Oct 2005 09:24:43 +0000 (GMT) Message-ID: <4361EE53.80603@roq.com> Date: Fri, 28 Oct 2005 19:24:35 +1000 From: Michael VInce User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.7.12) Gecko/20051019 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Marian Hettwer References: <435F48DA.6060009@kernel32.de> <20051026105411.L32255@fledge.watson.org> <435F6B01.5020003@kernel32.de> <4361DDBB.4060903@roq.com> In-Reply-To: <4361DDBB.4060903@roq.com> Content-Type: text/plain; charset=ISO-8859-15; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP Cc: current@freebsd.org Subject: Re: MySQL Performance 6.0rc1 X-BeenThere: freebsd-current@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Discussions about the use of FreeBSD-current List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 28 Oct 2005 09:24:37 -0000 Michael VInce wrote: > Marian Hettwer wrote: > >> Hi Robert, >> >> Robert Watson wrote: >> >>> >>> On Wed, 26 Oct 2005, Marian Hettwer wrote: >>> >>>> I'm using RC1 on some of my test servers and workstations. So far, >>>> it runs very good (as in stable and fast) :) However, I thought of >>>> doing a small benchmark of MySQL in comparison to Linux 2.6.8 >>>> (Debian Sarge) and the results are kinda... shocking. >>>> >>>> On FreeBSD I took a pretty much generic kernel, I just removed >>>> debugging options, added SMP and changed the scheduler from BSD to >>>> ULE. I used super-smack to do a select-key test. MySQL 4.1 was >>>> build from ports with BUILD_OPTIMIZED and BUILD_STATIC >>> >>> >>> >>> >>> Could you: >>> >>> - Switch back to 4BSD >>> >> yep. >> by the way, back in the days of FreeBSD 4.x and 5.x, after I did a >> config MYKERNEL, it doesn't said I need to do a "make cleandepend" >> before doing a "make depend"... why that? Seems like this additional >> step in compiling the Kernel is around since 6-CURRENT, right? >> >> Here we go with a super-smack with 4BSD (but unchanged libmap.conf)... >> Kernel is apart from scheduler still the same like the on uploaded at >> http://unixoid.de/rabauke/freebsd/ >> I did the select-smack 4 times. This is now: 4BSD, MySQL 4.1.14, >> BUILD_STATIC, BUILD_OPTIMIZED: >> >> select_index 200000 2 0 3776.34 >> select_index 200000 2 0 3802.04 >> select_index 200000 2 0 3809.38 >> select_index 200000 2 0 3796.41 >> >> Numbers are quite the same with 4BSD... >> >> >>> - Use libmap to configure the mysql server to use libthr instead of >>> libpthread >>> >> uh. Last time I fiddled around with libmap.conf was back in FreeBSD >> 5.1 ... could you enlighten me a wee bit in regards to libmap.conf ? :) >> If not, I'll stress google a bit and hopefully find something useful... >> >> >>> And see what that does to performance using otherwise identical >>> configurations? >>> >> my.cnf is pretty much my-medium.cnf on Debian and FreeBSD. Although, >> Debian has MySQL 4.1.11 and FreeBSD Ports has MySQL 4.1.14. >> As mentioned before, this benchmark here is a quick shot. However, >> the difference shouldn't be _that_ large (3600 vs. 14000). >> >> > I have run the exact same test and have had much higher benchmarks > (close to double) using my-large.cnf or my-huge.cnf. > > I am able to get 23,626 compared to your 3,800 on the 5.4 and I am > sure with out a doubt I could get higher on 6.0. > > The most important thing to remember about this benchmark is that its > not really a good test for MySQL performance for a lot of database > setups as its testing many queries at the same time performance, the > services I run largely have only 1 or maybe 2 connected constantly > querying clients. So its much more important for a single query to > just be able to get its data fast more them having 50 different > clients doing a queries at the same time for me and a lot of other db > setups out there. > > Here is a copy and paste of a post I did a while ago > > I compiled 4.0 MySQL with some optimizations > portupgrade -RN -m 'BUILD_STATIC=yes BUILD_OPTIMIZED=yes' > /usr/ports/databases/mysql40-server/ > > Copied the default large my.cnf profile cp > /usr/local/share/mysql/my-large.cnf /var/db/mysql/my.cnf > > # super-smack /usr/share/smacks/select-key.smack 50 1000 > Query Barrel Report for client smacker1 > connect: max=44ms min=11ms avg= 20ms from 50 clients > Query_type num_queries max_time min_time q_per_s > select_index 100000 3 0 23626.76 > > If I delete the /var/db/mysql/my.cnf and restart mysql so I get > default configuration, I get 13398.76 > # super-smack /usr/share/smacks/select-key.smack 50 1000 > Query Barrel Report for client smacker1 > connect: max=29ms min=7ms avg= 17ms from 50 clients > Query_type num_queries max_time min_time q_per_s > select_index 100000 7 0 13398.76 > > > Mike I realized I read your email wrong and noticed you used 10 clients and 200,000 queries, I have re-run the test and I scored 24,138 this is on a 4.1 MySQL install, to me it seems like there is something really wrong with your box. This is a Dual Pentium 4 box on 6.0RC1 generic SMP kernel. # super-smack /usr/share/smacks/select-key.smack 10 10000 Query Barrel Report for client smacker1 connect: max=4ms min=2ms avg= 2ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 200000 0 0 24138.77 My results from timer.c null function: 0.00339 getpid(): 0.29227 time(): 1.14151 gettimeofday(): 1.14168