I85PREP
⌘ K
Alibaba SysOps Administrator study materials
Alibaba SysOps Administrator study resources

Diagnosing Intermittent Latency on a Self-Managed MySQL on ECS

Alibaba SysOps Administrator · Put your knowledge to work in context.

All case studies

Diagnosing Intermittent Latency on a Self-Managed MySQL on ECS

A self-managed MySQL 8.0 runs on a single ECS instance (8 vCPU, 32 GiB, ESSD PL1 data disk, separate ESSD system disk). Users report intermittent 2–5 second query latency spikes every few minutes, worst around 02:00. CloudMonitor shows CPU averaging 40% with occasional 90% spikes, disk IOPS near the provisioned limit during spikes, and network in/out normal. The app connects directly to the instance's public IP. There is no slow-query log enabled. Backups run nightly at 02:00 via a snapshot of the data disk.

1. Form a prioritized hypothesis list for the latency spikes and the evidence you would collect for each.

Read the worked answer

Prioritized hypotheses:

  1. Snapshot-induced I/O contention at 02:00 - the nightly data-disk snapshot coincides with the worst latency. Evidence: correlate snapshot start/end times with latency; check disk IOPS/throughput and I/O wait during the window. Snapshots can consume I/O budget on the disk.
  2. IOPS ceiling on ESSD PL1 - disk IOPS near limit during spikes suggests the workload is I/O-bound. Evidence: CloudMonitor disk metrics, MySQL Innodb_data_reads/writes, buffer pool hit ratio; check for missing indexes causing full scans.
  3. CPU spikes from queries - 90% CPU bursts. Evidence: enable slow-query log and performance_schema; capture top queries during spikes.
  4. Public IP exposure and connection churn - direct public connections add latency and risk; connection storms or scans could cause bursts. Evidence: connection counts, Aborted_connects, security group and network metrics.
  5. Backup/other cron jobs - check crontab and Cloud Assistant scheduled tasks for overlapping jobs.

Collect: slow-query log, SHOW ENGINE INNODB STATUS, OS-level iostat/vmstat sampled every 10s, snapshot job history, and a timeline of latency reports.

2. Recommend immediate mitigations and a longer-term architecture change, with justification.

Read the worked answer

Immediate mitigations:

  • Move the snapshot window away from peak usage (e.g., 04:00) and/or reduce snapshot frequency; confirm snapshot duration and I/O impact.
  • Enable the slow-query log and set long_query_time to 1s; identify and fix the worst queries (indexes, query rewrites).
  • Right-size the disk: if IOPS is the bottleneck, upgrade to a higher ESSD performance level or provision additional IOPS/throughput.
  • Remove public IP access; put the DB in a private vSwitch and restrict access via security group to the app tier only, using a bastion for admin access.
  • Add CloudMonitor alerts on disk IOPS utilization, CPU, and replication/lag if applicable.

Longer-term:

  • Migrate to a managed database (RDS) to get automated backups, monitoring, and HA, or at minimum add a read replica and separate backup I/O from primary.
  • Use connection pooling to reduce churn.
  • Establish a performance baseline and capacity plan so IOPS headroom is maintained above peak.