anchor.diff:  ---
   layout: post
  -title:  Read-only Incremental Snapshots for MySQL connector
  +title:  Read-only Incremental Snapshots for MySQL
   date:   2022-03-25
   tags: [ mysql, snapshots ]
   author: kgalieva
   ---
   
  -The Debezium MySQL connector allows incremental snapshotting for databases without write access by the connector, which is required when pointing Debezium to read-only replicas. In addition, the Debezium MySQL connector allows schema changes during an incremental snapshot. This blog post explains the implementation details of those features.
  +The engineering team at Shopify recently improved the Debezium MySQL connector so that it supports incremental snapshotting for databases without write access by the connector, which is required when pointing Debezium to read-only replicas. In addition, the Debezium MySQL connector now also allows schema changes during an incremental snapshot. This blog post explains the implementation details of those features.
   
   +++<!-- more -->+++
   
   == Why read-only?
   
  -Debezium added the https://debezium.io/blog/2021/10/07/incremental-snapshots/[incremental snapshotting feature] in their 1.6 release after https://arxiv.org/pdf/2010.12597v1.pdf[Netflix announced their change data capture framework]. At Shopify, https://shopify.engineering/capturing-every-change-shopify-sharded-monolith[we use Debezium for change data capture (CDC)], and we were looking forward to being the early adopters. Besides, we wished to have a solution that is writes and locks-free.
  +Debezium added the link:/documentation/reference/stable/connectors/mysql.html#mysql-incremental-snapshots[incremental snapshotting feature] in the 1.6 release, after Netflix had announced https://arxiv.org/pdf/2010.12597v1.pdf[their change data capture framework]. At Shopify, https://shopify.engineering/capturing-every-change-shopify-sharded-monolith[we use Debezium for change data capture (CDC)], and we were looking forward to being the early adopters. Besides, we wished to have a solution that is writes and locks-free.
   
   The no writes solution allows to capture changes from read-replicas and provides the highest guarantee that CDC won't cause data corruption on the database side.
   
  -We’ve had to coordinate the snapshotting with migrations in the past since schema migrations blockades have affected other projects' development. The solution was to run snapshots only on weekends and as a result, we tried to snapshot as rarely as possible. We saw the opportunity to improve this part of the process as well.
  +We've had to coordinate the snapshotting with migrations in the past since schema migrations blockades have affected other projects' development. The solution was to run snapshots only on weekends and as a result, we tried to snapshot as rarely as possible. We saw the opportunity to improve this part of the process as well.
   
   This blog post dives into technical details of the read-only incremental snapshots implementation including lock-free schema changes handling during the incremental snapshot in MySQL connector.
   
   == Incremental snapshots
   
  -The https://debezium.io/blog/2021/10/07/incremental-snapshots/[Incremental Snapshots in Debezium] blog post covers the default implementation in detail. The algorithm utilizes a signaling table for two types of signals:
  +The link:/blog/2021/10/07/incremental-snapshots/[Incremental Snapshots in Debezium] blog post covers the default implementation in detail. The algorithm utilizes a signaling table for two types of signals:
   
   1. `snapshot-window-open/snapshot-window-close` as watermarks
   2. `execute-snapshot` as a way to trigger an incremental snapshot
   
  -We needed to replace both types of signals with alternatives.
  -
  -== SHOW MASTER STATUS for high and low watermarks
  -
  -The solution is MySQL specific and relies on https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html[the global transaction identifiers (GTIDs)]. Therefore, you need to set `gtid_mode` to `ON` and configure the database to preserve GTID ordering if you’re reading from the read replica.
  +For the read-only scenario, we needed to replace both types of signals with alternatives.
  +
  +=== SHOW MASTER STATUS for high and low watermarks
  +
  +The solution is specific to MySQL and relies on https://dev.mysql.com/doc/refman/8.0/en/replication-gtids-concepts.html[global transaction identifiers (GTIDs)]. Therefore, you need to set `gtid_mode` to `ON` and configure the database to preserve GTID ordering if you're reading from the read replica.
   
   Prerequisites:
  -[source,bash]
  +
  +[source]
   ----
   gtid_mode = ON
   enforce_gtid_consistency = ON
   if replica_parallel_workers > 0 set replica_preserve_commit_order = ON
   ----
   
  -The algorithm runs a https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html[SHOW MASTER STATUS] query to get the executed GTID set before and after the chunk selection.
  -[source,bash]
  +The algorithm runs a https://dev.mysql.com/doc/refman/8.0/en/show-master-status.html[SHOW MASTER STATUS] query to get the executed GTID set before and after the chunk selection:
  +
  +[source]
   ----
   low watermark = executed_gtid_set
   high watermark = executed_gtid_set - low watermark
   ----
  -In the read-only implementation, the watermarks have a form of GTID sets like
  -
  +
  +In the read-only implementation, the watermarks have a form of GTID sets, e.g. like this:
   `2174B383-5441-11E8-B90A-C80AA9429562:1-3, 24DA167-0C0C-11E8-8442-00059A3C7B00:1-19`
   
  -Such watermarks do not appear in the binlog stream. Instead, the algorithm compares each event’s GTID against the in-memory watermarks.
  -
  -The implementation ensures there are no stale reads and that a chunk has changes that are not older than events up to low watermark.
  -
  -===== Deduplication algorithm with read-only watermarks
  +Such watermarks do not appear in the binlog stream. Instead, the algorithm compares each event's GTID against the in-memory watermarks.
  +The implementation ensures there are no stale reads and that a chunk only has changes that are not older than events up to low watermark.
  +
  +=== Deduplication algorithm with read-only watermarks
  +
  +In pseudo-code, the algorithm for deduplicating events read from the binlog and events retrieved via snapshot chunks looks like this:
   
   [source]
   ----
  
positive.code: ----
  
  == Conclusion
  Debezium is an excellent change data capture tool under active development, and it’s a pleasure to be a part of its community. We’re excited to use incremental snapshots in production here at Shopify. If you have similar database usage restrictions, check out the read-only incremental snapshots feature. Many thanks to my team and the Debezium team without whom this project wouldn't happen.
negative.code:                        append row to outputbuffer
             // other steps of event processing loop
  ----
  
  === Watermark checks
  
  A database transaction can change several rows. In this case, multiple binlog events will have the same GTID. Due to GTIDs not being unique, it affects the logic of computing a chunk selection window. An event updates a window state when the watermark's GTID set doesn't contain its GTID. After the events like transaction completion and heartbeat, there won't be any further binlog events with the same GTID. For those events, it's enough to reach the watermark's upper bound to trigger a window open/close.
  
  [.centered-image.responsive-image]
  ====
  ++++
  <img src="/assets/images/2022-03-25-read-only-incremental-snapshots/window.png" style="max-width:90%;" class="responsive-image">
  ++++
  *Figure 1. A chunk selection window*
  ====
  
  The deduplication happens within the chunk selection window as in the default implementation. Finally, the algorithm inserts a deduplicated chunk right after the high watermark:
  
  [.centered-image.responsive-image]
  ====
  ++++
  <img src="/assets/images/2022-03-25-read-only-incremental-snapshots/deduplication.png" style="max-width:90%;" class="responsive-image">
  ++++
  *Figure 2. A chunk deduplication*
  ====
  
  === No updates for included tables
  
  It's crucial to receive binlog events for the snapshot to make progress. So the algorithm checks GTIDs of _all_ the events together with not included tables.
  
  === No binlog events
  
  The MySQL server sends a heartbeat event after the replication connection was idle for x-seconds. The read-only implementation utilizes heartbeats when the rate of binlog updates is low.
  
  The heartbeat has the same GTID as the latest binlog event. Thus, for a heartbeat, it's enough to reach the upper bound of the high watermark.
  
  The algorithm uses the `server_uuid` part of a heartbeat's GTID to get the max transaction id from the high watermark. The implementation makes sure the high watermark contains a single `server_uuid`. An unchanged `server_uuid` allows to avoid the scenario when the window is closed too early by a heartbeat. See the image below as an example:
  
  [.centered-image.responsive-image]
  ====
  ++++
  <img src="/assets/images/2022-03-25-read-only-incremental-snapshots/heartbeat.png" style="max-width:90%;" class="responsive-image">
  ++++
  *Figure 3. A scenario when the window would have been closed too early by a heartbeat*
  ====
  
  A heartbeat comparison against the low watermark isn't needed since it doesn't matter if the window was open or not. This simplifies the checks when there are no new events between the high and low watermarks.
  
  === No changes between watermarks
  
  A binlog event can open and close a window right away when there were no binlog events during the chunk selection. In this case, a high watermark will be an empty set. In this case, the snapshot chunk gets inserted right after the low watermark without deduplication.
  
  [.centered-image.responsive-image]
  ====
  ++++
  <img src="/assets/images/2022-03-25-read-only-incremental-snapshots/empty_window.png" style="max-width:90%;" class="responsive-image">
  ++++
  *Figure 4. An empty chunk selection window*
  ====
  
  == Kafka topic based signals
  
  Debezium supports ad-hoc incremental snapshots triggered via inserts to the signaling table. A read-only alternative is to send signals through a specific Kafka topic. The format of the message mimics the signaling table structure. An execute-snapshot Kafka message includes the parameters
  
  * `data-collections` - list of tables to be captured
  * `type` - set to INCREMENTAL
  