Scale MySQL beyond limits with ProxySQL
Submitted by Ratnadeep Debnath (@rtnpro) on Friday, 8 March 2019
Technical level: Intermediate
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.
- Basic understanding of ProxySQL
- Benchmarking ProxySQL
- Setting up a production ready ProxySQL, that scales
- 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
- Basic understanding of a web application stack: application server, database, etc.
- Connection pooling
- Kubernetes, AWS, RDS
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.