A number of blogs have been written with the intent of describing how
the thread pool manages to solve the requirements of the thread pool.
These blogs are:
MySQL Thread Pool: Problem Definition
MySQL Thread Pool: Scalability Solution
MySQL Thread Pool: Limiting number of concurrent statement executions
Automated benchmark tool for DBT2, Sysbench and flexAsynch
MySQL Thread Pool: Limiting number of concurrent transactions
MySQL Thread Pool: When to use?
MySQL Thread Pool vs. Connection Pool
MySQL Thread Pool: Optimal configuration
MySQL Thread Pool: Benchmarking
There are some interesting discussions in the comments on the scalability solution blog
and on the blog about limiting number of concurrent statement executions
and finally also on the blog about when to use.
These discussions are around when to use it, what other features might be worth
considering and some remarks on the type of benchmarks that could be used to
evaluate solutions.
The requirements we had on the thread pool solution and the solutions were:
1) Split threads into groups individually handled to avoid making the
solution a problem in itself, aim is to manage one active thread per
group.
Solution:
Connections are put into a thread group at connect time by round robin.
Configurable number of thread groups. This ensures that the thread pool
itself isn't a scalability hog.
2) Wait for execution of a query until the MySQL Server has sufficient
CPU and memory resources to execute it.
Solution:
Each thread group tries to keep the number of executing queries to one or
zero. If a query is already executing in the thread group, put connection
in wait queue.
3) Prioritize queries on connections that have an ongoing transaction.
Solution:
Put waiting connections in high priority queue when a transaction is
already started on the connection.
4) Avoid deadlocks when queries are stalled or execute for a long time.
Solution:
Allow another query to execute when the executing query in the thread
group is declared as stalled (after a configurable time).
4 comments:
Mikael, don't you think that the requirement "split threads into group.." was not a requirement, but rather a workaround for the sorry state of asynchronous IO on a single operating system Linux. epoll, used in multithreaded environemnts has pathetic performance. Some other OSes (especially those where context switch costs more than on Linux) have a much better async IO builtin in the OS. Windows IO completion ports are done well, and provide anything one would need for a threadpool - LIFO wakeup for threads waiting on them, concurrency limiting (*not* letting all threads run, if runnable thread count > parameter). Well, Windows provides also the threadpool itself. So does OSX with Grand Central Dispatch. Linux with its thundering herd wakeup-all-threads-waiting-on-epoll and FIFO scheduler activation is a joke, compared to others (I read on LKML, and many patches were suggested to fix the above problems, and all were rejected, due to some subtle semantics change). Anyway, my point is that you (or actually Kelly couple of years back from now) seem to have built a system to workaround Linux limitations. Why did not you plan to use something better if it is available?
Hi Wlad,
The model we designed wasn't a workaround. We looked at several options of how to implement things in various OSs and we also did consider Windows IO completion ports.
The choices we did was based on the requirements we had and also on discovering that the solution scaled well fairly independent of the way the OS solved the problems.
There isn't any solution in any OS that provides a perfect fit for the requirements of the MySQL thread pool.
I fail to understand how MySQL threadpool is different from a generic one. IO integration,filling cores to the max, ensure forward progress if task stall, is that what generic threadpool does, and OS-integrated one has a much better information to consider when making decisions, it has the knowlegde of any single wait, not what one would tell it via thd_begin_wait(). Given that both Windows threadpool and Grand Central Dispatch on Lion now also have task priorization if required, I fail to see see why this would not be a perfect fit.
Wlad,
I hope I have supplied sufficient information on my thoughts in the area now to answer that. I still beg to differ, there is still things we want in the MySQL Thread Pool which isn't available in the OS solutions. But I already mentioned these things in my blogs so no need to repeat it all again.
Post a Comment