From owner-freebsd-questions@FreeBSD.ORG Wed Aug 2 13:59:05 2006 Return-Path: X-Original-To: freebsd-questions@freebsd.org Delivered-To: freebsd-questions@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id CFE3A16A503 for ; Wed, 2 Aug 2006 13:59:05 +0000 (UTC) (envelope-from remorse@partners.org) Received: from phsmgmx4.partners.org (phsmgmx4.partners.org [155.52.251.35]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8301C43DCA for ; Wed, 2 Aug 2006 13:58:17 +0000 (GMT) (envelope-from remorse@partners.org) Received: from phsmgmx4.partners.org (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with SMTP id 9B8DC1883C6; Wed, 2 Aug 2006 09:58:14 -0400 (EDT) Received: from hedwig.mgh.harvard.edu (hedwig.mgh.harvard.edu [132.183.156.18]) by phsmgmx4.partners.org (Postfix) with ESMTP id 8B5FA1883C5; Wed, 2 Aug 2006 09:58:13 -0400 (EDT) Received: from [132.183.156.105] (buxtehude.mgh.harvard.edu [132.183.156.105]) by hedwig.mgh.harvard.edu (8.13.3/8.13.3) with ESMTP id k72DxbDD069410; Wed, 2 Aug 2006 09:59:37 -0400 (EDT) (envelope-from remorse@partners.org) In-Reply-To: <80f4f2b20608020636o3dd712c9vf1aad9b6f3ba6e6f@mail.gmail.com> References: <31F1465C-5B87-4B79-8D58-0A538BF30562@partners.org> <80f4f2b20608020636o3dd712c9vf1aad9b6f3ba6e6f@mail.gmail.com> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: Content-Transfer-Encoding: 7bit From: Richard Morse Date: Wed, 2 Aug 2006 09:58:08 -0400 To: Jim Stapleton X-Mailer: Apple Mail (2.752.2) Cc: freebsd-questions@freebsd.org Subject: Re: Problems with MySQL since upgrade X-BeenThere: freebsd-questions@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: User questions List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 02 Aug 2006 13:59:06 -0000 Hi! All of these queries worked just fine, without causing any problems. As this is more of a mysql issue, do you have any suggestions where it would be best for me to query them? Thanks, Ricky On Aug 2, 2006, at 9:36 AM, Jim Stapleton wrote: > Not sure, seems more of a mysql question, but here's some guesses I > have for diagnostics. > > could you run these queries? > > SELECT COUNT(*) FROM users WHERE status < 3 > SELECT COUNT(*) FROM sessiions WHERE sid = > "d9fe25949f79f2c767a0d237b4fdf841" > SELECT COUNT(*) FROM FROM users u INNER JOIN sessions s ON u.uid = > s.uid WHERE s.sid = "d9fe25949f79f2c767a0d237b4fdf841" AND u.status < > 3 > > -Jim Stapleton > > > > On 8/2/06, Richard Morse wrote: >> >> On Aug 1, 2006, at 9:24 AM, Richard Morse wrote: >> >> > Hi! I recently updated MySQL to version 5.0.22 using the ports >> > system (portupgrade -r -R mysql-\*). Previous to this, MySQL was >> > behaving perfectly normally. Since the upgrade, I have found that >> > every so often -- sometimes two or three times a day, sometimes >> > every other day, but no more often than that -- one of the >> > databases MySQL is hosting starts misbehaving, MySQL starts >> > climbing in processor usage, and I have to restart MySQL to >> > recover. By "misbehaving", I mean that some subset of queries to >> > this database start not returning -- they take forever. By >> > climbing in processor usage, I mean that my load averages, which >> > normally sit around 0, start going up to 3, 5, even 7. >> > >> > - How can I determine what query it is that is causing this to >> > happen? I have turned on the log files by adding the following >> > line to /etc/rc.conf: >> >> Hi! Since this time, I have, I think, found what query is causing >> the problem: >> >> SELECT u.*, s.* FROM users u INNER JOIN sessions s ON u.uid = s.uid >> WHERE s.sid = "d9fe25949f79f2c767a0d237b4fdf841" AND u.status < 3 >> LIMIT 0, 1 >> >> How can I determine _why_ this is causing MySQL to hang/enter some >> kind of infinite loop? >> >> Thanks, >> Ricky >> _______________________________________________ >> freebsd-questions@freebsd.org mailing list >> http://lists.freebsd.org/mailman/listinfo/freebsd-questions >> To unsubscribe, send any mail to "freebsd-questions- >> unsubscribe@freebsd.org" >> > _______________________________________________ > freebsd-questions@freebsd.org mailing list > http://lists.freebsd.org/mailman/listinfo/freebsd-questions > To unsubscribe, send any mail to "freebsd-questions- > unsubscribe@freebsd.org"