Proxysql: How can i find the correct number of mysql-threads

[eredi93]

I've been spending a few hours trying to figure out which is the correct number of mysql-threads i should set.
Is there a formula i can use? is the 4 default good enough? when should consider increasing it? does it depend on the number of connections my app opens?
i'd love to get some clarification on this 😄 , @renecannao any chance you could help me out?

[renecannao]

mysql-threads defines the number of worker threads that ProxySQL will use to handle MySQL traffic.
This should be proportional to the number of physical cores you want proxysql to use.

is the 4 default good enough?

It depends.
On a dedicated host with 4 cores where ProxySQL is the only CPU intensive process, 4 is a good value.
On a dedicated host with 32 cores, 4 is not enough.
On an application server where proxysql is running along side other application, maybe you can set mysql-threads to 1 or 2 .

when should consider increasing it?

If you are using mysql-threads=4 , you should increase it if you see proxysql using close to 400% cpu.

does it depend on the number of connections my app opens?

No, it depends from the nature of the traffic. A single worker thread can handle thousands of application connections, and a modern CPU a single worker thread running at 100% can easily handle over 30k point selects per second.
Of course, if CPU usage is getting close to 100% on a single core, it is good to add more worker threads

[abhijitbuchake]

What happens if I have a 4 core CPU which has a considerable traffic and I keep 16 mysql-threads?