From owner-freebsd-database@FreeBSD.ORG Wed Nov 26 03:13:43 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 2EB6C16A4CE for ; Wed, 26 Nov 2003 03:13:43 -0800 (PST) Received: from mailbox.univie.ac.at (mail.univie.ac.at [131.130.1.27]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2B65043FEC for ; Wed, 26 Nov 2003 03:13:40 -0800 (PST) (envelope-from l.ertl@univie.ac.at) Received: from pcle2.cc.univie.ac.at (pcle2.cc.univie.ac.at [131.130.2.177]) by mailbox.univie.ac.at (8.12.10/8.12.10) with ESMTP id hAQBDUu6235888 for ; Wed, 26 Nov 2003 12:13:35 +0100 Date: Wed, 26 Nov 2003 12:13:30 +0100 (CET) From: Lukas Ertl To: freebsd-database@freebsd.org Message-ID: <20031126120308.R414@pcle2.cc.univie.ac.at> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-DCC-ZID-Univie-Metrics: mailbox 4246; Body=0 Fuz1=0 Fuz2=0 Subject: 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 11:13:43 -0000 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? 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? best regards, le -- Lukas Ertl eMail: l.ertl@univie.ac.at UNIX Systemadministrator Tel.: (+43 1) 4277-14073 Vienna University Computer Center Fax.: (+43 1) 4277-9140 University of Vienna http://mailbox.univie.ac.at/~le/ 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 From owner-freebsd-database@FreeBSD.ORG Wed Nov 26 11:32:39 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 4FA7D16A4CE for ; Wed, 26 Nov 2003 11:32:39 -0800 (PST) Received: from eldritch.valis.net.pl (valis.net.pl [80.55.59.218]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2BF4743F85 for ; Wed, 26 Nov 2003 11:32:32 -0800 (PST) (envelope-from ex@valis.net.pl) Received: from localhost (localhost [127.0.0.1]) by eldritch.valis.net.pl (Postfix) with ESMTP id 50EB3FCDD for ; Wed, 26 Nov 2003 20:36:03 +0100 (CET) Received: from eldritch.valis.net.pl ([127.0.0.1]) by localhost (eldritch [127.0.0.1]) (amavisd-new, port 10024) with LMTP id 07929-06 for ; Wed, 26 Nov 2003 20:35:55 +0100 (CET) Received: by eldritch.valis.net.pl (Postfix, from userid 0) id E36C7FCDA; Wed, 26 Nov 2003 20:35:54 +0100 (CET) Received: from ex-home (localhost [127.0.0.1]) by eldritch.valis.net.pl (Postfix) with ESMTP id C22383B2AF for ; Wed, 26 Nov 2003 20:35:54 +0100 (CET) Date: Wed, 26 Nov 2003 20:32:33 +0100 From: Tomasz Szymczak To: freebsd-database@freebsd.org Message-Id: <20031126203233.42f4ca6c.ex@valis.net.pl> In-Reply-To: <20031126120308.R414@pcle2.cc.univie.ac.at> References: <20031126120308.R414@pcle2.cc.univie.ac.at> X-Mailer: Sylpheed version 0.9.6claws (GTK+ 1.2.10; i386-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-2 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new-20030616-p5 (Debian) at valis.net.pl 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 19:32:39 -0000 On Wed, 26 Nov 2003 12:13:30 +0100 (CET) Lukas Ertl wrote: > According to top, mysqld uses 515M RAM (148M RSS), and there's plenty of > RAM free (or rather, 'inactive'). >=20 > Any ideas, hints, tips? Try add following to kernel configuration: options MAXDSIZ=3D"(2048*1024*1024)" options DFLDSIZ=3D"(2048*1024*1024)" --=20 Tomasz Szymczak _.-. jgs GCS/M d- s-:- a-- C+++$ UL++$>++++$ P++ L++>++++ E W++ N+ w O+ '( ^{_} = ( PS+ PE !Y PGP+ t- X- R tv-- b++>+++ DI D>+++ G e>++++ h! r- y? `~\`----= -'\ Umys=B3 sprawia, =BFe wszystko si=EA zmienia, =BFeby upozorowa=E6 up=B3yw c= zasu )_)---)_)