Performance Profiling & Bottleneck Analysis Questions

Overview / goal
I would quickly gather evidence to determine whether the problem is host-local I/O saturation, network/storage-array issues (iSCSI/FC), or application-driven I/O patterns, then apply a short-term mitigation to restore app responsiveness.

Metrics & logs to collect

• Host: CPU, load, iowait, context switches, interrupts
  • Commands:
  bash

  vmstat 1 10
  mpstat -P ALL 1 10
  uptime
  

• Disk I/O per device and per-process:
  bash

  iostat -x 1 10
  iotop -o -b -n 10
  pidstat -d 1 10
  

• Filesystem and kernel messages:
  bash

  dmesg | tail -n 200
  journalctl -k --since "15 minutes ago"
  tail -n 200 /var/log/messages
  

• Network/iSCSI checks:
  bash

  ping -c 10 <storage_ip>
  traceroute <storage_ip>
  iscsiadm -m session -P3
  cat /proc/scsi/scsi
  multipath -ll
  

• Storage-array metrics/logs (via vendor portal/CLI): LUN latency, queue depth, controller CPU, cache hit ratio, pending ops per LUN.
• Database: slow query log, DB wait stats (e.g., pg_stat_statements/wait_event, Oracle AWR), connection/transaction rates, checkpoint activity.
• Compare with other hosts using same datastore to see if problem is multi-host.

Isolation strategy

1. Host-level vs per-process:
   • If iotop/pidstat shows one DB process or backup consuming majority IO -> application-driven.
   • If many processes across devices show high await and kernel shows device errors → likely storage/array.
2. Host vs network/storage:
   • Low device utilization but high latency + increased network RTT to storage → network/iSCSI or switch.
   • High queue size on LUN + array reports increased latency → storage-array.
3. Cross-check:
   • If other VMs/hosts on same datastore show similar latency → storage/array issue.
   • If only this DB host affected and local spikes coincide with DB checkpoints/backup → app-driven.

Short-term mitigations

• Immediately throttle or pause heavy background jobs/backups/repairs on the DB host.
• Route DB traffic to a healthy replica (failover or read-only redirect) if available.
• If iSCSI network shows congestion, move paths, failover multipath paths, or increase NIC bandwidth/priority.
• On host: change IO scheduler to noop or mq-deadline for virtualized paths, tune read-ahead, or temporarily raise filesystem writeback settings carefully.
• Engage storage team to check array cache, free spare capacity, and increase queue depth or add spindles/flash tier if needed.

What I’d report

• Collected metrics, top offending process(es), storage array latency/queue stats, whether other hosts affected, and immediate actions taken; then propose next steps (detailed RCA with timeline, capacity fixes, or app tuning).

Initial framing (0:00–0:30)

• Confirm alert details, affected host, service, business impact (which endpoints, error rate).
• Note machine: 4 cores, load 12, CPU 95%, latency 1.5s — likely CPU saturation or runaway IO.

Quick read-only checks (0:30–3:00)

• CPU and load processes:

top -b -n1 | head -n20
ps -eo pid,ppid,cmd,%cpu,%mem --sort=-%cpu | head

• Check per-CPU utilization and interrupts:

mpstat -P ALL 1 1
cat /proc/interrupts

• IO wait and disk:

vmstat 1 3
iostat -xz 1 2
dmesg | tail -n50

• Network/session pressure:

ss -tunp | wc -l
netstat -s | head

Identify culprit (3:00–6:00)

• If single process heavy CPU: note PID, service name from ps/top.
• If many processes: check cron/jobs, sudden traffic spike (nginx/access logs), or DDoS:

tail -n200 /var/log/nginx/access.log | awk '{print $1}' | sort | uniq -c | sort -nr | head

Immediate mitigations to reduce user impact (6:00–12:00)

• If one runaway process (safe to restart): gracefully restart service:

systemctl restart myservice

or

kill -15 <PID> && sleep 5 || kill -9 <PID>

• If CPU-bound but service must stay up: lower priority / limit CPU share:

renice +10 <PID>
cgcreate -g cpu:/limited && echo 50000 > /sys/fs/cgroup/cpu/limited/cpu.cfs_quota_us && cgclassify -g cpu:limited <PID>

• If traffic spike: enable maintenance/503, scale out (add instance / move load balancer), or temporarily block offending IPs:

# Nginx: return 503 via upstream config or toggle maintenance page
iptables -A INPUT -s 1.2.3.4 -j DROP

Short-term validation (12:00–14:00)

• Re-run top/vmstat/iostat and check latency metrics, error rates, and access logs to confirm mitigation effect.

Next steps (14:00–15:00)

• If mitigated: document root cause, collect logs (sar, top, system journal), schedule postmortem and permanent fixes (auto-scaling, resource limits, code optimization).
• If not mitigated: escalate to on-call devs/DBAs, and prepare to failover / detach instance from load balancer.

Notes on decision logic

• Prefer non-destructive ops first (renice, cgroup limits, blocking bad traffic).
• Restart only if safe and quick; always collect diagnostics before kill.
• Focus first 15 minutes on containment, diagnostics, and restoring user experience.

Situation assessment / profiling steps

• Confirm symptom: steady QPS, higher avg read latency, more connection wait (threads_sleep/threads_connected rising). Gather metrics: slow query log, MySQL performance_schema, OS I/O (iostat -x), vmstat, and tool-based summaries (pt-query-digest).
• Commands to run:

-- top offenders
SET GLOBAL slow_query_log = 1; SET GLOBAL long_query_time = 0.1;
-- explain suspect
EXPLAIN FORMAT=JSON SELECT ...;
-- buffer pool status
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';
SHOW ENGINE INNODB STATUS\G

Analyze queries & index usage

• Use EXPLAIN to see full table scans, filesort, temporary tables.
• Use pt-query-digest on slow log to rank by lock/rows/latency.
• Check missing or non-covering indexes; identify queries with large examined_rows.

Example index fix:

CREATE INDEX idx_orders_user_created ON orders (user_id, created_at);

Buffer pool behavior

• Evaluate buffer pool hit ratio:

-- compute:
Innodb_buffer_pool_read_requests vs Innodb_buffer_pool_reads

• High reads from disk (Innodb_buffer_pool_reads rising) ⇒ buffer too small or working set exceeds pool.

At least four tuning steps

1. Schema/query changes:
   • Add composite or covering indexes for top slow queries; rewrite queries to avoid SELECT * and use indexed predicates; add LIMIT and pagination where appropriate.
2. Query patterns:
   • Introduce read replicas for heavy read paths and route non-critical reads; implement connection pooling (ProxySQL / PgBouncer) to reduce connection churn and wait times.
3. Buffer pool and InnoDB config:
   • Increase innodb_buffer_pool_size to cover working set (e.g., 60–75% of RAM on dedicated DB host), enable multiple buffer pool instances (innodb_buffer_pool_instances).
   • Tune innodb_io_capacity / innodb_io_capacity_max to match underlying storage performance; set innodb_flush_method=O_DIRECT for Linux to reduce double cache.
4. Server-level/OS tuning:
   • Improve I/O: move data files to faster disks (NVMe), adjust vm.swappiness, ensure no swap; tune file descriptors and max_connections; use asynchronous/distributed caching (Redis) for hot reads.
   • If contention on metadata or locks, increase innodb_thread_concurrency carefully and monitor.

Validation & monitoring

• After changes, re-run pt-query-digest, compare 95th/99th percentile latencies, monitor Innodb_buffer_pool_reads reduction, connection wait metrics, and OS I/O stalls. Iterate: rollback or refine indexes that increase write cost.

Short goal: confirm IO-bound system (processes in D, high iowait) and quickly reduce IO to restore responsiveness.

Ordered checklist (commands + what to look for)

1. Confirm symptoms

uptime          # load avg vs CPU cores
top -b -n1 | head -20   # many D processes
ps -eo state,pid,cmd | awk '$1=="D"'

2. Check IO wait / device stats

vmstat 1 5       # see wa column high
iostat -x 1 3    # %util, await, svctm per device
iotop -ao        # per-process IO

3. Inspect kernel/storage errors

dmesg --ctime | tail -50
journalctl -k -n 100

4. Check filesystem & mounts (local or remote)

mount | grep -E 'nfs|cifs'
lsblk -o NAME,MOUNTPOINT,ROTA
df -h

5. Storage health & network path

smartctl -a /dev/sdX    # if local
sudo showmount -e server; nfsiostat 1 3   # if NFS
ip -s link; ethtool eth0

6. Find offending workload

ps aux --sort=-%mem,-%cpu | head
iotop -bo                    # heavy writers/readers
lsof /path/to/busy/file

Quick mitigations (fast, ordered)

• Throttle or deprioritize IO-heavy jobs: ionice -c3 -p <pid> or renice.
• Stop nonessential services: systemctl stop backup_job or app_workers.
• If NFS/remote storage is causing hang, check network and restart mount on client or storage side; remount read-only if safe.
• If kernel shows device faults and processes stuck in D cannot be killed, plan controlled reboot after scheduling downtime.

Notes / next steps

• If root cause is hardware/storage, escalate to storage team and schedule replacement. Collect logs (iostat, dmesg, iotop) before reboot.

Approach summary
Describe reproducible observation, classify growth (native vs JVM/managed vs cache), collect evidence, profile, apply short-term mitigations, then remediate.

Reproduce & baseline

• Observe resident growth with:

ps -o pid,cmd,%mem,rss,vsz -p <pid>
pmap -x <pid> | tail -n 20
smem -P <process_name>

• Time-series: collect every minute to confirm steady +100MB/hr.

Differentiate causes

• Managed heap (JVM): check GC metrics and heap usage:

jcmd <pid> GC.class_histogram
jcmd <pid> GC.heap_info
jmap -heap <pid> > /tmp/heapinfo.txt
jstat -gc <pid> 1000

If heap growth matches RSS, likely heap/G1 tuning or retention.

• Native leak: inspect native allocations and libs:

pmap -x <pid>
lsof -p <pid> | grep deleted
readelf -a /proc/<pid>/exe

Use native profilers: Linux perf, heaptrack, valgrind/massif for C/C++:

perf record -p <pid> -- sleep 60
heaptrack <pid>   # or run under heaptrack
massif --tool=massif ./app

• Intentional cache growth: check application metrics/logs, endpoints toggling cache, and in-JVM caches (Caffeine, Guava) sizes via JMX:

jmxterm -l localhost:9010 -n

Look for eviction settings or missing TTL.

Profiling tools

• JVM: jcmd, jmap, jstack, VisualVM, async-profiler
• Native: perf, heaptrack, valgrind/massif, pmap
• System: sar, vmstat, free -m, dstat, atop

Short-term mitigations

• Restart service during low traffic windows or implement rolling restarts
• Enforce cgroup memory limit and restart policy:

systemd-run --scope -p MemoryMax=1G <service_command>

• Increase swap temporarily:

fallocate -l 2G /swapfile; mkswap /swapfile; swapon /swapfile

• Tune GC flags (JVM): enable verbose GC, adjust heap sizes and G1 ergonomics, enable -XX:+UseStringDeduplication
• Add eviction/TTL to caches or reduce cache size via config

Next steps / remediation

• Reproduce with profiler, fix code paths (native free, correct cache logic), adjust GC/config, add monitoring/alerts for RSS slope.