>>>>> "Orlando" == Orlando Andico <orly@stripped> writes:
Orlando> On Sat, 13 Nov 1999, Michael Widenius wrote:
>> 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
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..