#!/bin/bash
# VICIfast Research, Report 01: The Two Bottlenecks Inside a Busy VICIdial Server
# Test harness. FOR A TEST SERVER, NOT A LIVE FLOOR. Read it before you run it.
# Read-only sampler: database counters, lock and stuck-statement counts, agents and
# calls every 10 s, for a long soak.
#
# Overnight soak sampler for the storage-engine experiment. Read-only.
# Writes to /root/vf-engine-ab/soak/. Stops itself at END (UTC epoch).
DB=$(awk -F'=> *' '/^VARDB_database/{print $2}' /etc/astguiclient.conf | tr -d ' \r')
O=/root/vf-engine-ab/soak; mkdir -p $O; END=$(date -d '2026-10-22 20:00 UTC' +%s)
G="'Table_locks_waited','Table_locks_immediate','Innodb_deadlocks','Innodb_row_lock_waits','Innodb_row_lock_time','Innodb_row_lock_time_max','Questions','Com_update','Com_insert','Com_delete','Com_select','Innodb_buffer_pool_reads','Innodb_buffer_pool_read_requests','Innodb_data_fsyncs','Innodb_os_log_written','Key_reads','Key_read_requests','Slow_queries','Threads_running'"
[ -f $O/samples.tsv ] || echo -e "utc\t$(mysql -N -e "select group_concat(variable_name order by variable_name separator '\t') from information_schema.global_status where variable_name in ($G)")\twait_tbl_lock\twait_tbl_lock_max_s\tinnodb_lock_wait\tvd_stmts_ge1s\tvd_max_s\tlive_agents\tfree_agents\tauto_calls" > $O/samples.tsv
n=0
while [ $(date +%s) -lt $END ]; do
  u=$(date -u '+%F %T')
  st=$(mysql -N -e "select group_concat(variable_value order by variable_name separator '\t') from information_schema.global_status where variable_name in ($G)")
  pl=$(mysql -N -e "select
    (select count(*) from information_schema.processlist where state='Waiting for table level lock' and user not in ('root','system user')),
    (select coalesce(max(time),0) from information_schema.processlist where state='Waiting for table level lock' and user not in ('root','system user')),
    (select count(*) from information_schema.innodb_trx where trx_state='LOCK WAIT'),
    (select count(*) from information_schema.processlist where command='Query' and time>=1 and user not in ('system user')),
    (select coalesce(max(time),0) from information_schema.processlist where command='Query' and user not in ('system user')),
    (select count(*) from $DB.vicidial_live_agents),
    (select count(*) from $DB.vicidial_live_agents where status in ('READY','CLOSER')),
    (select count(*) from $DB.vicidial_auto_calls)")
  echo -e "$u\t$st\t$pl" >> $O/samples.tsv
  # anything running >=5 s: what is it (truncated, no data values beyond the SQL text)
  mysql -N -e "select now(), user, time, state, left(replace(info,'\n',' '),220) from information_schema.processlist where command='Query' and time>=5 and user<>'system user' and info not like '%information_schema.processlist%'" >> $O/long-queries.tsv
  n=$((n+1))
  if [ $((n % 30)) -eq 0 ]; then   # every 5 min: calls and drops in the last 5 min
    mysql -N $DB -e "select now(), (select count(*) from vicidial_log where call_date >= now() - interval 5 minute), (select count(*) from vicidial_log where call_date >= now() - interval 5 minute and status='DROP'), (select count(*) from vicidial_closer_log where call_date >= now() - interval 5 minute), (select count(*) from vicidial_closer_log where call_date >= now() - interval 5 minute and status in ('DROP','AFTHRS','NANQUE'))" >> $O/calls-5min.tsv
  fi
  if [ $((n % 360)) -eq 0 ]; then  # hourly: per-table write/read heat
    mysql -N -e "select now(), table_name, rows_read, rows_changed, rows_changed_x_indexes from information_schema.table_statistics where table_schema='$DB' order by rows_changed desc limit 40" >> $O/table-stats.tsv
  fi
  sleep 10
done
mysql -N -e "select now(), table_name, rows_read, rows_changed, rows_changed_x_indexes from information_schema.table_statistics where table_schema='$DB' order by rows_changed desc limit 80" >> $O/table-stats.tsv