Running ProxySQL as a Kubernetes Service
This is the first installment of a multi-part series on introducing ProxySQL into Quizlet to help with database connection scaling. In this part, we focus on how we set up ProxySQL as a service in Google Kubernetes Engine (GKE).
At Quizlet, back to school season (BTS) starts a week before many US students return to school after summer break. This year is particularly interesting as many around the globe return to in-person learning after more than a year of distance learning.
In preparation for BTS 2021, the data engineering team at Quizlet identified connections between our web applications and the online transaction processing (OLTP) layer as a potential area for improvement, in particular how persistent connections between them are handled.
Handling Persistent Connections
The Quizlet web application is written in HackLang. It relies on the underlying HHVM to manage persistent connections to databases. Our database connection count hovers around 1500 during steady state. However, the connection count doubles or even triples during code deployment as we launch more Kubernetes pods. This runs the risk of exceeding the predefined maximum connection value. It led us to think about how to handle database connections better.
In the end, we implemented ProxySQL, an open source SQL proxy that optimizes database connections to overcome the persistent connection challenge.
Deployment Strategy
Quizlet runs on GKE and it is natural to host ProxySQL there too. Originally, we deployed ProxySQL using the RollingUpdate strategy. During testing, we discovered performance impact when old pods began to terminate immediately after new pods passed the readiness check.
We moved to blue/green deployments via ArgoCD before going live. This strategy has a 300 seconds scale down that allows new ProxySQL pods to warm up before the old ones are terminated.
ProxySQL Access
ProxySQL is only used by the web applications within GKE, therefore a cluster IP service is sufficient for pods to establish connection. Secondly, this gives our security team the confidence that ProxySQL is not reachable externally.
Beginning with ProxySQL 2.1, the software ships with a Prometheus exporter that exposes stats through the /metrics endpoint. Metrics like frontend free and used connections provide us vital information about whether the ProxySQL pods are under-resourced.
ProxySQL Pod Internals
ProxySQL enforces a one to one username to database relationship. The same username cannot be used by ProxySQL on two different databases. Hostgroups contain the association between usernames and databases.
This design allows ProxySQL to reroute traffic from replica to primary transparently when replication lag is above a predefined threshold.
We built our ProxySQL pods with three containers
- ProxySQL
- Vault agent
- Consul Template
Database usernames and passwords are stored in a HashiCorp Vault cluster, and database IPs and hostgroup information are stored in Consul. The ProxySQL configuration file is rendered by a Consul Template container with the help of a Vault agent. The generated file is placed on a shared disk volume accessible by the ProxySQL container and is used to start the ProxySQL service.
Next, we will discuss the steps taken to send traffic to ProxySQL during migration with zero downtime in the next part of the series.
