ProxySQL for Procedure call routing

Polyglot Projects ProxySQL
, ,
1

Our application deals only via procedure, we are planning to use proxysql to route read traffic to secondary node, we are using standard master-slave replication. We have started Test, but we are seeing some issue , sometime its routing sometime not,

  1. MySQL connecter sending SET user-defined variables = null ,
  2. we have defined query rule for procedure. but still sometime going to master node
  3. query_lock_to_hostgroup tried both 0 and 1

Can someone please guide how to use proxysql for procedures

2

This is most likely causing ProxySQL to route to the source. Can you remove these as a test?

According to the documentation, setting this to 0 should force query routing.

Make sure you are not reusing connections. If your connector is also doing connection pooling, this will cause problems. Do not pool connections to a connection pooler.

3

We are using java connection pool, is it required to removed ?

4

Required, no. Recommended, yes. You should not connection pool in front of a connection pooler. If you are using the Java connection pooler, then why use proxysql?

5

We want to load balance DB traffic, want to divert the read traffic to according to query rules.