From owner-freebsd-database@FreeBSD.ORG Wed Nov 26 06:22:14 2003 Return-Path: Delivered-To: freebsd-database@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id A86D916A4CE for ; Wed, 26 Nov 2003 06:22:14 -0800 (PST) Received: from search.sparks.net (search.sparks.net [207.5.180.136]) by mx1.FreeBSD.org (Postfix) with ESMTP id B68BA43FA3 for ; Wed, 26 Nov 2003 06:22:13 -0800 (PST) (envelope-from dmiller@miningworks.com) Received: by search.sparks.net (Postfix, from userid 100) id 25E55A902; Wed, 26 Nov 2003 09:22:13 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by search.sparks.net (Postfix) with ESMTP id 23481A901; Wed, 26 Nov 2003 09:22:13 -0500 (EST) Date: Wed, 26 Nov 2003 09:22:13 -0500 (EST) From: David Miller To: Lukas Ertl In-Reply-To: <20031126120308.R414@pcle2.cc.univie.ac.at> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: dmiller@miningworks.com cc: freebsd-database@freebsd.org Subject: Re: MySQL tuning X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 Nov 2003 14:22:14 -0000 On Wed, 26 Nov 2003, Lukas Ertl wrote: > Hi, > > can someone recommend some values for tuning a MySQL installation running > on FreeBSD 4.9 on a Dual 2.4Ghz Xeon with 4GB RAM? 4 GB of ram but only 384 set aside for the buffers? Sort of an aside, but I did a lot of speed testing with mysql and freebsd in the 4.7 timeframe. Enabling SMP actuall slowed mysql related tasks down by a dramatic factor - as much as 3-10X IIRC. I have a nice dual procesor SMP box with 2 GB of ram, and I run it in uniprocessor mode. FreeBSD also has issues with tuning for more than 2 GB of ram. I don't recall that having more in would hurt anything, but one of the kernel gurus could probably shed some light on it. If it were my system, I'd be inclined to pull 2 GB out and run in single CPU mode and see if the problems continue. > My my.cnf currently looks like this: > > skip-locking > key_buffer = 384M > max_allowed_packet = 1M > table_cache = 1024 > sort_buffer_size = 8M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 32M > set-variable = max_connections=500 > thread_concurrency = 8 > > Now I randomly get this error when connecting: > > ERROR 1135: Can't create a new thread (errno 12). if you are not out of > available memory, you can consult the manual for a possible OS-dependent > bug. > > And the MySQL error log says: > > Out of memory; Check if mysqld or some other process uses all > available memory. If not you may have to use 'ulimit' to allow mysqld to > use more memory or you can add more swap space > > This is a plain MySQL 4.0.16 installation from ports, statically linked. > > According to top, mysqld uses 515M RAM (148M RSS), and there's plenty of > RAM free (or rather, 'inactive'). > > Any ideas, hints, tips? How many connections are you trying to make? How many do you have when you start getting the messages? --- David