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" <[EMAIL PROTECTED]> To: "Heikki Tuuri" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> 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" <[EMAIL PROTECTED]> > > To: "Heikki Tuuri" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > 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" <[EMAIL PROTECTED]> > > > > To: "Heikki Tuuri" <[EMAIL PROTECTED]> > > > > Cc: <[EMAIL PROTECTED]> > > > > 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 <[EMAIL PROTECTED]> > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
