From owner-freebsd-questions@FreeBSD.ORG Tue Nov 27 16:45:38 2007 Return-Path: Delivered-To: freebsd-questions@freebsd.org Received: from mx1.freebsd.org (mx1.freebsd.org [IPv6:2001:4f8:fff6::34]) by hub.freebsd.org (Postfix) with ESMTP id 9ACC816A417 for ; Tue, 27 Nov 2007 16:45:38 +0000 (UTC) (envelope-from mksmith@adhost.com) Received: from mail-defer01.adhost.com (mail-defer01.adhost.com [216.211.128.150]) by mx1.freebsd.org (Postfix) with ESMTP id 7BC6D13C478 for ; Tue, 27 Nov 2007 16:45:38 +0000 (UTC) (envelope-from mksmith@adhost.com) Received: from mail-in02.adhost.com (mail-in02.adhost.com [10.211.128.129]) by mail-defer01.adhost.com (Postfix) with ESMTP id 9D087ED5A7 for ; Tue, 27 Nov 2007 08:29:30 -0800 (PST) (envelope-from mksmith@adhost.com) Received: from ad-exh01.adhost.lan (unknown [216.211.143.69]) by mail-in02.adhost.com (Postfix) with ESMTP id BF4671EE879; Tue, 27 Nov 2007 08:29:29 -0800 (PST) (envelope-from mksmith@adhost.com) Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable X-MimeOLE: Produced By Microsoft Exchange V6.5 Date: Tue, 27 Nov 2007 08:29:28 -0800 Message-ID: <17838240D9A5544AAA5FF95F8D52031602E53B14@ad-exh01.adhost.lan> In-Reply-To: <8cb6106e0711270811r706fa81di9fafa79acfd227a1@mail.gmail.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Help for very bad perf for MySQL Thread-Index: AcgxEGrX+9h9tsVHRJKpEY2Fvwk1YAAAd4FQ References: <20071127130020.GA81551@pcjas.obspm.fr> <8cb6106e0711270811r706fa81di9fafa79acfd227a1@mail.gmail.com> From: "Michael K. Smith - Adhost" To: , Cc: Subject: RE: Help for very bad perf for MySQL X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 27 Nov 2007 16:45:38 -0000 > -----Original Message----- > From: owner-freebsd-questions@freebsd.org [mailto:owner-freebsd- > questions@freebsd.org] On Behalf Of Josh Carroll > Sent: Tuesday, November 27, 2007 8:12 AM > To: Ted Mittelstaedt > Cc: Albert.Shih@obspm.fr; freebsd-questions@freebsd.org > Subject: Re: Help for very bad perf for MySQL >=20 > > Is Hyperthreading enabled (by default it is not under > > FreeBSD) mysql is heavily dependent on threading, if it is not > > built and linked into the freebsd threads package you will get > > poor performance. Some folks have installed the linux compat libs > > and linked mysql into the linux threads package and reported good > > results. >=20 > Actually, on 6.2, it's better to use libthr instead of libpthread. > This can be done for MySQL only, but to test this without recompiling > MySQL, he can: >=20 > % echo "libpthread.so.2 libthr.so.2" >> /etc/libmap.conf >=20 > Then restart the mysql server and test again. I noticed a huge > increase in performance on 6.2 with libthr instead of libpthread. It > wasn't a 10x improvement, though, so there is definitely something > else going on with his setup. >=20 > Regards, > Josh Here are some things that helped us on a high-volume MySQL server. -- /etc/sysctl.conf -- (these can be added dynamically from the command line) kern.threads.max_groups_per_proc=3D40000 kern.threads.max_threads_per_proc=3D40000 kern.maxfiles=3D65535 kern.maxfilesperproc=3D65535 -- /boot/loader.conf -- (You'll have to reboot for these to take effect) kern.maxdsiz=3D"1073741824" # 1GB kern.dfldsiz=3D"1073741824" # 1GB kern.maxssiz=3D"134217728" # 128MB -- /etc/libmap.conf -- (as Josh said) [mysqld] libpthread.so.2 libthr.so.2 libpthread.so libthr.so Regards, Mike