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.