Edit

Tutorial: Boost performance of Azure Database for MySQL - Flexible Server with Azure Cache for Redis

  • Article

APPLIES TO: Azure Database for MySQL - Flexible Server

This article demonstrates how to boost the performance of an Azure Database for MySQL flexible server using Azure Cache for Redis. Azure Cache for Redis is a secure data cache and messaging broker that provides high throughput and low-latency access to data for applications.

Prerequisites

For this quickstart you need:

  • An Azure account with an active subscription.

    If you don't have an Azure subscription, create an Azure free account before you begin. With an Azure free account, you can now try Azure Database for MySQL - Flexible Server for free for 12 months. For more information, see Try Flexible Server for free.

  • Create an Azure Database for MySQL - Flexible Server using Azure portal
    or Azure CLI if you don't have one.

  • Configure networking settings of Azure Database for MySQL - Flexible Server to make sure your IP has access to it. If you're using Azure App Service or Azure Kubernetes service, enable Allow public access from any Azure service within Azure to this server setting in the Azure portal.

Having issues? Let us know

Populate the MySQL database

Connect to MySQL Server using MySQL Workbench and run the following query to populate the database.

CREATE DATABASE tododb;

CREATE TABLE tasks
(
	id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
	title nvarchar(100) NOT NULL,
	completed TINYINT(1) NOT NULL
);

INSERT INTO tasks (id,title, completed) VALUES
(1,'Task1', 0),
(2,'Task2', 0),
(3,'Task3', 1),
(4,'Task4', 1),
(5,'Task5', 0);

Create a Redis cache

  1. To create a cache, sign in to the Azure portal and select Create a resource.

    Create a resource is highlighted in the left navigation pane.

  2. On the New page, select Databases and then select Azure Cache for Redis.

    On New, Databases is highlighted, and Azure Cache for Redis is highlighted.

  3. On the New Redis Cache page, configure the settings for your new cache.

    Setting Choose a value Description
    Subscription Drop down and select your subscription. The subscription under which to create this new Azure Cache for Redis instance.
    Resource group Drop down and select a resource group, or select Create new and enter a new resource group name. Name for the resource group in which to create your cache and other resources. By putting all your app resources in one resource group, you can easily manage or delete them together.
    DNS name Enter a unique name. The cache name must be a string between 1 and 63 characters that contain only numbers, letters, or hyphens. The name must start and end with a number or letter, and can't contain consecutive hyphens. Your cache instance's host name will be <DNS name>.redis.cache.windows.net.
    Location Drop down and select a location. Select a region near other services that will use your cache.
    Cache type Drop down and select a tier. The tier determines the size, performance, and features that are available for the cache. For more information, see Azure Cache for Redis Overview.

  4. Select the Networking tab or select the Networking button at the bottom of the page.

  5. In the Networking tab, select your connectivity method.

  6. Select the Next: Advanced tab or select the Next: Advanced button on the bottom of the page.

  7. In the Advanced tab for a basic or standard cache instance, select the enable toggle if you want to enable a non-TLS port. You can also select which Redis version you would like use, either 4 or 6.

    Redis version 4 or 6.

  8. In the Advanced tab for premium cache instance, configure the settings for non-TLS port, clustering, and data persistence. You can also select which Redis version you would like use, either 4 or 6.

  9. Select the Next: Tags tab or select the Next: Tags button at the bottom of the page.

  10. Optionally, in the Tags tab, enter the name and value if you wish to categorize the resource.

  11. Select Review + create. You're taken to the Review + create tab where Azure validates your configuration.

  12. After the green Validation passed message appears, select Create.

It takes a while for the cache to create. You can monitor progress on the Azure Cache for Redis Overview page. When Status shows as Running, the cache is ready to use.

Use Redis with Python

Install the latest version of Python on your local environment or on an Azure virtual machine or Azure App Service. Use pip to install redis-py.

pip install redis

The following code creates a connection to Azure Cache for Redis instance using redis-py, stores the query result into the Azure Cache for Redis and fetch the value from the cache.

import redis
import mysql.connector

r = redis.Redis(
    host='your-azure-redis-instance-name.redis.cache.windows.net',
    port=6379, 
    password='azure-redis-primary-access-key')
    
mysqlcnx = mysql.connector.connect(user='your-admin-username', password='db-user-password',
                              host='database-servername.mysql.database.azure.com',
                              database='your-databsae-name')
			      
mycursor = mysqlcnx.cursor()
mycursor.execute("SELECT * FROM tasks where completed=1")
myresult = mycursor.fetchall()

#Set the result of query in a key 
if result:
          cache.hmset(mykey, myresult)
          cache.expire(mykey, 3600)
      return result
      
#Get value of mykey
getkeyvalue= cache.hgetall(mykey)

#close mysql connection
mysqlcnx.close()

Using Redis with PHP

Install PHP on your local environment. Follow the steps below to write a PHP script that caches a SQL query from MySQL database. Here are a few pre-requisites before running the script:

  1. Install and enable Redis PECL extension to use Azure Cache for Redis with your PHP script. See how to install the extension locally
  2. Install and enable MySQL PDO extension
<?php

$redis = new Redis();
$redis->connect('azure-redis-instance-ame.redis.cache.windows.net', 6379);
$redis->auth('azure-redis-primary-access-key');

$key = 'tasks';

if (!$redis->get($key)) {
    /*Pulling data from MySQL database*/
    $database_name     = 'database-name';
    $database_user     = 'your-database-user';
    $database_password = 'your-database-password';
    $mysql_host        = 'database-servername.mysql.database.azure.com';

    $pdo = new PDO('mysql:host=' . $mysql_host . '; dbname=' . $database_name, $database_user, $database_password);
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql  = "SELECT * FROM tasks";
    $stmt = $pdo->prepare($sql);
    $stmt->execute();

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
       $tasks[] = $row;
    }

    $redis->set($key, serialize($tasks));
    $redis->expire($key, 10);

} else {
     /*Pulling data from Azure Cache for Redis*/
     $tasks = unserialize($redis->get($key));

}

echo $source . ': <br>';
print_r($tasks);

Using Redis with WordPress

The benefit of enabling Azure Cache for Redis instance to your WordPress application will allow you to deliver content faster since all of the WordPress content is stored in the database. You can cache content that is mostly read only from WordPress database to make the query lookups faster. You can use either of these plugins to setup Redis. Install and enable Redis PECL extension. See how to install the extension locally or how to install the extension in Azure App Service.

Install Redis Object cache and activate this plugin on our WordPress application. Now update the wp-config.php file right above the statement / That's all, stop editing! Happy blogging. /*

define( 'WP_REDIS_HOST', 'azure-redis-servername.redis.cache.windows.net' );
define( 'WP_REDIS_PORT', 6379 );
define( 'WP_REDIS_PASSWORD', 'azure-redis-primary-access-key' );
define( 'WP_REDIS_TIMEOUT', 1 );
define( 'WP_REDIS_READ_TIMEOUT', 1 );

// change the database for each site to avoid cache collisions
// values 0-15 are valid in a default redis config.
define( 'WP_REDIS_DATABASE', 0 );

// automatically delete cache keys after 7 days
define( 'WP_REDIS_MAXTTL', 60 * 60 * 24 * 7 );

// bypass the object cache, useful for debugging
// define( 'WP_REDIS_DISABLED', true );

/* That's all, stop editing! Happy blogging. */

Go to the Wordpress admin dashboard and select the Redis settings page on the menu. Now select enable Object Cache. Plugin will read the Azure Cache for Redis instance information from wp-config.php file.

You may also use W3 Total cache to configure Azure Cache for Redis on your WordPress app. You can evaluate the performance improvements using Query Monitor plugin, which allows you to debug database queries and it also shows total database queries grouped by a plugin.

Next steps

In this quickstart, you learned how to create an instance of Azure Cache for Redis and use it with Azure database for MySQL. See performance best practices for Azure database for MySQL.