Posted inDatabase

From 40 Seconds to 500ms: Processing Billions of Records in Real-time with Apache Druid

Database tutorial - IT technology blog
Database tutorial - IT technology blog

When MySQL or PostgreSQL “Collapse” Under Billions of Records

Two years ago, I had a rough time with a tracking project for a major e-commerce platform. The requirements were tough: the system had to handle 500 million events per day. The business department demanded near-instant dashboard reports with latency under 1 second.

At first, the team relied on PostgreSQL. Everything was fine when the data was under 100 million rows. But when the log table hit the 1 billion record mark, COUNT(DISTINCT user_id) queries became a nightmare. Despite indexing and partitioning, every time the dashboard loaded, we had to go make coffee and wait 30-40 seconds for the numbers to update. Customers complained, and the boss was on edge.

Why Are Traditional Databases Slow?

After many sleepless nights debugging, I realized that OLTP databases (like MySQL, Postgres) face three major hurdles when processing Big Data:

  • Row-based Storage: To calculate the total revenue of a single column, it has to read all other columns from the disk. This causes massive I/O waste.
  • Index Overload: With billions of rows, indexes swell beyond RAM capacity. Ingestion speed is dragged down because the system has to constantly update indexes.
  • Single-threaded Processing: Complex queries often fail to leverage the full power of all CPU cores in a server cluster.

Solutions Our Team Considered

Before deciding on Druid, our team tried several approaches, but each had its flaws:

  1. Pre-aggregation: Using Cronjobs to aggregate data every hour. This is fast but loses real-time capability. If the boss wanted to see data from 5 minutes ago, we were out of luck.
  2. Elasticsearch: Very fast text search. However, when performing aggregations on large datasets, it consumes a massive amount of RAM and sometimes returns results that aren’t 100% accurate.
  3. ClickHouse: Excellent performance. But at that time, ClickHouse’s direct integration with Kafka wasn’t as smooth or stable as Druid’s.

Apache Druid – The “Weapon” for Real-time Analytics

Apache Druid isn’t just a database. It’s a hybrid of a Data Warehouse, Time-series database, and Search Index. Druid uses columnar storage and automatically indexes data upon ingestion. Its ability to “ingest” millions of events per second from Kafka is its biggest selling point.

Druid’s smart mechanism lies in partitioning data into time-based Segments. When you query today’s data, it only scans the relevant segments. It completely ignores old data, making sub-second speeds easily achievable.

Quick Installation Guide (Micro-Quickstart)

If you want to try it on a personal machine or a VPS with about 4GB of RAM, the Micro-Quickstart version is the most reasonable choice.

Step 1: Prepare the Environment

Druid runs best on Java 11. Check your environment with the command:

java -version

Step 2: Download and Extract

Download the latest distribution (e.g., version 28.0.1) from an Apache mirror:

wget https://dlcdn.apache.org/druid/28.0.1/apache-druid-28.0.1-bin.tar.gz
tar -xzf apache-druid-28.0.1-bin.tar.gz
cd apache-druid-28.0.1

Step 3: Launch

In the root directory, run the provided script to activate the services:

./bin/start-micro-quickstart

Wait about 1 minute for components like the Coordinator and Broker to finish starting. Then, access the UI at: http://localhost:8888.

Ingesting Data and Real-world Querying

Druid doesn’t use standard INSERT INTO but uses an “Ingestion Spec” via JSON. Don’t worry, the console interface will guide you through every step.

Loading Sample Data

Suppose you have an events.json file with a simple structure:

{"time": "2023-10-27T10:00:00Z", "user_id": "u1", "event_type": "click", "value": 10}

On the Druid Console interface:

  1. Go to Load data -> Local disk and enter the file path.
  2. At the Parse data step, select the time column as the Primary Timestamp. This is crucial for Druid to shard data efficiently.
  3. Click Submit. Once the Task shows SUCCESS, your data is ready.

Querying with SQL

Druid supports standard SQL, making it very easy to learn. You can try this command immediately:

SELECT 
  event_type, 
  COUNT(*) as total_events
FROM "your_datasource"
WHERE __time >= CURRENT_TIMESTAMP - INTERVAL '1' DAY
GROUP BY 1

The results will return in an instant thanks to Bitmap index technology on each column.

“Hard-earned” Lessons from Operations

After running Druid in a production environment for a while, I’ve gathered three important notes:

  • Rollup is a Lifesaver: If you don’t need to look at individual user details, enable Rollup. It automatically aggregates rows with identical timestamps and dimensions. In my project, Rollup reduced storage from 100GB to just 15GB.
  • Avoid High Cardinality: Don’t use columns with too many unique values (like session_id) as dimensions. It will break Rollup capabilities and significantly slow down queries.
  • RAM Configuration: Druid is extremely thirsty for Direct Memory. Remember to set the -XX:MaxDirectMemorySize parameter carefully; otherwise, the system will crash when encountering heavy queries.

The final result? Our team’s dashboard went from 40 seconds to under 500ms. The boss is happy, and the DevOps guys are breathing a sigh of relief because they no longer have to stay up late optimizing indexes. If you’re facing a massive data problem that requires real-time speed, Apache Druid is a name worth investing in.

Share:
Tags: