From owner-freebsd-database@FreeBSD.ORG Sun Aug 1 03:34:26 2004 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 1CC6416A4CE; Sun, 1 Aug 2004 03:34:26 +0000 (GMT) Received: from watcher.puryear-it.com (ip-66-186-248-99.static.eatel.net [66.186.248.99]) by mx1.FreeBSD.org (Postfix) with ESMTP id E533243D5F; Sun, 1 Aug 2004 03:34:25 +0000 (GMT) (envelope-from dap99@i-55.com) Received: from localhost (unknown [127.0.0.1]) by watcher.puryear-it.com (Postfix) with ESMTP id 2128834D6F; Sat, 31 Jul 2004 22:32:07 -0500 (CDT) Received: from watcher.puryear-it.com ([127.0.0.1]) by localhost (watcher.puryear-it.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 07072-04; Sat, 31 Jul 2004 22:32:04 -0500 (CDT) Received: from THEBOX (localhost [127.0.0.1]) by watcher.puryear-it.com (Postfix) with SMTP id BB3A234D7D; Sat, 31 Jul 2004 22:07:33 -0500 (CDT) Message-ID: <00dd01c47775$0087df40$0200a8c0@THEBOX> From: "adp" To: Date: Sat, 31 Jul 2004 22:08:32 -0500 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1437 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 X-Virus-Scanned: by amavisd-new cc: freebsd-database@freebsd.org cc: freebsd-hackers@freebsd.org Subject: FreeBSD and MySQL - mysqld eats CPU alive 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: Sun, 01 Aug 2004 03:34:26 -0000 I recently posted the following message to MySQL discussion list. The response there, and the one I keep finding on Google, is that this is a long-standing issue betweeen FreeBSD and MySQL. For me this has been happening since FreeBSD 4.4. I have one site where we are going to have to move to Linux. I would much prefer keeping us on FreeBSD, but we just can't afford the downtime anymore. Another site is looking at moving to PostgreSQL on FreeBSD. Any help on this? Googling shows a long history of people having these problems but no solutions. Please don't give me a URL to a Google showing others having this problem--I've seen that and more. I want to know if there is a solution. Any help is appreciated! ... I have several MySQL and FreeBSD installs across a few different sites, and I consistently have problems with mysqld. It will begin to eat up all of the CPU and eventually become unresponsive (or the machine will just burn). I can't seem to manually reproduce this, but given enough time a FreeBSD box with mysqld will go down. Our servers are generally heavily loaded. I would say that I'm doing something wrong (although what I could be doing wrong I'm not sure), but I recently began working with another company that has the EXACT SAME PROBLEM. They are even thinking of moving to PostgreSQL, but we are trying to fix mysqld instead for now. This behavior has been seen on: FreeBSD 4.4, 4.7, 4.9, 4.10 MySQL 3.x and 4.x Typical load: 50 qps With and without replication enabled. Some sites are SELECT heavy, some are INSERT heavy. For one site I think we will be moving from FreeBSD to Linux for the MySQL servers since MySQL seems to run like a champ on Linux. We will continue to use FreeBSD for everything else. Anyone experienced this problem? Is it mysqld or FreeBSD? I can't pinpoint the exact issue. From owner-freebsd-database@FreeBSD.ORG Sun Aug 1 14:28:26 2004 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 B6EF916A4CE; Sun, 1 Aug 2004 14:28:26 +0000 (GMT) Received: from smtp.internal.alivewww.com (gp1.alivewww.com [217.158.94.60]) by mx1.FreeBSD.org (Postfix) with ESMTP id 2B70D43D2F; Sun, 1 Aug 2004 14:28:26 +0000 (GMT) (envelope-from enquiries@alivewww.com) Received: from host-83-146-2-180.bulldogdsl.com ([83.146.2.180]) by smtp.internal.alivewww.com with asmtp (Exim 4.34) id 1BrHK3-0009nI-HC; Sun, 01 Aug 2004 15:28:24 +0100 From: Alasdair Lumsden To: adp In-Reply-To: <00dd01c47775$0087df40$0200a8c0@THEBOX> References: <00dd01c47775$0087df40$0200a8c0@THEBOX> Content-Type: text/plain Message-Id: <1091370486.19294.42.camel@host-83-146-2-180.bulldogdsl.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6-1mdk Date: Sun, 01 Aug 2004 15:28:17 +0100 Content-Transfer-Encoding: 7bit cc: freebsd-database@freebsd.org cc: freebsd-hackers@freebsd.org cc: questions@freebsd.org Subject: Re: FreeBSD and MySQL - mysqld eats CPU alive 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: Sun, 01 Aug 2004 14:28:27 -0000 On Sun, 2004-08-01 at 04:08, adp wrote: > FreeBSD 4.4, 4.7, 4.9, 4.10 > MySQL 3.x and 4.x > Typical load: 50 qps > With and without replication enabled. > Some sites are SELECT heavy, some are INSERT heavy. > > For one site I think we will be moving from FreeBSD to Linux for the MySQL > servers since MySQL seems to run like a champ on Linux. We will continue to > use FreeBSD for everything else. > > Anyone experienced this problem? Is it mysqld or FreeBSD? I can't pinpoint > the exact issue. We've got MySQL running on several FreeBSD servers here, some doing 100 qps+, and have had no problems what so ever. We've been using MySQL 3 and 4 on FreeBSD since FreeBSD 4.5 upwards. Here is an example status output from one of our servers: # /usr/local/mysql/bin/mysqladmin -u root -p status Enter password: Uptime: 1271242 Threads: 8 Questions: 76203621 Slow queries: 370 Opens: 2461857 Flush tables: 2 Open tables: 511 Queries per second avg: 59.944 You haven't mentioned the source of your MySQL installs. Are you using MySQL from ports, packages, or a binary or source distribution from the MySQL website? We compile MySQL from source using the following: setenv CFLAGS "-O3 -mpentiumpro" setenv CXX gcc setenv CXXFLAGS "-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti -DEAPI" ./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/var --with-unix-socket-path=/var/lib/mysql/mysql.sock --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --enable-assembler You also haven't mentioned specifically what happens when there is a problem.. You say the machine becomes unresponsive.. what happens if the queries die down, does the box become responsive again? The only thing I can think of causing this is if queries start to come in faster than the box can process - they will build up and up until the machine, as you say, becomes unresponsive. This would indicate some form of bottleneck somewhere. Monitoring the output of "systat -vmstat" might give some clues as to whats going on. I would certainly investigate the cause of this before abandoning FreeBSD. Any threads on google relating to FreeBSD and MySQL not working together are vastly inaccurate or out of date. There are some performance issues with MySQL on FreeBSD 4 with regards to threading. Try using MySQL linked against LinuxThreads, which should give a performance boost. You can specify -DWITH_LINUXTHREADS when installing from ports. We have a test box running FreeBSD 5.2.1 with MySQL 4, linked against the new kse threading library, which should in theory provide performance comparable/superior to MySQL on a linux box. This box has been completely stable - no MySQL or FreeBSD lockups or crashes. Given that 5.3 and -STABLE are due out soon, it might be worth waiting for those, rather than switching to Linux. However we, and many many other people, run high load MySQL installations on FreeBSD without problem, so I would say your problems are specific to your setup, and not something that is a widespread issue. From owner-freebsd-database@FreeBSD.ORG Sun Aug 1 20:32:17 2004 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 A1F2316A4CE; Sun, 1 Aug 2004 20:32:17 +0000 (GMT) Received: from structbio.vanderbilt.edu (reef.structbio.Vanderbilt.Edu [160.129.138.217]) by mx1.FreeBSD.org (Postfix) with ESMTP id 02E0243D60; Sun, 1 Aug 2004 20:32:15 +0000 (GMT) (envelope-from bandix@home.funnyvalentine.net) Received: from home.funnyvalentine.net (adsl-065-007-237-012.sip.bna.bellsouth.net [65.7.237.12]) i71KWARg005909 (version=TLSv1/SSLv3 cipher=EDH-RSA-DES-CBC3-SHA bits=168 verify=NO); Sun, 1 Aug 2004 15:32:11 -0500 Received: (from bandix@localhost) by home.funnyvalentine.net (8.12.10/8.12.10/Submit) id i71KW5SZ061919; Sun, 1 Aug 2004 15:32:05 -0500 (CDT) (envelope-from bandix) Date: Sun, 1 Aug 2004 15:32:05 -0500 From: "Brandon D. Valentine" To: adp Message-ID: <20040801203205.GA61836@brandon.dvalentine.com> References: <00dd01c47775$0087df40$0200a8c0@THEBOX> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <00dd01c47775$0087df40$0200a8c0@THEBOX> User-Agent: Mutt/1.5.6i cc: freebsd-database@freebsd.org cc: freebsd-hackers@freebsd.org cc: questions@freebsd.org Subject: Re: FreeBSD and MySQL - mysqld eats CPU alive 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: Sun, 01 Aug 2004 20:32:17 -0000 On Sat, Jul 31, 2004 at 10:08:32PM -0500, adp wrote: > Any help on this? Googling shows a long history of people having these > problems but no solutions. Please don't give me a URL to a Google showing > others having this problem--I've seen that and more. I want to know if there > is a solution. Any help is appreciated! When this happens are you able to attach truss or gdb to the process? This is impossible to diagnosis without some kind of debugging information. Brandon D. Valentine -- brandon@dvalentine.com http://www.geekpunk.net Pseudo-Random Googlism: summer is slowly losing its grip From owner-freebsd-database@FreeBSD.ORG Sun Aug 1 21:55:37 2004 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 EAE6F16A4CE; Sun, 1 Aug 2004 21:55:37 +0000 (GMT) Received: from ns1.jnielsen.net (ns1.jnielsen.net [69.55.238.237]) by mx1.FreeBSD.org (Postfix) with ESMTP id CC48243D4C; Sun, 1 Aug 2004 21:55:37 +0000 (GMT) (envelope-from lists@jnielsen.net) Received: from neo.jnielsen.net (jn@c-24-2-72-123.client.comcast.net [24.2.72.123]) by ns1.jnielsen.net (8.12.9p2/8.12.9) with ESMTP id i71LtbPB017103; Sun, 1 Aug 2004 14:55:37 -0700 (PDT) (envelope-from lists@jnielsen.net) Received: from stealth-10-3-2-2.local (stealth@stealth-10-3-2-2.local [10.3.2.2]) by neo.jnielsen.net (8.12.10/8.12.10) with ESMTP id i71Ltalm048661; Sun, 1 Aug 2004 15:55:36 -0600 (MDT) (envelope-from lists@jnielsen.net) From: John Nielsen To: freebsd-hackers@freebsd.org Date: Sun, 1 Aug 2004 15:54:51 -0600 User-Agent: KMail/1.6.2 References: <00dd01c47775$0087df40$0200a8c0@THEBOX> In-Reply-To: <00dd01c47775$0087df40$0200a8c0@THEBOX> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Message-Id: <200408011554.51630.lists@jnielsen.net> X-Virus-Scanned: clamd / ClamAV version 0.75, clamav-milter version 0.75 on ns1.jnielsen.net X-Virus-Status: Clean cc: adp cc: freebsd-database@freebsd.org cc: questions@freebsd.org Subject: Re: FreeBSD and MySQL - mysqld eats CPU alive 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: Sun, 01 Aug 2004 21:55:38 -0000 On Saturday 31 July 2004 21:08, adp wrote: > I recently posted the following message to MySQL discussion list. The > response there, and the one I keep finding on Google, is that this is a > long-standing issue betweeen FreeBSD and MySQL. For me this has been > happening since FreeBSD 4.4. I don't have any additional info about the problem, but as you say it does seem to be a long-standing issue. The hosting company I use (johncompanies.com -- highly recommended) has a "nanny" script that you may find useful: http://www.johncompanies.com/collocation/knowledge/freebsd_mysqld_nanny.txt HTH, JN From owner-freebsd-database@FreeBSD.ORG Sun Aug 1 22:22:00 2004 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 0240516A4CF; Sun, 1 Aug 2004 22:22:00 +0000 (GMT) Received: from museful.corp.yahoo.com (museful.corp.yahoo.com [216.145.52.107]) by mx1.FreeBSD.org (Postfix) with ESMTP id DF40843D49; Sun, 1 Aug 2004 22:21:59 +0000 (GMT) (envelope-from jzawodn@yahoo-inc.com) Received: (from jzawodn@localhost) by museful.corp.yahoo.com (8.11.6/8.11.1) id i71MLrG87859; Sun, 1 Aug 2004 15:21:53 -0700 (PDT) (envelope-from jzawodn) Date: Sun, 1 Aug 2004 15:21:53 -0700 From: Jeremy Zawodny To: adp Message-ID: <20040801152153.B53045@yahoo-inc.com> References: <00dd01c47775$0087df40$0200a8c0@THEBOX> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.2.5.1i In-Reply-To: <00dd01c47775$0087df40$0200a8c0@THEBOX>; from dap99@i-55.com on Sat, Jul 31, 2004 at 10:08:32PM -0500 cc: freebsd-database@freebsd.org cc: freebsd-hackers@freebsd.org cc: questions@freebsd.org Subject: Re: FreeBSD and MySQL - mysqld eats CPU alive 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: Sun, 01 Aug 2004 22:22:00 -0000 On Sat, Jul 31, 2004 at 10:08:32PM -0500, adp wrote: > I recently posted the following message to MySQL discussion list. The > response there, and the one I keep finding on Google, is that this is a > long-standing issue betweeen FreeBSD and MySQL. For me this has been > happening since FreeBSD 4.4. > > I have one site where we are going to have to move to Linux. I would much > prefer keeping us on FreeBSD, but we just can't afford the downtime anymore. > Another site is looking at moving to PostgreSQL on FreeBSD. That's too bad. MySQL can run quite well on FreeBSD. > I have several MySQL and FreeBSD installs across a few different sites, and > I consistently have problems with mysqld. It will begin to eat up all of the > CPU and eventually become unresponsive (or the machine will just burn). I > can't seem to manually reproduce this, but given enough time a FreeBSD box > with mysqld will go down. Yes, it's FreeBSD's broken DNS libraries. Add skip-name-resolve to your my.cnf and the problem will probably go away. Jeremy -- Jeremy D. Zawodny, Technical Yahoo - Yahoo MySQL Geek Desk: (408) 349-7878 Cell: (408) 685-5936 From owner-freebsd-database@FreeBSD.ORG Mon Aug 2 14:29:30 2004 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 E640616A4CE; Mon, 2 Aug 2004 14:29:30 +0000 (GMT) Received: from search.sparks.net (search.sparks.net [207.5.180.136]) by mx1.FreeBSD.org (Postfix) with ESMTP id A90FD43D6A; Mon, 2 Aug 2004 14:29:30 +0000 (GMT) (envelope-from dmiller@miningworks.com) Received: by search.sparks.net (Postfix, from userid 100) id 219C1A902; Mon, 2 Aug 2004 10:29:30 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by search.sparks.net (Postfix) with ESMTP id 1AB37A901; Mon, 2 Aug 2004 10:29:30 -0400 (EDT) Date: Mon, 2 Aug 2004 10:29:30 -0400 (EDT) From: somethrowawayaddy@d.sparks.net X-Sender: dmiller@search.sparks.net To: adp In-Reply-To: <00dd01c47775$0087df40$0200a8c0@THEBOX> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII Sender: dmiller@miningworks.com cc: freebsd-database@freebsd.org cc: freebsd-hackers@freebsd.org cc: questions@freebsd.org Subject: Re: FreeBSD and MySQL - mysqld eats CPU alive 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: Mon, 02 Aug 2004 14:29:31 -0000 On Sat, 31 Jul 2004, adp wrote: > I recently posted the following message to MySQL discussion list. The > response there, and the one I keep finding on Google, is that this is a > long-standing issue betweeen FreeBSD and MySQL. For me this has been > happening since FreeBSD 4.4. I had that problem a couple of years ago and resolved it on a box that's still in use. Very heavily loaded machine, 15 GB database, over 300 million rows in one table. I couldn't find notes on it, however, and don't recall exactly what it was. One thing it might have been - did you change the HZ value in your kernel conf file, perhaps for device polling? I ran into problems on this machine when setting HZ above 500 but don't recall for sure it was mysql problems. I'm happy to share the mysql build and conf files, and the kernel conf file if you think it would help. I think it was a kernel tweak that solved the problem. HTH, --- David From owner-freebsd-database@FreeBSD.ORG Thu Aug 5 02:39:58 2004 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 C8B2316A4CE for ; Thu, 5 Aug 2004 02:39:58 +0000 (GMT) Received: from agok.alrosa-mir.ru (agok.alrosa-mir.ru [194.84.226.139]) by mx1.FreeBSD.org (Postfix) with ESMTP id DDC9B43D4C for ; Thu, 5 Aug 2004 02:39:53 +0000 (GMT) (envelope-from r.stadnik@agok.alrosa-mir.ru) Received: from stadnik.fabrika-14.agok.alrosa-mir.ru (f14.agok.alrosa-mir.ru [192.168.120.4] (may be forged)) by agok.alrosa-mir.ru (8.13.1/8.12.11) with ESMTP id i752nbxi018341 for ; Thu, 5 Aug 2004 12:49:43 +1000 Date: Thu, 5 Aug 2004 12:41:13 +1000 From: Roman Stadnik X-Mailer: The Bat! (v2.02.3 CE) CD5BF9353B3B7091 Organization: Home X-Priority: 3 (Normal) Message-ID: <1285844986.20040805124113@agok.alrosa-mir.ru> To: freebsd-database@freebsd.org MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Spam-Status: No, hits=-101.4 required=5.0 tests=BAYES_20,USER_IN_WHITELIST autolearn=no version=2.63 X-Spam-Report: * -1.4 BAYES_20 BODY: Bayesian spam probability is 20 to 30% * [score: 0.2845] * -100 USER_IN_WHITELIST From: address is in the user's white-list X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on agok.alrosa-mir.ru X-Virus-Scanned: clamd / ClamAV version 0.71, clamav-milter version 0.71 X-Virus-Status: Clean Subject: X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list Reply-To: Roman Stadik List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Thu, 05 Aug 2004 02:39:58 -0000 Hello freebsd-database, -- Best regards, Roman mailto:r.stadnik@agok.alrosa-mir.ru