From owner-freebsd-questions Sun Jan 21 18: 9:34 2001 Delivered-To: freebsd-questions@freebsd.org Received: from dan.emsphone.com (dan.emsphone.com [199.67.51.101]) by hub.freebsd.org (Postfix) with ESMTP id BA8A237B400 for ; Sun, 21 Jan 2001 18:09:16 -0800 (PST) Received: (from dan@localhost) by dan.emsphone.com (8.11.1/8.11.1) id f0M297K07100; Sun, 21 Jan 2001 20:09:07 -0600 (CST) (envelope-from dan) Date: Sun, 21 Jan 2001 20:09:07 -0600 From: Dan Nelson To: Noor Dawod Cc: freebsd-questions@FreeBSD.ORG Subject: Re: Unexplained high-loads on FreeBSD Message-ID: <20010121200907.A2542@dan.emsphone.com> References: Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline User-Agent: Mutt/1.3.13i In-Reply-To: ; from "Noor Dawod" on Sun Jan 21 17:07:26 GMT 2001 X-OS: FreeBSD 5.0-CURRENT Sender: owner-freebsd-questions@FreeBSD.ORG Precedence: bulk X-Loop: FreeBSD.ORG In the last episode (Jan 21), Noor Dawod said: > The problem persists, though, and here it is: every once in a while, > sometimes once a week, the mysqld process goes into OVERHEAT state > with CPU usage getting as high as 97%, thus making it almost > impossible for clients to connect to it. I checked mysqld's process > usage once while this happened, and I found around 60 processes > (connections). Not a big deal as I saw much more processes connect to > the same daemon before, and still it continued to work. Your high CPU usage is probably due to a complex query. Add "log-slow-queries" to the [mysqld] section of your my.cnf, restart mysql, and check slow.log after one of your high-load periods. Analyze any queries that show up and add indexes as needed. Also check the output of "show status" before and after, keeping an eye on Created_tmp_tables, Handler_read_rnd, Handler_read_rnd_next, Key_reads, and Sort_merge_passes. See the manual for details. > I'm attaching the my.cnf file for you to examine. Remember, this is > the stock my.cnf file that came with MySQL for large Web sites (those > having more than 512MB of memory), with a little modification. Try raising sort_buffer and tmp_table_size to 16M at least. This will help if you truly have a complex query that has to run through a lot of records. > By the way, someone on the MySQL mailing list suggested that BSD's > threading support "sucks", and only after he moved to Debian that > these simptoms disappeared. FreeBSD's threads are currently user-level and all threads block when one does disk I/O. This is sort of a worst-case scenario for a disk-intensive app like a database, but with enough memory you shouldn't really be hitting the disk that often anyway. -- Dan Nelson dnelson@emsphone.com To Unsubscribe: send mail to majordomo@FreeBSD.org with "unsubscribe freebsd-questions" in the body of the message