[Maria-discuss] doubt with thread pool
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size? -- Roberto Spadim
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
max_connections is the ultimate limit on number of total connections. it does not make sense to have thread pool size larger as the max. number of connections. thread pool size is something that limits number of concurrently running ( executing on CPU rather than waiting) commands. It does not make sense to have it larger than max. connections.
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
nice , if i have max_connections = thread_pool_size maybe i'm doing a one thread per connection right? other doubt.... about the memory use, i remember when mysqld crash there's a memory math formula using max_connections, when using thread pool are there the same memory limit? 2015-11-10 16:04 GMT-02:00 Vladislav Vaintroub <vvaintroub@gmail.com>:
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
max_connections is the ultimate limit on number of total connections. it does not make sense to have thread pool size larger as the max. number of connections. thread pool size is something that limits number of concurrently running ( executing on CPU rather than waiting) commands. It does not make sense to have it larger than max. connections.
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
Hi, There is really no formula for predicting MySQL memory size. There are many factors. But if your estimate uses max_connections, then use max_connections/threads_in_pool. That gives you the effective number of queries running at once. There are some things, like number of temporary tables per connection, that has to use max_connections, because these persist while a connection is not executing SQL in the pool of threads. On Tue, Nov 10, 2015 at 4:25 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
nice , if i have max_connections = thread_pool_size maybe i'm doing a one thread per connection right?
other doubt.... about the memory use, i remember when mysqld crash there's a memory math formula using max_connections, when using thread pool are there the same memory limit?
2015-11-10 16:04 GMT-02:00 Vladislav Vaintroub <vvaintroub@gmail.com>:
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
max_connections is the ultimate limit on number of total connections. it does not make sense to have thread pool size larger as the max. number of connections. thread pool size is something that limits number of concurrently running ( executing on CPU rather than waiting) commands. It does not make sense to have it larger than max. connections.
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
hm nice, but check my "problem" with one thread per connection, i can think about an estimation with max_connections with thread pool, what i could do? use the same max_connections or use another variable (thread size?), considering that i have a limit of threads running (i'm thinking that this one is better, right?) 2015-11-11 0:49 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Hi,
There is really no formula for predicting MySQL memory size. There are many factors. But if your estimate uses max_connections, then use max_connections/threads_in_pool. That gives you the effective number of queries running at once. There are some things, like number of temporary tables per connection, that has to use max_connections, because these persist while a connection is not executing SQL in the pool of threads.
On Tue, Nov 10, 2015 at 4:25 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
nice , if i have max_connections = thread_pool_size maybe i'm doing a one thread per connection right?
other doubt.... about the memory use, i remember when mysqld crash there's a memory math formula using max_connections, when using thread pool are there the same memory limit?
2015-11-10 16:04 GMT-02:00 Vladislav Vaintroub <vvaintroub@gmail.com>:
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
max_connections is the ultimate limit on number of total connections. it does not make sense to have thread pool size larger as the max. number of connections. thread pool size is something that limits number of concurrently running ( executing on CPU rather than waiting) commands. It does not make sense to have it larger than max. connections.
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
sorry i sent before writing the example of a thread that stoped, and is waiting a new "job", in this case i should consider max connections or thread pool size? i think it's the second, right? i never used thread pool with many connection, that's the problem (no experience), i will try a dev server to test and track the memory use, but a previous experience is nice :) i didn't found it on google 2015-11-11 13:36 GMT-02:00 Roberto Spadim <roberto@spadim.com.br>:
hm nice, but check my "problem" with one thread per connection, i can think about an estimation with max_connections with thread pool, what i could do? use the same max_connections or use another variable (thread size?), considering that i have a limit of threads running (i'm thinking that this one is better, right?)
2015-11-11 0:49 GMT-02:00 Justin Swanhart <greenlion@gmail.com>:
Hi,
There is really no formula for predicting MySQL memory size. There are many factors. But if your estimate uses max_connections, then use max_connections/threads_in_pool. That gives you the effective number of queries running at once. There are some things, like number of temporary tables per connection, that has to use max_connections, because these persist while a connection is not executing SQL in the pool of threads.
On Tue, Nov 10, 2015 at 4:25 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
nice , if i have max_connections = thread_pool_size maybe i'm doing a one thread per connection right?
other doubt.... about the memory use, i remember when mysqld crash there's a memory math formula using max_connections, when using thread pool are there the same memory limit?
2015-11-10 16:04 GMT-02:00 Vladislav Vaintroub <vvaintroub@gmail.com>:
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
max_connections is the ultimate limit on number of total connections. it does not make sense to have thread pool size larger as the max. number of connections. thread pool size is something that limits number of concurrently running ( executing on CPU rather than waiting) commands. It does not make sense to have it larger than max. connections.
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
On Tue, Nov 10, 2015 at 6:47 PM, Roberto Spadim <roberto@spadim.com.br> wrote:
i'm with a doubt if i have a thread pool with 1000 threads and a max_connections with 100 connections can i have more than 100 clients connected (1000 threads for example)? dos thread pool size limit the max connections, or max connections limite the thread pol size?
-- Roberto Spadim
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
Am 11.11.2015 um 16:36 schrieb Roberto Spadim:
hm nice, but check my "problem" with one thread per connection, i can think about an estimation with max_connections with thread pool, what i could do? use the same max_connections or use another variable (thread size?), considering that i have a limit of threads running (i'm thinking that this one is better, right?)
the thread pool is typically shorter then max connections, otherwise you won't need a pool and could stay with one thread per connection as before thread pools existed (for most workloads it's better, you need to benchamrk your application) how short or large? depends on how many at the same time active threads and so concurrency you need and at the same time your hardware is capable - too much concurrency quickly ruins performance when the underlying disk layer can't handle it too less concurrency, well, things are slower than they could be and keep in mind concurrency don't make tasks always faster in generel so nobody but you can asnwer your question by trial and measure
nice :) i will do it, if possible share here i got, the hardware is old it will not help a lot but ok 2015-11-11 13:43 GMT-02:00 Reindl Harald <h.reindl@thelounge.net>:
Am 11.11.2015 um 16:36 schrieb Roberto Spadim:
hm nice, but check my "problem" with one thread per connection, i can think about an estimation with max_connections with thread pool, what i could do? use the same max_connections or use another variable (thread size?), considering that i have a limit of threads running (i'm thinking that this one is better, right?)
the thread pool is typically shorter then max connections, otherwise you won't need a pool and could stay with one thread per connection as before thread pools existed (for most workloads it's better, you need to benchamrk your application)
how short or large?
depends on how many at the same time active threads and so concurrency you need and at the same time your hardware is capable - too much concurrency quickly ruins performance when the underlying disk layer can't handle it
too less concurrency, well, things are slower than they could be and keep in mind concurrency don't make tasks always faster in generel
so nobody but you can asnwer your question by trial and measure
_______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
-- Roberto Spadim SPAEmpresarial - Software ERP Eng. Automação e Controle
participants (4)
-
Justin Swanhart -
Reindl Harald -
Roberto Spadim -
Vladislav Vaintroub