Sunday, December 2, 2018

Database Proxy

Database proxy is a middleware which once setup in place will ensure that all reads/writes from the application passes through it. It can serve following purposes.
1) Balancing the load due to queries being performed on database. In most setups, database slaves are used with a DNS. This doesn't help in balancing the queries which are being performed on the slaves. It has been observed that while one slave is heavily loaded, the other is almost idle which clearly indicates balancing is not done in the right way and overall performance of the read queries are also degrading than the resources being used.
2) Routing/Rejecting queries based on regex. This allows the Engineering team to have the capability of blacklist filters on certain clauses depending upon the current indexes in the table. This will ensure, queries executed from mysql cli client do not impact critical slaves. There is more that can be achieved with this feature.
3) Automatically shun slaves with replication lag ProxySQL automatically stops routing the queries to a slave that are facing lag. No more replication lag related bugs.


To achieve above requirements/goals, the ProxySQL should be used. ProxySQL is a high performance MySQL proxy that can scale up to hundreds of backend servers.



Application that want to leverage the proxy can use the Proxy ELB DNS in their applications rather than using the DNS. Database teams can setup the required Slave server host group.


Post a Comment