Wrangling High Volume Gaming Transactions

With tens of thousands of concurrent online gamers sending data in real time, the studio’s data infrastructure was under stress. OmniArcs was engaged to evaluate, redesign, build, and tune a new solution. Our experience at Full 360 laid the foundation.

Original Architecture

The data warehouse running on Amazon Redshift was part of a complex stack:

• Archive data is flattened into JSON rows via Elastic Map Reduce.
• ETL job checks data files for new attributes, then adds attributes as a new column in target table.
• JSON data is parsed (by Redshift) into target table. JSON source, with a table for each type of event generated by the game.
• The original state has no optimizations for columnar storage.

Problem

The existing Redshift ingestion process and database design could not scale to 20TB per day of raw source data. Load performance was inadequate. The disk footprint in Redshift was too large. Handling of new attributes challenged the original design. Historical queries were unacceptably slow. This raised questions about cost, performance, and staffing.

Engagement

OmniArcs streamlined the data model, changed the source data format, created a new loader process using ECS, and tuned the tables to perform properly. First we identified pros and cons of the initial state. Then we evaluated two new design proposals, Event‑based CSV and KVP from CSV.

Our recommendation was KVP from CSV which employed a hybrid storage model giving the following characteristics.

Common data model between cheap and expensive storage

• KVP solution involves creating a DW tier in S3 that is in identical KVP format as Redshift.
• Redshift merely reflects a rolling window subset of the KVP data in S3.
• Hive can be used against S3 KVP data using SQL queries that are very similar to Redshift queries.
• Any question users may have can be answered through either Redshift or Hive.

Not all data is retained in expensive storage

• Redshift is expensive storage compared to S3.
• With event based tables, all data is stored regardless of whether or not it is required by end users. In a production environment this will not scale for longer timeframes.
• In KVP model, only fields that are actually required for analysis are persisted in non-KVP fact tables. While the actual KVP data takes more data storage per day in Redshift, the actual fact tables will be significantly smaller than the event tables in width and volume.

Optimization and performance

• Data loads will perform well because of narrow source data files and CSV format.
• All fact tables will be optimized for end user requirements.

Proposed Solution & Architecture

• Streamline the data model
• Create a new loader process
• KVP tables loaded from CSV source
• Tune Redshift tables
  • New Sorting
  • New distribution keys
  • New field encoding

Architecture & Technology

• Docker Containers
• ECS Fleet
• Redshift
• SneaQL
• HFMS / Amazon Elastic Map Reduce

Results, Outcomes & Success Metrics

The new ingestion process enabled the studio to handle transaction volume as needed. We analyzed the problem, addressed core design constraints, and left a path to expand capabilities going forward. Results included a significant reduction in Redshift spending, increased performance, and a satisfied customer.

About the Client

A U.S.‑based video game development company.