#!/usr/bin/env 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.
# The midnight latency-archive test (section 8.1): copies a day of
# vicidial_agent_latency_log rows into scratch tables (vf_lat_*) and replays VICIdial's
# nightly steps on MyISAM and InnoDB while 44 simulated agent screens write. Heavy; test
# servers only; drops its scratch tables at the end.
#
set -u
DB=$(awk -F'=> *' '/^VARDB_database/{print $2}' /etc/astguiclient.conf | tr -d ' \r')
W=/root/vf-engine-ab/lattest; mkdir -p $W
cat > $W/inserter.pl <<'PL'
#!/usr/bin/perl
# 44 "agent screens" writing one latency row a second each, like vicidial.php does,
# into vf_lat_src; reports the slowest write and how many took 1 s or more.
use strict; use DBI; use Time::HiRes qw(time sleep);
my ($db, $secs, $out) = @ARGV;
my %c; open(my $f, '<', '/etc/astguiclient.conf'); while (<$f>) { $c{$1} = $2 if /^(VARDB_\w+)\s*=>\s*(\S+)/ }
my $dbh = DBI->connect("DBI:mysql:$db:$c{VARDB_server}:$c{VARDB_port}", $c{VARDB_user}, $c{VARDB_pass}, { RaiseError => 0, AutoCommit => 1 });
my $sth = $dbh->prepare("INSERT INTO vf_lat_src SET latency=10, web_ip='127.0.0.1', user=?, log_date=NOW()");
my ($n, $slow, $max) = (0, 0, 0); my $end = time + $secs; my %ph;
while (time < $end && ! -e "$out.stop") {
  my $t0 = time;
  my $phase = -e "$out.phase" ? do { open(my $p, '<', "$out.phase"); my $l = <$p>; chomp $l; $l } : 'idle';
  for my $a (1..44) { my $s = time; $sth->execute("vf$a"); my $d = time - $s; $n++; $slow++ if $d >= 1; $max = $d if $d > $max; $ph{$phase} = $d if $d > ($ph{$phase} // 0); }
  my $rest = 1 - (time - $t0); sleep($rest) if $rest > 0;
}
open(my $o, '>', $out); printf $o "writes %d, >=1s %d, slowest %.2fs (%s)\n", $n, $slow, $max, join(' ', map { sprintf "%s %.2fs", $_, $ph{$_} } sort keys %ph); close $o;
PL
run_case() {   # $1 label, $2 engine, $3 isolation for the archiver session
  mysql "$DB" -e "DROP TABLE IF EXISTS vf_lat_src, vf_lat_arch;
    CREATE TABLE vf_lat_src LIKE vicidial_agent_latency_log; ALTER TABLE vf_lat_src ENGINE=$2;
    INSERT INTO vf_lat_src SELECT * FROM vicidial_agent_latency_log_archive
      WHERE log_date >= CURDATE() - INTERVAL 1 DAY AND log_date < CURDATE();
    CREATE TABLE vf_lat_arch LIKE vicidial_agent_latency_log_archive; ALTER TABLE vf_lat_arch ENGINE=$2;"
  rows=$(mysql -N "$DB" -e "select count(*) from vf_lat_src")
  rm -f $W/ins.$1.stop $W/ins.$1.phase
  perl $W/inserter.pl "$DB" 600 $W/ins.$1 & ip=$!
  sleep 10
  t0=$(date +%s.%N); echo copy > $W/ins.$1.phase
  mysql "$DB" -e "SET SESSION TRANSACTION ISOLATION LEVEL $3; INSERT IGNORE INTO vf_lat_arch SELECT * FROM vf_lat_src;"
  t1=$(date +%s.%N); echo delete > $W/ins.$1.phase
  mysql "$DB" -e "SET SESSION TRANSACTION ISOLATION LEVEL $3; DELETE FROM vf_lat_src WHERE log_date < CURDATE();"
  t2=$(date +%s.%N); echo optimize > $W/ins.$1.phase
  mysql "$DB" -e "OPTIMIZE TABLE vf_lat_src" >/dev/null
  t3=$(date +%s.%N); echo after > $W/ins.$1.phase
  sleep 5; touch $W/ins.$1.stop
  wait $ip
  printf "%-22s rows %7d | copy %5.1fs delete %5.1fs optimize %5.1fs | agent writes: %s" "$1" "$rows" \
    "$(echo "$t1-$t0" | bc)" "$(echo "$t2-$t1" | bc)" "$(echo "$t3-$t2" | bc)" "$(cat $W/ins.$1)"; echo
}
run_case myisam MyISAM "REPEATABLE READ"
run_case innodb-rr InnoDB "REPEATABLE READ"
run_case innodb-rc InnoDB "READ COMMITTED"
mysql "$DB" -e "DROP TABLE IF EXISTS vf_lat_src, vf_lat_arch"
echo "binlog: $(mysql -N -e 'select @@log_bin, @@binlog_format')"