MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:July 24 2003 9:39pm
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: Thursday, July 24, 2003 11:33 PM
Subject: Re: mysql stops processing


> We do not use LOCK TABLES anywhere.  Are there any other commands that
> could cause a LOCK TABLES to occur?

no.

> It's possible that we have some
> process that is opening up two threads and processing on both.  We
> definitely didn't intend to if it is happening.  What exactly would be
> the case that would cause problems?  What determines when a thread stays
> inside InnoDB?

Normally, a thread is declared to leave InnoDB when the processing of the
SQL statement ends. But with LOCK TABLES it happens only when you call
UNLOCK TABLES.

> We are looking at upgrading to 4.0.14 in the near future, we're in the
> process of testing it right now.  I don't think we're going to increase
> the concurrency just yet, but, after upgrading to 4.0.14 if we see the
> same problem, I'll grab some more diagnostics.

Please bump up innodb_thread_concurrency to 300 now. The low value 8 was
introduced in 2002 in an attempt to fix 'thread thrashing' problems in
glibc. But those glibc problems are long past.

This looks like an InnoDB bug. 4.0.14 will help in finding out what it is.

> thanks for your help

You are welcome,

> joe

Heikki

> On Thu, 2003-07-24 at 12:49, Heikki Tuuri wrote:
> > Joe,
> >
> > are you using LOCK TABLES? Or does your application program open two
> > connections to MySQL and work based on the results from 2 connections?
> >
> > "
> > ---TRANSACTION 0 221806686, ACTIVE 925 sec, OS thread id 163881
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 31, query id 910 pas01.plaxo.com 10.1.0.1 pas
> > Trx read view will not see trx with id >= 0 221806723, sees < 0
221806596
> > ---TRANSACTION 0 221806623, ACTIVE 925 sec, OS thread id 180269
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 35, query id 991 pas03.plaxo.com 10.1.0.3 pas
> > Trx read view will not see trx with id >= 0 221806695, sees < 0
221806596
> > ---TRANSACTION 0 221806619, ACTIVE 925 sec, OS thread id 172075
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 33, query id 650 pas02.plaxo.com 10.1.0.2 pas
> > Trx read view will not see trx with id >= 0 221806695, sees < 0
221806596
> > ---TRANSACTION 0 221806610, ACTIVE 926 sec, OS thread id 135202
> > 2 lock struct(s), heap size 320, undo log entries 1
> > MySQL thread id 24, query id 448 pas03.plaxo.com 10.1.0.3 pas
> > Trx read view will not see trx with id >= 0 221806623, sees < 0
221806592
> >
> > ...
> >  8 queries inside InnoDB, 183 queries in queue; main thread: making
> > checkpoint
> > "
> >
> > Look above. The oldest transactions are just sitting there without doing
> > anything. Why is that? If they used LOCK TABLES they can still be
declared
> > to be "inside InnoDB", though they are actually waiting for further
commands
> > from the client. They are blocking other queries from entering InnoDB.
> >
> > Fix: set
> >
> > innodb_thread_concurrency=300
> >
> > Then during the next 2 weeks monitor with SHOW INNODB STATUS that the
number
> > of queries "inside InnoDB" does not grow. If it grows, then there is a
leak
> > in InnoDB.
> >
> > MySQL-4.0.14 has better diagnostic prints. It would be easier to find
the
> > problem with it.
> >
> > Regards,
> >
> > Heikki
> >
> > ----- Original Message ----- 
> > From: "Joe Shear" <joe@stripped>
> > To: "Heikki Tuuri" <Heikki.Tuuri@stripped>
> > Cc: <mysql@stripped>
> > Sent: Thursday, July 24, 2003 9:57 PM
> > Subject: Re: mysql stops processing
> >
> >
> > > Attached to this are two "show innodb status" reports from one of db
> > > servers while it appeared to have stopped processing.  One was taken
> > > after waiting 4 additional minutes to see if anything would change.
The
> > > numbers are fairly low because this happened to occur almost
immediately
> > > after we restarted mysql because of the same thing happening.  Again
> > > this is on mysql/innodb 3.23.56.
> > >
> > >
> > >
> > > On Wed, 2003-07-23 at 13:30, 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:23 PM
> > > > Subject: Re: mysql stops processing
> > > >
> > > >
> > > > >
> > > > >
> > > > > 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
> > > > ...
> > > > > > 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.
> > > >
> > > > The 'thread thrashing' problem of 2002 caused CPU usage to increase
by a
> > > > factor of 100, but it did not stop processing completely.
> > > >
> > > > Anyway, we need the diagnostic outputs to conclude anything further.
> > > >
> > > > Regards,
> > > >
> > > > Heikki
> > > -- 
> > > 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