Experiment with 'buffered' specific algorithm

[morgo]

Currently spirit works like this:

• It starts a replication client to subscribe to table changes (tracking the PK only).
• It then progressively copies from the table using INSERT IGNORE .. SELECT (src)
• Progressively the replication client changes are flushed using REPLACE INTO .. SELECT and DELETE statements (src)

I propose we experiment with a modified algorithm, which I am calling the Buffered algorithm. I don't know if it will be faster, but I think it's worth creating a proof of concept to try it. Here's how it works:

• The replication client starts, but rather than tracking just the PK, it buffers the whole row.
• The copy reads from the table using much larger chunks (10-20s instead of 2s) and fills a buffer.
• The copy buffer is then flushed in 1-2s writes, but at a higher parallel factor.
• The replication client flushes conditionally. It has to check that the key is below the low watermark of the copier. This ensures that there is no race from the copy being split into two parts and there being a lost update (the race is very brief: as an alternative we could rely on the checksum to repair issues, but I think it's better to use it as a back-stop).

Why I think this has merit:

• (for Aurora specifically) The longer reads trigger Aurora's read ahead which helps a lot when the data is not in cache. We can also use longer reads, because they won't be locking.
• The increased parallelism writes make good use of group commit, which Aurora also really benefits from. This is particularly beneficial because the writes are basically guaranteed to be disjoint sets.
• (for MySQL) the medium sized batches are more likely to be optimal for multi-threaded replicas. I believe (if I am paraphrasing JFGs research correctly) that disjoint small batches work best.
• This algorithm can also be used in other contexts, such as ShardMerge/MoveTables. These INSERT.. SELECT / REPLACE .. SELECT statements are the only thing in spirit tying it to a single server.

I call it the 'buffered' algorithm because this is the most distinctive difference from the existing algorithms. I previously considered calling this the 'Aurora' algorithm, but upon reflection I think that's not entirely true.

It's not too much of a code change to implement:

• Because we technically already have 2 methods to track replication changes (deltaMap and deltaQueue) we should extract this to an interface.
• There will be some work (and testing) to read in multiple chunks, fill a buffer, and write in a greater number (5-10x more threads).

There will be a lot of work in experimenting though, to confirm it is beneficial.

Edit: I was reading back through https://netflixtechblog.com/dblog-a-generic-change-data-capture-framework-69351fb9099b and noticed that this describes the buffered algorithm exactly (lock free on the source, high and lower watermarks for replication stream processing, using chunks for copying table data).

[morgo]

I have a branch that implements this here: https://github.com/morgo/spirit/tree/mtocker-experimental-buffered

On my macbook pro it takes 2m9s to perform a schema change that takes 1m59s previously (comparing using a 5s target-chunk-time even though the locking is reduced with this implementation, so in theory it's being unfair to the new branch). Being local, it has the advantage that all the data copying is slightly less impactful. But.. MySQL was not the target of this optimization, Aurora is. So it may take a little bit of effort to test.

[morgo]

I tested this against an Aurora instance (r6g.8xlarge). The table in question:

CREATE TABLE `big_table` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `c1` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `c2` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `c3` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB

The operation was to add an index on (c1), which is entirely random values. Final database size was:

mysql> select format_bytes(792295636992+236570198016);
+-----------------------------------------+
| format_bytes(792295636992+236570198016) |
+-----------------------------------------+
| 958.21 GiB                              |
+-----------------------------------------+
1 row in set (0.00 sec)

The final copy time (note bug: #454 )

2025-09-24T09:16:48.385 info apply complete: instant-ddl=false inplace-ddl=false total-chunks=62790 copy-rows-time=7h6m50s checksum-time=1h45m56s total-time=7h6m50s conns-in-use=0

Historically we've averaged about 100G/hr, so this is a little bit faster.. but it's all relative to how much stress you are willing to put on the system.

There are some tuning things that need fixing:

• The selects are quickly hitting the 100K maximum chunk size - so that needs a configuration option to go higher.
• The connections are being cycled a lot (enough that it is likely costing quite. a bit of CPU since TLS is enabled).

The writes (chunklets) are fixed to 1000 rows, which in this instance I think is correct, and it doesn't need dynamic chunking. Dynamic chunking serves the purpose of yielding locks, but since this is only locking the new table, what we care about more is how much of a shared resource (redo logs + any reads from read/modify/write that happens when indexes no longer fit in memory) we consume. To this point, I believe we need to add a throttler instead, which limits the concurrency of the writers instead.

Because the gains are more modest so far, there might not be a case for switching the default to buffered. But if we consider it for MoveTables (copy from one server to another) cases, it could be quite an unlock. One behavior characteristic that is quite nice is on the read (source) side, the overhead is quite low.