1. EXPLAIN ANALYZE — Query Profiling
-- PostgreSQL EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT p.*, c.name as category_name,
COUNT(r.id) as review_count, AVG(r.rating) as avg_rating
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON r.product_id = p.id
WHERE p.price BETWEEN 100 AND 500
AND c.slug = 'electronics'
GROUP BY p.id, c.name
ORDER BY avg_rating DESC NULLS LAST
LIMIT 20;
-- Output phân tích:
-- Sort (cost=1234.56..1234.78 rows=20 width=120)
-- (actual time=15.234..15.245 rows=20 loops=1)
-- Sort Key: avg_rating DESC NULLS LAST
-- Sort Method: top-N heapsort Memory: 30kB
-- -> HashAggregate (...)
-- -> Hash Join (...) ← Join strategy
-- -> Seq Scan on products ← ⚠️ Sequential scan = chậm!
-- Filter: (price >= 100 AND price <= 500)
-- Rows Removed by Filter: 45000
-- -> Hash
-- -> Index Scan on categories ...
-- Planning Time: 0.892 ms
-- Execution Time: 15.567 ms
Reading and understanding Execution Plan
Red flags cần chú ý:
⚠️ Seq Scan trên bảng lớn → Cần index
⚠️ Nested Loop với rows lớn → N+1 pattern
⚠️ Sort trên disk (external merge) → Tăng work_mem
⚠️ Rows Removed by Filter cao → Index không hiệu quả
⚠️ Buffers: shared read cao → Data không trong cache
⚠️ actual rows >> estimated rows → Statistics cần ANALYZE
-- Fix: Thêm composite index
CREATE INDEX idx_products_category_price
ON products (category_id, price)
INCLUDE (name, description); -- Covering index
-- Fix: Partial index cho hot data
CREATE INDEX idx_products_active
ON products (created_at DESC)
WHERE status = 'active';
-- Verify improvement
EXPLAIN (ANALYZE, BUFFERS)
-- Giờ sẽ thấy: Index Scan thay vì Seq Scan
2. Connection Pool Saturation Testing
// k6 — Test connection pool limits
import sql from 'k6/x/sql';
import { check } from 'k6';
import { Trend } from 'k6/metrics';
const queryDuration = new Trend('db_query_duration');
const db = sql.open('postgres', __ENV.DATABASE_URL);
export const options = {
scenarios: {
concurrent_queries: {
executor: 'ramping-vus',
stages: [
{ duration: '1m', target: 10 },
{ duration: '2m', target: 50 }, // Pool size thường = 20-30
{ duration: '2m', target: 100 }, // Vượt pool → queuing
{ duration: '2m', target: 200 }, // Extreme → timeout/errors
{ duration: '1m', target: 0 },
],
},
},
thresholds: {
db_query_duration: ['p(95)<100', 'p(99)<500'],
},
};
export default function () {
const start = Date.now();
const results = sql.query(db, `
SELECT p.*, c.name
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.price > $1
LIMIT 20
`, Math.random() * 100);
queryDuration.add(Date.now() - start);
check(results, { 'has rows': (r) => r.length > 0 });
}
-- Monitor connection pool từ PostgreSQL
SELECT 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
FROM pg_stat_activity
WHERE datname = 'myapp';
-- Monitor connection wait time
SELECT * FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL
ORDER BY query_start;
3. Read Replica Lag Testing
// Test: Write to primary, read from replica, measure lag
export default function () {
// Write to primary
const writeRes = http.post(`${PRIMARY_URL}/api/products`, JSON.stringify({
name: `Product-${Date.now()}`,
price: Math.random() * 1000,
}), { headers: { 'Content-Type': 'application/json' } });
const productId = JSON.parse(writeRes.body).id;
const writeTime = Date.now();
// Poll replica until data appears
let found = false;
let attempts = 0;
while (!found && attempts < 50) {
const readRes = http.get(`${REPLICA_URL}/api/products/${productId}`);
if (readRes.status === 200) {
found = true;
replicaLag.add(Date.now() - writeTime);
}
attempts++;
sleep(0.1); // 100ms between polls
}
if (!found) {
replicaLag.add(5000); // Max 5s timeout
}
}
4. ORM N+1 Detection
-- PostgreSQL: Log slow queries
ALTER SYSTEM SET log_min_duration_statement = 100; -- Log queries > 100ms
ALTER SYSTEM SET log_statement = 'all'; -- Tạm bật cho profiling
-- Count queries per request (detect N+1)
-- Nếu 1 API call tạo 100+ queries → N+1 problem
SELECT query, calls, mean_exec_time, total_exec_time
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 20;
N+1 Pattern Example:
GET /api/orders → 1 query: SELECT * FROM orders LIMIT 20
→ 20 queries: SELECT * FROM users WHERE id = ? (cho mỗi order)
→ 20 queries: SELECT * FROM products WHERE order_id = ?
= 41 queries cho 1 API call!
Fix: JOIN hoặc DataLoader/eager loading
SELECT o.*, u.name, p.name
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON oi.product_id = p.id
= 1 query
5. Database Benchmark Scripts
# pgbench — Built-in PostgreSQL benchmark
pgbench -i -s 100 mydb # Initialize (10M rows)
pgbench -c 50 -j 4 -T 300 mydb # 50 clients, 4 threads, 5 min
pgbench -c 100 -j 8 -T 600 -r mydb # With per-statement report
pgbench -c 50 -T 300 -f custom-workload.sql mydb # Custom workload
# sysbench — MySQL benchmark
sysbench oltp_read_write \
--mysql-host=localhost --mysql-db=mydb \
--tables=10 --table-size=1000000 \
--threads=64 --time=300 \
run
6. Summary
- EXPLAIN ANALYZE: Understand execution plan, identify bottleneck
- Connection pool: Test saturation limits, queuing behavior
- Replica lag: Measure replication delay under write load
- N+1 detection: pg_stat_statements, query counting per request
- Benchmarks: pgbench/sysbench for baseline throughput
The next article will learn Cloud-native Performance Testing.