#!/usr/bin/env bash
# VICIfast Research, Report 01: "The Two Bottlenecks Inside a Busy VICIdial Server"
# Check 1: who is holding VICIdial's tables right now?
#
# READ-ONLY. Safe on a live floor: it reads MySQL's process list and two
# status counters about once a second, for 30 seconds by default.
# Usage:  sudo bash vicidial-check-locks.sh [seconds]
#
# On MyISAM a statement locks the whole table while it runs. One slow read
# (a report, a dashboard, a lead search) makes the dialer's next write wait,
# and every read after that write waits too: a lock convoy. This shows how
# often that happens, how deep the queue gets, and which statements hold it.
set -u
SECS=${1:-30}
# Shared by the report's database checks: connect the way VICIdial does,
# from /etc/astguiclient.conf, through a temporary client file only root can
# read, so the password never appears in `ps` or on screen.
CONF=${ASTGUICLIENT_CONF:-/etc/astguiclient.conf}
MYCNF=$(mktemp)
chmod 600 "$MYCNF"
trap 'rm -f "$MYCNF"' EXIT
conf() { awk -F'=>' -v k="$1" '{ gsub(/[[:space:]\r]/, "", $1) } $1 == k { gsub(/^[[:space:]]+|[[:space:]\r]+$/, "", $2); print $2; exit }' "$CONF"; }
if [ -r "$CONF" ]; then
  printf '[client]\nhost=%s\nuser=%s\npassword=%s\nport=%s\n' \
    "$(conf VARDB_server)" "$(conf VARDB_user)" "$(conf VARDB_pass)" "$(conf VARDB_port)" > "$MYCNF"
  DB=$(conf VARDB_database)
else
  echo "Cannot read $CONF: run this as root on a VICIdial database server." >&2
  exit 1
fi
M() { mysql --defaults-extra-file="$MYCNF" -N -B "$@"; }
M -e 'SELECT 1' >/dev/null 2>&1 || { echo "Cannot connect to MySQL with the credentials in $CONF." >&2; exit 1; }

echo "== VICIdial lock check on $(hostname -s), $(date '+%Y-%m-%d %H:%M:%S %Z'), ${SECS}s"
echo
echo "-- Engines of the busiest VICIdial tables"
M -e "SELECT table_name, engine, table_rows FROM information_schema.tables
      WHERE table_schema='$DB' AND table_name IN ('vicidial_log','vicidial_list','vicidial_agent_log',
      'vicidial_closer_log','call_log','vicidial_live_agents','vicidial_auto_calls','vicidial_log_extended')
      ORDER BY table_rows DESC" | awk -F'\t' '{ printf "   %-24s %-7s %12s rows\n", $1, $2, $3 }'
echo "   concurrent_insert=$(M -e 'SELECT @@concurrent_insert')  low_priority_updates=$(M -e 'SELECT @@low_priority_updates')"
echo

SAMPLES=$(mktemp); trap 'rm -f "$MYCNF" "$SAMPLES"' EXIT
w0=$(M -e "SHOW GLOBAL STATUS LIKE 'Table_locks_waited'" | awk '{print $2}')
i0=$(M -e "SHOW GLOBAL STATUS LIKE 'Table_locks_immediate'" | awk '{print $2}')
t0=$(date +%s)
maxq=0; busy=0
for _ in $(seq 1 "$SECS"); do
  # One row per running statement: seconds, state, and the statement with
  # numbers and quoted values blanked, so the same query groups together.
  M -e "SELECT TIME, IF(STATE LIKE '%table level lock%' OR STATE='Locked','WAIT','RUN'),
        LEFT(REGEXP_REPLACE(REGEXP_REPLACE(REPLACE(REPLACE(INFO, '\n', ' '), '\t', ' '),
        '\\'[^\\']*\\'', '?'), '[0-9]+', '#'), 110)
        FROM information_schema.PROCESSLIST
        WHERE COMMAND='Query' AND INFO IS NOT NULL AND INFO NOT LIKE '%PROCESSLIST%'" > "$SAMPLES.now" 2>/dev/null
  q=$(awk -F'\t' '$2=="WAIT"' "$SAMPLES.now" | wc -l)
  [ "$q" -gt "$maxq" ] && maxq=$q
  [ "$q" -ge 20 ] && busy=$((busy + 1))
  # A statement running a second or more while others wait is a holder.
  if [ "$q" -gt 0 ]; then awk -F'\t' '$2=="RUN" && $1>=1 { print $3 }' "$SAMPLES.now" >> "$SAMPLES"; fi
  sleep 1
done
rm -f "$SAMPLES.now"
t1=$(date +%s)
w1=$(M -e "SHOW GLOBAL STATUS LIKE 'Table_locks_waited'" | awk '{print $2}')
i1=$(M -e "SHOW GLOBAL STATUS LIKE 'Table_locks_immediate'" | awk '{print $2}')
dt=$((t1 - t0)); [ "$dt" -lt 1 ] && dt=1
waits=$(( (w1 - w0) / dt )); total=$(( (w1 - w0) + (i1 - i0) )); [ "$total" -lt 1 ] && total=1

echo "-- Lock waits: $waits per second ($(( 100 * (w1 - w0) / total ))% of table locks had to wait; most are brief)"
echo "-- Deepest queue: $maxq statements waiting at once; $busy of $SECS samples had 20 or more"
echo
if [ -s "$SAMPLES" ]; then
  echo "-- Statements that ran 1 s or more while others waited (samples, statement):"
  sort "$SAMPLES" | uniq -c | sort -rn | head -8 | sed 's/^/   /'
else
  echo "-- Nothing ran 1 s or more while others waited."
fi
echo
echo "-- Verdict"
# Queue depth, not waits per second, is what the dialer feels: a healthy busy
# floor still logs 40-60 brief lock waits a second with nothing queued.
if [ "$maxq" -ge 20 ] || [ "$busy" -ge 2 ]; then
  echo "   Lock convoys: the dialer's writes are queueing behind slow reads."
  echo "   Match the statements above against section 3 of the report (vicifast.com/research/vicidial-bottlenecks):"
  echo "   day-long COUNT/SUM over vicidial_log = a dashboard or stats tool; 'vicidial_log where phone_number'"
  echo "   = VICIdial's lead search without an index (check 2); 'where user=' = user_stats.php."
elif [ "$maxq" -ge 5 ]; then
  echo "   Some queueing, not yet convoys. Run it again at your busiest hour."
else
  echo "   Healthy right now: no queue worth the name."
fi