Answer Diff [205]
== About Pool of Threads
¶
¶
This is an extended version of the pool-of-threads code in MySQL 6.0. This ¶
allows you to use a limited set of threads to handle all queries, instead of ¶
the old 'one-thread-per-connection' style. In recent times, its also been referred to as "thread pool" or "thread pooling" as this feature (in a different implementation) is available in Enterprise editions of MySQL (not in the Community edition). ¶
¶
This can be a very big win if most of your queries are short running queries ¶
and there are few table/row locks in your system. ¶
¶
== Instructions ¶
¶
To enable pool-of-threads you must first run configure with the ¶
<<fixed>>--with-libevent<</fixed>> option. (This is automatically done if you ¶
use any 'max' scripts in the BUILD directory): ¶
¶
<<code lang=bash inline=false>> ¶
./configure --with-libevent ¶
<</code>> ¶
¶
When starting mysqld with the pool of threads code you should use ¶
¶
<<code lang=bash inline=false>> ¶
mysqld --thread-handling=pool-of-threads --thread-pool-size=20 ¶
<</code>> ¶
¶
Default values are: ¶
¶
<<code>> ¶
thread-handling= one-thread-per-connection ¶
thread-pool-size= 20 ¶
<</code>> ¶
¶
One issue with pool-of-threads is that if all worker threads are doing ¶
work (like running long queries) or are locked by a row/table lock no ¶
new connections can be established and you can't login and find out ¶
what's wrong or login and kill queries. ¶
¶
To help this, we have introduced two new options for mysqld: ¶
¶
<<code>> ¶
--extra-port=# (Default 0) ¶
--extra-max-connections=# (Default 1) ¶
<</code>> ¶
¶
If extra-port is <> 0 then you can connect max_connections number of ¶
normal threads + 1 extra SUPER user through the 'extra-port' TCP/IP ¶
port. These connections use the old one-thread-per-connection method. ¶
¶
To connect with through the extra port, use: ¶
¶
<<code lang=bash inline=false>> ¶
mysql --port='number-of-extra-port' --protocol=tcp ¶
<</code>> ¶
¶
This allows you to freely use, on connection bases, the optimal ¶
connection/thread model. ¶
¶
Some useful links: ¶
* [[http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_thread_handling|Thread-handling and thread-pool-size variables]] ¶
* [[http://dev.mysql.com/doc/refman/5.6/en/connection-threads.html|Connection options]] ¶
¶
¶
This is an extended version of the pool-of-threads code in MySQL 6.0. This ¶
allows you to use a limited set of threads to handle all queries, instead of ¶
the old 'one-thread-per-connection' style. In recent times, its also been referred to as "thread pool" or "thread pooling" as this feature (in a different implementation) is available in Enterprise editions of MySQL (not in the Community edition). ¶
¶
This can be a very big win if most of your queries are short running queries ¶
and there are few table/row locks in your system. ¶
¶
== Instructions ¶
¶
To enable pool-of-threads you must first run configure with the ¶
<<fixed>>--with-libevent<</fixed>> option. (This is automatically done if you ¶
use any 'max' scripts in the BUILD directory): ¶
¶
<<code lang=bash inline=false>> ¶
./configure --with-libevent ¶
<</code>> ¶
¶
When starting mysqld with the pool of threads code you should use ¶
¶
<<code lang=bash inline=false>> ¶
mysqld --thread-handling=pool-of-threads --thread-pool-size=20 ¶
<</code>> ¶
¶
Default values are: ¶
¶
<<code>> ¶
thread-handling= one-thread-per-connection ¶
thread-pool-size= 20 ¶
<</code>> ¶
¶
One issue with pool-of-threads is that if all worker threads are doing ¶
work (like running long queries) or are locked by a row/table lock no ¶
new connections can be established and you can't login and find out ¶
what's wrong or login and kill queries. ¶
¶
To help this, we have introduced two new options for mysqld: ¶
¶
<<code>> ¶
--extra-port=# (Default 0) ¶
--extra-max-connections=# (Default 1) ¶
<</code>> ¶
¶
If extra-port is <> 0 then you can connect max_connections number of ¶
normal threads + 1 extra SUPER user through the 'extra-port' TCP/IP ¶
port. These connections use the old one-thread-per-connection method. ¶
¶
To connect with through the extra port, use: ¶
¶
<<code lang=bash inline=false>> ¶
mysql --port='number-of-extra-port' --protocol=tcp ¶
<</code>> ¶
¶
This allows you to freely use, on connection bases, the optimal ¶
connection/thread model. ¶
¶
Some useful links: ¶
* [[http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_thread_handling|Thread-handling and thread-pool-size variables]] ¶
* [[http://dev.mysql.com/doc/refman/5.6/en/connection-threads.html|Connection options]] ¶
¶
