Skip to main content
AIDevOps
  • Learn
  • Learning Paths
  • Practice
  • Open Source
  • Books
  • Engineering

    AI DevOpsThe full map of building and operating AI servicesLLMOpsLLM deployment ยท evaluation ยท observabilityHands-on ProjectsBuild AI Agent projects

    Knowledge

    DocsTechnical documentationBlogEngineering articlesPloggerDevelopment log feed

    Validate

    Certification3-level skills certification ยท coming soon
AI Models
LlamaTutorial|MistralTutorial|GemmaTutorial|DeepSeekTutorial|QwenTutorial
๐Ÿง  AI Core
AI Intro & RoadmapML FundamentalsLLM Fundamentals|Python AIC++|PyTorchTensorFlowJAX
๐Ÿค– AI Applied Development
Applied AI Intro & RoadmapHugging FaceLangChainLlamaIndexLLMOps|LangGraphMCPMulti-AgentAgent Evaluation
๐Ÿง  AI Agent Development
Finance AI AgentLLM API ServerStock Investing AgentAIOps AI AgentEducation AI AgentCoding AI Agent
๐ŸŒฑ Spring Cloud
Spring Intro & RoadmapSpring Cloud GatewaySpring BootJava|Spring AISpring SecuritySpring BatchSpring JPA
๐Ÿณ DevOps
DevOps Intro & RoadmapLinuxDockerCI/CD|Kubernetes BasicsK8s AdvancedPrometheusGrafana
๐Ÿงฑ Infrastructure
Infrastructure Intro & RoadmapNginxRedis
โ˜๏ธ Cloud
Cloud Intro & RoadmapAWSGCPAzureNCPCloudflare
๐ŸŽจ Frontend
Frontend Intro & RoadmapJavaScriptTypeScript|ReactNext.js|VueNuxt|Electron
๐Ÿ“ฑ Mobile
Mobile Intro & RoadmapKotlinAndroidFlutter
โš™๏ธ Backend
Backend Intro & RoadmapPython BasicsFastAPIDjangoFlask|CGoGinNode.js
๐Ÿ’พ Database
DB Intro & RoadmapCore SQLOracleMySQLPostgreSQL|MongoDBVector DB
๐Ÿงช Testing
k6JMeternGrinder
AIDevOps

Engineering AI. From Code to Production.
An engineering learning platform for building and operating AI and AI Agents

Learn

  • All Guides
  • Learning Paths
  • AI Tutorial
  • Practice
  • Books

Resources

  • AI DevOps
  • LLMOps
  • Hands-on Projects
  • Docs
  • Blog
  • Plogger
  • Open Source
  • Certification (coming soon)

Start Here

  • AI Core Roadmap
  • AI Applied Development Roadmap
  • Spring Cloud Roadmap
  • DevOps Roadmap
  • Infrastructure Roadmap

ย 

  • Cloud Roadmap
  • Frontend Roadmap
  • Mobile Roadmap
  • Backend Roadmap
  • Database Roadmap
ยฉ 2026 AIDevOps. All rights reserved.
Terms of ServicePrivacy PolicySitemaptestforge.kr
AIDevOps ยท System Performance Guide

โš™๏ธ System Performance Tuning A Practical Guide

Visitors

DB query optimization, Redis caching strategy, connection pool tuning, faster API responses, and k6 load testing. A step-by-step look at performance techniques that actually work in production.

โ† Blog
โš™๏ธ

Contents

0 / 12
  1. Metrics and targets
  2. Diagnosing bottlenecks
  3. DB query optimization
  4. Index strategy
  5. Fixing N+1
  6. Analyzing with EXPLAIN
  7. Caching strategy
  8. Redis cache design
  9. HTTP cache & CDN
  10. Connection pool & thread tuning
  11. Async processing & pagination
  12. Verifying with k6
Contents 12 sections
  1. Metrics and targets
  2. Diagnosing bottlenecks
  3. DB query optimization
  4. Index strategy
  5. Fixing N+1
  6. Analyzing with EXPLAIN
  7. Caching strategy
  8. Redis cache design
  9. HTTP cache & CDN
  10. Connection pool & thread tuning
  11. Async processing & pagination
  12. Verifying with k6

Metrics and targets

Before you start optimizing, define the metrics and the targets. Optimization that starts without numbers easily loses its way.

MetricDescriptionProduction target (typical)
p50 response time50% of all requests finish within this time< 100ms
p95 response time95% of all requests finish within this time< 500ms
p99 response time99% of all requests finish within this time< 1,000ms
Error rateShare of 5xx responses< 0.1%
Throughput (TPS)Requests handled per secondDepends on service scale
๐Ÿ’ก

Go after p95 before p99. p99 is affected by factors that are hard to control, such as network jitter and GC pauses. The efficient order is to bring p95 down reliably first, then improve p99.

Diagnosing bottlenecks

The fastest way to find the cause of a slow request is to measure the time of each stage directly. Measure, do not guess.

PYTHON
import time
import logging
from contextlib import contextmanager

logger = logging.getLogger(__name__)

@contextmanager
def timer(label: str):
    """Context manager that times each stage of request handling"""
    start = time.perf_counter()
    try:
        yield
    finally:
        elapsed_ms = (time.perf_counter() - start) * 1000
        logger.info(f"[PERF] {label}: {elapsed_ms:.1f}ms")

# Usage example โ€” FastAPI handler
async def get_dashboard(user_id: int):
    with timer("db.fetch_user"):
        user = await db.get_user(user_id)

    with timer("db.fetch_stats"):
        stats = await db.get_stats(user_id)

    with timer("cache.set"):
        await redis.set(f"dashboard:{user_id}", stats, ex=300)

    return {"user": user, "stats": stats}

# Sample log output:
# [PERF] db.fetch_user: 3.2ms
# [PERF] db.fetch_stats: 847.5ms  โ† the bottleneck!
# [PERF] cache.set: 1.1ms
โ„น๏ธ

Enable the slow query log: in PostgreSQL, log_min_duration_statement = 100 automatically logs queries that take 100ms or longer. In MySQL, set slow_query_log = ON and long_query_time = 0.1.

DB query optimization

Most API performance problems start in the database. The three main causes are missing indexes, N+1 queries, and unnecessary full scans.

Index strategy

Indexes speed up reads at the cost of write speed and storage. Index the frequently queried columns first, especially those used in WHERE / ORDER BY / JOIN.

SQL
-- Problem: the combined user_id + created_at lookup is a Full Scan
SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 20;

-- Fix: create a composite index (most selective column first)
CREATE INDEX CONCURRENTLY idx_orders_user_created
  ON orders (user_id, created_at DESC);

-- Covering index: include the SELECT columns in the index โ†’ 0 table accesses
CREATE INDEX CONCURRENTLY idx_orders_cover
  ON orders (user_id, created_at DESC)
  INCLUDE (status, total_amount);
Index typeBest forWatch out for
Single columnSimple equality lookupsNo benefit when selectivity is low
CompositeWHERE + ORDER BY combinationsNeeds a condition on the leading column
CoveringQueries with a fixed SELECT listLarger index size
Partial indexLookups on specific status values onlyWHERE status = 'active'

Fixing N+1

N+1 is the pattern of fetching a list of N items and then firing an extra query for each item. A list of 100 items runs 101 queries.

PYTHON
# Bad: N+1 queries (10 posts โ†’ 11 queries)
posts = await db.query("SELECT * FROM posts LIMIT 10")
for post in posts:
    # a separate query runs for every post
    author = await db.query("SELECT * FROM users WHERE id = ?", post.user_id)
    post.author = author

# Good 1: a single query with JOIN
posts = await db.query("""
    SELECT p.*, u.name AS author_name, u.avatar AS author_avatar
    FROM posts p
    JOIN users u ON u.id = p.user_id
    LIMIT 10
""")

# Good 2: batch lookup with an IN clause (when using an ORM)
posts = await Post.query.limit(10).all()
user_ids = {p.user_id for p in posts}
users = {u.id: u for u in await User.query.filter(User.id.in_(user_ids)).all()}
for post in posts:
    post.author = users[post.user_id]
โš ๏ธ

Watch out for ORM lazy loading. Django ORM, SQLAlchemy, and JPA all lazy-load related objects by default. Load them explicitly with select_related /prefetch_related / JOIN FETCH.

Analyzing the execution plan with EXPLAIN

SQL
-- PostgreSQL: see the actual execution plan + timing
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.*, u.name
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 'pending'
ORDER BY o.created_at DESC
LIMIT 50;

-- How to read the key terms
-- Seq Scan     โ†’ full scan (no index, or index not used)
-- Index Scan   โ†’ index used (good)
-- Index Only   โ†’ covering index (best)
-- Hash Join    โ†’ large JOIN (normal)
-- Nested Loop  โ†’ small JOIN (normal); a problem when large
-- rows=10000 / actual rows=1  โ†’ stale statistics โ†’ run ANALYZE
SQL
-- Refresh statistics (when plan estimates differ widely from reality)
ANALYZE orders;

-- Check index usage (unused indexes are candidates for removal)
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

Caching strategy

A cache reuses the result of an expensive operation. Without a strategy for what to cache and when to invalidate it, the cache itself becomes a cause of incidents.

Redis cache design

PYTHON
import json
import hashlib
from functools import wraps
import redis.asyncio as redis

r = redis.from_url("redis://localhost:6379", decode_responses=True)

def cache(ttl: int = 300, key_prefix: str = ""):
    """Decorator that caches the result in Redis"""
    def decorator(func):
        @wraps(func)
        async def wrapper(*args, **kwargs):
            # Cache key: function name + hash of the arguments
            raw_key = f"{key_prefix or func.__name__}:{args}:{sorted(kwargs.items())}"
            cache_key = hashlib.md5(raw_key.encode()).hexdigest()

            cached = await r.get(cache_key)
            if cached:
                return json.loads(cached)  # cache hit

            result = await func(*args, **kwargs)
            await r.set(cache_key, json.dumps(result), ex=ttl)
            return result
        return wrapper
    return decorator

# Usage example
@cache(ttl=60, key_prefix="user_stats")
async def get_user_stats(user_id: int) -> dict:
    """DB aggregate query โ€” cached for 60 seconds"""
    return await db.query(
        "SELECT COUNT(*) orders, SUM(amount) total FROM orders WHERE user_id = ?",
        user_id,
    )
PatternDescriptionSuitable data
Cache-AsideThe app reads the cache itself โ†’ on a miss, reads the DB and stores the resultFrequently read master data
Write-ThroughUpdates the DB and the cache together on writeData where consistency matters
Write-BehindWrites to the cache first and to the DB asynchronouslyWrite-heavy data that can tolerate loss
TTL-based invalidationDeleted automatically after the expiry timeStatistics, aggregates, external API results
โš ๏ธ

Beware of cache stampedes: right after a TTL expires, many requests hit the DB at the same time. Defend against it by implementing a lock with Redis SET NX or by refreshing in the background before expiry.

HTTP cache & CDN

TYPESCRIPT
// Next.js App Router โ€” cache headers per response
import { NextResponse } from 'next/server';

// Public data: CDN caches for 1 hour, the browser for 5 minutes
export async function GET() {
  const data = await fetchPublicData();
  return NextResponse.json(data, {
    headers: {
      'Cache-Control': 'public, s-maxage=3600, stale-while-revalidate=86400',
    },
  });
}

// Per-user data: no CDN caching, the browser caches for 1 minute
export async function GET(req: Request) {
  const data = await fetchUserData();
  return NextResponse.json(data, {
    headers: {
      'Cache-Control': 'private, max-age=60',
    },
  });
}
YAML
# Cloudflare Cache Rules example (wrangler.jsonc)
# Static assets: cached for 1 year
# /api/public/*: cached at the edge for 30 minutes
# /api/user/*: no cache (bypass)

# Cache-Control header guide
# public, s-maxage=N      โ†’ CDN caches for N seconds
# stale-while-revalidate  โ†’ serves the stale version after expiry while refreshing in the background
# no-store                โ†’ no caching at all (sensitive data)
# must-revalidate         โ†’ must revalidate with the server as soon as it expires

Connection pool & thread tuning

When the connection pool is too small, requests wait in the pool and response time spikes. Make it too large and the DB server is overloaded instead.

PYTHON
# SQLAlchemy async connection pool settings
from sqlalchemy.ext.asyncio import create_async_engine

engine = create_async_engine(
    "postgresql+asyncpg://user:pass@localhost/db",
    pool_size=20,          # connections kept open (rule of thumb: CPU cores ร— 2-4)
    max_overflow=10,       # extra connections allowed at peak (pool_size + max_overflow = max)
    pool_timeout=30,       # max time to wait for a connection from the pool (seconds)
    pool_recycle=1800,     # recreate connections after 30 minutes (avoids DB timeouts)
    pool_pre_ping=True,    # validate the connection before use
)
YAML
# How to size PostgreSQL max_connections
# max_connections = (CPU cores ร— 4) + headroom
# e.g. 8-core server โ†’ max_connections = 200

# PgBouncer (connection pooler) โ€” funnels hundreds of app connections into 10 DB connections
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction    # transaction-level pooling (most efficient)
max_client_conn = 1000    # max connections accepted from the app
default_pool_size = 20    # actual connections opened to the DB
๐Ÿ’ก

Connection pool size formula: pool_size = (DB CPU cores ร— 2) + effective disk parallelism. The recommendation from HikariCP author Brett Wooldridge is 2 per CPU core. Bigger is not faster โ€” the DB pays context-switching costs too.

Async processing & pagination

Running independent I/O tasks in parallel can cut total response time sharply. Move heavy work to the background, and always paginate large lists.

PYTHON
import asyncio

# Bad: sequential execution (total time = sum of each task)
async def get_dashboard_slow(user_id: int):
    profile = await fetch_profile(user_id)    # 50ms
    orders  = await fetch_orders(user_id)     # 80ms
    stats   = await fetch_stats(user_id)      # 60ms
    # 190ms in total

# Good: parallel execution (total time โ‰ˆ the slowest task)
async def get_dashboard_fast(user_id: int):
    profile, orders, stats = await asyncio.gather(
        fetch_profile(user_id),   # โ”
        fetch_orders(user_id),    # โ”œโ”€ run concurrently
        fetch_stats(user_id),     # โ”˜
    )
    # ~80ms in total (bounded by orders, the slowest)
PYTHON
# Cursor-based pagination โ€” more consistent than OFFSET on large tables
async def get_orders_cursor(user_id: int, cursor: int | None, limit: int = 20):
    """
    OFFSET pagination approaches a full scan by the 100,000th page.
    The cursor (last id) approach always uses only the index.
    """
    query = """
        SELECT id, status, total_amount, created_at
        FROM orders
        WHERE user_id = :user_id
          AND (:cursor IS NULL OR id < :cursor)
        ORDER BY id DESC
        LIMIT :limit
    """
    rows = await db.fetch_all(query, {"user_id": user_id, "cursor": cursor, "limit": limit})
    next_cursor = rows[-1]["id"] if len(rows) == limit else None
    return {"items": rows, "next_cursor": next_cursor}

Verifying with k6

Measuring with k6 before and after an optimization lets you confirm the improvement in numbers. Always verify that p95 response time meets the SLA at the target TPS.

system-perf-test.jsJAVASCRIPT
import http from 'k6/http';
import { check, sleep } from 'k6';
import { Trend } from 'k6/metrics';

const dbQueryTime = new Trend('db_query_time', true);

export const options = {
  stages: [
    { duration: '1m', target: 50  }, // warm-up
    { duration: '3m', target: 200 }, // normal load
    { duration: '2m', target: 500 }, // peak load
    { duration: '1m', target: 0   }, // cool-down
  ],
  thresholds: {
    'http_req_duration':          ['p(95)<500', 'p(99)<1000'],
    'http_req_failed':            ['rate<0.001'],
    'db_query_time':              ['p(95)<100'],
  },
};

export default function () {
  // 1. List query (validates DB + cache)
  const listRes = http.get('http://localhost:3000/api/orders?limit=20', {
    tags: { name: 'list' },
  });
  check(listRes, {
    'list status 200': (r) => r.status === 200,
    'list has items':  (r) => r.json()?.items?.length > 0,
  });

  // Measurable if the server adds X-DB-Time to the response headers
  const dbTime = listRes.headers['X-DB-Time'];
  if (dbTime) dbQueryTime.add(Number(dbTime));

  sleep(0.5);

  // 2. Single-item query (checks the cache hit rate)
  const id = Math.floor(Math.random() * 1000) + 1;
  const detailRes = http.get(`http://localhost:3000/api/orders/${id}`, {
    tags: { name: 'detail' },
  });
  check(detailRes, {
    'detail status 200 or 404': (r) => [200, 404].includes(r.status),
  });

  sleep(Math.random() + 0.5);
}
BASH
# Sample result summary โ€” before vs after optimization
# โ”€โ”€ Before โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€
# http_req_duration  p(95)=1,240ms  p(99)=3,820ms  โœ— over the SLA
# http_req_failed    rate=0.82%                     โœ— error rate too high

# โ”€โ”€ After (index added + Redis cache) โ”€โ”€โ”€โ”€โ”€โ”€โ”€
# http_req_duration  p(95)=187ms    p(99)=412ms    โœ“
# http_req_failed    rate=0.00%                    โœ“
# db_query_time      p(95)=23ms                    โœ“

k6 run system-perf-test.js --out json=result.json
๐Ÿ’ก

Expose server-side timing through response headers. Adding X-DB-Time and X-Cache-Status headers lets k6 track DB query time and the cache hit rate directly. In production, restrict them so they are only exposed to internal IPs.