Scale MySQL beyond limits with ProxySQL
Channel: Hasgeek TV
Date: 2019-06-24

At Zapier, we connect over 1000 SaaS applications and enable people to automate their workflows spanning across multiple web applications. This involves intensive database operations. We use RDS MySQL databases, and it’s really easy and addictive to scale up RDS., and that’s what we have been doing so far. We are already at db.m4.4xlarge. We have a plethora of services running in our infrastructure and making huge number of queries to our databases. There are times when our RDS instances gets bombarded with huge number of connections and it leads to high CPU & memory usage, thereby, degrading RDS performance, bringing our application to its knees. In our applications (Django app), we already had implemented database connection pooling to alleviate this issue, however, Django’s connection pooling is just limited to a single process, and does not work across multiple processes and servers. This is when, we started looking into database connection pooling and proxy solutions, in order to achieve 10x scale without any fundamental changes and keeping costs low.

It was then, we stumbled upon ProxySQL along with other solutions like HAProxy, Nginx, MaxScale, etc. and after doing our homework we chose to go ahead with ProxySQL. This talk will walk you through our exciting journey from choosing ProxySQL and taking it to production. Why we chose ProxySQL, how we benchmarked it, connecting the missing dots, what architecture we chose for production deployment and why, what were the challenges and how we worked around them, and finally the end results.

Key Takeaways
Basic understanding of ProxySQL
Benchmarking ProxySQL
Setting up a production ready ProxySQL, that scales
Outline
Setting up the scene: MySQL database flooded with connections, more than it can handle
Vision: Achieve 10x scale without 10x cost
An ideal solution?
Solutions available: ProxySQL, MaxScale, Nginx, HAProxy
Why ProxySQL?
Benchmarking ProxySQL
Conneting the missing dots
Chosing an architecture for deployment and why
Challenges and workarounds
The end result!
The Future

Ratnadeep Debnath AKA rtnpro works as a Site Reliability Engineer at Zapier. He has deep interest in Kubernetes and CNCF projects. He loves contributing to Open Source and mentoring people to start their Open Source journey. When he is not coding, he likes spending time with friends and family, practise Mantra Meditation or cooking.

Links
https://github.com/rtnpro
https://www.rtnpro.com
https://www.youtube.com/watch?v=yf5uXWoRJoo&index=1&list=PL4Aox7_vWyu-zYSgYkeo90cFrnwmeMSdd
https://www.youtube.com/watch?v=WMYnxaexmk4