JusDB vs Percona

Identifies blocking transaction IDs, query execution times, and thread pool connection saturation during high-concurrency production spikes.

# 1. Audit active transaction lock wait tree and blocking queries
mysql -h db-primary -u dbre_admin -p -e "
SELECT 
  waiting_trx_id, 
  waiting_pid, 
  waiting_query, 
  blocking_trx_id, 
  blocking_pid, 
  blocking_query, 
  wait_age, 
  sql_kill_blocking_query 
FROM sys.innodb_lock_waits 
ORDER BY wait_age_secs DESC 
LIMIT 5;"

# 2. Inspect thread pool utilization and running queries vs max_connections
mysql -h db-primary -u dbre_admin -p -e "
SHOW GLOBAL STATUS WHERE Variable_name IN (
  'Threads_connected', 'Threads_running', 'Max_used_connections', 'Aborted_connects'
);
SHOW GLOBAL VARIABLES LIKE 'max_connections';"

Monitors active PostgreSQL client connection states and audits replication slot WAL retention bytes to prevent unexpected volume full lockouts.

# 1. Inspect connection concentration and idle-in-transaction states
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  state, 
  count(*) AS total_connections, 
  max(now() - state_change) AS max_duration 
FROM pg_stat_activity 
GROUP BY state 
ORDER BY total_connections DESC;"

# 2. Audit logical replication slot WAL accumulation and lag bytes
psql -h pg-primary -U dbre_admin -d postgres -c "
SELECT 
  slot_name, 
  plugin, 
  active, 
  pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn) AS retained_wal_bytes 
FROM pg_replication_slots 
ORDER BY retained_wal_bytes DESC;"