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://")
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
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.
- 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.
- means more work to implement.