Part 6: CloudWatch Query Best Practices and Performance

Optimizing CloudWatch Queries

After writing thousands of CloudWatch queries, I've learned what separates fast from slow queries, and expensive from economical ones. This part shares the optimization techniques that made the biggest difference.

Performance Optimization Fundamentals

How CloudWatch Logs Insights Works

Understanding the execution model helps optimize queries:

1. Data scanning: Reads log events from storage

2. Filtering: Applies filter conditions

3. Parsing: Extracts fields if needed

4. Aggregation: Performs stats calculations

5. Sorting: Orders results

6. Limiting: Returns final result set

Key insight: Data scanning is the most expensive operation.

Query Execution Costs

CloudWatch Logs Insights charges $0.005 per GB of data scanned.

Cost = (Data Scanned in GB) × $0.005

Example costs from my experience:

Scenario	Data Scanned	Cost
Last 1 hour, 1 log group	2 GB	$0.01
Last 24 hours, 1 log group	48 GB	$0.24
Last 7 days, 5 log groups	1.7 TB	$8.50
Last 30 days, all groups	5 TB	$25.00

Best Practice #1: Time Range Optimization

The single most important optimization.

Use Specific Time Ranges

# Bad - queries all data
fields @timestamp, @message

# Good - last hour only
fields @timestamp, @message
| filter @timestamp > ago(1h)

# Better - exact window
fields @timestamp, @message
| filter @timestamp >= "2024-01-15T10:00:00" 
    and @timestamp < "2024-01-15T11:00:00"

Relative Time Ranges

ago(5m)   # 5 minutes
ago(1h)   # 1 hour
ago(1d)   # 1 day
ago(1w)   # 1 week

Real Example: Progressive Time Windows

When troubleshooting, start small and expand:

# Step 1: Last 15 minutes
filter @timestamp > ago(15m)

# Step 2: If nothing found, expand to last hour
filter @timestamp > ago(1h)

# Step 3: Expand to last 24 hours
filter @timestamp > ago(24h)

Best Practice #2: Filter Early and Often

Apply filters as early as possible to reduce data processed.

Filter Before Parse

# Less efficient
fields @message
| parse @message /duration=(?<dur>[0-9]+)/
| filter dur > 1000

# More efficient
fields @message
| filter @message like /duration/
| parse @message /duration=(?<dur>[0-9]+)/
| filter dur > 1000

Multiple Specific Filters

# Combine filters
fields @timestamp, @message
| filter @message like /ERROR/
| filter @message not like /DEBUG|INFO/
| filter @timestamp > ago(1h)
| filter @logStream like /production/

Use Field Existence Checks

# Skip irrelevant log entries
filter ispresent(requestId)
filter ispresent(duration)
filter statusCode > 0

Real Example: Efficient Error Query

# Optimized query
fields @timestamp, @message, @requestId
| filter @timestamp > ago(1h)           # Time filter first
| filter @message like /ERROR|FATAL/     # Content filter
| filter @logStream like /production/    # Environment filter
| filter ispresent(@requestId)           # Valid entries only
| parse @message /(?<error_type>[A-Za-z]+Error)/
| stats count() by error_type
| sort count desc
| limit 10

Best Practice #3: Select Only Needed Fields

Avoid selecting all fields.

Specific Field Selection

# Bad - processes everything
fields @*

# Good - only what's needed
fields @timestamp, @message, statusCode, duration

# Best - calculate on the fly
fields @timestamp, statusCode, duration / 1000 as seconds

For JSON Logs

# Bad - entire JSON object
fields @message

# Good - specific fields
fields @timestamp, request.method, request.path, response.statusCode

Best Practice #4: Optimize Aggregations

Aggregations can be expensive on large datasets.

Pre-filter Before Aggregating

# Less efficient
fields duration
| stats avg(duration) by endpoint
| filter avg(duration) > 1000

# More efficient
fields duration, endpoint
| filter duration > 0
| stats avg(duration) as avg_dur by endpoint
| filter avg_dur > 1000

Limit Group-By Cardinality

# High cardinality - expensive
stats count() by @requestId   # Millions of unique values

# Lower cardinality - better
stats count() by endpoint     # Dozens of unique values
stats count() by statusCode   # Few unique values

Use Appropriate Time Bins

# For 1 hour of data
stats count() by bin(1m)      # Good - 60 data points

# For 1 day of data
stats count() by bin(5m)      # Good - 288 data points
stats count() by bin(1m)      # Overkill - 1440 data points

# For 1 week of data
stats count() by bin(1h)      # Good - 168 data points

Real Example: Efficient Percentile Calculation

fields @timestamp, endpoint, duration
| filter @timestamp > ago(1h)
| filter duration > 0
| filter endpoint in ["/api/users", "/api/orders", "/api/products"]
| stats 
    count() as requests,
    pct(duration, 50) as p50,
    pct(duration, 95) as p95,
    pct(duration, 99) as p99
    by endpoint
| filter requests > 100    # Only endpoints with significant traffic
| sort p95 desc

Best Practice #5: Efficient Parsing

Parsing is computationally expensive.

Use Glob Patterns When Possible

# Glob patterns (simpler, faster)
parse @message "* * *" as timestamp, level, message

# Regex (more powerful, slower)
parse @message /\[(?<timestamp>[^\]]+)\] (?<level>[A-Z]+) (?<message>.*)/

Parse Only When Needed

# Bad - parse all entries
parse @message /duration=(?<dur>[0-9]+)/
| stats count()

# Good - filter first, then parse
filter @message like /duration/
| parse @message /duration=(?<dur>[0-9]+)/
| filter dur > 1000
| stats count()

Optimize Regex Patterns

# Less efficient - greedy matching
parse @message /.*error: (.*)/

# More efficient - specific patterns
parse @message /error: ([A-Za-z]+)/

# Even better - non-greedy
parse @message /error: ([^,\n]+)/

Real Example: Efficient Log Parsing

# Optimized parsing query
fields @timestamp, @message
| filter @timestamp > ago(1h)
| filter @message like /duration=/
| parse @message "method=* endpoint=* duration=* ms" as method, endpoint, duration
| filter duration > 500
| stats 
    count() as slow_requests,
    avg(duration) as avg_ms,
    max(duration) as max_ms
    by method, endpoint
| sort avg_ms desc

Best Practice #6: Limit Results Appropriately

Control the amount of data returned.

Always Use limit

# No limit - might return thousands
fields @timestamp, @message
| sort @timestamp desc

# With limit - controlled output
fields @timestamp, @message
| sort @timestamp desc
| limit 100

Limit After Aggregation

# Top 10 endpoints
stats count() by endpoint
| sort count desc
| limit 10

# Not all endpoints
stats count() by endpoint
| sort count desc

Progressive Investigation

# Start with  - verify results
... | limit 10

# Expand as needed
... | limit 50

# Full dataset when confirmed
... | limit 1000

Best Practice #7: Query Organization

Structure queries for readability and debugging.

One Command Per Line

# Hard to read
fields @timestamp, @message | filter @message like /ERROR/ | sort @timestamp desc | limit 10

# Easy to read and debug
fields @timestamp, @message
| filter @message like /ERROR/
| sort @timestamp desc
| limit 10

Comment Complex Queries

CloudWatch doesn't support comments in queries, so document externally:

# Query: Error rate by endpoint
# Purpose: Monitor error distribution
# Owner: DevOps team
# Last updated: 2024-01-15

fields @timestamp, endpoint, statusCode
| filter statusCode >= 400
| stats count() by endpoint, statusCode
| sort count desc

Use Meaningful Names

# Bad
fields x, y, z

# Good
fields @timestamp as time, 
       endpoint as api_endpoint,
       duration as response_time_ms

Best Practice #8: Saved Queries

Reuse common queries for consistency.

Save Frequently Used Queries

In CloudWatch console:

1. Write query

2. Click "Save"

3. Give descriptive name

4. Access from "Saved queries"

Query Library Structure

Organize by category:

Performance/
  - P95 Latency by Endpoint
  - Slow Queries
  - Response Time Distribution

Errors/
  - Recent Errors
  - Error Rate Over Time
  - Top Error Messages

Security/
  - Failed Login Attempts
  - Suspicious Activity
  - Access from Unusual IPs

Business/
  - Order Completion Rate
  - Active Users
  - Revenue Tracking

Share Queries Across Team

Export queries as JSON:

{
  "queryString": "fields @timestamp, @message | filter @message like /ERROR/ | sort @timestamp desc | limit 20",
  "logGroupNames": ["/aws/lambda/my-function"],
  "queryRange": "1h"
}

Best Practice #9: Cost Management

Keep CloudWatch costs under control.

Set Data Retention Policies

# Set retention to 7 days
aws logs put-retention-policy \
  --log-group-name /aws/lambda/my-function \
  --retention-in-days 7

Retention options:

• 1, 3, 5, 7, 14 days

• 1, 2, 3, 4, 5, 6 months

• 1, 2, 5, 10 years

• Never expire

Archive to S3

For long-term storage:

# Export logs to S3
aws logs create-export-task \
  --log-group-name /aws/lambda/my-function \
  --from 1642204800000 \
  --to 1642291200000 \
  --destination s3-bucket-name \
  --destination-prefix lambda-logs/

Cost comparison:

• CloudWatch: $0.50/GB/month

• S3 Standard: $0.023/GB/month

• S3 Glacier: $0.004/GB/month

Monitor Query Costs

Track data scanned:

fields @timestamp
| stats sum(@bytesScanned) / 1024 / 1024 / 1024 as gb_scanned

Cost Optimization Checklist

• Set appropriate retention periods

• Archive old logs to S3

• Use specific time ranges

• Filter early in queries

• Limit high-cardinality group-bys

• Delete unused log groups

• Use sampling for development

Best Practice #10: Testing and Validation

Ensure queries return expected results.

Test with Small Datasets

# Start with short time range
filter @timestamp > ago(5m)
| ...

# Verify results

# Expand time range
filter @timestamp > ago(1h)
| ...

Validate Parsing

# Test parse pattern
fields @message
| parse @message /pattern/
| limit 10

# Verify fields extracted correctly

Check Aggregation Results

# Verify count matches expectations
stats count() as total

# Check for missing data
stats count() by bin(1h)
| sort bin(1h)
# Look for gaps in timeline

Sample Data During Development

# Sample 10% of data
fields @message
| filter rand() < 0.1
| ...

Common Anti-Patterns to Avoid

Anti-Pattern 1: Querying All Log Groups

# Bad - scans everything
# (selecting all log groups in console)

# Good - specific log groups
# Select only relevant log groups

Anti-Pattern 2: No Time Filter

# Bad
fields @message
| filter @message like /ERROR/

# Good
fields @message
| filter @timestamp > ago(1h)
| filter @message like /ERROR/

Anti-Pattern 3: Parse and Discard

# Bad - parse for no reason
parse @message /user=(?<user>[^ ]+)/
| stats count()

# Good - only parse if using field
stats count()

Anti-Pattern 4: High-Cardinality Group By

# Bad - millions of unique values
stats count() by @requestId

# Good - reasonable cardinality
stats count() by endpoint

Anti-Pattern 5: Querying in Loops

# Bad - sequential queries (scripts)
for region in regions:
    query(log_group)

# Good - single query across multiple groups
# Select multiple log groups at once

Performance Monitoring

Track query performance over time.

Measure Query Execution Time

CloudWatch shows:

• Query duration

• Data scanned

• Results returned

Set Performance Baselines

Typical query stats:
- Duration: < 5 seconds
- Data scanned: < 1 GB
- Results: < 1000 rows

Optimize Slow Queries

If query takes > 30 seconds:

1. Reduce time range

2. Add filters earlier

3. Limit aggregation cardinality

4. Select fewer fields

5. Consider sampling

Query Debugging Techniques

Technique 1: Progressive Build

# Step 1: Basic selection
fields @timestamp, @message

# Step 2: Add filter
fields @timestamp, @message
| filter @message like /ERROR/

# Step 3: Add parsing
fields @timestamp, @message
| filter @message like /ERROR/
| parse @message /Error: (?<error_type>[A-Za-z]+)/

# Step 4: Add aggregation
fields @timestamp, @message
| filter @message like /ERROR/
| parse @message /Error: (?<error_type>[A-Za-z]+)/
| stats count() by error_type

Technique 2: Validate Intermediate Results

# Add limit to see intermediate data
fields @timestamp, @message
| filter @message like /ERROR/
| parse @message /Error: (?<error_type>[A-Za-z]+)/
| limit 10    # Check parsing works correctly

Technique 3: Check Field Existence

# Verify fields exist
fields @timestamp, @message
| filter ispresent(statusCode)
| stats count() as entries_with_status_code

# vs total
fields @timestamp, @message
| stats count() as total_entries

Real-World Optimization Example

Before Optimization

# Slow query - scans 50 GB
fields @message
| parse @message /request_id=(?<req_id>[^ ]+) endpoint=(?<endpoint>[^ ]+) duration=(?<dur>[0-9]+)/
| filter dur > 1000
| stats count() by endpoint
| sort count desc

Performance:

• Duration: 45 seconds

• Data scanned: 50 GB

• Cost: $0.25

After Optimization

# Fast query - scans 2 GB
fields @message
| filter @timestamp > ago(1h)           # Time filter
| filter @message like /duration/       # Reduce dataset
| filter @message like /endpoint/       # Further reduce
| parse @message "endpoint=* duration=*" as endpoint, dur
| filter dur > 1000                     # Post-parse filter
| stats count() as slow_requests by endpoint
| filter slow_requests > 5              # Significant only
| sort slow_requests desc
| limit 20

Performance:

• Duration: 3 seconds

• Data scanned: 2 GB

• Cost: $0.01

Improvements:

• 93% faster

• 96% less data scanned

• 96% cheaper

Key Takeaways

• Time range is the most important optimization

• Filter early and often to reduce data scanned

• Select only needed fields

• Apply filters before parsing

• Use appropriate time bins for aggregations

• Limit high-cardinality group-bys

• Save and reuse common queries

• Set retention policies to control costs

• Archive old logs to S3

• Test queries with small datasets first

• Structure queries for readability

• Monitor query performance and costs

• Avoid common anti-patterns

In Part 7, we'll explore real-world CloudWatch query patterns for production monitoring, troubleshooting, security analysis, and cost optimization that I use every day.