Ability to control auto_increment_delay_multiplex and connection_delay_multiplex_ms per server or user

[banpei-dbart]

As I described in my update of Multiplexing documentation, the mysql-auto_increment_delay_multiplex and mysql-connection_delay_multiplex_ms are global settings that change the behaviour on all connection within ProxySQL 1.4.14 and higher. Changing these settings with the intention to fix multiplexing a mis-behaving ORM or application means all other servers/hostgroups/users will have to suffer.

When multiplexing in ProxySQL is working less efficiently, this means more backend connections will be opened and less backend connections being reused. This causes a performance impact on both ProxySQL and the MySQL servers it's connecting to due to it opening (way) more connections than necessary .

As ProxySQL is intended and designed to be able to proxy multiple users over many topologies, I would like to request the ability to control this per user or server as well. This would prevent a huge performance penalty if there is one misbehaving application.

[renecannao]

This should be an extension (2 more columns) on mysql_query_rules . We can't add columns in mysql_query_rules in 2.0.x without breaking ProxySQL Cluster, so this feature requests cannot be implemented before 2.1

[renecannao]

Created milestone 2.1 , and associated this issue to it.

[banpei-dbart]

Thanks Rene! :)
How would this work with the query rules then? We're not using any rules at the moment, so would we need to create a catch-all rule for a specific username or schemaname then?

[renecannao]

yes, it would be that simple as creating a rule matching a specific username or schemaname, or even a query pattern, or a client's comment

[banpei-dbart]

I gave it a little bit more thought and I'm not convinced this to solve the issue entirely.

Imagine setting the multiplex delay on a particular user to the default 5.
This would allow the following queries to be run:
INSERT INTO ... VALUES (...); SELECT LAST_INSERT_ID();
ProxySQL will disable multiplexing after the insert for five consecutive queries and still retrieve the correct last inserted identifier because it's the first query after the insert.

But it wouldn't resolve the following case:
INSERT INTO ... VALUES (...); SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT 1; SELECT LAST_INSERT_ID();
ProxySQL will disable multiplexing after the insert for five consecutive queries, so after the fifth select it would enable multiplexing once again. This means the SELECT LAST_INSERT_ID wouldn't return the correct value anymore. And it wouldn't work for the mysql-connection_delay_multiplex_ms either once the number of milliseconds has exceeded. If I would set the delay to 10 queries (just to be sure) it could potentially disable multiplexing on 99% of my insert queries because I want to catch them all. If I have an insert-heavy application this means I will never be able to use multiplexing.

Adding these variables to the query_rules table feels to me like a solution where I have to set a lot of exceptions. It means that if I have one misbehaving application I need to start creating query rules and keep tuning it until it's matching the behaviour of the application. Also if I have one query rule that sets the delay to 0 queries, would this all of a sudden allow multiplexing again? It sounds to me it's just getting over complicated due to being a fix upon a fix.

Now ProxySQL is intercepting the OK packet with the last inserted identifier to trigger disabling multiplexing on the connection. ProxySQL is already aware of which ingress connection has triggered the auto increment to increase as it has to lock the frontend connection to the backend connection for x-consecutive queries or milliseconds. So why not store the last inserted identifier from the OK packet and tie this to the ingress connection? Once this connection runs a query containing the LAST_INSERT_ID() function, it will simply return the last inserted identifier for that particular connection. This would make it still possible to perform multiplexing without going through too many hoops while retaining compatibility with the function.

[pgporada]

I just read this blog post (which pointed me here) and am hoping this can make it into ProxySQL 2.1.