List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:July 23 2003 8:11pm
Subject:Re: mysql stops processing
View as plain text  
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'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?

> 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>
>


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