An overview of change data capture with LeapDB
LeapDB supports change data capture of changes from MySQL into what are called materialized view logs. Materialized view logs contain the row change information captured by the binary log and additional LeapDB metadata. The materialized view logs can be used to examine the changes to a table that have been captured, process them with an external tool, or maintain incrementally refreshable materialized views using LeapDB. Materialized view logs are regular MySQL tables which can be queried directly.
Changes are captured by the LeapDB change data capture daemon which runs in the background on the LeapDB server. This change data capture daemon is written using Debezium, an open source change data capture utility but no Kafka broker is required. Instead of capturing changes into Kafka, LeapDB captures them into materialized view logs on the LeapDB server. It is possible to capture changes from database servers other than the LeapDB server. This allows the creation of 'remote' materialized views, that is a materialized view that is the output of a remote query. This is functionally equivalent to replication of the source table.
In order to capture changes from a table, the table must be registered with LeapDB using the CREATE MATERIALIZED VIEW LOG command. Materialized view logs can be created on remote tables, which form the basis for remote materialized views.
Please see this video demo of change data capture for more information:
I use my benchmark queries on the BTS Ontime data set in a materialized view context and compare performance of the queries when using the materialized view against querying the base table.
A comparison between InnoDB and WARP on two decades of the BTS ontime flight data, a 109 column wide table.