A solution would be to have some new "Session change type" SESSION_CONNECTION_COUNTER : When establishing a connection to maxscale, maxscale can send to connectors current number of connection in connection creation ending OK_Packet.

When using loadbalancing, connector could by default use a round-robin pattern and when having those informations, will connect to the less use maxscale by default. The advantage is when a failover occurs, and that maxscale is up again, connectors will then priorize new connections creation to this maxscale until number of connections reach other's.

Another option is to do what is planned for MDEV-15935 and just reserve a variable name for this purpose. This would be backwards compatible and would allow this to be emulated by applications as long as the connector supports session variable tracking.

Exactly, I was wondering how far this should be carried by connectors or maxscale. in fact the second solution seems more appropriate

This should be straightforward to implement:

• Extend mxs_mysql_create_ok() to support session state information.
• Add the current number of connections as the value of a custom session variable name (similar to last_gtid). The number of connections can be read from m_session->service->stats().n_current_conns().

A preliminary name for the variable could be _connections. This could also be used by the MariaDB server to indicate how many connections there are. This would give the connectors more information that they could base their load balancing decisions on.

Once this is implemented, implementing an initial version of the redirection mechanism described in MDEV-15935 would be easy: just add a new configuration variable that sets the redirection URL. This would not perform redirection while the sessions are open but in the general case where connections have a finite lifetime, this would be a interruption-free way of moving connections from once instance to another.

Attached a proof-of-concept patch for fake status changes for threads_connected (a status variable in MariaDB) which contains the number of current connections. I also added a version of the redirection URL into it since it was trivial to implement once the session state change tracker was done.

I'd rather do it on the server. It could set redirect_url when the number of connections grow above certain limit

MaxScale will now by default send the same values that are set as the default values of session_track_system_variables in the first OK packet: character_set_client, character_set_connection, character_set_results, time_zone. Additionally, the system_time_zone, max_allowed_packet and tx_isolation values are sent as well. The current connection count is also added as threads_connected along with the 64-bit connection ID as connection_id. The autocommit value is not sent since it's redundant as one of the server status bits is reserved for it.