Hi Istvan, thanks for your reply. The client-side load balance would help with the stickiness problem but that means that:
- We need to expose our deployment topology (URL to individual nodes) to dozens of consumers in our company, creating a maintenance burden unless we build additional URL discovery tooling specifically for this use case. - We need to continue to store the connection/statements state in each individual node memory. The problem is that our default runtime platform assumes that nodes are stateless in order to significantly simplify the deployment and disaster recovery operations. While there is precedence for deviating towards a stateful model, we would like to continue to follow the paved road of stateless services for our own convenience. That being said, is there a way we can implement a server for a Calcite Avatica driver that does not depend on the state for storing connections or statements to work correctly? On Thu, Jan 11, 2024 at 7:01 PM Istvan Toth <st...@cloudera.com> wrote: > You could use the new client side load balancing feature in Avatica, which > solves the stickiness problem. > > See https://issues.apache.org/jira/browse/CALCITE-5581 > > On Thu, Jan 11, 2024 at 7:31 AM Sophie Wang <swa...@atlassian.com.invalid> > wrote: > >> Hi Calcite Devs, >> >> Happy new year! Hope you’re all well. >> >> I have a question related to the Avatica Driver server implementation >> (specifically about caching in Meta / JdbcMeta components). >> >> https://github.com/apache/calcite-avatica/blob/78c5f54280b2fe3813aea0cbd9fbb7faf39[ >> …]ver/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java >> >> We are implementing a JDBC Query Proxy microservice using Avatica >> components. However, our end-to-end querying (i.e. client using Avatica >> Driver -> Query Proxy -> JDBC Driver -> Database Server) does not work if >> our Query Proxy is deployed multiple nodes, and throws >> NoSuchConnectionException. (Querying works fine if the proxy is only >> deployed on 1 node) >> >> We use the JdbcMeta as per the snippet below: >> >> > val meta = JdbcMeta("jdbc:mysql://{databaseUrl}:{port}/{schema}", >> > properties) >> > val service = LocalService(meta) >> > return LocalProtobufService(service, protobufTranslation()) >> >> >> And, when querying the Proxy, we receive NoSuchConnectionException if the >> proxy is hosted on 2 nodes, due to the connectionCache in the Meta: >> >> https://github.com/apache/calcite-avatica/blob/78c5f54280b2fe3813aea0cbd9fbb7faf39[ >> …]ver/src/main/java/org/apache/calcite/avatica/jdbc/JdbcMeta.java. >> The Proxy will have 1x JdbcMeta per node (and therefore 1x connectionCache >> and 1x statementCache per node), for a total of 2x connectionCache and 2x >> statementCache objects across the proxy. >> >> Thus, upon querying with JDBC: >> >> - The client sends an OpenConnectionRequest with connectionId abcde and >> hits node1 , and the connection is stored in node1's connectionCache >> - The ConnectionSyncRequest for the same connectionId abcde hits node2 >> , >> due to load balancing. This connectionId does not exist in node2's >> connectionCache, it is in node1’s connectionCache, and we receive >> NoSuchConnectionException. >> >> Would you have any recommendations on how to solve this problem without >> using sticky sessions? Apache Druid implements their own DruidMeta class, >> which has a similar connectionCache/statementCache implementation to >> JdbcMeta in the Avatica Server dependency >> >> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/avatica/DruidMeta.java#L101 >> . >> But they seem to solve this multi-node problem with connection stickiness >> via the router >> < >> https://github.com/apache/druid/blob/master/sql/src/main/java/org/apache/druid/sql/avatica/DruidMeta.java#L101 >> > >> . >> >> Thank you so much for your time, it's greatly appreciated! Please let me >> know if you have questions or want more details :) >> >> Warm regards, >> Sophie >> > > > -- > *István Tóth* | Sr. Staff Software Engineer > *Email*: st...@cloudera.com > cloudera.com <https://www.cloudera.com> > [image: Cloudera] <https://www.cloudera.com/> > [image: Cloudera on Twitter] <https://twitter.com/cloudera> [image: > Cloudera on Facebook] <https://www.facebook.com/cloudera> [image: > Cloudera on LinkedIn] <https://www.linkedin.com/company/cloudera> > ------------------------------ > ------------------------------ > -- Thanks, Mike Dias
