Exiling a Growing RDB Table to S3 and Querying It with Athena - feat. Optimization with Spark Bucketing

Banksalad's Core Infra team saw storage costs rise as MyData logs from the Credit Score Boost service accumulated in MySQL, so they moved the data to S3 and switched to a structure that queries it via Athena. However, with the existing Partition (dt, hour) structure, it was impossible to locate files for a specific user id query, forcing calls to all S3 Objects for an entire year's worth of data, which caused Athena costs to surge.

Optimizing the Bucketing and Partition Structure

To solve the cost problem, the team applied Bucketing and changed the Partition structure. Bucketing uses a high-cardinality column such as user id as the key, calculating a hash to locate file positions so that only the necessary files are read. They also changed the Partition structure to remove the hour partition and keep only dt, minimizing the number of Object calls. Through this, Athena data call costs decreased by about 700x compared to before.

Spark Bucketing Implementation and Compatibility Issues

Spark Bucketing has compatibility issues with Athena (which is based on Presto), so the team took a workaround approach using CTAS (CREATE TABLE AS) to create tables and set up partitions. Also, since Spark Bucketing file creation generates a file per Executor, causing an explosive increase in the number of files, the team performed repartition in advance based on the Bucketing Key and the number of Buckets to reduce the number of files per Bucket to 1. This requires memory allocation capable of handling a single Partition.

Cost Savings and Future Challenges

Ultimately, the savings in MySQL data storage costs amounted to about 3 times the increased Athena call costs, securing overall cost efficiency. However, a phenomenon was observed where Athena calls the same file multiple times, leaving room for further optimization. Optimizing the query plan with Bucketing characteristics in mind and establishing guidelines for engineers and analysts remain future challenges.