List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 20 1999 12:31pm
Subject:Re: MySQL lockups revisited
View as plain text  
>>>>> "Orlando" == Orlando Andico <orly@stripped> writes:

Orlando> On Sat, 13 Nov 1999, Michael Widenius wrote:
Orlando> ..
>> One big difference between linux and solaris/freebsd is that by
>> default MySQL uses external locking on Solaris/freebsd.  There is a
>> change that it's the lockd deamon that causes problems.
>> Can you try starting mysqld with --skip-locking to check this out ?

Orlando> It's not this. I tried both with --skip-locking and without, the result is
Orlando> the same.

>> My guess would however be that there is a bug when you combine INSERT
>> DELAYED together with INSERT without DELAYED on the same table.  Can
>> you check that all statements really uses INSERT DELAYED ?

Orlando> It's not this, either. I've modified the client code so that INSERT
Orlando> DELAYED is used all the time. Same problem.

Orlando> Here's what I've noticed: when there are a large number of threads waiting
Orlando> on a single table and you start a long-running query on that same table,
Orlando> eventually mysqld will "lock up." It still can accept queries, etc, but is
Orlando> very, very slow. This I think is due to the table-level locking.

Orlando> Our queries are kind of slow (SELECT SUM(...) with a join on a 1-million
Orlando> row table). If it's the only query running, it can finish quickly (< 10
Orlando> seconds). But when there are several of these processes contending for the
Orlando> lock, the wait time will exceed 2 minutes (sometimes as much as five
Orlando> minutes). I guess there's no way around this problem.

Is the above on FreeBSD?  In this case MySQL 3.23.6 should probably
fix this;  FreeBSD has very slow mutex handling in some context and
after I removed some mutexes on statistic variables, MySQL got MUCH
faster on FreeBSD!

Orlando> That's why I want to split up these million-row tables into thousands of
Orlando> smaller ones (one table per dialin user, instead of one huge table holding
Orlando> all the call-detail-records). I am not sure if there will be problems
Orlando> though.

MySQL 3.22.28 will also have he mutex-patch for FreeBSD; It may be
that this will solve your problem...

I shall do some testing with INSERT DELAYED as soon as possible..


MySQL lockups revisitedOrlando Andico23 Oct
  • Re: MySQL lockups revisitedsinisa24 Oct
    • Re: MySQL lockups revisitedOrlando Andico25 Oct
      • Re: MySQL lockups revisitedsinisa25 Oct
        • Re: MySQL lockups revisitedOrlando Andico25 Oct
          • Re: MySQL lockups revisitedsinisa25 Oct
      • Re: MySQL lockups revisitedMichael Widenius13 Nov
        • Re: MySQL lockups revisitedOrlando Andico20 Nov
          • Re: MySQL lockups revisitedMichael Widenius20 Nov
  • MySQL lockups revisitedMichael Widenius13 Nov