Are you able to run show process list? What is the status of your query? What date was your freeBSD 5-current built on? Please send output of uname -a, so that we know exactly which version on which date because -current changes every day. Also are you using libmap.conf? If yes, what are it's contents. What does top and ps show when this query is "locked up", can you include output of this? Why do you feel you MUST use freebsd5 (I am just curious)? Have you tried a GDB backtrace (please use debug version of mysql!)?
I know you already know this, but you shouldn't be using 5-current on a production server and even the releases such as 5.2.1 can have issues that you need to be aware of. Also, one final recommendation, if you are using current and have not updated in the last 30 to 60 days I would recommend that you update again as many thing have been changed and fixed. Hope this helps, Ken ----- Original Message ----- From: "Ganbold" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Sunday, April 11, 2004 11:35 PM Subject: FBSD 5.2.CURRENT-p4 and mysqld problems > Hi, > > I found some problematic queries which locks mysql server any further > processing. I'm using mysql-4.0.18 > from FreeBSD ports collection. I'm using FreeBSD-5.2-CURRENT. > 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'; > > Above query is not using any index. It locks mysql server and all other > following queries go into queue until I kill locked mysqld thread. > After killing locked mysqld thread everything goes back to function normally. > I made composite index on contract_id,time_stamp,total_amount and let's see > what will happen. > > In the meantime, can somebody explain me what the problem is? > I already sent email to freebsd-current and freebsd-ports mailing list > about this problem. > Does somebody have this problem before? Let me know if there is any solution. > I know I should use FreeBSD 4.9 stable since it doesn't have any problem > with this issue. However I need to solve > this problem on FreeBSD-5.2-CURRENT. > > thanks in advance, > > 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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 > > > > > [EMAIL PROTECTED] | www.FreeBSD.org > > > > > > > > > > > >__________________________________ > > > >Do you Yahoo!? > > > >Yahoo! Small Business $15K Web Design Giveaway > > > >http://promotions.yahoo.com/design_giveaway/ > > > >_______________________________________________ > > > >[EMAIL PROTECTED] mailing list > > > > > >http://lists.freebsd.org/mailman/listinfo/freebsd-current > > > >To unsubscribe, send any mail to > > > "[EMAIL PROTECTED]" > > > > > > > > >__________________________________ > >Do you Yahoo!? > >Yahoo! Small Business $15K Web Design Giveaway > >http://promotions.yahoo.com/design_giveaway/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
