While tuning PostgreSQL the benchmark exams can be brutal, they take a long time and are repetitive. The script here wraps pgbench to automate this testing. When using this tool run once with default PostgreSQL configuration, then tune up

The Script

Below is the script, you can copy and paste it into your favourite editor, and save it or down load from here. 

#!/usr/bin/php
<?php
// auth: Edoceo Inc.
// spec: pg_benchmark.php - automates the running of multiple benchmarking tests for PostgreSQL
// spec: will regenerate the pgbench database every run unless --noinit specified, must reinit after changing $clients
// note: Remember Heisenberg when running the test on the machine your testing

error_reporting(E_ALL);

$opt = isset($argv[1]) ? strval($argv[1]) : null;

$pgbench = '/usr/bin/pgbench';
$pgbench_db = 'pgbench';
$pgconf = '/var/lib/postgresql/data/postgresql.conf';

//putenv('PGHOST=localhost');
//putenv('PGPORT=5432');
putenv('PGUSER=postgres');

// Quick & Dirty Benchmarking
$clients = array(1,5);
$txtions = array(1,5);

// Not as quick, not as dirty
// $clients = array(5,10);
// $txtions = array(5,10,50);

// Very through but long benchmarking
#$clients = array(10,50,100);
#$txtions = array(10,50,100,1000,10000);

$scaling = max($clients); // Remember? at least max number of clients
$samples = 5; // "More testicals means more iron" - Lunch Lady Dorris

$c_c = count($clients);
$c_t = count($txtions);
$c_tests = 0;

$t0 = time();

// This displays some settings from the system
$buf = file_get_contents('/proc/sys/kernel/shmmax');
echo "Shared Memory Max is ".number_format($buf/1024/1024,2)."Mb\n";
$buf = `/usr/bin/ipcs -m`;
if (preg_match('/ postgres\s+600\s+(\d+)\s+(\d+) /',$buf,$m)) echo "PostgreSQL is using ".number_format($m[1]/1024/1024,2)."Mb\n";

if ($opt == '--noinit')
{
  echo "Initializing Benchmark Database ($pgbench_db) for scaling factor $scaling\n";
  unset($cmd,$out,$ret);
  $cmd = "$pgbench -i $pgbench_db -s $scaling 2>&1";
  //echo "exec: $cmd\n";
  exec($cmd,$out,$ret);
  if ($ret!=0) die($pgbench.': '.join("\n",$out));
}

// Spin Clients
for ($j=0;$j<$c_c;$j++)
{
  for ($k=0;$k<$c_t;$k++)
  {
    echo 'Testing: '.$clients[$j].' clients with '.$txtions[$k]." transactions ($samples samples)\n";
    $ex_tps=$in_tps=array();
    for ($l=0;$l<$samples;$l++)
    {
      $c_tests++;
      unset($cmd,$out,$ret);
      $cmd = $pgbench.' -c '.$clients[$j].' -t '.$txtions[$k].' '.$pgbench_db.' 2>&1';
      //echo "exec: $cmd\n";
      exec($cmd,$out,$ret);
      if ($ret!=0) die($pgbench.': '.join("\n",$out));
      $buf = join("\n",$out);
      if (preg_match('/tps = ([\d\.]+) \(including connections establishing\)/',$buf,$m))
      {
        $in_tps[] = $m[1];
        //echo "TPS: ".$m[1]."\n";
      }
      if (preg_match('/tps = ([\d\.]+) \(excluding connections establishing\)/',$buf,$m))
      {
        $ex_tps[] = $m[1];
        //echo "CTPS: ".$m[1]."\n";
      }
    }
    //print_r($in_tps);
    //print_r($ex_tps);
    echo "  TPS Min ".min($in_tps)."; Max: ".max($in_tps)."; Avg: ".avg($in_tps)."; Dev: ".dev($in_tps)."\n";
    echo "  CTPS Min: ".min($ex_tps)."; Max: ".max($ex_tps)."; Avg: ".avg($ex_tps)."; Dev: ".dev($ex_tps)."\n";
    /*
    $ex_avg = $ex_tps/$samples;
    $in_avg = $in_tps/$samples;
    echo "TPS: $in_tps/$samples\n";
    echo "CTPS: $ex_tps/$samples\n";
    */
  }
}
$t1 = time();


echo "pg_benchmark executed $c_tests tests in about ".($t1 - $t0)." seconds\n";

function avg($a)
{
  $sum = 0;
  foreach ($a as $x) $sum+=$x;
  return $sum/count($a);
}

// func: dev($a) - Standard Deviation
function dev($a)
{
  $sum = 0;
  $mean = avg($a);

  if (count($a)==0) return '0';
  foreach ($a as $x) $sum += pow(($x-$mean),2);
  $var = sqrt($sum/(count($a)-1));
  return $var;
}

Add Comment or Note