Phần 14: Cheatsheet tổng hợp Database Optimization

-- Full option (PG 16+)
EXPLAIN (ANALYZE, BUFFERS, VERBOSE, SETTINGS, WAL, FORMAT JSON)
  SELECT ...;

-- Safe UPDATE/DELETE
BEGIN;
EXPLAIN (ANALYZE, BUFFERS) UPDATE ...;
ROLLBACK;

-- Top queries by time
SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC LIMIT 20;

-- Parallel workers (PG 13+)
SELECT leader.pid, leader.query, COUNT(w.pid) AS workers
FROM pg_stat_activity leader
JOIN pg_stat_activity w ON w.leader_pid = leader.pid
GROUP BY leader.pid, leader.query;

-- pg_stat_io (PG 16+)
SELECT backend_type, object, context,
       reads, writes, hits, ROUND(100.0*hits/NULLIF(hits+reads,0),2) AS hit_pct
FROM pg_stat_io;

-- Replication lag (từ primary)
SELECT client_addr, state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS lag_bytes,
       NOW() - reply_time AS lag_time
FROM pg_stat_replication;

-- Replication lag (từ standby)
SELECT pg_is_in_recovery(),
       NOW() - pg_last_xact_replay_timestamp() AS replay_lag;

-- Slot bloat
SELECT slot_name,
       pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(),
                      confirmed_flush_lsn)) AS lag
FROM pg_replication_slots;

-- Long-running transactions
SELECT pid, usename, state, query_start,
       NOW() - query_start AS duration, query
FROM pg_stat_activity
WHERE state != 'idle' AND NOW() - query_start > INTERVAL '5 min';

-- Locks
SELECT blocked.pid AS blocked_pid, blocked.query AS 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 blocked.state = 'active';

-- B-tree (mặc định) cho =, <, >, ORDER BY
CREATE INDEX ON t(col);

-- Covering index (PG 11+)
CREATE INDEX ON t(key) INCLUDE (col1, col2);

-- Partial index
CREATE INDEX ON orders(order_date) WHERE status = 'active';

-- Expression index
CREATE INDEX ON users(lower(email));

-- GIN cho JSONB, array, tsvector
CREATE INDEX ON t USING GIN (jsonb_col jsonb_path_ops);
CREATE INDEX ON t USING GIN (to_tsvector('simple', body));

-- GiST cho ranges, geometry, ltree
CREATE INDEX ON t USING GIST (range_col);

-- BRIN cho time-series / append-only
CREATE INDEX ON events USING BRIN (created_at) WITH (pages_per_range = 32);

-- HNSW cho vector (pgvector 0.5+)
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

# Memory (cho máy 32 GB RAM)
shared_buffers = 8GB              # 25% RAM
effective_cache_size = 22GB       # 70% RAM
work_mem = 64MB                   # cho sort/hash
maintenance_work_mem = 2GB        # cho VACUUM/CREATE INDEX

# Write
wal_level = logical               # logical replication + CDC
max_wal_size = 4GB
checkpoint_timeout = 15min
synchronous_commit = on           # = off nếu OK mất vài s khi crash

# Parallelism
max_worker_processes = 16
max_parallel_workers = 8
max_parallel_workers_per_gather = 4

# JIT (PG 11+)
jit = on
jit_above_cost = 500000           # tune cao cho OLTP

# Logging
log_min_duration_statement = 500ms
log_checkpoints = on
log_lock_waits = on
auto_explain.log_min_duration = 1000ms

# Enable trên primary
# archive_mode = on
# archive_command = 'pgbackrest --stanza=main archive-push %p'

# Base backup
pgbackrest --stanza=main backup --type=full

# Restore PITR
pgbackrest --stanza=main --type=time \
  --target="2026-04-15 14:30:00+00" restore

# Tạo file cờ
touch /var/lib/postgresql/16/main/recovery.signal
systemctl start postgresql

EXPLAIN ANALYZE SELECT ...;      -- MySQL 8.0.18+
EXPLAIN FORMAT=TREE SELECT ...;  -- dễ đọc thứ tự thực hiện

-- Performance Schema
SELECT digest_text, count_star, sum_timer_wait/1e9 AS total_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY sum_timer_wait DESC LIMIT 20;

-- Dùng thuật ngữ replica/source thay master/slave
SHOW REPLICA STATUS\G
START REPLICA;
STOP REPLICA;
CHANGE REPLICATION SOURCE TO SOURCE_HOST='...', SOURCE_LOG_FILE='...';

-- Parallel replication
-- My.cnf replica:
--   binlog_transaction_dependency_tracking = WRITESET
--   replica_parallel_type = LOGICAL_CLOCK
--   replica_parallel_workers = 8
--   replica_preserve_commit_order = ON

[mysqld]
# Memory (cho máy 32 GB)
innodb_buffer_pool_size = 22G         # 70% RAM
innodb_buffer_pool_instances = 8
innodb_log_buffer_size = 64M

# Redo log (MySQL 8.0.30+)
innodb_redo_log_capacity = 4G         # thay cho log_file_size * files_in_group

# Flush
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1    # = 2 nếu OK mất vài s khi crash
sync_binlog = 1

# IO
innodb_io_capacity = 2000             # phù hợp SSD gp3
innodb_io_capacity_max = 4000

# Binary logging (cho replication / CDC)
log_bin = mysql-bin
binlog_format = ROW
binlog_row_image = FULL
gtid_mode = ON
enforce_gtid_consistency = ON

# Performance Schema
performance_schema = ON
performance_schema_consumer_statements_digest = ON

-- Bật slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;          -- giây
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';

-- Phân tích
-- pt-query-digest /var/log/mysql/slow.log

# Set với TTL (atomic)
SET key value EX 600                    # 600s

# Delete lớn không block
UNLINK bigkey1 bigkey2

# Scan thay vì KEYS (production-safe)
SCAN 0 MATCH user:* COUNT 500

# ACL (Redis 6+)
ACL SETUSER alice on >password ~cache:* +@read +@write
ACL WHOAMI
ACL LIST

# Config online
CONFIG SET maxmemory 4gb
CONFIG SET maxmemory-policy allkeys-lru

# TTL jitter + mutex, chống stampede
def get_with_lock(key, recompute, lock_ttl=5):
    v = cache.get(key)
    if v is not None: return v
    if redis.set(f"lock:{key}", "1", nx=True, ex=lock_ttl):
        try:
            v = recompute()
            cache.set(key, v, ttl=3600 + random.randint(-600, 600))
            return v
        finally:
            redis.delete(f"lock:{key}")
    else:
        time.sleep(0.05)
        return get_with_lock(key, recompute, lock_ttl)

# Negative cache
def get_user(uid):
    key = f"user:{uid}"
    v = cache.get(key)
    if v == "__NULL__": return None
    if v: return json.loads(v)
    u = db.users.find_one(uid)
    cache.set(key, json.dumps(u) if u else "__NULL__",
              ttl=60 if not u else 3600 + random.randint(-300, 300))
    return u

# Tạo cluster
redis-cli --cluster create 10.0.0.1:7000 10.0.0.2:7000 10.0.0.3:7000 \
  10.0.0.4:7000 10.0.0.5:7000 10.0.0.6:7000 --cluster-replicas 1

# Thêm node + reshard
redis-cli --cluster add-node 10.0.0.10:7000 10.0.0.1:7000
redis-cli --cluster reshard 10.0.0.1:7000 --cluster-from all \
  --cluster-to <new-id> --cluster-slots 4096 --cluster-yes

# Check
redis-cli --cluster check 10.0.0.1:7000

-- Data model theo query pattern
CREATE TABLE messages_by_room (
  room_id UUID,
  ts TIMESTAMP,
  msg_id TIMEUUID,
  body TEXT,
  PRIMARY KEY ((room_id), ts, msg_id)
) WITH CLUSTERING ORDER BY (ts DESC, msg_id DESC)
  AND default_time_to_live = 2592000
  AND compaction = {'class': 'TimeWindowCompactionStrategy',
                    'compaction_window_unit': 'DAYS',
                    'compaction_window_size': 1};

-- Consistency level
CONSISTENCY LOCAL_QUORUM;

-- Nodetool, ops đời thường
nodetool status
nodetool compactionstats
nodetool tablehistograms keyspace table
nodetool repair -pr

CREATE EXTENSION vector;

-- Table
CREATE TABLE docs (id BIGSERIAL, embedding vector(1536));

-- Index
CREATE INDEX ON docs USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Runtime tune
SET hnsw.ef_search = 100;

-- Query (cosine)
SELECT id, 1 - (embedding <=> $1) AS sim
FROM docs ORDER BY embedding <=> $1 LIMIT 10;

-- Hybrid (RRF)
WITH vec AS (SELECT id, ROW_NUMBER() OVER (ORDER BY embedding <=> $1) r FROM docs LIMIT 50),
     kw  AS (SELECT id, ROW_NUMBER() OVER (ORDER BY ts_rank_cd(...) DESC) r FROM docs LIMIT 50)
SELECT id, SUM(1.0/(60 + r)) AS score
FROM (SELECT * FROM vec UNION ALL SELECT * FROM kw) t
GROUP BY id ORDER BY score DESC LIMIT 10;

-- Outbox table
CREATE TABLE outbox (
  id BIGSERIAL PRIMARY KEY,
  aggregate_type TEXT, aggregate_id TEXT,
  event_type TEXT, payload JSONB,
  created_at TIMESTAMPTZ DEFAULT now(),
  processed_at TIMESTAMPTZ
);
CREATE INDEX ON outbox (id) WHERE processed_at IS NULL;

-- Worker polling safely
SELECT id, event_type, payload FROM outbox
WHERE processed_at IS NULL
ORDER BY id FOR UPDATE SKIP LOCKED LIMIT 500;

-- Debezium PG prerequisites
-- postgresql.conf:
--   wal_level = logical
--   max_replication_slots = 4
--   max_wal_senders = 4
CREATE USER debezium WITH REPLICATION PASSWORD '...';
CREATE PUBLICATION dbz_pub FOR TABLE orders, order_items;

# pgbench
pgbench -i -s 100 bench
pgbench -c 32 -j 8 -T 600 -P 10 bench

# sysbench
sysbench oltp_read_write --mysql-host=... --threads=64 --time=600 run

# k6 (app-level)
k6 run -u 100 -d 30m script.js