Jdbc calls to Coordinator through haproxy or nginx tcp proxy

Like many other data bases where we use haproxy to load balance calls to multiple nodes. In case of Dremio where we have a topology of 3 coordinators, 1 master all of these using embedded ZK to coordinate the cluster. Since we have 3 coordinators to increase more concurrency and throughput mire importantly load balance jdbc calls, is it recommended to use ha proxy or nginx tcp proxy to connect to coordinator hosts using jdbc:dremio:direct protocol.

When we tried ZK based approach it slows down the over all performance. Not sure if it is due to embedded zk quorum. But embedded zk quorum works perfectly for cluster coordination between master and coordinator plus executors.

Are you saying you have 4 embedded zookeepers as you have 3 coordinators + master?
How do you set it up so it forms a quorum? Do you specify it in dremio.conf as a full quorum - like explicitly?
Apart from that just few points:

  1. Even number of zookeepers is not a good setup, as as soon as you lose two nodes (in your case) quorum won’t function (essentially 4 zookeepers is not better then 3)
  2. Have you tried external Zookeeper Quorum - as again it would not be dependent on coordinators going up and down.
  3. When you say then performance is worse with ZK approach. Does it really load balance for you?

Yes we configured in dremio conf as comma separated list of hosts port of other coordinator hosts and in executor nodes the host and port of all nodes where embedded zk enabled. It atleast starts up fine. Do you see any issue with this. We can start another coordinator to make odd quorum.

But my other question was about ha proxy based approach makes more sense to me than zk base. Do you see any issue. The zk based load balance was not round robin it used to swicth to next node only after connection pool recreated or session expired. Im looking for a more round robin approach to connect to coordinator nodes from jdbc.

On the client side when i use HikariCP as data source it complains that DremIO driver is not a proper type 4 driver and does not implement few methods like isValid setNetworkTimeout etc. Now question is if i dont use a connection pool and create connection everytime, what is the challenge. Does the sabot engine avatica capable of handling the connection if we create more connections per coordinator.

This is bit important to know so that we can set it up right and do some serious testing to see where it works best and where it fails.

I don’t think it does what you expect it to do: the embedded zookeeper server doesn’t support having a multi-nodes quorum and I believe you are actually ending up with N independent zookeeper servers. If you need HA, you should use a independent Zookeeper cluster.

If you use a connection pool, the connection pool might give you back the same connection again and again. Only when the connection pool would create a new connection (because all connections in the pool are borrowed) would you get a new connection which should be on a different node if your other coordinator nodes are registered correctly in zookeeper.

The JDBC spec doesn’t mandate for drivers to support isValid or setNetworkTimeout, and for example Dremio driver would throw SQLFeatureNotSupportedException when calling isValid(), which is conform to the JDBC specification (in 2.1, the driver would not throw anymore but check if the connection was closed). If you want to share the error message from HikariCP, we might be able to identify issues, but so far, the driver has been working fine with other frameworks or tools (Also note that most connection pool framework have alternative ways of checking connection validity by running test queries on borrow or return).
Finally using a connection pool is not mandatory by all means. The drawback would be a bit more use of system resources/memory (for the socket) and some extra latency when opening a new connection (for the handshake/authentication), but Dremio server itself should be able to support all these extra connections.

JDBC_00 - Cannot connect to specified database: com.streamsets.pipeline.api.StageException: JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: null

What does this mean?

It might be better asking to Streamsets as the error doesn’t show any Dremio error.