Search indexing optimisation

Summary

Grab Deliveries uses MySQL as canonical storage and Elasticsearch as derived search storage, with Food-Puxian synchronising updates through Kafka. The original incremental-sync design treated Kafka events mainly as notifications, reloaded records from MySQL, and used separate consumers and in-memory event buffering, creating heavy database traffic, data-loss gaps, coupling to table schemas, redundant Elasticsearch updates, and duplicate cascade updates. A CDC system based on MySQL row-based Binlog provides common events containing Operation, PayloadBefore, and PayloadAfter, enabling filtering of irrelevant changes, partial Elasticsearch updates, event merging in the buffer, and deduplication of cascade updates through a dedicated Kafka stream. The reported results were an 80% reduction in database load from the first optimisation and an 80% reduction in cascade-update events. Overall, DSP saved about 91% of database reads and 90% of Elasticsearch reads, while stream-consumer throughput rose from 200 to 800 QPS, with peaks above 1000 and lower synchronisation latency.

Takeaways

1. 1

   MySQL row-based Binlog captures data modifications made to MySQL tables, including changes made through command-line or database-management tools, avoiding the application-code capture gap.

2. 2

   Comparing the Elasticsearch-relevant fields in PayloadBefore and PayloadAfter filters irrelevant events; the reported database-load reduction from this optimisation was 80%.

3. 3

   Cascade Update events sharing an ID are routed to the same Kafka partition and consumed by one DSP instance, allowing in-memory deduplication that reduced such events by 80%.

FAQ