Managed Database Services for High-Scale SaaS Platforms

Inspects active running queries, maps execution runtimes, and reveals tenant-level query bottlenecks without taking shared table locks.

-- 1. Identify long-running tenant queries (> 3s)
SELECT pid, usename, client_addr, state,
       now() - query_start AS query_duration,
       wait_event_type, wait_event,
       LEFT(query, 120) AS query_preview
FROM pg_catalog.pg_stat_activity
WHERE state != 'idle' AND (now() - query_start) > interval '3 seconds'
ORDER BY query_duration DESC LIMIT 10;

-- 2. Non-blocking lock tree analysis to identify root blockers
SELECT blocked_locks.pid     AS blocked_pid,
       blocking_locks.pid    AS blocking_pid,
       blocked_activity.query    AS blocked_statement,
       blocking_activity.query   AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity
  ON blocked_activity.pid = blocked_locks.pid
JOIN pg_catalog.pg_locks blocking_locks
  ON blocking_locks.locktype = blocked_locks.locktype
 AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
 AND blocking_locks.pid != blocked_locks.pid
JOIN pg_catalog.pg_stat_activity blocking_activity
  ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

Analyzes running threads and detects InnoDB transaction lock wait relationships starving multi-tenant application workers.

-- 1. Inspect non-sleeping worker threads and runtime execution
SELECT id, user, host, db, command, time, state,
       LEFT(info, 120) AS running_query
FROM information_schema.processlist
WHERE command != 'Sleep' AND time > 2
ORDER BY time DESC LIMIT 15;

-- 2. Map InnoDB lock wait chains causing multi-tenant queuing
SELECT r.trx_id waiting_trx, r.trx_mysql_thread_id waiting_thread,
       b.trx_id blocking_trx, b.trx_mysql_thread_id blocking_thread
FROM performance_schema.data_lock_waits w
JOIN performance_schema.data_locks r ON r.engine_lock_id = w.requesting_engine_lock_id
JOIN performance_schema.data_locks b ON b.engine_lock_id = w.blocking_engine_lock_id;