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

Article
12/02/2024

This article demonstrates how to boost the performance of an Azure Database for MySQL Flexible Server instance 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. Currently, with an Azure free account, you can try Azure Database for MySQL - Flexible Server free for 12 months. For more information, see Use an Azure free account to try Azure Database for MySQL - Flexible Server for free.

Create an Azure Database for MySQL Flexible Server instance using Quickstart: Create an instance of Azure Database for MySQL with the Azure portal
or Quickstart: Create an instance of Azure Database for MySQL - Flexible Server by using the Azure CLI if you don't have one.
Configure networking settings of the Azure Database for MySQL Flexible Server instance 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 Use MySQL Workbench with Azure Database for MySQL - Flexible Server 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

To create a cache, sign in to the Azure portal. On the portal menu, select Create a resource.
On the Get Started pane, enter Azure Cache for Redis in the search bar. In the search results, find Azure Cache for Redis, and then select Create.

On the New Redis Cache pane, on the Basics tab, configure the following settings for your cache:

Setting	Action	Description
Subscription	Select your Azure subscription.	The subscription to use to create the new instance of Azure Cache for Redis.
Resource group	Select a resource group, or select Create new and enter a new resource group name.	A 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 of 1 to 63 characters that contains only numbers, letters, and hyphens. The name must start and end with a number or letter, and it can't contain consecutive hyphens. Your cache instance's host name is `\<DNS name>.redis.cache.windows.net`.
Location	Select a location.	An Azure region that is near other services that use your cache.
Cache SKU	Select a SKU.	The SKU determines the size, performance, and feature parameters that are available for the cache. For more information, see Azure Cache for Redis overview.
Cache size	Select a cache size.	For more information, see Azure Cache for Redis overview.

Select the Networking tab or select Next: Networking.
On the Networking tab, select a connectivity method to use for the cache.
Select the Advanced tab or select Next: Advanced.
On the Advanced pane, verify or select an authentication method based on the following information:
- By default, for a new Basic, Standard, or Premium cache, Microsoft Entra Authentication is enabled and Access Keys Authentication is disabled.
- For Basic or Standard caches, you can choose the selection for a non-TLS port.
- For Standard and Premium caches, you can choose to enable availability zones. You can't disable availability zones after the cache is created.
- For a Premium cache, configure the settings for non-TLS port, clustering, managed identity, and data persistence.
Important

For optimal security, we recommend that you use Microsoft Entra ID with managed identities to authorize requests against your cache if possible. Authorization by using Microsoft Entra ID and managed identities provides superior security and ease of use over shared access key authorization. For more information about using managed identities with your cache, see Use Microsoft Entra ID for cache authentication.
(Optional) Select the Tags tab or select Next: Tags.
(Optional) On the Tags tab, enter a tag name and value if you want to categorize your cache resource.
Select the Review + create button.

On the Review + create tab, Azure automatically validates your configuration.
After the green Validation passed message appears, select Create.

A new cache deployment occurs over several minutes. You can monitor the progress of the deployment on the Azure Cache for Redis Overview pane. When Status displays 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 fetches 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='<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()

Use Redis with PHP

Install PHP on your local environment. Follow the steps below to write a PHP script that caches a SQL query from the Azure Database for MySQL Flexible Server database. Here are a few prerequisites before running the script:

Install and enable Redis PECL extension to use Azure Cache for Redis with your PHP script. See how to install the extension locally
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);

Use 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 might 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 step

performance best practices

Share via