chris - Fotolia


Tune database query performance with Redis cache

To tune a database, start with query optimization and data model tuning. When those fail to deliver necessary query performance, turn to Redis cache.

Tuning a relational database to achieve high-query performance can be difficult. If optimizing a data model and...

tuning queries doesn't work, administrators can use a cache such as Redis, a key-value data store that offers in-memory and persistent data storage.

Because Redis has the ability to quickly read and write data to the data store, it has a performance advantage over relational databases. But key value data stores are simple; they don't have a SQL-like query language or structured data model. Instead, they contain a simple dictionary or hash model that uses keys as identifiers associated with values. Admins store and retrieve values using these keys.

Key value stores are simple and fast, which makes them a good match for rich data models and query capabilities of relational databases. Still, sometimes it's better to use a combination of key-value and relational databases. In addition, there are a number of commercially supported key value databases, including Redis, Riak, and Areospike.

To run a Redis cache to optimize the performance of popular queries, first identify query results you want to cache. Focus on most frequently used and time-consuming queries and then identify the data from the query you should cache. For simplicity, cache all column values that a query returns.

Define a naming convention for keys values; keys could be constructed using a combination of the row's primary key and a column name. For example, the description of product with primary key ID 198278 could be stored with key '198278:descr.' Keep your naming convention simple and rule-driven to enable programmatic key creation with minimal code.

Next, determine whether to run Redis cache as a self-managed service or run Amazon ElastiCache. Running your own Redis instance gives admins full control over the cache. And that control means flexibility, such as the ability to use existing Reserved Instances when there is excess capacity.

In addition, full administrative control is ideal when you want to move the application from one cloud provider to another. If you choose to run a self-managed Redis instance, download the server. Redis has clients for more than 30 languages -- from Java and Python to Prolog and Smalltalk.

Companies already committed to an AWS environment may want to use ElastiCache. In addition to advantages such as managed patching, Amazon ElastiCache supports a list of cache-optimized node types, including m3 nodes from medium to 2xlarge, r3 nodes from large to 8xlarge, and t2 nodes from micro to medium. ElastiCache also supports some previous-generation node types, such as select m1, m2, t1 and c1 nodes.

ElastiCache also supports multiple availability zones. If there is a node failure, a read replica node will replace the failed node. Any DNS changes needed to keep an application running are done automatically and a new read replica is created. ElastiCache allows on-demand pricing models based on node usage per hour, as well as upfront one-year or three-year node terms. A full pricing list is available here.

If you use Redis cache with Amazon ElastiCache, you can launch a cluster from the AWS Management Console. In addition to setting up a Redis service, you need to modify the application code to use the cache. A commonly used pattern is to check if a key exists in the cache and if not to execute a SQL query to retrieve the data and then store it in the cache. You can configure Redis to delete old data when the cache is full, so you do not need specialized code to handle a full cache.

Next Steps

Understanding the benefits of NoSQL databases on AWS

A look at handling data in the cloud

Dig Deeper on AWS database management