← Back to team overview

maria-discuss team mailing list archive

Re: connection handling is buggy (HY000/2002): Resource temporarily unavailable)


On 17.07.2017 15:47, Reindl Harald wrote:

Am 17.07.2017 um 15:37 schrieb Vladislav Vaintroub:
On 17.07.2017 15:07, Reindl Harald wrote:
see above, also another piece of my intial posting - why in the world does "Threadpool_threads" not reach the highest value under load but after the benchmark is finished

what makes me really worry here is while the load is running (mysqld each time restartet before start apache-benchmark)

| Variable_name           | Value |
| Threadpool_idle_threads | 181   |
| Threadpool_threads      | 189   |

after the benchmark has finished and the machine is idle:
| Variable_name           | Value |
| Threadpool_idle_threads | 207   |
| Threadpool_threads      | 208   |

The benchmarks are usually written in such a way, that all connections will disconnect *at the same time* at the end of benchmark.

The disconnect code runs through couple of into global locks (LOCK_thread_count, if I remember correctly, maybe some others), i.e if several connections disconnect at the same time, there will be contention.

What happens in case of the contention is that threadpool notices it , starts getting worried about all threads getting blocked and possibly deadlocked, and about not having enough spare threads to handle new requests. Thus threadpool will *increase* the number of threads in presence of contention.

If you wait long enough after the benchmark (long enough is longer that thread_pool_idle_timeout seconds), the excess threads will disappear

it's clear that they disappear after thread_pool_idle_timeout but on a benchmarks running for 120 seconds with 250 connections i expect that thread number also reache while the benchmark is actevily running

Our thread pool tries to keep the number of active (non-waiting) threads the same as thread_pool_size. You can calculate "active count" as (Threadpool_threads - Threadpool_idle_threads) in the "show status" output.

If all queries run lightning fast / never block, active count would be thread_pool_size, and Threadpool_idle_threads will be 0.

Yet, there are different situations where overall number of threads grows, due to contention and/or long running queries. Often, also active count grows somewhat, but we have a mechanism to handle this "oversubscription", so it does not run out of control.

It is well possible that bumping active threads will increase the throughput and decrease the query time in your benchmark. If you like to experiment, the easiest way to do it is to increase thread_pool_size. You may, dependent on the load, also get more active threads by decreasing thread_pool_stall_limit and/or increasing thread_pool_oversubscribe, but I would not recommend this, as it is not really reliably way to achieve more active threads.

As explained before, the situation with "benchmark end", where a lot of threads are created, is probably due to lock contention in concurrent disconnects.

Mailing list: https://launchpad.net/~maria-discuss
Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
Unsubscribe : https://launchpad.net/~maria-discuss
More help   : https://help.launchpad.net/ListHelp

Follow ups