On Wed, Apr 8, 2009 at 9:13 AM, Michael Widenius <monty@askmonty.org> wrote:
Hi!
"MARK" == MARK CALLAGHAN <mdcallag@gmail.com> writes:
MARK> On Wed, Mar 18, 2009 at 7:23 AM, Michael Widenius <monty@askmonty.org> wrote:
Hi!
>> "Vadim" == Vadim Tkachenko <vadim@percona.com> 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@gmail.com