From owner-freebsd-questions Sat Jan 8 1:25:42 2000 Delivered-To: freebsd-questions@freebsd.org Received: from subcellar.mwci.net (subcellar.mwci.net [205.254.160.2]) by hub.freebsd.org (Postfix) with ESMTP id 7F1F3152CE for ; Sat, 8 Jan 2000 01:25:34 -0800 (PST) (envelope-from sean@bebits.com) Received: from opps (kb0lcj-10.dbq.mwci.net [209.207.4.10]) by subcellar.mwci.net (8.8.8/8.8.8) with SMTP id DAA00415 for ; Sat, 8 Jan 2000 03:25:29 -0600 (CST) Message-ID: <000b01bf59ba$4dd11380$0a04cfd1@mwci.net> From: "Sean Heber" To: Subject: FreeBSD and MySQL don't seem to like each other Date: Sat, 8 Jan 2000 03:25:28 -0600 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 5.00.2314.1300 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300 Sender: owner-freebsd-questions@FreeBSD.ORG Precedence: bulk X-Loop: FreeBSD.ORG The website I administrate is growing rather quickly and we decided awhile back to move from a hosting service to our own co-located machine. The OS of choice was FreeBSD. The machine is a dual Pentium II 400 with 256MB of RAM. I've looked all over the web, dug through freebsd-questions archives, mysql docs, etc. and I'm still at a loss. I'm somewhat new to this side of sysadmining, so please let me know if I'm doing something stupid or missed something totaly obvious.. :-) We're nearly ready to ship things off, but I'm running into a problem with mySQL. It seems to eat CPU. We're using Apache + PHP to access a rather complex database structure. The problem is, some of the queries are rather complicated and span many tables--and it seems to hurt mysql (as many as 8 in one case) (is this bad for a busy (24k impressions per day) website? It seems the only logical way given that databases are supposed to do this sort of thing... but I could be wrong.. :-). While the pages load extremely fast when hitting the page casually of my LAN, it really falls apart when tapping the reload button on the browser about 2 or 3 times per second. After about 10 seconds of that the box seems to start falling way behind. If I let up the load and allow the page to finish downloading it will often take several seconds. Watching top reveals that mysql is slowly but surely increasing it's CPU usage--even when I've stopped hitting reload! It can take as long as 10 minutes before the system calms down and falls back to a more sane 99% idle.. I'm in no way an SQL expert, but I do have the tables well indexed (or so they seem). As a benchmark, our current hosting provider runs Linux on a single PII 400. The exact same tables with the exact same queries seems to cause almost no extra CPU usage at all on the Linux box. Not so for my FreeBSD box. For example, I have a script to pre-calculate some of the tree structures on the site (software categories and such) every few minutes. Right now, when it runs on the Linux box we're currently hosted on, it takes <15 seconds. On my FreeBSD box it takes much longer and uses tons of CPU. (over 50% usually). This causes problems because if the job isn't finished by the time the next cron rolls around the processes start to back up. This makes no sense to me as I have 2x the cpu power and a machine all to myself. Why is mySQL misbehaving so badly? Will I be forced to change to Linux in order to get around this? I sure hope not as I've gotten to like FreeBSD--and I imagine there are others doing something like this. What's the secret? Hopefully this makes sense and I don't look completely stupid (it is getting rather late..er.. early here). But if there's anything anyone can suggest to help me out (on any topic.. :-), I'm all ears. Some specs: - FreeBSD3.4-RELEASE (compiled for SMP) - MySQL 3.22.29 installed from ports (using native threads as is default) - Apache 1.3.9 (custom compiled with PHP4b3 and Raven SSL) - My code uses persistent connections (p_connect in php) And, if anyone has any suggestions on what a reasonable mysql connection limit, table buffer sizes, etc would be on a site that sees about 24,000 impressions per day, I'd appreciate it. Thanks. l8r Sean BeBits Admin http://www.bebits.com/ "I have no respect for a man who can only spell a word one way." - Samuel Clemens (Mark Twain) To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-questions" in the body of the message