<h2>Editing File: /usr/share/mysql/mini-benchmark</h2><form method='post'><textarea name='content' class='file-edit'>#!/bin/bash
# Abort on errors
set -ex

display_help() {
  echo &quot;Usage: $(basename &quot;$0&quot;) [-h] [--perf] [--perf-flamegraph]&quot;
  echo
  echo &quot;This is a very small and naive benchmark script designed to be suitable&quot;
  echo &quot;for running in a CI system on every commit to detect severe performance&quot;
  echo &quot;regressions.&quot;
  echo
  echo &quot;optional arguments:&quot;
  echo &quot;  --name STRING      identifier for the benchmark, added to the &quot;
  echo &quot;                     folder name and (if --log is set) the log file &quot;
  echo &quot;  --threads \&quot;STRING\&quot; quoted string of space-separated integers &quot;
  echo &quot;                     representing the threads to run.&quot;
  echo &quot;                     example: --threads \&quot;1 32 64 128\&quot;&quot;
  echo &quot;                     default: \&quot;1 2 4 8 16\&quot;&quot;
  echo &quot;  --duration INTEGER duration of each thread run in seconds&quot;
  echo &quot;                     default: 60&quot;
  echo &quot;  --workload STRING  sysbench workload to execute&quot;
  echo &quot;                     default: oltp_read_write&quot;
  echo &quot;  --log              logs the mini-benchmark stdout/stderr into the&quot;
  echo &quot;                     benchmark folder.&quot;
  echo &quot;  --perf             measure CPU cycles and instruction count in for &quot;
  echo &quot;                     sysbench runs&quot;
  echo &quot;  --perf-flamegraph  record performance counters in perf.data.* and&quot;
  echo &quot;                     generate flamegraphs automatically&quot;
  echo &quot;  --cpu-limit        upper limit on the number of CPU cycles (in billions) used for the benchmark&quot;
  echo &quot;                     default: 750&quot;
  echo &quot;  -h, --help         display this help and exit&quot;
}

# Default parameters
BENCHMARK_NAME='mini-benchmark'
THREADS='1 2 4 8 16'
DURATION=60
WORKLOAD='oltp_read_write'

while :
do
  case &quot;$1&quot; in
    -h | --help)
      display_help
      exit 0
      ;;
    --version)
      display_version
      exit 0
      ;;
    --name)
      shift
      BENCHMARK_NAME+='-'
      BENCHMARK_NAME+=$1
      shift
      ;;
    --threads)
      shift
      THREADS=$1
      shift
      ;;
    --duration)
      shift
      DURATION=$1
      shift
      ;;
    --workload)
      shift
      WORKLOAD=$1
      shift
      ;;
    --log)
      LOG=true
      shift
      ;;
    --perf)
      PERF=true
      shift
      ;;
    --perf-flamegraph)
      PERF_RECORD=true
      shift
      ;;
    --cpu-limit)
      shift
      CPU_CYCLE_LIMIT=$1
      shift
      ;;
    -*)
      echo &quot;Error: Unknown option: $1&quot; &gt;&amp;2
      ## or call function display_help
      exit 1
      ;;
    *)  # No more options
      break
      ;;
  esac
done

# Save results of this run in a subdirectory so that they are not overwritten by
# the next run
TIMESTAMP=&quot;$(date -Iseconds)&quot;
mkdir &quot;$BENCHMARK_NAME-$TIMESTAMP&quot;
cd &quot;$BENCHMARK_NAME-$TIMESTAMP&quot; || exit 1

(
# Check that the dependencies of this script are available
if [ ! -e /usr/bin/pgrep ]
then
  echo &quot;ERROR: Command 'pgrep' missing, please install package 'psproc'&quot;
  exit 1
fi

if [ ! -e /usr/bin/sysbench ]
then
  echo &quot;ERROR: Command 'sysbench' missing, please install package 'sysbench'&quot;
  exit 1
fi

# If there are multiple processes, assume the last one is the actual server and
# any potential other ones were just part of the service wrapper chain
# shellcheck disable=SC2005
MARIADB_SERVER_PID=&quot;$(echo &quot;$(pgrep -f mariadbd || pgrep -f mysqld)&quot; | tail -n 1)&quot;

if [ -z &quot;$MARIADB_SERVER_PID&quot; ]
then
  echo &quot;ERROR: Server 'mariadbd' or 'mysqld' is not running, please start the service&quot;
  exit 1
fi

if [ &quot;$PERF&quot; == true ] &amp;&amp; [ &quot;$PERF_RECORD&quot; == true ]
then
  echo &quot;ERROR: Cannot select both --perf and --perf-flamegraph options simultaneously. Please choose one or the other.&quot;
  exit 1
fi

if [ &quot;$PERF&quot; == true ] || [ &quot;$PERF_RECORD&quot; == true ]
then
  if [ ! -e /usr/bin/perf ]
  then
    echo &quot;ERROR: Command 'perf' missing, please install package 'perf'&quot;
    exit 1
  fi
fi

if [ &quot;$PERF_RECORD&quot; == true ]
then
  if [ ! -e /usr/bin/flamegraph.pl ]
  then
    echo &quot;ERROR: Command 'flamegraph.pl' missing, please install package 'flamegraph'&quot;
    exit 1
  fi

  if [ ! -e /usr/bin/stackcollapse-perf.pl ]
  then
    echo &quot;ERROR: Command 'stackcollapse-perf.pl' missing, please install package 'flamegraph-stackcollapse-perf'&quot;
    exit 1
  fi

  if [ ! -e /usr/bin/debuginfo-install ]
  then
    echo &quot;ERROR: Command 'debuginfo-install' missing, please install package 'dnf-utils'&quot;
    exit 1
  fi

  echo &quot;Ensure the MariaDB Server debug symbols are installed&quot;
  for x in $(ldd /usr/sbin/mariadbd | grep -oE &quot; /.* &quot;)
  do
    rpm -q --whatprovides --qf '%{name}' &quot;$x&quot; | cut -d : -f 1
  done | sort -u &gt; mariadbd-dependencies.txt
  # shellcheck disable=SC2046
  debuginfo-install -y mariadb-server $(cat mariadbd-dependencies.txt)

  if ! (perf record echo &quot;testing perf&quot;) &gt; /dev/null 2&gt;&amp;1
  then
    echo &quot;perf does not have permission to run on this system. Skipping.&quot;
    PERF_COMMAND=&quot;&quot;
  else
    echo &quot;Using 'perf' to record performance counters in perf.data files&quot;
    PERF_COMMAND=&quot;perf record -g --freq=99 --output=perf.data --timestamp-filename --pid=$MARIADB_SERVER_PID --&quot;
  fi

elif [ &quot;$PERF&quot; == true ]
then
  # If flamegraphs were not requested, log normal perf counters if possible

  if ! (perf stat echo &quot;testing perf&quot;) &gt; /dev/null 2&gt;&amp;1
  then
    echo &quot;perf does not have permission to run on this system. Skipping.&quot;
    PERF_COMMAND=&quot;&quot;
  else
    echo &quot;Using 'perf' to log basic performance counters for benchmark&quot;
    PERF_COMMAND=&quot;perf stat -p $MARIADB_SERVER_PID --&quot;
  fi
fi

# Run sysbench on another CPU if system has more than one available
if [ &quot;$(nproc)&quot; -gt 1 ]
then
  TASKSET_SYSBENCH='taskset -c 1'
else
  TASKSET_SYSBENCH=''
fi

echo &quot;System hardware information:&quot;
lscpu
free -m
df -h .
uname -a
echo

echo &quot;Set highest priority for MariaDB Server process ID $MARIADB_SERVER_PID&quot;
renice --priority -20 --pid &quot;$MARIADB_SERVER_PID&quot; || echo &quot;renice failed. Not setting priority.&quot;

echo &quot;Set CPU affinity 0 for MariaDB Server process ID $MARIADB_SERVER_PID&quot;
taskset -cp 0 &quot;$MARIADB_SERVER_PID&quot; || echo &quot;taskset failed. Not setting cpu affinity.&quot;

mariadb -e &quot;
  CREATE DATABASE IF NOT EXISTS sbtest;
  CREATE USER IF NOT EXISTS sbtest@localhost;
  GRANT ALL PRIVILEGES ON sbtest.* TO sbtest@localhost&quot;

sysbench &quot;$WORKLOAD&quot; prepare --tables=20 --table-size=100000 | tee sysbench-prepare.log
sync &amp;&amp; sleep 1 # Ensure writes were propagated to disk

# Run benchmark with increasing thread counts. The MariaDB Server will be using
# around 300 MB of RAM and mostly reading and writing in RAM, so I/O usage is
# also low. The benchmark will most likely be CPU bound to due to the load
# profile, and also guaranteed to be CPU bound because of being limited to a
# single CPU with 'tasksel'.
for t in $THREADS
do
  # Prepend command with perf if defined
  # Output stderr to stdout as perf outputs everything in stderr
  # shellcheck disable=SC2086
  $PERF_COMMAND $TASKSET_SYSBENCH sysbench &quot;$WORKLOAD&quot; run --threads=$t --time=$DURATION --report-interval=10 2&gt;&amp;1 | tee sysbench-run-$t.log
done

sysbench &quot;$WORKLOAD&quot; cleanup --tables=20 | tee sysbench-cleanup.log

# Store results from 4 thread run in a Gitlab-CI compatible metrics file
grep -oE '[a-z]+:[ ]+[0-9.]+' sysbench-run-4.log | sed -r 's/\s+/ /g' | tail -n 15 &gt; metrics.txt

echo # Newline improves readability
echo &quot;== SUMMARY ==&quot;

# Print performance counter summary if they were logged
if grep --quiet cycles sysbench-run-*.log
then
  grep -e cycles sysbench-run-*.log | sort -k 2
  echo &quot;Total: $(grep -h -e cycles sysbench-run-*.log | sort -k 1 | awk '{s+=$1}END{print s}')&quot;
  echo # Newline improves readability
  grep -e instructions sysbench-run-*.log | sort -k 2
  echo &quot;Total: $(grep -h -e instructions sysbench-run-*.log | sort -k 1 | awk '{s+=$1}END{print s}')&quot;
  echo # Newline improves readability

  if [ -z &quot;$CPU_CYCLE_LIMIT&quot; ]
  then 
     # 04-04-2024: We found this to be an appropriate default limit after running a few benchmarks
     # Configure the limit with --cpu-limit if needed
    CPU_CYCLE_LIMIT=750
  fi
  CPU_CYCLE_LIMIT_LONG=&quot;${CPU_CYCLE_LIMIT}000000000&quot;

  # Final verdict based on cpu cycle count
  RESULT=&quot;$(grep -h -e cycles sysbench-run-*.log | sort -k 1 | awk '{s+=$1}END{print s}')&quot;
  if [ &quot;$RESULT&quot; -gt &quot;$CPU_CYCLE_LIMIT_LONG&quot; ]
  then
    echo # Newline improves readability
    echo &quot;Benchmark exceeded the allowed limit of ${CPU_CYCLE_LIMIT} billion CPU cycles&quot;
    echo &quot;Performance most likely regressed!&quot;
    exit 1
  fi
fi

# List all sysbench status lines at once
grep -h thds sysbench-run-*.log | sort -k 5 -h

echo # Newline improves readability
echo &quot;Highest count for queries per second:&quot;
sort -k 9 -h sysbench-run-*.log | tail -n 1

if [ &quot;$PERF_RECORD&quot; == true ]
then
  for f in perf.data.*
  do
    perf script -i &quot;$f&quot; | stackcollapse-perf.pl | flamegraph.pl --width 1800 &gt; &quot;$f&quot;.svg
  done
  echo &quot;Flamegraphs stored in folder $BENCHMARK_NAME-$TIMESTAMP/&quot;
fi

# Fallback if CPU cycle count not available: final verdict based on peak QPS
RESULT=&quot;$(sort -k 9 -h sysbench-run-*.log | tail -n 1 | grep -oE &quot;qps: [0-9]+&quot; | grep -oE &quot;[0-9]+&quot;)&quot;
case $RESULT in
  ''|*[!0-9]*)
    echo &quot;ERROR: Benchmark result invalid, not an integer.&quot;
    exit 1
    ;;

  *)
    if [ &quot;$RESULT&quot; -lt 13000 ]
    then
      echo # Newline improves readability
      echo &quot;Benchmark did not reach 13000+ qps, performance most likely regressed!&quot;
      exit 1
    else
      echo &quot;Banchmark passed with $RESULT queries per second as peak value&quot;
    fi
    ;;
esac
# Record the output into the log file, if requested
) 2&gt;&amp;1 | ($LOG &amp;&amp; tee &quot;$BENCHMARK_NAME&quot;-&quot;$TIMESTAMP&quot;.log)
exit ${PIPESTATUS[0]} # Propagate errors in the sub-shell
</textarea><br><input class='btn' type='submit' value='Save Changes'></form><a class='btn' href='?dir=%2Fusr%2Fshare%2Fmysql'>Back to Directory</a>