feature: Master slave synchronization - improve the synchronization performance of tianmu

[konghaiya]

Is your feature request related to a problem? Please describe.

At present, as a slave library, the tianmu has a large delay for dml modification. It is necessary to improve the synchronization performance to reduce the delay

Describe the solution you'd like

Describe alternatives you've considered

Additional context

[konghaiya]

At present, there are the following plans:

1. When synchronizing data, push down the conditions for delete and update. Only qualified data will be scanned to avoid full table scanning.
2. Improve the execution speed of (delete_row) and (update_row) functions. At present, it is considered to parallelize the modification of each column

[adofsauron]

1. In what scenarios can conditional push be performed? What are the specific grammar rules?

2. How much of a performance boost does conditional pushing bring? What are the estimates?

3. Expect the performance improvement of master-slave synchronization to be conditional push? What is the current performance bottleneck analysis for master-slave synchronization? How much of a performance bottleneck is present at which SQL executions? Why is there such a performance bottleneck? Why does conditional push solve these bottlenecks?

[konghaiya]

At present, the performance consumption point of synchronization lies in full table scanning. For example, a table has 5000 pieces of data, and a record needs to be synchronously changed. In the worst case, it needs to be scanned nearly 5000 times. This is totally unnecessary operation. We can use metadata to filter out the qualified data in the tianmu engine when the execution condition is delayed.
It is still in the process of investigation and research to try the feasibility of this scheme

[adofsauron]

 We can use metadata to filter out the qualified data in the tianmu engine when the execution condition is delayed.

Using metadata to filter out entries that match the criteria, and conditional pushing, are two different levels of abstraction. Why is it that metadata can only be used for filtering if conditional push is used, and why not if conditional push is not used?

[adofsauron]

At present, the performance consumption point of synchronization lies in full table scanning

At present, the performance consumption point of synchronization lies in full table scanning

So issuse is currently dealing with full table scans? Then investigate how many solutions there are to solve the full table scan. Don't decide directly what strategy to adopt. If you decide which strategy to adopt, give the reasons, why to adopt this strategy, and why this strategy can solve the problem.

[konghaiya]

This problem is obvious. When executing the push down condition, tianmu will use metadata to perform rough filtering to determine the qualified packages. In addition, when executing the push down condition, we only need to make a materialization comparison on the qualified records, instead of making a materialization comparison on the unqualified records.
How much can the performance be improved? The function needs to be tested after implementation

[konghaiya]

At present, the binlog row format master-slave performs conditional push down under the path of full table scanning synchronously. The performance is improved by more than three times, but there is still a big delay in the extreme cases. In the future, support optimization can be carried out for the primary key and hash comparison (INDEX_SCAN, HASH_SCAN) paths