From owner-freebsd-performance@FreeBSD.ORG Fri Oct 6 07:20:55 2006 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 0CF0C16A47C for ; Fri, 6 Oct 2006 07:20:55 +0000 (UTC) (envelope-from mv@thebeastie.org) Received: from p4.roq.com (ns1.ecoms.com [207.44.130.137]) by mx1.FreeBSD.org (Postfix) with ESMTP id C2EAD43D72 for ; Fri, 6 Oct 2006 07:20:46 +0000 (GMT) (envelope-from mv@thebeastie.org) Received: from p4.roq.com (localhost.roq.com [127.0.0.1]) by p4.roq.com (Postfix) with ESMTP id 924624CD9D for ; Fri, 6 Oct 2006 07:21:03 +0000 (GMT) Received: from beaste5.jumbuck.com (ppp166-27.static.internode.on.net [150.101.166.27]) by p4.roq.com (Postfix) with ESMTP id D9AC04CD9A for ; Fri, 6 Oct 2006 07:21:02 +0000 (GMT) Received: from beaste5.jumbuck.com (beast5 [192.168.46.105]) by beaste5.jumbuck.com (Postfix) with ESMTP id ABFD220A1784; Fri, 6 Oct 2006 17:20:43 +1000 (EST) Received: from [192.168.46.102] (unknown [192.168.46.250]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) by beaste5.jumbuck.com (Postfix) with ESMTP id 70BBC20A16E8; Fri, 6 Oct 2006 17:20:43 +1000 (EST) Message-ID: <452603CA.8070800@thebeastie.org> Date: Fri, 06 Oct 2006 17:20:42 +1000 From: Michael Vince User-Agent: Mozilla/5.0 (X11; U; FreeBSD amd64; en-US; rv:1.7.13) Gecko/20060727 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Jerry Bell References: <3731.71.56.92.181.1160009571.squirrel@www.stelesys.com> <45255A36.5010108@quip.cz> <2840.71.56.92.181.1160090644.squirrel@www.stelesys.com> In-Reply-To: <2840.71.56.92.181.1160090644.squirrel@www.stelesys.com> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: ClamAV using ClamSMTP X-Virus-Scanned: ClamAV using ClamSMTP Cc: freebsd-performance@freebsd.org Subject: Re: Help with improving mysql performance on 6.2PRE X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Fri, 06 Oct 2006 07:20:55 -0000 Yeah the static compiling recommendations by MySQL documents are really more a linux thing more then anything else. The other other thing to check is to make sure you use larger buffer settings I recommend the large-my.cnf cp /usr/local/share/mysql/my-large.cnf /var/db/mysql/ Then restart MySQL. The disk IO doesn't play much into this as far as I know because the supersmack benchmark doesn't generate a large database to play with, also because its a med-high end Dell 1950 it should have the 256meg read and battery backed write cache controller card which would also hold a lot of that data without doing much real access. Yup there you have it, even though you didn't know about the best tweaks for MySQL you still managed to practically post some of the highest MySQL benchmarks posted here on performance out of the box with libthr, it also shows how much libthr gives extra performance when you have 4 CPU cores over most peoples posts with 1 or 2 CPU cores. Jerry Bell wrote: >Many thanks to all who responded. You are an incredibly smart group of >people. > >The recompiling without static yielded much better results: > >2950# super-smack -d mysql select-key-mysql.smack 10 10000 >Query Barrel Report for client smacker1 >connect: max=1ms min=0ms avg= 0ms from 10 clients >Query_type num_queries max_time min_time q_per_s >select_index 200000 0 0 51118.22 > >>From ~34k qps to ~51k qps is quite an improvement! > >I always thougt that compiling something static increased performance, but >then that's probably true for things that have to startup and shutdown >frequently. > >Thanks again. > >Jerry > > >>Jerry Bell wrote: >> >> >> >>>I have a Dell PE2950 with 2 dual core 3.73Ghz processors and 4G of ram. >>>I've looked through some of the lists here and have seen super-smack >>>results in the 42k qps range on a 2 dual core opteron system. I'm able >>>to >>>get up to about 34k with the wide at the back of my server whilest >>>rubbing >>>the side of it. >>> >>>Here's what I've done: >>>built both mysql 5.0 and 5.1 from ports with build_static and >>>optimazations on. >>>changed the clock to TSC >>> >>>added the following to my /etc/libmap.conf file: >>>[mysqld] >>>libc_r.so.5 libthr.so.2 >>>libc_r.so.6 libthr.so.2 >>>libthr.so.2 libthr.so.2 >>>libpthread.so.1 libthr.so.2 >>>libpthread.so.2 libthr.so.2 >>> >>> >>[...] >> >>As Nick Evans said, you can't use static version of MySQL daemon if you >>want to use /etc/libmap.conf >> >>I tested both (static vs. dynamic with libmap.conf), dynamic with libthr >>performs much better than static on Dual Xeon 3GHz SMP system with 2GB >>of RAM. >> >>My /etc/libmap.conf is just >> >>[/usr/local/libexec/mysqld] >>libpthread.so.2 libthr.so.2 >>libpthread.so libthr.so >> >>Miroslav Lachman >>_______________________________________________ >>freebsd-performance@freebsd.org mailing list >>http://lists.freebsd.org/mailman/listinfo/freebsd-performance >>To unsubscribe, send any mail to >>"freebsd-performance-unsubscribe@freebsd.org" >> >> >> > > >_______________________________________________ >freebsd-performance@freebsd.org mailing list >http://lists.freebsd.org/mailman/listinfo/freebsd-performance >To unsubscribe, send any mail to "freebsd-performance-unsubscribe@freebsd.org" > >