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