How to Systematically Identify a Performance Bottleneck
Your load test shows degradation: latency jumps from 50 ms to 2000 ms, and the logs are silent. This comprehensive bottleneck analysis reveals root causes. Our performance analysis has been trusted by enterprise clients for over a decade. In one case, the cause was a slow JSON.parse in a hot path — replacing it with simdjson reduced p95 latency from 200 ms to 30 ms (a 85% reduction), and infrastructure costs dropped by over 60% (saving $7,000 per month). The approach is straightforward: measure → find → eliminate → repeat.
Diagnostic Framework: From Metrics to Bottleneck
First, look at top-level metrics. If p95 latency is high but CPU is below 70%, suspect the database or external calls. If CPU is 90–100%, profile the code. If memory grows and swap is active, look for a leak. System errors (ENOMEM, EMFILE) indicate OS limits. 502/504 errors — check the load balancer.
High latency or errors
│
├── p95 latency high, CPU < 70%, memory OK
│ └── → Database: slow queries, locks, N+1
│
├── CPU 90–100%, latency grows proportionally
│ └── → Compute bottleneck: profile CPU-hot paths
│
├── Memory grows, swap active
│ └── → Memory leak or heap too small
│
├── ENOMEM / EMFILE / ECONNREFUSED
│ └── → System limits: ulimit, file descriptors, TCP backlog
│
└── Errors 502/504, app OK
└── → Nginx upstream, load balancer timeout
Database Optimization: Slow Queries and N+1
How to Find Slow Queries in PostgreSQL?
Run these queries during the load test. The first shows active queries with duration. The second — locks (who is waiting for whom). The third — heaviest queries by total time. The fourth — tables with sequential scans (potential missing indexes). For more details, see the PostgreSQL Documentation on pg_stat_statements.
-- Running queries right now (run during the test)
SELECT pid, now() - query_start AS duration,
state, wait_event_type, wait_event,
left(query, 100) AS query_preview
FROM pg_stat_activity
WHERE state != 'idle'
AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration DESC;
-- Locks: who blocks whom
SELECT blocked.pid, blocked.query,
blocking.pid AS blocking_pid,
blocking.query AS blocking_query
FROM pg_stat_activity blocked
JOIN pg_stat_activity blocking
ON blocking.pid = ANY(pg_blocking_pids(blocked.pid))
WHERE cardinality(pg_blocking_pids(blocked.pid)) > 0;
-- Heaviest queries (pg_stat_statements)
SELECT query, calls, mean_exec_time, total_exec_time,
stddev_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;
-- Missing indexes: sequential scans on large tables
SELECT relname, seq_scan, seq_tup_read,
idx_scan, seq_tup_read / nullif(seq_scan, 0) AS avg_rows_per_seqscan
FROM pg_stat_user_tables
WHERE seq_scan > 100
AND seq_tup_read > 10000
ORDER BY seq_tup_read DESC;
Why N+1 Queries Are a Common Cause of Degradation?
N+1 occurs when an ORM issues a separate query for each parent object to fetch related data. With 1000 users, that's 1001 queries instead of a single JOIN. Symptoms: the number of active connections to the database equals the number of virtual users, and pg_stat_statements shows the same query with a large number of calls. Solution: eager loading, DataLoader, or manual JOIN. We specialize in N+1 query elimination. Using pg_stat_statements to detect N+1 is 10 times faster than manual log analysis.
Application Profiling: Node.js, Python, and Connection Pool
CPU Profiling in Node.js
The simplest method is to enable the V8 profiler via a signal. Run the code under load, send kill -USR1 <pid>, after 30 seconds get cpu-profile.cpuprofile, open in Chrome DevTools.
// server.js — enable V8 profiling via signal
process.on('SIGUSR1', () => {
const { Session } = require('inspector')
const session = new Session()
session.connect()
session.post('Profiler.enable')
session.post('Profiler.start')
// Profile for 30 seconds
setTimeout(() => {
session.post('Profiler.stop', (err, { profile }) => {
require('fs').writeFileSync('./cpu-profile.cpuprofile', JSON.stringify(profile))
console.log('CPU profile saved to cpu-profile.cpuprofile')
session.disconnect()
})
}, 30000)
})
// Run under load: kill -USR1 <pid>
// Open in Chrome DevTools → More Tools → JavaScript Profiler
An alternative is to use the 0x utility to generate a flamegraph. It collects stack traces and renders an interactive graph where bar width represents execution time. Typical findings: JSON.parse/stringify in hot paths, bcrypt with high cost factor, uncached regex, synchronous file operations.
npm install -g 0x
0x --output-dir profile node server.js &
APP_PID=$!
k6 run tests/load/main.js
kill -USR2 $APP_PID
# Opens flamegraph.html
Python Profiling Under Load
For production we use pyinstrument — it doesn't require restart and gives a detailed report per request. Add middleware that enables profiling via ?profile=true.
from pyinstrument import Profiler
from flask import request, g
@app.before_request
def start_profiler():
if request.args.get('profile') == 'true':
g.profiler = Profiler()
g.profiler.start()
@app.after_request
def stop_profiler(response):
if hasattr(g, 'profiler'):
g.profiler.stop()
response.data = g.profiler.output_html()
response.content_type = 'text/html'
return response
# Request with profiling: GET /api/posts?profile=true
Connection Pool Analysis
If clients are waiting for a connection (cl_waiting > 0 in pgBouncer), the pool is too small. Check via SHOW POOLS; or SQL query to pg_stat_activity.
-- PostgreSQL: connection pool statistics
SELECT datname, count(*) AS total_connections,
count(*) FILTER (WHERE state = 'active') AS active,
count(*) FILTER (WHERE state = 'idle') AS idle,
count(*) FILTER (WHERE wait_event_type = 'Lock') AS waiting_lock
FROM pg_stat_activity
GROUP BY datname;
k6 Time-Series Analysis: How to Find the Degradation Point?
The script analyzes the p95 latency time series and finds the first minute when the value exceeded a threshold (e.g., 500 ms). This helps pinpoint the degradation to a specific test moment.
Script for k6 time-series analysis (click to expand)
import json
import pandas as pd
def find_degradation_point(json_results: str):
"""Find the degradation point from metric time series"""
records = []
with open(json_results) as f:
for line in f:
try:
record = json.loads(line)
if record.get('type') == 'Point':
records.append({
'timestamp': record['data']['time'],
'metric': record['metric'],
'value': record['data']['value']
})
except:
continue
df = pd.DataFrame(records)
df['timestamp'] = pd.to_datetime(df['timestamp'])
p95_df = df[df['metric'] == 'http_req_duration'].copy()
p95_df = p95_df.set_index('timestamp').resample('1min')['value'].quantile(0.95)
threshold = 500
degradation = p95_df[p95_df > threshold]
if not degradation.empty:
print(f"Degradation detected at: {degradation.index[0]}")
print(f"p95 at degradation: {degradation.iloc[0]:.0f}ms")
else:
print("No degradation detected (all within threshold)")
return p95_df
Tools and Typical Optimizations
Profiling Tools Comparison
| Tool | Scope | Depth | Production Impact |
|---|---|---|---|
| pg_stat_statements | PostgreSQL | High | None |
| V8 profiler | Node.js | High | Minimal |
| pyinstrument | Python | Medium | None |
| 0x | Node.js | High | Requires restart |
| flamegraph | General | High | Depends on collector |
Typical Optimizations After Analysis
| Bottleneck | Symptom | Solution |
|---|---|---|
| N+1 queries to DB | DB active queries >> VU count | DataLoader / eager loading / JOIN |
| Missing index | SeqScan on large table | CREATE INDEX CONCURRENTLY |
| Slow JSON serialization | CPU high, hot path in serialize | Protobuf / simdjson / msgpack |
| Connection pool overflow | cl_waiting > 0 in pgBouncer |
Increase pool_size or add replicas |
| GC pauses | Spiky latency without CPU load | Increase heap, tune GC flags |
| Table locks | wait_event = Lock in pg_stat |
Optimize operation order, NOWAIT |
Analysis Results and Order Process
After the analysis, we deliver:
- Report with time-series graphs (latency, throughput, CPU, memory, database metrics)
- Scripts to reproduce the load
- Detailed list of bottlenecks with code and configuration snippets
- Optimization recommendations (priority, effort estimates)
- Verification test after changes are applied
- Architecture consultation to prevent future issues
What’s Included in the Analysis
Our comprehensive package includes:
- Detailed documentation of findings
- Access to load testing scripts and profiling tools
- Training session for your team on bottleneck identification
- 30 days of email support for questions on optimizations
A full analysis with report and verification takes 1–2 business days. Pricing is customized based on system complexity. Contact us — we'll evaluate your project and recommend the optimal engagement. Our engineers have 10+ years of experience in load testing and optimization. Average cloud cost savings after our optimizations range from $3,000 to $10,000 per month.