Shadow DB #

Under distributed application architecture based on microservices, businesses require multiple services to be completed through a series of services and middleware calls. The pressure testing of a single service can no longer reflect the real scenario.

In the test environment, the cost of rebuilding a complete set of pressure test environments similar to the production environment is too high. It’s usually impossible to simulate the complexity and data of the production environment.

This means that it’s generally better to use the production environment for pressure tests. The test results obtained accurately reflect the real capacity and performance of the system.

Overview #

Based on the kernel’s SQL parsing capability and pluggable platform architecture, shadow DB enables the isolation of pressure test data from production data, helps applications to automatically route, and supports full-link pressure testing. It helps users to perform pressure testing in production environments to obtain a more accurate system real capacity level and performance test results.

SphereEx-DBPlusEngine focuses on database-level solutions for full-linked pressure testing scenarios, with automatic routing of pressure test data to user-specified databases being the main design goal of the SphereEx-DBPlusEngine Shadow Database plug-in.

Full-linked pressure testing is a complex and large task. It requires cooperation and adjustment between various microservices and middleware to cope with different traffic volumes and transparent transmission of stress test identifiers.

Core Concept #

• Production Database

The database is used for production data.

• Shadow Database

The database for pressure testing data isolation.

Applicable Scenarios #

Under microservices-based distributed application architecture, in order to improve the accuracy of system stress testing and reduce testing costs, stress testing is usually chosen to be conducted in the production environment, and the risks in testing are greatly increased. With the SphereEx-DBPlusEngine shadow database plugin capability, combined with the flexible configuration of the shadowing algorithm, the real throughput capacity of the system can be obtained, while data contamination can be avoided to meet the online stress testing requirements of complex business scenarios.

Usage Prerequisites #

• SphereEx-DBPlusEngine and the database cluster are installed on the server and the service is running properly.
• SphereEx-Console has been installed and deployed to keep track of SphereEx-DBPlusEngine load in real-time during the pressure test (optional).
• For press test scenarios where Hint needs to be triggered artificially, the MySQL client needs to use the -c parameter to enable annotation.

Usage Restrictions #

Hint-based shadowing algorithm

• None

Column-based shadowing algorithm

• Not supported.

  • DDL is not supported.

  • Range, grouping and subqueries such as BETWEEN, GROUP BY ... HAVING etc. are not supported.

• Support：

  • INSERT

  	SQL	Supported
  1	INSERT INTO table (column,…) VALUES (value,…)	Y
  2	INSERT INTO table (column,…) VALUES (value,…),(value,…),…	Y
  3	INSERT INTO table (column,…) SELECT column1 from table1 where column1 = value1	N

  • SELECT/UPDATE/DELETE

  	Condition	SQL	Supported
  1	=	SELECT/UPDATE/DELETE … WHERE column = value	Y
  2	LIKE/NOT LIKE	SELECT/UPDATE/DELETE … WHERE column LIKE/NOT LIKE value	Y
  3	IN/NOT IN	SELECT/UPDATE/DELETE … WHERE column IN/NOT IN (value1,value2,…)	Y
  4	BETWEEN	SELECT/UPDATE/DELETE … WHERE column BETWEEN value1 AND value2	Y
  5	GROUP BY … HAVING…	SELECT/UPDATE/DELETE … WHERE … GROUP BY column HAVING column > value	N
  6	Sub Query	SELECT/UPDATE/DELETE … WHERE column = (SELECT column FROM table WHERE column = value)	N

Notes #

When using a shadow database pressure testing solution, the high availability of the business system needs to be fully considered, so it is recommended to deploy multiple SphereEx-DBPlusEngine nodes for pressure testing to avoid a single point of failure. For an application access solution, you can choose to use connection pooling to configure multiple SphereEx-DBPlusEngine addresses or use other mature load-balancing solutions.

Introduction to the Principle #

SphereEx-DBPlusEngine determines the shadowing of incoming SQL by parsing the SQL and routing the request to the production or shadow repository based on the shadowing rules set by the user in the configuration file.

The shadowing algorithm is closely related to business implementation and currently offers a total of two types of shadowing algorithms, column-based and Hint-based.

• Column-based shadowing algorithm: scenarios where the data in SQL is identified and matched for routing to a shadow database. It’s suitable for pressure testing scenarios driven by pressure testing data lists and supports precision matching of field values and regular matching of field values.

• Hint-based shadowing algorithm: matching and routing shadow databases by identifying commentsin SQL. Suitable for pressure testing scenarios driven by upstream system pass-through identification.

Take an INSERT statement. For example, when writing data, SphereEx-DBPlusEngine will parse SQL and then construct a routing chain based on the rules in the configuration file. In the current version of the feature, the shadow function is in the last execution unit in the routing chain, i.e. if other rules that require routing exist, such as a sharding scenario, SphereEx-DBPlusEngine will first route to a particular database according to the sharding rules, and then perform a shadow routing determination process to determine that the execution SQL satisfies the configuration of the shadow rules and the data is routed to the corresponding shadow database and the production data remains unchanged.

For DML statements, SphereEx-DBPlusEngine supports both column-based and Hint-based shadowing algorithms.

Shadowing determines if there is an intersection between the table associated with the execution SQL and the configured shadow table. If there is an intersection, the shadowing algorithm associated with the shadowed table in the intersection is determined in turn, with any of the determinations being successful, and the SQL statement is routed to the shadowed database. If the shadow table does not have an intersection or the shadow algorithm is unsuccessful, the SQL statement is routed to the production database.

For DDL statements, SphereEx-DBPlusEngine only supports the Hint shadowing algorithm. In a pressure testing scenario, DDL statements generally do not need to be tested. It is mainly used when initializing or modifying shadow tables in the shadow database. The shadowing decision will first determine if the execution SQL contains annotations. If it contains annotations, the HINT shadowing algorithm configured in the shadowing rule determines them in turn. If any of the determinations are successful, the SQL statement is routed to the shadow database. If the execution SQL does not contain annotations or the HINT shadowing algorithm is unsuccessful, the SQL statement is routed to the production database.

Usage Guide #

Based on the production environment, several SphereEx-DBPlusEngines will need to be deployed to carry business traffic, depending on the size of the pressure test. You will also need to deploy a set of shadow repositories with the same configuration and architecture as the production repository to host the pressure test data.

1. Environment Validation

Before using the SphereEx-DBPlusEngine read/write separation plugin, you need to do a more comprehensive confirmation of the current environment, such as the configuration information of the production and shadow databases, the database software version information, and the network latency from the application server to the production and shadow databases.

2. Rule confirmation

Select the appropriate column-based or Hint-based algorithm, or both, according to the pressure test content and the shadow database usage limit.

3. Enable SQL comment parsing (optional)）

If you want to use the Hint-based shadow database algorithm, you need to configure it in ./conf/server.yaml, you need to change sqlCommentParseEnabled parameter to true and restart SphereEx-DBPlusEngine after the change.

If the parameters are not configured correctly, SphereEx-DBPlusEngine will not be able to process statements with Hint and the pressure test data will be routed directly to the production repository, so please make sure to configure and validate carefully. After the configuration is complete, you can verify the route by using the PREVIEW command, refer to the shadow configuration validation instructions.

4. Data Source Confirmation and Registration

Confirm the connectivity of all production and shadow data sources in turn, which can be visited and confirmed at the node where SphereEx-DBPlusEngine is located. In SphereEx-DBPlusEngine, register the data sources with the REGISTER STORAGE UNIT command.

5. Creating Shadow Database Rules

When creating a shadow database rule, configure it according to the rules planned in step 2 and select the appropriate shadow database algorithm or a mix of both shadow database algorithms.

6. Shadow Configuration Validation

Use the PREVIEW SQL command to verify the SQL routing. This command does not actually execute the SQL, but only confirms the execution path. If the shadow database rule does not take effect, double-check the shadow database rule configuration details, the pressed SQL features, and the sqlCommentParseEnabled configuration. Please make sure the shadow rule is correct before proceeding to the full-link pressure test process.

7. Modify the application URL

Modify the access information of the database in the application to the access address of SphereEx-DBPlusEngine, which can be done through connection pool configuration or other load-balancing solutions.

8. Enter the pressure testing process

At the appropriate time period, use the test tool to start a full-link test of the system. The test process requires real-time observation of the SphereEx-DBPlusEngine load through the SphereEx-Console.

9. Modify the application URL again

After the full link pressure test is completed, the database access information in the application can be modified back to the database address to restore the system topology.

10. Shadow database processing

Finally, depending on the usage requirements, the test data in the shadow database can be retained or cleaned up. If you need to clean up, please make sure the database you access is a shadow database before cleaning up to avoid damage caused by misuse of the production database.

Operation Guide #

1. Prepare a SphereEx-DBPlusEngine instance, the deployment operation will not be demonstrated, prepare the shadow database.
2. Complete the creation of the logical repository in SphereEx-DBPlusEngine.
3. Add the data source and complete the cluster build.
4. Create shadow database rules and create shadow tables.
5. Verify the shadowing rules are in effect.

Configuration examples #

Environment Description #

In this example, 3 test machines are used, 1 SphereEx-DBPlusEngine instance, and 2 MySQL instances are deployed.

Topology Diagram #

Configuration Process #

1. Prepare the production and shadow database data sources

Create the ds_product data source to simulate the production database, and create the t_user table and insert a number of records.

mysql -uroot -p -h192.168.xx.103 -P3306

CREATE DATABASE IF NOT EXISTS ds_product;
Query OK, 1 row affected (0.01 sec)

USE ds_product;
Database changed

CREATE TABLE t_user
(
    mobile char(11) PRIMARY KEY,
    status varchar(18)  NOT NULL,
    type   varchar(255) NOT NULL
);
Query OK, 0 rows affected (0.11 sec)

SHOW TABLES;
+----------------------+
| Tables_in_ds_product |
+----------------------+
| t_user               |
+----------------------+
1 row in set (0.00 sec)

INSERT INTO t_user (mobile, status, type)
VALUES (18099515621, 1, 'ds_product'),
       (15639784703, 1, 'ds_product'),
       (15716172114, 1, 'ds_product'),
       (18766747515, 1, 'ds_product'),
       (18099515602, 1, 'ds_product'),
       (15639784713, 1, 'ds_product'),
       (18099515622, 1, 'ds_product'),
       (15639784513, 1, 'ds_product'),
       (15716173114, 1, 'ds_product'),
       (18766746515, 1, 'ds_product');
Query OK, 10 rows affected (0.06 sec)
Records: 10  Duplicates: 0  Warnings: 0

SELECT * FROM t_user;
+-------------+--------+------------+
| mobile      | status | type       |
+-------------+--------+------------+
| 15639784513 | 1      | ds_product |
| 15639784703 | 1      | ds_product |
| 15639784713 | 1      | ds_product |
| 15716172114 | 1      | ds_product |
| 15716173114 | 1      | ds_product |
| 18099515602 | 1      | ds_product |
| 18099515621 | 1      | ds_product |
| 18099515622 | 1      | ds_product |
| 18766746515 | 1      | ds_product |
| 18766747515 | 1      | ds_product |
+-------------+--------+------------+
10 rows in set (0.00 sec)

Create a ds_shadow data source for the shadow database.

mysql -uroot -p -h192.168.xx.104 -P3306

CREATE DATABASE IF NOT EXISTS ds_shadow;
Query OK, 1 row affected (0.05 sec)

CREATE TABLE t_user
(
    mobile char(11) PRIMARY KEY,
    status varchar(18)  NOT NULL,
    type   varchar(255) NOT NULL
);
Query OK, 0 rows affected (0.11 sec)

2. Login to SphereEx-DBPlusEngine using the MySQL client and create the logical database

Note: Be sure to add the -c parameter for access to ensure that subsequent SQL with comments will take effect.

--Access to DBPlusEngine instances
mysql -uroot -p -P3307 -h192.168.xx.102 -c

CREATE DATABASE testdb;
Query OK, 0 rows affected (0.21 sec)

SHOW DATABASES;
5 rows in set (0.00 sec)

3. Add data sources and complete the cluster build

Register two database instances in SphereEx-DBPlusEngine.

USE testdb;
Database changed

REGISTER STORAGE UNIT ds_0 (
     URL="jdbc:mysql://192.168.xx.103:3306/ds_product?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
), ds_1 (
     URL="jdbc:mysql://192.168.xx.104:3306/ds_shadow?serverTimezone=UTC&useSSL=false",
     USER="test",
     PASSWORD="Test@123"
);
Query OK, 0 rows affected (0.05 sec)

SHOW STORAGE UNITS\G

4. Creating shadow database rules and creating shadow tables

Note: The shadow table is created here using the comment operation. Please make sure again that the session is opened with the -c parameter before creating the table.

--Create shadow database rules
CREATE SHADOW RULE shadow_rule(
    SOURCE=ds_0,
    SHADOW=ds_1,
    t_user(TYPE(NAME=VALUE_MATCH, PROPERTIES("operation"="insert","column"="status", "value"='2' )))
);
Query OK, 0 rows affected (0.20 sec)

This step creates a shadow database rule, which includes both value-based and hint-based shadow algorithm matching.

• Value-based matching: when the insert operation is performed, records with a status of 2 are written to the shadow database

SHOW SHADOW RULES;
+-------------+-------------+-------------+--------------+
| rule_name   | source_name | shadow_name | shadow_table |
+-------------+-------------+-------------+--------------+
| shadow_rule | ds_0        | ds_1        | t_user       |
+-------------+-------------+-------------+--------------+
1 row in set (0.10 sec)

5. Verify that shadow rules are in effect

First, verify that the shadow rules have taken effect through the PREVIEW command, and the data of the compound shadow library rules should be written to the ds_1 data source.

PREVIEW INSERT INTO t_user (mobile, status, type) VALUES (18236483857, 2, 'ds_shadow');
+------------------+--------------------------------------------------------------------------------+
| data_source_name | actual_sql                                                                     |
+------------------+--------------------------------------------------------------------------------+
| ds_1             | INSERT INTO t_user (mobile, status, type) VALUES (18236483857, 2, 'ds_shadow') |
+------------------+--------------------------------------------------------------------------------+
1 row in set (0.50 sec)

Based on the results above, it is confirmed that the two records of the composite shadow rule will be written to the shadow database as expected and the test data will be inserted in SphereEx-DBPlusEngine below.

--Querying the t_user table in the Proxy
SELECT * FROM t_user;
+-------------+--------+------------+
| mobile      | status | type       |
+-------------+--------+------------+
| 15639784513 | 1      | ds_product |
| 15639784703 | 1      | ds_product |
| 15639784713 | 1      | ds_product |
| 15716172114 | 1      | ds_product |
| 15716173114 | 1      | ds_product |
| 18099515602 | 1      | ds_product |
| 18099515621 | 1      | ds_product |
| 18099515622 | 1      | ds_product |
| 18766746515 | 1      | ds_product |
| 18766747515 | 1      | ds_product |
+-------------+--------+------------+
10 rows in set (0.01 sec)

--Test data based on shadow database column value algorithm
INSERT INTO t_user (mobile, status, type)
VALUES (18236483857, 2, 'ds_shadow'),
       (15686689114, 2, 'ds_shadow'),
       (14523360225, 2, 'ds_shadow'),
       (18143924353, 2, 'ds_shadow'),
       (15523349333, 2, 'ds_shadow'),
       (18143924153, 2, 'ds_shadow'),
       (15523349313, 2, 'ds_shadow'),
       (18143924253, 2, 'ds_shadow'),
       (15523349323, 2, 'ds_shadow'),
       (13261527931, 2, 'ds_shadow');
Query OK, 10 rows affected (0.06 sec)

mysql> SELECT * FROM t_user;
+-------------+--------+------------+
| mobile      | status | type       |
+-------------+--------+------------+
| 15639784513 | 1      | ds_product |
| 15639784703 | 1      | ds_product |
| 15639784713 | 1      | ds_product |
| 15716172114 | 1      | ds_product |
| 15716173114 | 1      | ds_product |
| 18099515602 | 1      | ds_product |
| 18099515621 | 1      | ds_product |
| 18099515622 | 1      | ds_product |
| 18766746515 | 1      | ds_product |
| 18766747515 | 1      | ds_product |
+-------------+--------+------------+
10 rows in set (0.01 sec)

As you can see, after inserting data that matches the shadow database, only the data from the production database is still available in SphereEx-DBPlusEngine, as expected.

Finally, the data is confirmed in the shadow database.

SELECT * FROM t_user;
+-------------+--------+-----------+
| mobile      | status | type      |
+-------------+--------+-----------+
| 13261527931 | 2      | ds_shadow |
| 14523360225 | 2      | ds_shadow |
| 15523349313 | 2      | ds_shadow |
| 15523349323 | 2      | ds_shadow |
| 15523349333 | 2      | ds_shadow |
| 15686689114 | 2      | ds_shadow |
| 18143924153 | 2      | ds_shadow |
| 18143924253 | 2      | ds_shadow |
| 18143924353 | 2      | ds_shadow |
| 18236483857 | 2      | ds_shadow |
| 18816175114 | 0      | ds_shadow |
+-------------+--------+-----------+
11 rows in set (0.00 sec)

The output confirms that data with a status of 2 and 0 can be seen and that the validation of the shadow database scenario is as expected.

FAQ #

1. Will the use of a shadow database pressure testing solution have any impact on the production database?

   No. There will be no impact. Provided the rules are correctly configured, the shadow database will isolate the test data and will not have any impact on the production database.

2. Is it possible to create two identical shadow rules?

   Not supported. Multiple shadow database rules can be created in SphereEx-DBPlusEngine, but they can not be applied to the same field.