A question regarding read-write splitting and health checks for the slave database

[limng06]

About Read-Write Splitting and Health Checks for the Slave Database

Description

The scenario is as follows: My database adopts a 1-master, multiple-slaves design. The connection to the master database uses a VIP (Virtual IP) address, which provides failover capability for the master database. However, the slave databases do not have the same failover capability. Therefore, I would like to implement a custom load-balancing algorithm for the slave databases that includes a health check mechanism, allowing for fault isolation and automatic recovery.

In practice, after reviewing the code, I realized that I need to obtain an instance of the ContextManager in order to check if the slave database connections are available. However, I found that there is no straightforward way to get this instance. The only option seems to be using reflection to retrieve the contextManager field from the ShardingSphereDataSource.

try (Connection conn = DriverStateContext.getConnection(slaveDataSource, contextManager)) {
    isHealthy = conn.isValid(2); // 2-second timeout
    return isHealthy;
}

<!-- https://mvnrepository.com/artifact/org.apache.shardingsphere/shardingsphere-jdbc -->
<dependency>
    <groupId>org.apache.shardingsphere</groupId>
    <artifactId>shardingsphere-jdbc</artifactId>
    <version>5.5.0</version>
</dependency>

Finally, I use DriverStateContext to determine whether the connection is available. Is there a better way to achieve this?

I sincerely seek your advice. I have also raised an issue regarding this.

[thicv]

@limng06
The open-source ShardingSphere does not provide the functionality for replica health checks. The commercial version by SphereEx has enhanced this aspect, offering automatic discovery of master-slave topology and routing based on the actual state of replicas. This ensures that SQL routes to nodes that are always live replicas. Additionally, SphereEx commercial version can prioritize routing based on the minimal delay time of the replicas.
ShardingSphere 开源版本应该没有提供从库健康检查的功能。SphereEx 的商业版本对这方面做了增强，不仅可以完成主从拓扑关系的自动发现，并根据从库的真实状态进行路由，保证 SQL 路由的节点始终是存活的从库，同时也可以根据从库的延迟最小时间优先路由。

[limng06]

@limng06 The open-source ShardingSphere does not provide the functionality for replica health checks. The commercial version by SphereEx has enhanced this aspect, offering automatic discovery of master-slave topology and routing based on the actual state of replicas. This ensures that SQL routes to nodes that are always live replicas. Additionally, SphereEx commercial version can prioritize routing based on the minimal delay time of the replicas. ShardingSphere 开源版本应该没有提供从库健康检查的功能。SphereEx 的商业版本对这方面做了增强，不仅可以完成主从拓扑关系的自动发现，并根据从库的真实状态进行路由，保证 SQL 路由的节点始终是存活的从库，同时也可以根据从库的延迟最小时间优先路由。

Thank you very much for the reply, it seems I'd better ensure availability from the database side.
非常感谢回复，看起来我最好还是从数据库这端保障可用性了。 帮我翻译成英语