MySQL vs MongoDB

Identifies blocking metadata lock chains, active InnoDB transactions, and multi-threaded replica worker lag.

-- 1. Inspect blocking metadata lock threads
SELECT 
  waiting.THREAD_ID AS waiting_thread,
  waiting.OBJECT_SCHEMA,
  waiting.OBJECT_NAME,
  blocking.THREAD_ID AS blocking_thread
FROM performance_schema.metadata_locks waiting
JOIN performance_schema.metadata_locks blocking
  ON waiting.OBJECT_SCHEMA = blocking.OBJECT_SCHEMA
  AND waiting.OBJECT_NAME = blocking.OBJECT_NAME
WHERE waiting.LOCK_STATUS = 'PENDING';

-- 2. Inspect replica lag and applier worker status
SHOW REPLICA STATUS\G

Scans system profiler for queries performing collection scans (COLLSCAN) and slow aggregation pipelines exceeding execution thresholds.

// 1. Find top slow queries performing unindexed collection scans
db.system.profile.find({
  "planSummary": "COLLSCAN",
  "millis": { $gt: 100 }
}).sort({ millis: -1 }).limit(5).pretty();

// 2. Audit current active operations running longer than 5 seconds
db.currentOp({
  "active": true,
  "secs_running": { $gt: 5 },
  "ns": { $ne: "local.oplog.rs" }
});