Metrics collection in HLD - System Design Exercise

Design: Metrics Collection System

Design covers metrics ingestion, storage, querying, and aggregation. Out of scope: detailed alerting system, visualization dashboards.

Functional Requirements

FR1: Collect metrics data from multiple application instances in real-time

FR2: Support different types of metrics: counters, gauges, histograms

FR3: Allow querying aggregated metrics for monitoring and alerting

FR4: Store metrics data efficiently for at least 30 days

FR5: Provide APIs for metrics ingestion and querying

FR6: Ensure minimal impact on application performance during metrics collection

Non-Functional Requirements

NFR1: Handle up to 100,000 metrics data points per second

NFR2: API response latency for queries should be under 200ms (p99)

NFR3: System availability should be at least 99.9%

NFR4: Data retention for 30 days with efficient storage

NFR5: Support horizontal scaling for ingestion and querying

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

❓ Question 6

Key Components

Metrics ingestion API servers

Buffering and batching layer

Time-series database or storage

Aggregation and query engine

Cache layer for hot queries

Monitoring and alerting hooks

Design Patterns

Push vs pull metrics collection

Batching and buffering for ingestion

Time-series data modeling

Data downsampling and retention policies

CQRS (Command Query Responsibility Segregation) for ingestion and querying

Sharding and partitioning for scale

Reference Architecture

  +----------------+      +----------------+      +------------------+      +------------------+
  | Application(s) | ---> | Ingestion API  | ---> | Buffering Layer  | ---> | Time-Series DB   |
  +----------------+      +----------------+      +------------------+      +------------------+
                                                                                      |
                                                                                      v
                                                                               +------------------+
                                                                               | Query API Server |
                                                                               +------------------+
                                                                                      |
                                                                                      v
                                                                               +------------------+
                                                                               | Cache Layer      |
                                                                               +------------------+

Components

Ingestion API

REST/gRPC servers

Receive metrics data from applications and validate input

Buffering Layer

Message queue (e.g., Kafka)

Buffer and batch incoming metrics for efficient processing

Time-Series Database

TSDB like Prometheus, InfluxDB, or TimescaleDB

Store metrics data with time-based indexing and support aggregation queries

Query API Server

REST/gRPC servers

Serve aggregated metrics queries to clients

Cache Layer

In-memory cache like Redis

Cache frequent query results to reduce latency

Request Flow

1. 1. Applications push metrics data to the Ingestion API.

2. 2. Ingestion API validates and forwards data to the Buffering Layer.

3. 3. Buffering Layer batches data and writes to the Time-Series Database.

4. 4. Clients query metrics via the Query API Server.

5. 5. Query API checks Cache Layer for results; if missing, queries Time-Series Database.

6. 6. Query results are returned to clients and cached for future requests.

Database Schema

Entities: - Metric: id, name, type (counter, gauge, histogram) - MetricData: metric_id, timestamp, value, labels (key-value pairs) Relationships: - MetricData references Metric by metric_id - Labels stored as JSON or separate key-value table for filtering - Time-series data indexed by metric_id and timestamp for efficient range queries

Scaling Discussion

Bottlenecks

Ingestion API servers overwhelmed by high write volume

Buffering Layer lag causing delayed writes

Time-Series Database storage and query performance degradation

Query API latency under heavy read load

Cache misses causing increased DB load

Solutions

Scale Ingestion API horizontally behind load balancers

Partition Buffering Layer topics by metric or tenant for parallelism

Use sharded or distributed TSDB clusters with data partitioning

Implement query rate limiting and optimize query plans

Increase cache size and implement cache warming strategies

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying scope, 20 minutes designing components and data flow, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing.

Clarify metric types and query patterns early

Explain choice of buffering and storage technologies

Discuss data modeling for time-series data

Highlight caching to reduce query latency

Address scaling challenges with partitioning and horizontal scaling

Mention trade-offs between data freshness and system load

Practice

1. What is the main purpose of using aggregate functions like COUNT, SUM, and AVG in metrics collection?

easy

A. To create new tables for storing data

B. To change the structure of the database tables

C. To calculate totals, averages, and counts from data

D. To delete unwanted data from the database

Solution

1. Step 1: Understand aggregate functions

   Aggregate functions like COUNT, SUM, and AVG are used to calculate summary values from multiple rows of data.

2. Step 2: Identify their purpose in metrics

   These functions help get totals, averages, and counts which are useful for metrics and reports.

3. Final Answer:

   To calculate totals, averages, and counts from data -> Option C

4. Quick Check:

   Aggregate functions = summary numbers [OK]

Hint: Aggregate functions summarize data into numbers fast [OK]

Common Mistakes:

• Thinking aggregate functions modify data structure
• Confusing aggregate functions with data deletion
• Believing aggregate functions create tables

2. Which of the following SQL queries correctly counts the number of orders in a table named Orders?

easy

A. SELECT COUNT(Orders) FROM *;

B. SELECT COUNT FROM Orders;

C. SELECT SUM(*) FROM Orders;

D. SELECT COUNT(*) FROM Orders;

Solution

1. Step 1: Recall correct COUNT syntax

   The correct syntax to count rows is COUNT(*) inside SELECT.

2. Step 2: Check each option

   SELECT COUNT(*) FROM Orders; uses COUNT(*) correctly. Others have syntax errors or wrong functions.

3. Final Answer:

   SELECT COUNT(*) FROM Orders; -> Option D

4. Quick Check:

   COUNT(*) counts rows correctly [OK]

Hint: Use COUNT(*) to count all rows in a table [OK]

Common Mistakes:

• Omitting parentheses after COUNT
• Using COUNT without specifying columns or *
• Confusing SUM with COUNT

3. Given the table Sales with columns amount and region, what will this query return?

SELECT AVG(amount) FROM Sales WHERE region = 'East';

medium

A. The average sales amount for the East region

B. The total sales amount for all regions

C. The count of sales records in the East region

D. An error because AVG cannot be used with WHERE

Solution

1. Step 1: Understand the WHERE clause effect

   The WHERE clause filters rows to only those where region is 'East'.

2. Step 2: Understand AVG function

   AVG calculates the average of the filtered amount values.

3. Final Answer:

   The average sales amount for the East region -> Option A

4. Quick Check:

   AVG with WHERE filters = average for filtered data [OK]

Hint: WHERE filters rows before aggregate functions run [OK]

Common Mistakes:

• Thinking AVG returns total sum
• Confusing COUNT with AVG
• Believing WHERE cannot be used with aggregates

4. Identify the error in this SQL query that tries to get the total sales for the 'West' region:

SELECT SUM(amount) FROM Sales WHERE region == 'West';

medium

A. Using double equals (==) instead of single equals (=) in WHERE clause

B. SUM cannot be used with WHERE clause

C. Missing GROUP BY clause for aggregation

D. amount column cannot be summed

Solution

1. Step 1: Check WHERE clause syntax

   SQL uses single equals (=) for comparison, not double equals (==).

2. Step 2: Confirm aggregate and WHERE usage

   SUM works with WHERE filtering; GROUP BY is not needed here.

3. Final Answer:

   Using double equals (==) instead of single equals (=) in WHERE clause -> Option A

4. Quick Check:

   Use = for comparisons in SQL WHERE [OK]

Hint: Use single = for comparisons in SQL WHERE [OK]

Common Mistakes:

• Using == instead of = in WHERE
• Thinking GROUP BY is always required with SUM
• Assuming SUM cannot be used with WHERE

5. You want to find the average order amount per customer but only for customers who placed more than 5 orders. Which SQL approach correctly achieves this?

hard

A. Use WHERE COUNT(*) > 5 and SELECT AVG(amount) without GROUP BY

B. Use GROUP BY customer_id with HAVING COUNT(*) > 5 and SELECT AVG(amount)

C. Use WHERE AVG(amount) > 5 and GROUP BY customer_id

D. Use HAVING AVG(amount) > 5 without GROUP BY

Solution

1. Step 1: Understand grouping by customer

   To get average per customer, group rows by customer_id.

2. Step 2: Filter groups with more than 5 orders

   Use HAVING with COUNT(*) > 5 to keep only customers with more than 5 orders.

3. Step 3: Select average order amount

   Use AVG(amount) in SELECT to get average per filtered customer group.

4. Final Answer:

   Use GROUP BY customer_id with HAVING COUNT(*) > 5 and SELECT AVG(amount) -> Option B

5. Quick Check:

   GROUP BY + HAVING filters groups before AVG [OK]

Hint: Use HAVING to filter groups after GROUP BY [OK]

Common Mistakes:

• Using WHERE to filter aggregated counts
• Forgetting GROUP BY when using aggregates per group
• Using HAVING without GROUP BY