From owner-freebsd-questions@FreeBSD.ORG Tue Sep 18 15:34:21 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 B625B16A419; Tue, 18 Sep 2007 15:34:21 +0000 (UTC) (envelope-from philip@ridecharge.com) Received: from exhub015-1.exch015.msoutlookonline.net (exhub015-1.exch015.msoutlookonline.net [207.5.72.93]) by mx1.freebsd.org (Postfix) with ESMTP id 97C7E13C45D; Tue, 18 Sep 2007 15:34:21 +0000 (UTC) (envelope-from philip@ridecharge.com) Received: from philip.hq.rws (74.93.213.161) by smtpx15.msoutlookonline.net (207.5.72.103) with Microsoft SMTP Server (TLS) id 8.0.730.1; Tue, 18 Sep 2007 08:34:20 -0700 Message-ID: <46EFEFF8.7070202@ridecharge.com> Date: Tue, 18 Sep 2007 11:34:16 -0400 From: "Philip M. Gollucci" Organization: Rideray Inc. User-Agent: Thunderbird 2.0.0.4 (X11/20070703) MIME-Version: 1.0 To: "freebsd-questions@freebsd.org" References: <26ddd1750709141351i3646e9bdg8d8b7e93461167f9@mail.gmail.com> <26ddd1750709151014x2112b022r9bcb999fbf1e7e49@mail.gmail.com> <46EC270A.3020100@FreeBSD.org> <8cb6106e0709151421h7bfdeb6fo7dc671820294e9c7@mail.gmail.com> <46EC4F59.7070104@FreeBSD.org> <8cb6106e0709151435p72cd328by63895421f3a63ea4@mail.gmail.com> <46EC50DA.6000104@FreeBSD.org> <8cb6106e0709151446x4c54a520u2d5cd543ba1541e@mail.gmail.com> <46EC55B8.2090107@FreeBSD.org> <46EEE3ED.6080304@ridecharge.com> <46EF21C2.7010104@FreeBSD.org> <46EFED9B.7010300@ridecharge.com> In-Reply-To: <46EFED9B.7010300@ridecharge.com> X-Enigmail-Version: 0.95.2 Content-Type: text/plain; charset="ISO-8859-1" Content-Transfer-Encoding: 7bit Cc: Kris Kennaway Subject: Re: MySQL config [WAS: ]uilding a new workstation - dual or quad-core CPU for FreeBSD 7? 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, 18 Sep 2007 15:34:21 -0000 Philip M. Gollucci wrote: >>> my.cnf >>> innodb_thread_concurrency = 8 >> You want '0' or performance will suck. There's a basic architectural >> flaw in how mysql handles non-zero concurrency values here (innodb >> accesses are serialized by a global mutex that protects a counter to >> check if it should try to allow more innodb concurrency. Duh.) >> >> Anyway, assuming your disks can keep up you should see a big performance >> boost when you switch to 7.0. This is a fairly big "if" though: I don't >> know if it's even feasible for a write-heavy database to saturate 8 CPUs >> instead of being bottlenecked by disk speeds and leaving the CPUs mostly from /usr/local/share/my-innodb-heavy-4G.cnf # This permits the application to give the threads system a hint for the # desired number of threads that should be run at the same time. This # value only makes sense on systems that support the # thread_concurrency() # function call (Sun Solaris, for example). # You should try [number of CPUs]*(2..4) for thread_concurrency thread_concurrency = 8 # Number of IO threads to use for async IO operations. This value is # hardcoded to 4 on Unix, but on Windows disk I/O may benefit from a # larger number. innodb_file_io_threads = 4 # Number of threads allowed inside the InnoDB kernel. The optimal value # depends highly on the application, hardware as well as the OS # scheduler properties. A too high value may lead to thread thrashing. innodb_thread_concurrency = 16 Apparently its only set in this file. We should probably submit a bug to MySQL rather then add a patch to ports or do both and remove the ports when its released. -- ------------------------------------------------------------------------ Philip M. Gollucci (philip@ridecharge.com) c:323.219.4708 o:703.749.9295x206 Senior System Admin - Riderway, Inc. http://riderway.com / http://ridecharge.com 1024D/EC88A0BF 0DE5 C55C 6BF3 B235 2DAB B89E 1324 9B4F EC88 A0BF Work like you don't need the money, love like you'll never get hurt, and dance like nobody's watching.