[Maria-developers] Working on microsecond patches
![](https://secure.gravatar.com/avatar/06b8a7f236cf59aa9a83cab1a0551ca8.jpg?s=120&d=mm&r=g)
Hi! Just wanted to inform that I just pushed and extended pool-of-threads handling to the active MariaDB tree at launchpad. http://askmonty.org/wiki/index.php/MariaDB_versus_MySQL#Pool_of_threads_.28l... I plan to during the next few days also add the following patches to MariaDB: http://www.percona.com/mysql/5.0.77-b13/patches/microslow_innodb.patch http://www.percona.com/mysql/5.0.77-b13/patches/microsec_process.patch Regards, Monty
![](https://secure.gravatar.com/avatar/b217799bb4424807bac67c08691f4d72.jpg?s=120&d=mm&r=g)
Michael, Thread-pool should be used very carefully. We tested it and InnoDB hangs in sysbench benchmarks when count of client connection > size of thread-pool. The problem is transaction state. Some connections may do internals locks and after that could not enter to pool, because all slots are busy. I expect you will have the same problem with Maria when it can fully support transactions. Michael Widenius wrote:
Hi!
Just wanted to inform that I just pushed and extended pool-of-threads handling to the active MariaDB tree at launchpad.
http://askmonty.org/wiki/index.php/MariaDB_versus_MySQL#Pool_of_threads_.28l...
I plan to during the next few days also add the following patches to MariaDB:
http://www.percona.com/mysql/5.0.77-b13/patches/microslow_innodb.patch http://www.percona.com/mysql/5.0.77-b13/patches/microsec_process.patch
Regards, Monty
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Vadim Tkachenko, CTO Percona Inc. ICQ: 369-510-335, Skype: vadimtk153, Phone +1-888-401-3403 MySQL Performance Blog - http://www.mysqlperformanceblog.com MySQL Consulting http://www.percona.com/ Attend the 2009 Percona Performance Conference April 22-23 - http://conferences.percona.com/
![](https://secure.gravatar.com/avatar/06b8a7f236cf59aa9a83cab1a0551ca8.jpg?s=120&d=mm&r=g)
Hi!
"Vadim" == Vadim Tkachenko
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. In the future we have to also look at creating more pool-threads when all pool-threads gets locked by a handler. Regards, Monty
![](https://secure.gravatar.com/avatar/d1fa154903ca7ed342c36888aab05236.jpg?s=120&d=mm&r=g)
On Wed, Mar 18, 2009 at 7:23 AM, Michael Widenius
Hi!
"Vadim" == Vadim Tkachenko
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.
I want a method in handler.h to timeout/wakeup threads blocked in a handler method. Many engines have the notion of blocking on a lock for a row/page (Innodb, Maria, maybe Falcon and PBXT). The scheduler cannot do anything to get those threads back today. I run Innodb with a 50 second lock wait timeout. That is a very long time to wait when all threads get tied up. The scheduler may be able to do this with THD::enter_cond for things blocked above a storage engine and that may require killing the current statement for the thread to be waked.
In the future we have to also look at creating more pool-threads when all pool-threads gets locked by a handler.
I don't trust this unless the server is able to create one thread per connection and when running a server with 10,000+ connections I don't think that will be a wise thing to do. I think this approach will lead to rare but spectacular failures for hung servers.
Regards, Monty
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : maria-developers@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
-- Mark Callaghan mdcallag@gmail.com
![](https://secure.gravatar.com/avatar/06b8a7f236cf59aa9a83cab1a0551ca8.jpg?s=120&d=mm&r=g)
Hi!
"MARK" == MARK CALLAGHAN
writes:
MARK> On Wed, Mar 18, 2009 at 7:23 AM, Michael Widenius
Hi!
> "Vadim" == Vadim Tkachenko
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? 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)
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
![](https://secure.gravatar.com/avatar/d1fa154903ca7ed342c36888aab05236.jpg?s=120&d=mm&r=g)
On Wed, Apr 8, 2009 at 9:13 AM, Michael Widenius
Hi!
"MARK" == MARK CALLAGHAN
writes: MARK> On Wed, Mar 18, 2009 at 7:23 AM, Michael Widenius
wrote: Hi!
>> "Vadim" == Vadim Tkachenko
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
participants (3)
-
MARK CALLAGHAN
-
Michael Widenius
-
Vadim Tkachenko