Khi hệ thống vận hành ở môi trường production, một trong những cách rẻ, nhanh và hiệu quả nhất để giám sát hiệu suất API, phân tích lỗi, và phát hiện hành vi bất thường là tận dụng ALB Access Logs và Amazon Athena.

Trong bài viết này, mình sẽ chia sẻ bộ query Athena tốt nhất (best practices) để bạn có thể ngay lập tức áp dụng cho monitoring, debugging và cost optimization, dựa trên dữ liệu từ Application Load Balancer (ALB).

⚙️ Kiến trúc tổng quan

Luồng dữ liệu cơ bản: 

Client → ALB → ECS / EC2 Targets
                 ↓
          S3 (ALB Access Logs)
                 ↓
          Athena (SQL Query)
                 ↓
        QuickSight / Grafana Dashboard

• ALB Access Logs tự động ghi lại toàn bộ request/response.

• S3 là nơi lưu log thô.

• Athena dùng SQL để đọc trực tiếp log trên S3 mà không cần server.

• Có thể tích hợp với QuickSight để tạo dashboard hoặc EventBridge + Lambda để alert khi phát hiện bất thường.

🧠 Bộ Query Tốt Nhất Cho Athena

🔹 I. Performance Monitoring

1️⃣ Top Slowest API Endpoints

Phát hiện API nào xử lý lâu nhất (trung bình, tối đa, p95).

SELECT 
    request_url,
    COUNT(*) as request_count,
    AVG(target_processing_time) as avg_time,
    MAX(target_processing_time) as max_time,
    APPROX_PERCENTILE(target_processing_time, 0.95) as p95_time
FROM alb_access_logs.access_logs 
WHERE target_processing_time > 0
    AND day >= '2025/11/01'
GROUP BY request_url
HAVING COUNT(*) > 10
ORDER BY avg_time DESC
LIMIT 20;​

👉 Ứng dụng: giúp phát hiện API “nút cổ chai”, xác định endpoint cần tối ưu.

2️⃣ Response Time Distribution

Phân loại request theo các nhóm thời gian phản hồi.

SELECT 
    CASE 
        WHEN target_processing_time < 0.1 THEN '< 100ms'
        WHEN target_processing_time < 0.5 THEN '100ms - 500ms'
        WHEN target_processing_time < 1.0 THEN '500ms - 1s'
        WHEN target_processing_time < 2.0 THEN '1s - 2s'
        ELSE '> 2s'
    END as response_time_bucket,
    COUNT(*) as request_count,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
FROM prod_alb_access_logs.prod_access_logs 
WHERE target_processing_time > 0
    AND day >= '2025/11/01'
GROUP BY 1
ORDER BY 1;

👉 Ứng dụng: biểu đồ dạng donut chart rất trực quan để đánh giá tổng thể hiệu suất.

🔹 II. Error Analysis

3️⃣ Error Rate by Endpoint

Phát hiện API có tỉ lệ lỗi cao nhất.

SELECT 
    request_url,
    COUNT(*) as total_requests,
    COUNT(CASE WHEN elb_status_code >= 400 THEN 1 END) as error_count,
    COUNT(CASE WHEN elb_status_code >= 400 THEN 1 END) * 100.0 / COUNT(*) as error_rate
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY request_url
HAVING COUNT(*) > 50
ORDER BY error_rate DESC
LIMIT 20;

4️⃣ 5xx Errors Analysis

Phân tích lỗi 5xx theo giờ và endpoint.

SELECT 
    DATE_FORMAT(CAST(time AS timestamp), '%Y-%m-%d %H:00:00') as hour,
    request_url,
    elb_status_code,
    COUNT(*) as error_count,
    COUNT(DISTINCT client_ip) as affected_users
FROM prod_alb_access_logs.prod_access_logs 
WHERE elb_status_code >= 500
    AND day >= '2025/11/01'
GROUP BY 1, 2, 3
ORDER BY hour DESC, error_count DESC;​

👉 Ứng dụng: phát hiện spike lỗi theo giờ để correlate với deployment hoặc hệ thống backend.

🔹 III. Traffic Analysis

5️⃣ Hourly Traffic Pattern

Xem lưu lượng và thời gian phản hồi theo từng giờ.

SELECT 
    DATE_FORMAT(time, '%Y-%m-%d %H:00:00') as hour,
    COUNT(*) as total_requests,
    COUNT(DISTINCT client_ip) as unique_users,
    AVG(target_processing_time) as avg_response_time
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY 1
ORDER BY hour DESC;

6️⃣ Top User Agents

Xác định nguồn client phổ biến nhất (ứng dụng, trình duyệt, bot...).

SELECT 
    user_agent,
    COUNT(*) as request_count,
    COUNT(DISTINCT client_ip) as unique_ips
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY user_agent
ORDER BY request_count DESC
LIMIT 20;​

🔹 IV. Security Monitoring

7️⃣ Suspicious Activity Detection

Phát hiện IP gửi request bất thường (spam, scan, brute force).

SELECT 
    client_ip,
    COUNT(*) as request_count,
    COUNT(CASE WHEN elb_status_code = 404 THEN 1 END) as not_found_count,
    COUNT(CASE WHEN elb_status_code = 403 THEN 1 END) as forbidden_count,
    COUNT(DISTINCT request_url) as unique_urls
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY client_ip
HAVING COUNT(*) > 1000 OR COUNT(CASE WHEN elb_status_code = 404 THEN 1 END) > 100
ORDER BY request_count DESC;

8️⃣ Failed Authentication Attempts

Theo dõi đăng nhập thất bại liên tục — cảnh báo sớm tấn công brute force.

SELECT 
    client_ip,
    request_url,
    COUNT(*) as failed_attempts,
    MIN(time) as first_attempt,
    MAX(time) as last_attempt
FROM prod_alb_access_logs.prod_access_logs 
WHERE elb_status_code = 401
    AND day >= '2025/11/01'
GROUP BY client_ip, request_url
HAVING COUNT(*) > 5
ORDER BY failed_attempts DESC;

🔹 V. API Usage Analytics

9️⃣ API Endpoint Usage Ranking

Thống kê endpoint nào được gọi nhiều nhất.

SELECT 
    REGEXP_EXTRACT(request_url, '/api/v[0-9]+/([^/?]+)') as api_endpoint,
    COUNT(*) as usage_count,
    COUNT(DISTINCT client_ip) as unique_users,
    AVG(target_processing_time) as avg_response_time
FROM prod_alb_access_logs.prod_access_logs 
WHERE request_url LIKE '%/api/%'
    AND day >= '2025/11/01'
GROUP BY 1
ORDER BY usage_count DESC
LIMIT 30;

🔟 API Version Distribution

Theo dõi phân bổ các version API đang được sử dụng.

SELECT 
    REGEXP_EXTRACT(request_url, '/api/(v[0-9]+)/') as api_version,
    COUNT(*) as request_count,
    COUNT(*) * 100.0 / SUM(COUNT(*)) OVER() as percentage
FROM prod_alb_access_logs.prod_access_logs 
WHERE request_url LIKE '%/api/v%'
    AND day >= '2025/11/01'
GROUP BY 1
ORDER BY request_count DESC;

🔹 VI. Real-time Monitoring

11️⃣ Last 5 Minutes Activity

Giám sát request và lỗi trong 5 phút gần nhất.

SELECT 
    COUNT(*) as total_requests,
    COUNT(CASE WHEN elb_status_code >= 400 THEN 1 END) as errors,
    AVG(target_processing_time) as avg_response_time,
    MAX(target_processing_time) as max_response_time
FROM prod_alb_access_logs.prod_access_logs 
WHERE time >= FORMAT_DATETIME(current_timestamp - interval '5' minute, 'yyyy-MM-dd''T''HH:mm:ss.SSS''Z''');

12️⃣ Error Spike Detection

Phát hiện đột biến lỗi trong 30 phút gần nhất.

WITH recent_errors AS (
    SELECT 
        DATE_FORMAT(FROM_ISO8601_TIMESTAMP(time), '%Y-%m-%d %H:%i:00') as minute,
        COUNT(CASE WHEN elb_status_code >= 500 THEN 1 END) as error_count
    FROM prod_alb_access_logs.prod_access_logs 
    WHERE FROM_ISO8601_TIMESTAMP(time) >= current_timestamp - interval '30' minute
    GROUP BY 1
)
SELECT 
    minute,
    error_count,
    AVG(error_count) OVER (ORDER BY minute ROWS BETWEEN 5 PRECEDING AND 1 PRECEDING) as avg_previous_5min
FROM recent_errors
WHERE error_count > 10
ORDER BY minute DESC;

🔹 VII. Data Quality & Debugging

13️⃣ Log Completeness Check

Kiểm tra log có ghi đủ dữ liệu trong ngày không.

SELECT 
    day,
    COUNT(*) as log_count,
    COUNT(DISTINCT DATE_FORMAT(FROM_ISO8601_TIMESTAMP(time), '%H')) as hours_with_data,
    MIN(time) as first_log,
    MAX(time) as last_log
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY day
ORDER BY day DESC;

14️⃣ Unusual Response Sizes

Phát hiện response quá lớn hoặc bất thường.

SELECT 
    request_url,
    AVG(sent_bytes) as avg_response_size,
    MAX(sent_bytes) as max_response_size,
    COUNT(*) as request_count
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
GROUP BY request_url
HAVING AVG(sent_bytes) > 1000000 OR MAX(sent_bytes) > 10000000
ORDER BY avg_response_size DESC;

🔹 VIII. Cost Optimization

15️⃣ Partition Filtering

So sánh query hiệu quả và không hiệu quả.

-- GOOD: Uses partition pruning
SELECT COUNT(*) 
FROM prod_alb_access_logs.prod_access_logs 
WHERE day = '2025/11/04';

-- BAD: Scans all partitions
SELECT COUNT(*) 
FROM prod_alb_access_logs.prod_access_logs 
WHERE time >= '2025-11-04 00:00:00';

16️⃣ Limit Data Scanned

Giảm chi phí quét dữ liệu.

SELECT time, request_url, elb_status_code
FROM prod_alb_access_logs.prod_access_logs 
WHERE day >= '2025/11/01'
    AND elb_status_code >= 400
LIMIT 1000;

🧩 Best Practices

1. Luôn sử dụng partition day trong WHERE để Athena chỉ quét dữ liệu cần thiết.

2. Dùng LIMIT khi thử nghiệm query để tránh tốn chi phí.

3. Chọn cụ thể các cột cần thiết, không dùng SELECT *.

4. Dùng hàm approximate (APPROX_DISTINCT, PERCENTILE_APPROX) cho dataset lớn.

5. Filter càng sớm càng tốt trong WHERE.

6. Tạo view Athena cho những query phức tạp và dùng lại nhiều.

7. Tích hợp QuickSight/Grafana để trực quan hóa dữ liệu realtime.

🎯 Kết luận

Bộ query này cung cấp một framework hoàn chỉnh để:

• Giám sát hiệu suất API và latency,

• Phân tích lỗi 4xx / 5xx theo thời gian thực,

• Phát hiện hành vi bất thường về bảo mật,

• Kiểm soát chi phí Athena một cách tối ưu.

Chỉ với ALB + S3 + Athena, bạn đã có thể xây dựng một data observability platform mini mà không cần thêm công cụ phức tạp.