MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Joe Shear Date:July 23 2003 8:23pm
Subject:Re: mysql stops processing
View as plain text  

On Wed, 2003-07-23 at 13:11, Heikki Tuuri wrote:
> Joe,
> 
> ----- Original Message ----- 
> From: "Joe Shear" <joe@stripped>
> To: "Heikki Tuuri" <Heikki.Tuuri@stripped>
> Cc: <mysql@stripped>
> Sent: Wednesday, July 23, 2003 11:03 PM
> Subject: Re: mysql stops processing
> 
> 
> > Unfortunately, we haven't been able to connect to the database because
> > of the maxed out connections.  This happens so fast that the maxed out
> > connections is usually the first sign.
> 
> can you keep open the interactive SQL client mysql so that when the hang
> occurs you would have a connection where to run SHOW PROCESSLIST etc.?
> 

I can try, but the ops guys are usually the ones who react, and they
often have to reset mysql from home, so they wouldn't have access to
open mysql client windows here.

> > I've been hesitant to start the
> > monitor, since this has only been happening once every couple days.
> > It's been becoming more frequent, so I'll try turning them on and see if
> > we get lucky.  Top isn't showing that our CPU usage is that high.  Last
> > time we saw this, it was around 50% on both procs.
> 
> 50 % during the hang? Is mysqld taking that much though it is not processing
> anything?
> 

I think its all mysqld -- even when it's not going anything.  Could it
be thrashing on something?  We don't have our max connections set that
high (100).  When we were using 3.23.52, we had really bad problems with
a similar problem but the CPU usage would hit 100%.  When we upgraded,
the problem went away for a month.  

> > Also, we are
> > flushing logs every 5 minutes (this has caused problems in the past).
> 
> Regards,
> 
> Heikki
> 
> > On Wed, 2003-07-23 at 12:56, Heikki Tuuri wrote:
> > > Joe,
> > >
> > > ----- Original Message ----- 
> > > From: "Joe Shear" <joe@stripped>
> > > Newsgroups: mailing.database.mysql
> > > Sent: Wednesday, July 23, 2003 10:40 PM
> > > Subject: RE: mysql stops processing
> > >
> > >
> > > > hi,
> > > >
> > > > We're pretty careful about preventing that.  Also, no queries are
> moving
> > > > forward, no inserts, updates, or selects, even the ones on tables
> that
> > > > are only a mixture of inserts and selects.
> > >
> > > what do SHOW PROCESSLIST and SHOW INNODB STATUS print in this situation?
> > >
> > > Or is it so that you cannot run them because of maxed out connections?
> You
> > > could write script to run them every 5 seconds so that we could see what
> is
> > > happening.
> > >
> > > Also, if you do
> > >
> > > CREATE TABLE innodb_monitor(a INT) TYPE=InnoDB;
> > >
> > > then mysqld will print the output of SHOW INNODB STATUS to the .err file
> > > every 15 seconds.
> > >
> > > What is the CPU usage from 'top' during the hang?
> > >
> > > 4.0.14 has better diagnostics than 3.23.56. An upgrade might help, but
> let
> > > us wait a couple of days first.
> > >
> > > > joe
> > >
> > > Regards,
> > >
> > > Heikki
> > >
> > >
> > > > On Wed, 2003-07-23 at 12:02, Andrew Braithwaite wrote:
> > > > > Hi,
> > > > >
> > > > > I don't know what type of applications are using your database
> but
> if I
> > > had
> > > > > to guess at the problem, I would guess that you have a very slow
> query
> > > which
> > > > > runs every now and again that is locking up certain
> tables/records
> > > causing
> > > > > all the other queries to queue up and eventually running out of
> > > connections
> > > > > on the DB.  To find out if this is the case, when your server
> gets
> into
> > > this
> > > > > state run a "mysqladmin -v pro" and see if all the queries are
> waiting
> > > for a
> > > > > lock to be freed up.
> > > > >
> > > > > Hope this helps,
> > > > >
> > > > > Andrew
> > > > >
> > > > > -----Original Message-----
> > > > > From: Joe Shear [mailto:joe@stripped]
> > > > > Sent: Wednesday 23 July 2003 19:45
> > > > > To: mysql@stripped
> > > > > Subject: mysql stops processing
> > > > >
> > > > >
> > > > > Hi,
> > > > >
> > > > > Has anyone ever had any problems with MySQL w/ all InnoDB tables
> just
> > > stop
> > > > > processing queries?  There doesn't seem to be any pattern to it,
> it
> > > happens
> > > > > at times of relatively high load (load avg of 4 on a dual proc)
> but
> the
> > > CPUs
> > > > > still have plenty of idle time, and the disks aren't maxing out. 
> It
> > > doesn't
> > > > > happen everytime there is a high load either.  The box just
> stops
> > > processing
> > > > > every query, and we quickly hit the max connection limit.  The
> only
> > > solution
> > > > > we've found is to shutdown mysql and restart it -- at which point
> it
> > > works
> > > > > fine for a couple days.  We are running mysql 3.23.56 w/ redhat
> 7.3
> and
> > > > > 2.4.20 kernel.  Anybody have any suggestions?
> > > > >
> > > > > thanks,
> > > > > joe
> > > > -- 
> > > > Joe Shear <joe@stripped>
> > > >
> > > >
> > > > -- 
> > > > MySQL General Mailing List
> > > > For list archives: http://lists.mysql.com/mysql
> > > > To unsubscribe:
> > > http://lists.mysql.com/mysql?unsub=1
> > > >
> > -- 
> > Joe Shear <joe@stripped>
> >
-- 
Joe Shear <joe@stripped>

Thread
mysql stops processingJoe Shear23 Jul
RE: mysql stops processingAndrew Braithwaite23 Jul
  • RE: mysql stops processingJoe Shear23 Jul
  • HELP WITH mySQL!!!Michael C. Podlesny23 Jul
    • Re: HELP WITH mySQL!!!Nils Valentin24 Jul
Re: mysql stops processingHeikki Tuuri23 Jul
  • Re: mysql stops processingJoe Shear23 Jul
Re: mysql stops processingHeikki Tuuri23 Jul
  • Re: mysql stops processingJoe Shear23 Jul
RE: mysql stops processingAndrew Braithwaite23 Jul
Re: mysql stops processingHeikki Tuuri23 Jul
Re: mysql stops processingHeikki Tuuri24 Jul
  • Re: mysql stops processingJoe Shear24 Jul
Re: mysql stops processingHeikki Tuuri24 Jul
Re: mysql stops processingInfo26 Jul
Re: mysql stops processingInfo26 Jul