How Selective Row Replication Works: MySQL to TiDB

One of our esteemed customers, a prominent player in the realm of video streaming services, faced a unique challenge. Tasked with delivering an exclusive feature tailored for Platinum subscription holders, their MySQL database was straining under the weight of resource-intensive aggregate queries.

A separate dashboard, log streaming, and other essential features were in demand, making it clear that a more robust solution was needed.

We have created a TiDB cluster and configured the TiDB cluster as a replica for MySQL.

Detailed architecture can be referred from this post

Challenges

The adoption of TiDB brought about a significant improvement in query performance, thanks to its columnar facility, TiFlash.

However, the sheer volume of data, 5TB, to be replicated across various TiDB nodes for OLAP requests was proving to be a costly affair.

We uncovered a key feature in TiDB that proved to be useful, i.e., Row-Wise Filtered Replication

In TiDB DM, we have many filtering techniques while replicating the data. When we are replicating data in MySQL, MariaDB, Postgresql,etc we can filter the replication at the Database and Table level only.

In TiDB, we can filter the replication data at the row level and also filter the binlog events. So, in this blog, we will see how these filtering methods are possible by using TiDB.

The following example shows how this feature works

 

In the above diagram, we categorized users based on their usage plan, and when we replicate it from MySQL to TiDB, we only replicate the PREMIUM users.

The rows that have the PREMIUM user type replicate to the TiDB. This one done by the filter technique used in the DM task.

Samples

Inserting the data into the table

1. mysql> insert into details(id,name,age,user_type)values(1,'User1',22,'Premium'),(2,'User2',28,'regular'),(3,'User3',19,'Premium'),(4,'User4',21,'Trial');
2. Query OK, 4 rows affected (0.19 sec)
3. Records: 4  Duplicates: 0  Warnings: 0

Viewing the data in MySQL

1. mysql> select * from details order by id;
2. +----+-------+------+-----------+
3. | id | name  | age  | user_type |
4. +----+-------+------+-----------+
5. |  1 | User1 |   22 | Premium   |
6. |  2 | User2 |   28 | Regular   |
7. |  3 | User3 |   19 | Premium   |
8. |  4 | User4 |   21 | Trial     |
9. +----+-------+------+-----------+
10. 4 rows in set (0.01 sec)

This output has 4 rows; now let’s check on the TiDB side

1. MySQL [users]> select * from details;
2. +----+-------+------+-----------+
3. | id | name  | age  | user_type |
4. +----+-------+------+-----------+
5. |  1 | User1 |   22 | Premium   |
6. |  3 | User3 |   19 | Premium   |
7. +----+-------+------+-----------+
8. 2 rows in set (0.00 sec)

With this replication row filter, TiDB has only rows with user_type is PREMIUM.

Another feature of TiDB DM replication is we have filter based on binlog events, events such as deletes, drops can be avovied in replication.

The following are examples where we can have multiple filters,

Example of how deletes are not replicated to TiDB,

In upstream MySQL we are deleting all record from the table.

1. mysql> delete from details;
2. Query OK, 12 rows affected (0.09 sec)
3.  
4. mysql> select * from details;
5. Empty set (0.02 sec)

Now there are no records in the table and we are checking the TiDB cluster replica.

1. MySQL [users]> select * from details;
2. +----+-------+------+-----------+
3. | id | name  | age  | user_type |
4. +----+-------+------+-----------+
5. |  1 | User1 |   22 | Premium   |
6. |  3 | User3 |   19 | Premium   |
7. +----+-------+------+-----------+
8. 2 rows in set (0.00 sec)

There are no records deleted from the TiDB cluster. This one is done by the binlog filter configuration.

Conclusion

In conclusion, TiDB stands out with its native support for features like row-wise filtering, empowering users to optimize storage and compute costs significantly. By selectively replicating only the necessary rows for analytics, our client experienced tangible savings while ensuring seamless data accessibility.

Unlike other databases that often provide only table-level filtration, TiDB offers a superior level of flexibility with its granular row-wise filtering capabilities. This ensures that users have precise control over their data, facilitating more efficient and tailored data management strategies.

At Mafiree, we’re committed to providing comprehensive solutions tailored to your specific requirements. Our dedicated team of experts is poised to review your setup and offer customized solution architecture recommendations.

For any inquiries or assistance, please don’t hesitate to contact sales@mafiree.com. Your database challenges are our priority, and we’re here to help you navigate them effectively.