MySQL Lists are EOL. Please join:

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


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