MySQL Bulk Update using erlang gen-server(virtual server) for Inventory Platform

From time to time we were facing issues related to MySQL and during all the RCA we had found one of the many reasons for the issue was massive write happening on MySql.

How does bulk write help?

• As we know all SQL operations(insert/update/delete) are transactions in nature and each transition is required to replicate by replica separately and replication in read replica is single-threaded.
• By doing bulk update we are converting multiple(max 200) transactions into 1 transaction so it helps expedite the replication process in reading replicas. it also reduces the number of transactions to master. More details on the replication link.
• For r4.xlarge(2TB storage and 32 GB RAM) RDS performance starts degrading when 40K+/min writes happen.

How does bulk write work?

Following is the diagram of the underlying system architecture,

System Architecture

• Inventory platform passes each update to the virtual bulk update server(i.e. gen-server here) instead of directly writing to MySQL.
• We keep inventory sharded by the table based on the date of the inventory. Bulk Update Server passes data to the respective process using PIDs based on which table the updated is meant for. i.e If inventory update is for 10th May 2020, it will be passed to the 10th May process for an update of the 10th May table.
• Processes will be created dynamically if it does not exist.
• Each process will send a message to itself after every 2 seconds, on receiving the message process will take 200–200 chunks from process memory and start writing into MySQL.
• In case the process has less than 200 updates, it will write all the updates in MySQL.
• If the process dies abruptly, the process will write the unwritten data into the file so when the next time the process starts it will first take back-up of unwritten data from the file and restore back into the process memory.
• As a fallback, in case the Bulk Update server doesn’t send the ack back Inventory platform will directly write into MySQL.
• Following is the chart of how we convert ~80K queries into 2.12K queries.

Bulk Update

• As we are holding objects into memory for 2 seconds, we anticipated some caveats on memory but there was no increase in memory,

Memory Utilization(Capacity= 16GB)

References

• [Online]. https://dev.mysql.com/doc/
• [Online]. Available: http://www.erlang.org.
• [Online]. Available: https://learnyousomeerlang.com/content