git.8kb.co.uk Git - postgresql/pgbench_looper/blob - pgbench_looper.sh

   1 #!/bin/bash
   2
   3 host="$(hostname)"
   4 timeslot=`date +%Y%m%d_%H-%M`
   5 logfile="/tmp/$host-$timeslot-pgbench.log"
   6 csvfile="/tmp/$host-$timeslot-pgbench.csv"
   7 customfile="/tmp/$host-$timeslot-pgbench.sql"
   8 customsql="SELECT sum(generate_series) FROM generate_series(1,100000);"
   9 customdb="TEST"
  10 kernel="$(uname -a)"
  11 kernel_params="$(sysctl -p)"
  12 rc_local="$(cat /etc/rc.local)"
  13 ram="$(cat /proc/meminfo | grep MemTotal)"
  14 cores="$(nproc)"
  15 cpudata="$(lscpu)"
  16 disk_controllers="$(lspci | grep -E 'RAID|SCSI|IDE|SATA')"
  17 mounts="$(cat /etc/mtab)"
  18 pguser="pgcontrol"
  19 scale=500
  20 runs=2000
  21 max_clients=256
  22 clients_inc=8
  23 repeat=3
  24
  25 echo "Host,Test,Scale,Runs,Clients,Iteration,TPS (exc),TPS (inc)" > $csvfile
  26 printf %100s |tr " " "=" > $logfile
  27 echo "### Starting pgbench testing on $host at $timeslot" >> $logfile
  28 echo "### Running kernel: $kernel" >> $logfile
  29 echo "### Running kernel parameters: $kernel_params" >> $logfile
  30 echo "### Local parameters: $rc_local" >> $logfile
  31 echo "### Total CPU Cores: $cores" >> $logfile
  32 echo "### CPU data: $cpudata" >> $logfile
  33 echo "### $ram" >> $logfile
  34 echo "### Disk controllers: $disk_controllers" >> $logfile
  35 echo "### Mounts: $mounts" >> $logfile
  36
  37 printf %100s |tr " " "-" >> $logfile
  38 echo "### Creating pgbench database" >> $logfile
  39 result=`psql -U $pguser -d postgres -c 'DROP DATABASE IF EXISTS "pgbench";'`
  40 echo $result >> $logfile
  41 result=`createdb pgbench -U $pguser 2>&1`
  42 echo $result >> $logfile
  43
  44 echo "### Creating populating database with scaling factor of $scale" >> $logfile
  45 result=`pgbench -i -s $scale pgbench -U $pguser 2>&1`
  46 echo $result >> $logfile
  47
  48 printf %100s |tr " " "-" >> $logfile
  49 echo "### Running read only tests" >> $logfile
  50 for i in $(eval echo {8..$max_clients..$clients_inc}); do
  51         echo "##Running test with $i clients" >> $logfile
  52         for j in $(eval echo {1..$repeat}); do
  53                 result=`pgbench -t $runs -c $i -S pgbench -U $pguser 2>&1`
  54                 echo "#Run $j: $result" >> $logfile
  55                 csvdata=$(echo "$result" | grep tps | cut -d "=" -f2 | cut -d "(" -f1 | sed -e 's/^[ \t]*//' | tr '\n' ',')
  56                 echo "$host,Read Only,$scale,$runs,$i,$j,$csvdata" >> $csvfile
  57         done
  58 done
  59
  60 printf %100s |tr " " "-" >> $logfile
  61 echo "### Running read write tests" >> $logfile
  62 for i in $(eval echo {8..$max_clients..$clients_inc}); do
  63         echo "##Running test with $i clients" >> $logfile
  64         for j in $(eval echo {1..$repeat}); do
  65                 result=`pgbench -t $runs -c $i pgbench -U $pguser 2>&1`
  66                 echo "#Run $j: $result" >> $logfile
  67                 csvdata=$(echo "$result" | grep tps | cut -d "=" -f2 | cut -d "(" -f1 | sed -e 's/^[ \t]*//' | tr '\n' ',')
  68                 echo "$host,Read Write,$scale,$runs,$i,$j,$csvdata" >> $csvfile
  69         done
  70 done
  71
  72 printf %100s |tr " " "-" >> $logfile
  73 echo "$customsql" > $customfile
  74 echo "### Running custom tests" >> $logfile
  75 for i in $(eval echo {8..$max_clients..$clients_inc}); do
  76         echo "##Running test with $i clients" >> $logfile
  77         for j in $(eval echo {1..$repeat}); do
  78                 result=`pgbench -n -f $customfile -t $runs -c $i $customdb -U $pguser 2>&1`
  79                 echo "#Run $j: $result" >> $logfile
  80                 csvdata=$(echo "$result" | grep tps | cut -d "=" -f2 | cut -d "(" -f1 | sed -e 's/^[ \t]*//' | tr '\n' ',')
  81                 echo "$host,Custom,$scale,$runs,$i,$j,$csvdata" >> $csvfile
  82         done
  83 done
  84
  85 rm $customfile
  86 echo "### Done" >> $logfile
  87 printf %100s |tr " " "=" >> $logfile