Thread Pool Size tuning

MySQL & MariaDB Percona Server for MySQL 8.0
1

Hi,
I am looking for some direction/documentation on tuning the thread pool size for the following 2 configurations which I am benchmarking.

  1. 8/16 CPU (up-to 128/256 active connections)
  2. 64 CPU (up-to 1000 active connections)

I have also gone through the previous posts on this topic but need more guidance: Optimum thread_pool_size

2

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. :slight_smile: 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).

3

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?

4

This is a surprising result from your benchmarks. Have you looked over this post?

Also, direct from our documentation (Thread pool - Percona Server for MySQL):

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.

5

Here is my set-up:

MySQL version: 8.0.37-29
MySQL configuration is the default for this release. I have not made any other change.

numCPU: 32
Memory: 128 GB

Thread Pool Size: 32 (default)
Sysbench OLTP Point-Select: 6X TPS over baseline
Sysbench read-only: Up-to 2X TPS
Sysbench read-write: 1.5X TPS (after tuning the thread pool size to 128).

I am unable to increase the thread pool size to more than 128. Is this the maximum?

6

Also, I am seeing better results with thread_pool_size=128 (4x TPS for Sysbench read-write) compared to the default thread_pool_size (32 for the 32-CPU). Any idea why the recommended value (and the default) is same as the number of CPU cores?