Create a load balance method as previously discussed that allows load balancing and rotation per query/transaction versus the current method which is based on connection.

This concern master/slave configuration. ("jdbc:mariadb:replication://" or "jdbc:mariadb:aurora://")

MySQL connector implementation for information :

Initially creating a master / random slave socket.
After each commit, rotating slave socket: used slave socket is put in a cache, and if not created a new socket to a new slave is created and will be used next transaction.

example: 20 connections with a configuration for 4 slaves.

For one connection:

• when created: a creation of 2 sockets (to the master and to slave1)
• after the 1st transaction, a new socket to slave2 will be created. socket to slave1 will be cached.
• after the 2nd transaction, a new socket to slave3 will be created. socket to slave2 will be cached with slave1 socket.
• after the 3rd transaction, a new socket to slave4 will be created. socket to slave3 will be cached.
• after the 4th transaction, reusing slave1 socket.

This is a straightforward implementation, but have the big disadvantage of resulting of having a lot of sockets, consuming resources unnecessarily.
Having so many connections will impact the servers.
Creating connection takes resources, impacting servers in case of failover will take huge

Design of this task:

Maintaining a pool of slave socket for all connections.
When creating a connection, only create a master socket and send an event to the slave pool that will ensure having a socket to all slaves and repartition.
When querying on a slave and for the time of a transaction, the connection will use next slave available socket.

advantages :

• not creating useless slave sockets. Less socket means better failover time (and server CPU).
• slave pool can have a mechanism to validate other sockets to the same host in case of socket error detection.

disadvantage:

• means more work to implement.