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).
Combine ApsaraDB RDS for MySQL with Tair (Redis OSS-compatible)
Combine ApsaraDB RDS for MySQL with ApsaraDB for Memcache
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
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
mysql_host = '<Endpoint of the RDS instance>'
mysql_user = '<Username>'
mysql_password = '<Password>'
mysql_port = 3306
mysql_charset = 'utf8'
redis_host = '<Endpoint of the Tair (Redis OSS-compatible) instance>'
redis_port = 6379
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()
cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")
cursor.execute("USE testdb;")
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;
""")
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):
json_data = json.dumps(data)
redis_client.setex(key, 600, json_data)
def get_from_redis(redis_client, key):
json_data = redis_client.get(key)
if json_data:
data = json.loads(json_data)
return data
else:
return None
def main():
redis_client = redis.StrictRedis(
host=redis_host,
port=redis_port,
password=redis_password,
db=redis_db,
decode_responses=True
)
create_database_and_tables()
redis_key = 'students'
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.")
students = fetch_from_mysql()
if students:
print(students)
cache_to_redis(redis_client, redis_key, students)
if __name__ == '__main__':
main()
Run 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']]
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
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
mysql_host = '<Endpoint of the RDS instance>'
mysql_user = '<Username>'
mysql_password = '<Password>'
mysql_port = 3306
mysql_charset = 'utf8'
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()
cursor.execute("CREATE DATABASE IF NOT EXISTS testdb;")
cursor.execute("USE testdb;")
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;
""")
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):
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):
json_data = memcache_client.get(key)
if json_data:
data = json.loads(json_data)
return data
else:
return None
def main():
memcache_client = memcache.Client([memcache_host], debug=0)
create_database_and_tables()
memcache_key = 'students'
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.")
students = fetch_from_mysql()
if students:
print(students)
cache_to_memcache(memcache_client, memcache_key, students)
if __name__ == '__main__':
main()
Run 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']]