All Products
Search

This Product

    ApsaraDB RDS:Persistence of cached data

    Document Center

    ApsaraDB RDS:Persistence of cached data

    Last Updated:Nov 22, 2024

    If you use ApsaraDB RDS for MySQL and you want to accelerate data processing and reduce access latency, Alibaba Cloud provides an efficient solution for persistence of cached data. This solution combines ApsaraDB RDS with Tair (Redis OSS-compatible) and ApsaraDB for Memcache to ensure high-speed access and persistent storage. This solution features high throughput and low latency.

    Background information

    Tair (Redis OSS-compatible) and ApsaraDB for Memcache have the following advantages over ApsaraDB RDS:

    • Tair (Redis OSS-compatible) and ApsaraDB for Memcache can respond to queries at higher speeds. In most cases, the latency per query is within a few milliseconds.

    • The caches of Tair (Redis OSS-compatible) and ApsaraDB for Memcache support higher queries per second (QPS).

    Procedure

    Combine ApsaraDB RDS for MySQL with Tair (Redis OSS-compatible)

    Prerequisites

    • An Elastic Compute Service (ECS) instance, a Tair (Redis OSS-compatible) instance, and an ApsaraDB RDS for MySQL instance are created.

      • If you want to connect to these instances over an internal network, we recommend that you create the instances in the same virtual private cloud (VPC).

      • If you have created an ECS instance, a Tair (Redis OSS-compatible) instance, and an RDS instance but these instances reside in different VPCs, you can enable public endpoints for the Tair (Redis OSS-compatible) instance and the RDS instance. This way, you can connect to the instances over the Internet.

      Note

      • In this example, the image version of the ECS instance is Alibaba Cloud Linux 3.2104 LTS 64-bit.

      • In this example, the programming language is Python. Before you start, install Python 3, Pip 3, PyMySQL, and redis-py on the ECS instance.

        Run the following commands to install PyMySQL and redis-py:

        sudo pip3 install pymysql
sudo pip3 install redis

    • IP address whitelists are configured.

    • Usernames and passwords of database accounts are obtained for the Tair (Redis OSS-compatible) instance and the RDS instance. For more information, see Create and manage database accounts and Create an account on an ApsaraDB RDS for MySQL instance.

    Procedure

    1. Log on to the ECS instance and write a Python script to simulate a business scenario. If no data is found in the cache of the Tair (Redis OSS-compatible) instance, query data from the RDS instance. In this example, the Python script named test.py is created.

      Warning

      This section provides the sample code for demonstration purposes. In the actual business code, do not set the user and password parameters to values in plaintext. We recommend that you configure the parameters by using methods such as external configuration files and environment variables and then reference the parameters in the code. 

      import json
import redis
import pymysql

# Define the parameters that are used to connect to the RDS instance.
mysql_host = '<Endpoint of the RDS instance>'
mysql_user = '<Username>'
mysql_password = '<Password>'
mysql_port = 3306
mysql_charset = 'utf8'

# Define the parameters that are used to connect to the Tair (Redis OSS-compatible) instance.
redis_host = '<Endpoint of the Tair (Redis OSS-compatible) instance>'
redis_port = 6379
# The password for the Tair (Redis OSS-compatible) instance is in the Username:Password format. If no password is not configured, set the redis_password parameter to an empty string.
redis_password = '<Password for the Tair (Redis OSS-compatible) instance>'
redis_db = 0


def create_database_and_tables():
    db = pymysql.connect(host=mysql_host,
                         user=mysql_user,
                         password=mysql_password,
                         port=mysql_port,
                         charset=mysql_charset)
    cursor = db.cursor()

    # Create a test database.
    cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")

    # Select a database.
    cursor.execute("USE testdb;")

    # Create a test table.
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS student (
        s_id INT AUTO_INCREMENT PRIMARY KEY,
        s_name VARCHAR(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """)

    # Insert data for testing.
    cursor.execute("""
    INSERT INTO student (s_name) VALUES
    ('Zhangsan'),
    ('Lisi'),
    ('Wangwu')
    ON DUPLICATE KEY UPDATE s_name = VALUES(s_name);
    """)

    db.commit()
    cursor.close()
    db.close()


def fetch_from_mysql():
    db = pymysql.connect(host=mysql_host,
                         user=mysql_user,
                         password=mysql_password,
                         database="testdb",
                         port=mysql_port,
                         charset=mysql_charset)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM student")
    rows = cursor.fetchall()
    cursor.close()
    db.close()
    return rows


def cache_to_redis(redis_client, key, data):
    # Encode the data into JSON strings to store data of complex data types.
    json_data = json.dumps(data)
    # Store data to the Tair (Redis OSS-compatible) instance and set the validity period to 600 seconds, which is equivalent to 10 minutes.
    redis_client.setex(key, 600, json_data)


def get_from_redis(redis_client, key):
    # Query data from the Tair (Redis OSS-compatible) instance.
    json_data = redis_client.get(key)
    if json_data:
        # If data is found, decode the JSON strings.
        data = json.loads(json_data)
        return data
    else:
        return None


def main():
    # Create a client for the Tair (Redis OSS-compatible) instance.
    redis_client = redis.StrictRedis(
        host=redis_host,
        port=redis_port,
        password=redis_password,
        db=redis_db,
        decode_responses=True  # Automatically decode response data.
    )

    # Create a test table for the RDS instance and insert test data into the table.
    create_database_and_tables()

    # Define the key for storing student information in the Tair (Redis OSS-compatible) instance.
    redis_key = 'students'

    # Query data from the Tair (Redis OSS-compatible) instance.
    students = get_from_redis(redis_client, redis_key)

    if students:
        print("Query data from the Tair (Redis OSS-compatible) instance.")
        print(students)
    else:
        print("No data is found in the Tair (Redis OSS-compatible) instance. Query data from the RDS instance.")
        # Query data from the RDS instance.
        students = fetch_from_mysql()
        if students:
            print(students)
            # Cache data to the Tair (Redis OSS-compatible) instance.
            cache_to_redis(redis_client, redis_key, students)


if __name__ == '__main__':
    main()

    2. Run test.py. 

      python3 test.py

      • If this is the first time you run test.py, data is read from the RDS instance because no data is found in the cache of the Tair (Redis OSS-compatible) instance. Sample output:

        No data is found in the Tair (Redis OSS-compatible) instance, but data is found in the RDS instance.
((1, 'Zhangsan'), (2, 'Lisi'), (3, 'Wangwu'))

      • If this is not the first time you run test.py, data is read from the Tair (Redis OSS-compatible) instance because the result of the previous query is cached to the Tair (Redis OSS-compatible) instance. Sample output:

        Data queried from the Tair (Redis OSS-compatible) instance:
[[1, 'Zhangsan'], [2, 'Lisi'], [3, 'Wangwu']]

    Combine ApsaraDB RDS for MySQL with ApsaraDB for Memcache

    Prerequisites

    • An ECS instance, an ApsaraDB for Memcache instance, and an RDS instance are created. Make sure that these instances are created in the same VPC.

      Note

      • You must make sure that these instances are created in the same VPC because ApsaraDB for Memcache does not support connections over the Internet.

      • In this example, the image version of the ECS instance is Alibaba Cloud Linux 3.2104 LTS 64-bit.

      • In this example, the programming language is Python. Before you start, install Python 3, Pip 3, PyMySQL, and python-memcached on the ECS instance.

        Run the following commands to install PyMySQL and python-memcached:

        sudo pip3 install pymysql
sudo pip3 install python-memcached

    • The CIDR block of the VPC is added to the IP address whitelists of the ApsaraDB for Memcache instance and the RDS instance. For more information, see Configure an IP address whitelist and Configure an IP address whitelist.

    • The username and password of a database account are obtained for the RDS instance. For more information, see Create an account on an ApsaraDB RDS for MySQL instance.

    • The password-free access is enabled for the ApsaraDB for Memcache instance. For more information, see Password-free access.

    Procedure

    1. Log on to the ECS instance and write a Python script to simulate a business scenario. If no data is found in the cache of the ApsaraDB for Memcache instance, query data from the RDS instance. In this example, the Python script named test.py is created.

      Warning

      This section provides the sample code for demonstration purposes. In the actual business code, do not set the user and password parameters to values in plaintext. We recommend that you configure the parameters by using methods such as external configuration files and environment variables and then reference the parameters in the code. 

      import json
import pymysql
import memcache

# Define the parameters that are used to connect to the RDS instance.
mysql_host = '<Endpoint of the RDS instance>'
mysql_user = '<Username>'
mysql_password = '<Password>'
mysql_port = 3306
mysql_charset = 'utf8'

# Define the parameters that are used to connect to the ApsaraDB for Memcache instance.
memcache_host = '<Endpoint of the ApsaraDB for Memcache instance>:<Port>'


def create_database_and_tables():
    db = pymysql.connect(host=mysql_host,
                         user=mysql_user,
                         password=mysql_password,
                         port=mysql_port,
                         charset=mysql_charset)
    cursor = db.cursor()

    # Create a test database.
    cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")

    # Select a database.
    cursor.execute("USE testdb;")

    # Create a test table.
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS student (
        s_id INT AUTO_INCREMENT PRIMARY KEY,
        s_name VARCHAR(255) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """)

    # Insert data for testing.
    cursor.execute("""
    INSERT INTO student (s_name) VALUES
    ('Zhangsan'),
    ('Lisi'),
    ('Wangwu')
    ON DUPLICATE KEY UPDATE s_name = VALUES(s_name);
    """)

    db.commit()
    cursor.close()
    db.close()


def fetch_from_mysql():
    db = pymysql.connect(host=mysql_host,
                         user=mysql_user,
                         password=mysql_password,
                         database="testdb",
                         port=mysql_port,
                         charset=mysql_charset)
    cursor = db.cursor()
    cursor.execute("SELECT * FROM student")
    rows = cursor.fetchall()
    cursor.close()
    db.close()
    return rows


def cache_to_memcache(memcache_client, key, data):
    # Encode the data into JSON strings to store data of complex data types.
    json_data = json.dumps(data)
    Store data to the ApsaraDB for Memcache instance and set the validity period to 600 seconds, which is equivalent to 10 minutes.
    memcache_client.set(key, json_data, time=600)


def get_from_memcache(memcache_client, key):
    # Query data from the ApsaraDB for Memcache instance.
    json_data = memcache_client.get(key)
    if json_data:
        # If data is found, decode the JSON strings.
        data = json.loads(json_data)
        return data
    else:
        return None


def main():
    # Create a client for the ApsaraDB for Memcache instance.
    memcache_client = memcache.Client([memcache_host], debug=0)

    # Create a test table for the RDS instance and insert test data into the table.
    create_database_and_tables()

    # Define the key for storing student information in the ApsaraDB for Memcache instance.
    memcache_key = 'students'

    # Query data from the ApsaraDB for Memcache instance.
    students = get_from_memcache(memcache_client, memcache_key)

    if students:
        print("Query data from the ApsaraDB for Memcache instance.")
        print(students)
    else:
        print("No data is found in the ApsaraDB for Memcache instance. Query data from the RDS instance.")
        # Query data from the RDS instance.
        students = fetch_from_mysql()
        if students:
            print(students)
            # Cache data to the ApsaraDB for Memcache instance.
            cache_to_memcache(memcache_client, memcache_key, students)


if __name__ == '__main__':
    main()

    2. Run test.py. 

      python3 test.py

      • If this is the first time you run test.py, data is read from the RDS instance because no data is found in the cache of the ApsaraDB for Memcache instance. Sample output:

        No data is found in the ApsaraDB for Memcache instance, but data is found in the RDS instance.
((1, 'Zhangsan'), (2, 'Lisi'), (3, 'Wangwu'))

      • If this is not the first time you run test.py, data is read from the ApsaraDB for Memcache instance because the result of the previous query is cached to the ApsaraDB for Memcache instance. Sample output:

        Data queried from the ApsaraDB for Memcache instance:
[[1, 'Zhangsan'], [2, 'Lisi'], [3, 'Wangwu']]

    References