Hi @Senthil,
Neither of the two scenarios you describe warrant using the thread pool plugin. The thread pool plugin is for MySQL servers that are in excess of 20,000 connections. Set your thread_cache_size to 64, then monitor this using Percona Monitoring and Management. There is a graph specifically for this. If you see too many “threads created”, then bump up this parameter. If you are still having performance problems, check CPU max utilization, disk latency, and the ‘Handler’ graphs for read_rnd_next activity (this is indication of poor queries not using indexes which require much more CPU to execute).
Thanks for the reply @matthewb. I ran Sysbench benchmarks with thread pool enabled and even with 1000 connections, it gives 6X TPS compared to without thread pool. Similar TPS gains observed with thread pool for read_only workloads as well (3X) with same number of connections (1000). Also, the performance of Thread Pool is better than ProxySQL as well in those cases.
Why does the recommendation more than 20,000 connections? Am I missing something?
If you have fewer than 20,000 connections, using the thread pool does not provide significant benefits. It’s better to keep thread pooling disabled and use the default method.
If you could share some PMM graphs of CPUs, MySQL QPS, etc with and without thread-pool enabled, I’m sure several people over here would like to see. And also share your my.cnf for each test, and sysbench test commands.