Setting up a ProxySQL Sidecar Container

Recently, a client of ours, Duolingo, was using Aurora was reaching the max connection limit of 16,000 (that is Aurora’s hard limit for all instance classes). In this case, Percona recommended implementing ProxySQL to manage connections (now their max connections only peak to 6000!).

Duolingo’s main application is run in AWS ECS (Elastic Container Service) so they decided to attach ProxySQL as a sidecar container to the main application container.

What is a Sidecar?

The sidecar design concept consists of a main application container plus a second container running a service to support, enhance, or extend the main container. The second container is “attached” to the main container, much like a sidecar attaches to a motorcycle.

The most common example you’ll likely find is a web server plus a log forwarding service (pictured above). The web server is the main container, the log forwarder is the sidecar, and in this case they actually share data (the volume is mounted to both containers). The web server writes the data, while the log forwarder just reads and sends it along (perhaps to some aggregation service like Splunk).

Sidecars don’t have to just be logs. It could be for monitoring or even a MySQL proxy…

Launch A ProxySQL Sidecar

Let’s set up a WordPress container (our main container) and a ProxySQL 2 sidecar with an Aurora cluster backend.

Prerequisites

  • Docker and Docker Compose installed.
  • An Aurora cluster (I used 2.07.0) with at least one writer and one reader instance.
  • A ProxySQL “monitor” user and “wordpress” user created on Aurora.

  • A “wordpress” database created on Aurora.

Configuration

1. Create a project directory and change into it.

2. Create a Dockerfile to build our sidecar.

3. Create our ProxySQL configuration file.

  • When adding your nodes to the mysql_servers, you must use the instance endpoint and not the Aurora reader/writer VIPs.
  • The reason is after a failover ProxySQL will move the individual instances around within the hostgroups to direct read/write traffic. It determines an instance’s role itself based on innodb_read_only and does not need to wait for Aurora to update the VIPs.
  • This is advantageous. Our client reported failovers taking as little as 15 seconds verse 30 – 60 seconds for Aurora’s DNS failovers.

4. Create our Docker Compose file to bring it all together.

  • This calls the “Dockerfile” to build our ProxySQL image, download the latest WordPress image, and start the containers.
  • WordPress depends_on ProxySQL. This instructs Docker Compose to start ProxySQL first. If WordPress launched first, it could not connect to Aurora.
  • We are using Docker’s internal naming to define the database endpoint. It’s simply the ProxySQL container name – WORDPRESS_DB_HOST: proxysql:6033
  • Take a look at the volumes mounted to each container. It is not required in this case, but I mounted the ProxySQL volume to the WordPress container as well.

5. Now we’re ready to bring up the containers. This will take several minutes as Docker needs to download and build our images.

Review

Once finished, check that the containers are up.

Then visit your WordPress site! http://127.0.0.1:8080/

Summary

The sidecar design pattern is an easy solution to SPOF (single point of failure) because each application container gets its very own ProxySQL. For applications that are already containerized this is a simple and effective approach. Using Kubernetes? You can launch a sidecar container into the same pod as your application container.

Lastly, there is an official ProxySQL Docker image on Docker Hub you can pull instead of building your own!

Duolingo is the most popular language-learning platform worldwide, with over 300 million total learners. Their mission is to make education free and accessible to everyone. They offer 94 total courses across 38 distinct languages, from the world’s top most-spoken languages to endangered languages like Hawaiian, Navajo, and Scottish Gaelic. Duolingo is available on iOS, Android and web at www.duolingo.com.

Share this post

Comments (5)

  • Paul D Carlucci Reply

    ProxySQL is poster child for a use case for the ambassador design pattern sidecar.

    March 23, 2020 at 10:40 am
  • JZ Reply

    How do you end managing proxySQL configuration?

    May 10, 2020 at 2:50 pm
    • Jake Davis Reply

      Hi JZ,

      The ProxySQL configuration file can be kept with the application container files/application repository (rather than kept with ProxySQL). This allows you to keep service specific ProxySQL configs with that application. Then, when building the Dockerfile, the correct proxysql.cnf is mounted.

      The proxysql.cnf (and all application files) can be kept in git for version control.

      May 19, 2020 at 8:26 am
  • foreversrikar Reply

    Can we create give a read only privilege for monitor user? or does it need write access? I already have a read only user so want to make use of it instead of creating new users. Pls let me know. TIA

    May 29, 2020 at 3:53 pm
  • foreversrikar Reply

    We don’t actually have a replication setup. Its just a single node RDS instance without replication and other features enabled.

    May 29, 2020 at 3:56 pm

Leave a Reply