Boosting MySQL database performance

with Amazon ElastiCache (Redis OSS)

Introduction: Boosting MySQL database performance

Follow step-by-step instructions to boost the performance of your applications.

Overview

Relational databases are a cornerstone of most applications. When it comes to scalability and low latency though, there’s only so much you can do to improve performance. Even if you add replicas to scale reads, there’s a physical limit imposed by disk based storage. The most effective strategy for coping with that limit is to supplement disk-based databases with in-memory caching.

The ElastiCache (Redis OSS) node and the MySQL database created in this tutorial are eligible for the AWS Free Tier.

What You Will Learn

In this tutorial, you will learn how to boost the performance of your applications by adding an in-memory caching layer to your relational database. You will implement a cache-aside strategy using Amazon ElastiCache (Redis OSS) on top of a MySQL database. The cache-aside strategy is one of the most popular options for boosting database performance. When an application needs to read data from a database, it first queries the cache. If the data is not found, the application queries the database and populates the cache with the result. There are many ways to invalidate the cache if the relevant records are modified in the underlying database, but for this tutorial we will use the Time To Live (TTL) expiration feature provided by Redis.

Application Architecture

 AWS Experience

Beginner to Intermediate

 Time to Complete

120 minutes

 Cost to Complete

Free Tier eligible

 Tutorial Prereqs

  • AWS Account with administrator-level access**
  • Recommended browser: The latest version of Chrome or Firefox

[*]This estimate assumes you follow the recommended configurations throughout the tutorial and terminate all resources within 2 hours. 
[**]Accounts created within the past 24 hours might not yet have access to the services required for this tutorial.

 

Implementation of Cache-Aside with Amazon ElastiCache for Redis

Figure 1. Implementation of Cache-Aside with Amazon ElastiCache (Redis OSS)

Prerequisite

Please allocate 20 minutes to complete the prerequisite section. 

This tutorial illustrates some mechanisms with examples written in Python to illustrate some caching techniques. Hopefully the code will be easy to translate to your language of choice.

In order to complete this tutorial, you need access to an EC2 instance. If you don’t already have one running, follow these instructions to provision one.

Once you have access to your EC2 instance, run the following commands: 

$ sudo yum install git -y
$ sudo yum install mysql -y
$ sudo yum install python3 -y
$ pip3 install --user virtualenv
$ git clone https://github.com/aws-samples/amazon-elasticache-samples/
$ cd amazon-elasticache-samples/database-caching
$ virtualenv venv
$ source ./venv/bin/activate
$ pip3 install -r requirements.txt

Now you are all set to start the tutorial.

Modules

This tutorial is divided into five short modules. You must complete each module in order before moving on to the next one.

  1. Create a Redis Cluster (10 minutes): Set up your first Redis Cluster, and configure its node type and security group
  2. Create a MySQL database (20 minutes): Create a RDS/MySQL database instance and your MySQL table
  3. Populate your MySQL database (10 minutes): Connect, populate, and run your first MySQL table with a pre-populated SQL script
  4. Caching and Best Practices (60 minutes): You will learn two techniques for storing and retrieving data
  5. Cleanup (10 minutes): You will learn how to delete your Redis Cluster and your MySQL database

Create a Redis Cluster