1

Right now I have set the backend as a managed instance group with "N" machines. Where N in 2 to 5. Inside each machine is installed a ProxySQL.

As persistence layer, I have set up a Master/Slave configuration with CloudSQL where M is the numer of the slaves M in 1 to 3.

At this point, the requirement is to load balancing queries between the multiple replica that i can spinup for heavy load from the MIG and the MIG ProxySQL need to be reconfigured to bind to new created slaves .

For example, at 5PM i am going to face high traffic for my webservices and i will need to create more SQL slaves to accept more SQL connection. So I will create multiple slave replication. M increasing from 1 to 3, N increasing from 2 to 4.

Now I'm trying to get the best solution for this purpose. One possible solution that I found is to use ProxySQL but with some heavy limitation for the MIG.

The GPC MIG is stateless, and actually doesn't know how many replicas are up and running. Plus when a new MIG instance is created how can it get the new configuration if not all the SQL replicas are yet running?

What is the best implementation for discovery the number of possible healthy SQL replicas and reconfigure the ProxySQL configuration (runtime/disk/memory), maybe with a custom polling of 5 seconds where the VM is asking for new configuration.

Are available already some implementation? What is the best solution? I have found some links but not the best slution for me.

Thanks in advance.

Ruben M
  • 89
  • 7
jacorl
  • 396
  • 2
  • 9
  • 1
    1) Your idea will not work in production without major issues. 2) Implement connection management at the client. 3) Do not spin up DB read replicas like they are autoscaling VMs. If your database is so small that you can quickly spin up replicas, then you do not need read replicas. Just cache the database in memory at the master. – John Hanley Jul 23 '21 at 17:08
  • Thanks for the response. I didn't go in deep detail for DB autoscaling. What you explain is totally correct. The purpose of this task is to not affect client code in application. ProxySQL is the perfect tool for balancing the query in my SQL architecture. Please, can you explain why this solution with MIG and ProxySQL doesn't work? – jacorl Jul 27 '21 at 07:10

0 Answers0