Limiting ProxySQL Service on High Backend Load

[Prime Minister of Fun]

Hi all,

Per the title, I'm load testing proxysql and have come across a likely scenario in my environment where applications connecting to ProxySQL can overload all available backends, taking all database service offline.

Before comments about fixing my environment, that's not the point.  The point is, I'd like ProxySQL to reject connections when the backend databases hit some kind of defined limits.

I assume I have to write a script scraping the MySQL backends and then updating the frontend.  But, not sure what settings to update ProxySQL to reject more clients and queries.

Have a great weekend.

[René Cannaò]

ProxySQL is meant to protect the database servers behind it: that said, "fixing your environment" is not the comment you will get :-)

ProxySQL has a lot of features to achieve its goal of protecting the database from being overloaded: the first feature is limiting the number of backend connections.

Clients can create a lot of connections to proxysql, and proxysql can limit the connections to the backend to a way smaller value.

Tuning mysql_servers.max_connections is the first step in this journey.

Thanks,

René

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e880a95b-9af6-4dea-9154-2bb7d3861fccn%40googlegroups.com.

[Prime Minister of Fun]

Hi Rene, 

Thanks for you quick reply.

Currently, servers in the mysql_servers table are configured to have max_connections = 100. Per the documentation, that's maximum backend connections.

If the load to the MySQL servers is "too much", I update the number of connections to the runtime_mysql_servers to a number like 40.   I assume that kills frontend and backend connections and 60 losers (100 - 40 == loser_count) connected to the frontend returns an error? 

We very much need multiplexing to scale up the front end connections,  Can you describe in general terms what happens in a multiplexing environment where the number of backend connections are dynamically reduced?

Thank you for your time. 

[Prime Minister of Fun]

I imply some mistaken assumptions the way that backend connection pooling works.  But, maybe it's reducing max_connections is sufficient.

[René Cannaò]

Answers inline

On Fri, Sep 23, 2022, 23:43 Prime Minister of Fun <mpa...@edify.cx> wrote:

Hi Rene, 

Thanks for you quick reply.

Currently, servers in the mysql_servers table are configured to have max_connections = 100. Per the documentation, that's maximum backend connections.

Correct. The maximum number of connections that that proxysql instance will create.

If the load to the MySQL servers is "too much",

I don't like this approach.

Proxysql should protect your database server in a proactive way, not reactive.

Sure proxysql can be reconfigured at runtime and can be tuned during emergencies, but you should try to configure proxysql in a way to prevent emergencies (be proactive), not react to them.

I update the number of connections to the runtime_mysql_servers to a number like 40. 

I assume you meant you update mysql_servers and then LOAD TO RUNTIME . Table runtime_mysql_servers is read only.

 I assume that kills frontend and backend connections and 60 losers (100 - 40 == loser_count) connected to the frontend returns an error? 

No. Connections in use cannot be killed simply tuning max_connections. When connections are returned to the connections pool, if the total number of connections exceeds max_connections then the connections are dropped (when returned to the connections pool).

We very much need multiplexing to scale up the front end connections,  Can you describe in general terms what happens in a multiplexing environment where the number of backend connections are dynamically reduced?

I think I just described it.

To reword it:

* free connections may be dropped "immediately" (there is a small delay because this is an internal  "maintenance" task)

* used connections are not affected. Once they become free, they may be dropped if max_connections is exceeded.

Thank you for your time. 

You are welcome.

I want to point again that you should generally avoid the load to happen.

If the load already happened you enter in emergency mode, and you may need to kill connections (not just reduce max_connections), hoping this won't cause even more load on the mysql server (just to make an example: rolling back transactions is a very expensive process).

On Friday, September 23, 2022 at 2:15:11 PM UTC-7 rene....@gmail.com wrote:

ProxySQL is meant to protect the database servers behind it: that said, "fixing your environment" is not the comment you will get :-)

ProxySQL has a lot of features to achieve its goal of protecting the database from being overloaded: the first feature is limiting the number of backend connections.

Clients can create a lot of connections to proxysql, and proxysql can limit the connections to the backend to a way smaller value.

Tuning mysql_servers.max_connections is the first step in this journey.

Thanks,

René

On Fri, 23 Sept 2022 at 23:06, Prime Minister of Fun <mpa...@edify.cx> wrote:

Hi all,

Per the title, I'm load testing proxysql and have come across a likely scenario in my environment where applications connecting to ProxySQL can overload all available backends, taking all database service offline.

Before comments about fixing my environment, that's not the point.  The point is, I'd like ProxySQL to reject connections when the backend databases hit some kind of defined limits.

I assume I have to write a script scraping the MySQL backends and then updating the frontend.  But, not sure what settings to update ProxySQL to reject more clients and queries.

Have a great weekend.

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/e880a95b-9af6-4dea-9154-2bb7d3861fccn%40googlegroups.com.

--
You received this message because you are subscribed to the Google Groups "proxysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to proxysql+u...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/proxysql/2657b576-27e5-435d-996c-fa3c100054d8n%40googlegroups.com.