maria-developers team mailing list archive

[MARK CALLAGHAN <mdcallag@xxxxxxxxx>]

On Wed, Apr 8, 2009 at 9:13 AM, Michael Widenius <monty@xxxxxxxxxxxx> wrote:
>
> Hi!
>
>>>>>> "MARK" == MARK CALLAGHAN <mdcallag@xxxxxxxxx> writes:
>
> MARK> On Wed, Mar 18, 2009 at 7:23 AM, Michael Widenius <monty@xxxxxxxxxxxx> wrote:
>>>
>>> Hi!
>>>
>>>>>>>> "Vadim" == Vadim Tkachenko <vadim@xxxxxxxxxxx> writes:
>>>
> Vadim> Michael,
> Vadim> Thread-pool should be used very carefully.
>>>
> Vadim> We tested it and InnoDB hangs in sysbench benchmarks when count of
> Vadim> client connection > size of thread-pool.
>>>
>>> Yes, this a problem when you have more locks than threads.
>>> The innodb deadlock detector has to timeout the hanged items.
>>>
> Vadim> The problem is transaction state. Some connections may do internals
> Vadim> locks and after that could not enter to pool, because all slots are busy.
> Vadim> I expect you will have the same problem with Maria when it can fully
> Vadim> support transactions.
>>>
>>> That is one of the main reasons I added --extra-port to MariaDB
>>> This allows you to connect and check/kill things if you get a hang.
>>>
>>> So things are not perfect now, but at least a little better.
>
> MARK> I want a method in handler.h to timeout/wakeup threads blocked in a
> MARK> handler method. Many engines have the notion of blocking on a lock for
> MARK> a row/page (Innodb, Maria, maybe Falcon and PBXT). The scheduler
> MARK> cannot do anything to get those threads back today. I run Innodb with
> MARK> a 50 second lock wait timeout. That is a very long time to wait when
> MARK> all threads get tied up. The scheduler may be able to do this with
> MARK> THD::enter_cond for things blocked above a storage engine and that may
> MARK> require killing the current statement for the thread to be waked.
>
> That's not that hard to do (Alredy works for table locks, which can be
> killed).
>
> Do you have any ideas of how this wakeup should work?

Not yet, we are almost done with the backport of pool-of-threads to
5.0.37. The next step is to fix the SMP performance and we have a
proof of concept for that. After performance is fixed we will look at
this. For us the problem is limited to InnoDB and there is a function
in InnoDB where threads go to sleep when waiting on row locks (for as
many seconds as the lock wait timeout allows). So we may implement
something that is convenient in this function rather than doing the
right thing with a generic interface that could be implemented by any
table handler.

>
> Sending a 'kill' manuall would not be hard to do, but how to do this
> automaticly in certain situation?
> (Especially being able to define the situations is the hard part)

We will need a new thread to check for this case.

>
>>> In the future we have to also look at creating more pool-threads when
>>> all pool-threads gets locked by a handler.
>
> MARK> I don't trust this unless the server is able to create one thread per
> MARK> connection and when running a server with 10,000+ connections I don't
> MARK> think that will be a wise thing to do. I think this approach will lead
> MARK> to rare but spectacular failures for hung servers.
>
> Having a more dynamic limit for the pool of thread doesn't sound like
> a bad idea and relatively easy to do.
>
> But I agree it doesn't solve all problems.  Extra-port also helps, but
> we need something more.
>
> Regards,
> Monty
>

We have also added per-account concurrent query limits for our
implementation. This required a new column in mysql.user. We don't
want a misbehaving account to take over all of the threads in the
thread pool.

-- 
Mark Callaghan
mdcallag@xxxxxxxxx