From owner-freebsd-questions@FreeBSD.ORG Thu Aug 3 19:43:13 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 CD5D216A4DA for ; Thu, 3 Aug 2006 19:43:13 +0000 (UTC) (envelope-from remorse@partners.org) Received: from phsmgmx1.partners.org (phsmgmx1.partners.org [155.52.251.32]) by mx1.FreeBSD.org (Postfix) with ESMTP id D11FA43D73 for ; Thu, 3 Aug 2006 19:43:12 +0000 (GMT) (envelope-from remorse@partners.org) Received: from phsmgmx1.partners.org (localhost.localdomain [127.0.0.1]) by localhost (Postfix) with SMTP id 80F992742A6; Thu, 3 Aug 2006 15:43:12 -0400 (EDT) Received: from hedwig.mgh.harvard.edu (hedwig.mgh.harvard.edu [132.183.156.18]) by phsmgmx1.partners.org (Postfix) with ESMTP id 682C727429D; Thu, 3 Aug 2006 15:43:11 -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 k73JiZGB070470; Thu, 3 Aug 2006 15:44:35 -0400 (EDT) (envelope-from remorse@partners.org) In-Reply-To: <1E8F77CE-AC74-4C24-89DC-89201CD4E113@partners.org> References: <31F1465C-5B87-4B79-8D58-0A538BF30562@partners.org> <80f4f2b20608020636o3dd712c9vf1aad9b6f3ba6e6f@mail.gmail.com> <80f4f2b20608020700l53410a09u6efb90aadb6294cb@mail.gmail.com> <1E8F77CE-AC74-4C24-89DC-89201CD4E113@partners.org> Mime-Version: 1.0 (Apple Message framework v752.2) Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed Message-Id: <607A6828-089C-4D21-B5F5-E88249F831D0@partners.org> Content-Transfer-Encoding: 7bit From: Richard Morse Date: Thu, 3 Aug 2006 15:43:07 -0400 To: Richard Morse 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: Thu, 03 Aug 2006 19:43:13 -0000 Hi! I finally figured out what was going on. Thanks for the people who gave suggestions. The query I thought was the problem really wasn't -- at some point between 5.0.13 and 5.0.22 a change was introduced which affected inner joins. I had a view which was aggresively created using inner joins (in order to take an EAV-like table and view it as though it were a regular table), and if I tried to do a three table inner join with a view involved, it sat there and entered some kind of loop. This either caused the tables to be locked, and later queries involving these tables were waiting for a freed lock, or eventually the number of open connections / threads climbed too high and all later connections were waiting. I was able to (for now) solve the problem by recreating the view as a realized table which gets rebuilt every hour. Thanks, Ricky