Re: FBSD 5.2.1-p4 and mysqld problems

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 <ganbold_at_micom.mng.net> 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 <dwhite_at_gumbysoft.com> 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_at_gumbysoft.com          |  www.FreeBSD.org
> > >
> > >
> > >__________________________________
> > >Do you Yahoo!?
> > >Yahoo! Small Business $15K Web Design Giveaway
> > >http://promotions.yahoo.com/design_giveaway/
> > >_______________________________________________
> > >freebsd-current_at_freebsd.org mailing list
> >
> >http://lists.freebsd.org/mailman/listinfo/freebsd-current
> > >To unsubscribe, send any mail to
> > "freebsd-current-unsubscribe_at_freebsd.org"
> >
>
>
>__________________________________
>Do you Yahoo!?
>Yahoo! Small Business $15K Web Design Giveaway
>http://promotions.yahoo.com/design_giveaway/