Proxysql to mysql connection bottleneck

[chanakagl]

This morning, we experienced a significant increase in traffic to our system, resulting in a rise in active frontend and backend connections. However, we've noticed latency issues specifically with database queries, despite those queries being well-optimized. This latency seems to occur primarily when load increases.

To address this, we've increased the pool size on the application side to prevent exhaustion during peak traffic periods, and we can confirm that our backend MySQL servers have sufficient headroom to handle the load. Additionally, our ProxySQL instances, which each have 8 vCPUs, have not exceeded 50% CPU utilization, even with the MySQL thread set to 8 in the ProxySQL configuration.

Given that ProxySQL appears to be underutilized, I wonder if there are additional settings or configurations we should consider to optimize performance and better manage peak traffic. Any insights would be appreciated.

Front end connections:-

backend connections:-

Proxysql CPU:-

[renecannao]

Hi @chanakagl .
Be aware that mysql-threads is one of the only 2 variables that cannot be changed at runtime, thus it is possible that mysql-threads is still 4 if you loaded to runtime without a restart.
Important: do not try to load to runtime and later save to disk, as load to runtime will fail and revert the change. Instead, save to disk and restart.

[chanakagl]

I checked that all proxysql and mysql-threads is fine it's set to 8 in runtime_global_variables table as well. Could you please suggest if there is any other setting that we need to check on?

[chanakagl]

@renecannao I increased the mysql_threads in ProxySQL to 2 times the number of CPUs, which helped resolve the issue to some extent. However, during high traffic, ProxySQL still doesn't fully utilize the available resources, with CPU usage remaining at only 50%. As a result, we've had to horizontally scale the system to handle the increased load.

[chanakagl]

@renecannao could you please help here?