Introduction: Boosting MySQL database performance
Follow step-by-step instructions to boost the performance of your applications.
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.
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 for Redis 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.
Figure 1. Implementation of Cache-Aside with Amazon ElastiCache for Redis
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.
This tutorial is divided into five short modules. You must complete each module in order before moving on to the next one.
- Create a Redis Cluster (10 minutes): Set up your first Redis Cluster, and configure its node type and security group
- Create a MySQL database (20 minutes): Create a RDS/MySQL database instance and your MySQL table
- Populate your MySQL database (10 minutes): Connect, populate, and run your first MySQL table with a pre-populated SQL script
- Caching and Best Practices (60 minutes): You will learn two techniques for storing and retrieving data
- Cleanup (10 minutes): You will learn how to delete your Redis Cluster and your MySQL database