[GitHub] [shardingsphere] xunyard commented on issue #8736: Support mysql jdbc's loadbalance

[GitBox <gi...@apache.org>]

xunyard commented on issue #8736:
URL: https://github.com/apache/shardingsphere/issues/8736#issuecomment-753619937


   > Hi @xunyard ,
   > 
   > Currently, we suggest users achieve the load-balance of Sharding Proxy by `LVS` or `F5` . On the other hand, I am interested in your approach. Could you provide a more detailed log about the exception when the configuration is `mysql:loadbalance`?
   > I am considering how to load balance leveraging the power of the database. Do you think PostgreSQL has a similar JDBC driver property?
   
   `F5` was first used to make HA for sharding-proxy, but some problems happened. F5 was gave up, because we are not familiar with it, at the same time without enough time. 
   
   The problem when using F5 is：
   - sometimes error may occur when executing simple query SQL such as `select * from table-xxx`.
   
   Error occur for sharding-proxy [follows](https://github.com/apache/shardingsphere/blob/master/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/communication/jdbc/connection/BackendConnection.java) line 93. Just forbidden swith schema during transaction process.
   
   I thought this maybe some mistake between mysql's loading-balance strategy for switch schema, so follow stuff found:
   
   - mysql's loading balance proxy [LoadBalancedConnectionProxy](https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/user-impl/java/com/mysql/cj/jdbc/ha/LoadBalancedConnectionProxy.java) (line 602) will try to pick new connection after `commit` or `rollback` is called.
   - sync will be executed when `target` connection chosen during [pick](https://github.com/mysql/mysql-connector-j/blob/release/8.0/src/main/user-impl/java/com/mysql/cj/jdbc/ha/MultiHostConnectionProxy.java) (line 405) process.
   - autocommit flag still be `false` when `sync` method mentioned above is under executing, with 'change schema' followed.
   
   conclusion:
   1. `autocommit` status sync maybe drawback for loading-balance switch, or maybe the real pick could happen after a complete transaction and so on. 
   2. mysql server can accept the logic of switching schema during transcation, so just considering the possibility of supporting it (I don't know about PostgreSQL, so please give your viewpoint).
   
   Also, I will try for find more kind of adjust in client side, maybe loading-balance stratgy or other stuff. Hope for your point.


----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

For queries about this service, please contact Infrastructure at:
users@infra.apache.org