From owner-freebsd-current@FreeBSD.ORG Sun Apr 11 20:14:59 2004 Return-Path: Delivered-To: freebsd-current@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id B892916A4CE; Sun, 11 Apr 2004 20:14:59 -0700 (PDT) Received: from publicd.ub.mng.net (publicd.ub.mng.net [202.179.0.88]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0A9DF43D2D; Sun, 11 Apr 2004 20:14:45 -0700 (PDT) (envelope-from ganbold@micom.mng.net) Received: from [202.179.0.164] (helo=ganbold.micom.mng.net) by publicd.ub.mng.net with asmtp (Exim 4.30; FreeBSD) id 1BCslw-00051E-Lf; Mon, 12 Apr 2004 12:10:12 +0800 Message-Id: <6.0.3.0.2.20040412121357.029b5a20@202.179.0.80> X-Sender: ganbold@micom.mng.net@202.179.0.80 X-Mailer: QUALCOMM Windows Eudora Version 6.0.3.0 Date: Mon, 12 Apr 2004 12:19:26 +0900 To: chancedj@yahoo.com From: Ganbold Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii"; format=flowed cc: freebsd-current@freebsd.org cc: freebsd-ports@freebsd.org cc: ale@FreeBSD.org Subject: Re: FBSD 5.2.1-p4 and mysqld problems X-BeenThere: freebsd-current@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Discussions about the use of FreeBSD-current List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 12 Apr 2004 03:15:00 -0000 Hi Daryl, I found the problematic queries. Some of the queries like : select sum(total_amount) pbc from customers.payment where contract_id>=0 and contract_id<=4999 and from_unixtime(time_stamp,'%Y-%m')='2004-03'; are not using any index. I made composite index on contract_id,time_stamp,total_amount and let's see what will happen. Ganbold At 01:36 PM 05.04.2004, you wrote: >yes, we resolved the issue it looks like. I wanted >to hold off on announcing this until i was sure, but >it's been running for about 24 hours now without a >lockup. > >options MAXDSIZ="(1024*1024*1024)" # change max from >512M to 1G >add that to your kernel config. > >basically whats happening ( and i'm probably wrong >with the technicals on this, but bear with me :D ) is >that mysqld would aquire a lock (on a table more then >likely) and then try to allocate above the FBSD 5.2 >max default memory size (512M). when it did this, it >crashed and left the lock on the table. from there it >just froze. > >making that change seems to have fixed it. it was >never evident before because we had a slower webserver >that wasn't loading the sql server all that much. now >it is, and we started developing problems. :). > >as you can tell with this link: >http://sql.tribalwar.com/before-ps.txt the mysqld proc >wen't above 512M. > >check your sql.err log, if you're getting a malloc >error, then this is more then likely your problem. > >Thanks to everyone for the help in tracking this down. > >Let me know if this fixes the problem for you. Also, >are you running -CURRENT in production? or testing? I >had given some thought to upgrading the box to >-CURRENT after the commit for the network stack >settled. But I first need to get serial console >enabled on it :). > >Daryl > >--- Ganbold wrote: > > Hi Daryl, > > > > I have exactly same problem as you. I have FreeBSD > > 5.2-CURRENT (did cvsup > > on March 23) with mysql-4.0.18 from ports > > collection. > > It is compiled with linuxthreads. Mysql is working > > fine, except it > > sometimes freezes, sometimes one or two tables get > > corrupted. > > Usually freezes once per day. Just freezes and I had > > to kill mysql process > > and start. > > > > Did you solve your problem? I also downloaded latest > > snapshot from > > mysql.com web site and installed, but nothing > > changes. > > Let me know if you find something. > > > > TIA, > > > > Ganbold > > > > > > At 12:48 PM 03.04.2004, you wrote: > > >ah! thanks :) > > > > > >It just happened again and I was able to collect > > the > > >data again. > > > > > >Before I did mysqladmin shutdown/killall -11 mysqld > > >http://sql.tribalwar.com/before-ps.txt > > > > > >a few times the 3 giant processes would show as -, > > but > > >then it got to the point where they weren't > > changing > > >one bit (staying at Giant) > > > > > >after I did mysqladmin shutdown/killall -11 mysqld > > >http://sql.tribalwar.com/after-ps.txt > > > > > >this eventually cleared up after a minute or so. > > > > > >I've given some serious thought to upgrading to > > >-CURRENT, but with the network stack commit comming > > >soon (or happening now), I don't want to get caught > > in > > >the middle of it and have severe problems. Perhaps > > I > > >should use a tag for the time right around the > > sysctl > > >change for the mp stack? > > > > > >The guy running the site just wants me to (have > > >someone at the colo) format the machine and go back > > to > > >4.9. > > > > > >Oh, I now have witness in the kernel...no deadlocks > > >from what I've seen. > > > > > >HTH, > > >Daryl > > > > > >--- Doug White wrote: > > > > On Fri, 2 Apr 2004, Daryl Chance wrote: > > > > > > > > > Thanks for your response. It just happened a > > few > > > > > minutes ago and i was able to capture all the > > > > output > > > > > into a text file. > > > > > > > > > > http://sql.tribalwar.com/ps.txt > > > > > > > > oops, sorry, I get those confused. You want 'ps > > > > axlwww'. WCHAN will be a > > > > string like "select". > > > > > > > > > I have WITNESS* options compiled into the > > kernel > > > > and > > > > > am going to reboot soon. Do you think it > > would > > > > help > > > > > in debugging this to see if there are any > > > > deadlocks? > > > > > > > > Potentially, although I get 100% cpu loops with > > kde > > > > that won't trigger any > > > > witness or invariants checks. > > > > > > > > -- > > > > Doug White | FreeBSD: The > > Power > > > > to Serve > > > > dwhite@gumbysoft.com | www.FreeBSD.org > > > > > > > > >__________________________________ > > >Do you Yahoo!? > > >Yahoo! Small Business $15K Web Design Giveaway > > >http://promotions.yahoo.com/design_giveaway/ > > >_______________________________________________ > > >freebsd-current@freebsd.org mailing list > > > >http://lists.freebsd.org/mailman/listinfo/freebsd-current > > >To unsubscribe, send any mail to > > "freebsd-current-unsubscribe@freebsd.org" > > > > >__________________________________ >Do you Yahoo!? >Yahoo! Small Business $15K Web Design Giveaway >http://promotions.yahoo.com/design_giveaway/