(unnamed)

text/plain

Download

#! /usr/bin/perl

use strict;
use IPC::Open2;
use Time::HiRes  qw( usleep ualarm gettimeofday tv_interval );
use Errno qw/ECHILD/;

my $ndbs = 50;
my $ntbls = 100;

my $loops = 6;
my $chiter = 2000;
my $chtrlen = 10;
my $nprocs = 90;

my $trg_file = '/tmp/dbrun_pl.trg';
my $refresult_file = '/tmp/refresult.txt';

#       title  loops,ndbs,clnts,ntbls,childiter   ,  xactlen,refprocs
testrun("A1", $loops,   1,    1,    1,  $chiter*10, $chtrlen,     0);
testrun("A2", $loops,   1,    1,    1,  $chiter*10, $chtrlen,     1);
testrun("B1", $loops,   1,   90,    1,  $chiter   , $chtrlen,     0);
testrun("B2", $loops,   1,   90,    1,  $chiter   , $chtrlen,     1);
testrun("C1", $loops,   1,   90,   50,  $chiter   , $chtrlen,     0);
testrun("C2", $loops,   1,   90,   50,  $chiter   , $chtrlen,    10);
testrun("D1", $loops,  50,   90,    1,  $chiter   , $chtrlen,     0);
testrun("D2", $loops,  50,   90,    1,  $chiter   , $chtrlen,     1);
testrun("E1", $loops,  50,   90,    1,  $chiter   , $chtrlen,    10);
testrun("F1", $loops ,  1,   10,    1,  $chiter   , $chtrlen,    90);
exit;

sub testrun
{
	my ($test_name, $loops, $ndbs, $nprocs, $ntbls,
		$childiter, $childtrlen, $nreferers)
		= @_;

	my @results = ();
	my @refresults = ();

	# run each iteration
	for (my $l = 0 ; $l < $loops ; $l++)
	{
		my %starttime = ();
		my %endtime = ();

		# This file is used for stopping free-running processes.
		open(OUT, '>', $trg_file) || die "failed to open file:$!\n";
		print OUT "$$\n";
		close(OUT);

		# It's very mysterious that one dummy subprocess makes things
		# stable. Perl runs slowly if there's only one psql process
		# without this...
		{
			my $pid = fork;
			if ($pid == 0)
			{
				while (-f $trg_file)
				{
					usleep (500000);
				}
				exit;
			}
		}

		# start referer processes, collecting how many times the query ran.
		pipe(my $refresrd, my $refreswr);
		for(my $i = 0 ; $i < $nreferers ; $i++)
		{
			my $pid = fork;

			if ($pid < 0) { die "fork referer process failed : $!\n"; }
			
			if ($pid == 0)
			{
				my $pid = open2(my $psqlrd, my $psqlwr, "psql postgres");
				my $count = 0;

				close($refresrd);

				if ($pid < 0) { die "fork psql failed : $!\n"; }

				while (-f $trg_file)
				{
					print $psqlwr "select * from pg_stat_user_tables;\n";
					while (<$psqlrd>)
					{
						last if ($_ =~ /rows\)/);
					}
					$count++;
				}
				print $refreswr "$count\n";
				exit;
			}
		}
		close($refreswr);

		# launch updator processes
		for (my $i = 0 ; $i < $nprocs ; $i++)
		{
			my $dbn = rand($ndbs);  # equal dist.
			my $dbname = sprintf("db%03d", $dbn);

			my $pid = fork;

			if ($pid < 0)
			{
				die "fork failed: $!\n";
			}
			elsif ($pid == 0)
			{
				my $pid = open2(my $rd, my $wr, "psql $dbname > /dev/null");
				if ($pid < 0) { die "sub fork failed: $!\n"; }

				my $ncmd = $childtrlen;
				#print $wr "set log_min_duration_statement to 0;\n";
				print $wr "begin;\n";
				for (my $i = 0 ; $i < $childiter ; $i++)
				{
					my $tbn = rand($ntbls);  # equal dist.

					printf $wr "select /* $dbname\[$i\]*/ count(*) from t%03d;\n", $tbn;
					if (--$ncmd == 0)
					{
						print $wr "commit;begin;\n";
						$ncmd = $childtrlen;
					}
				}

				print $wr "commit;\n";
				print $wr "\\q\n";
				my $res = <$rd>;
				exit;
			}

			(my $sec, my $usec) = &gettimeofday();
			$starttime{$pid} = $sec * 1000 + $usec / 1000;
			# print "start\[$pid\] = $starttime{$pid}\n";
		}

		# wait for updateors to finish
		for (my $i = 0 ; $i < $nprocs ; $i++)
		{
			my $pid = wait();

			if ($pid < 0) { if ($! != ECHILD) {die "???: $!\n"; }}
			redo if (!defined $starttime{$pid});

			(my $sec, my $usec) = &gettimeofday();
			$endtime{$pid} = $sec * 1000 + $usec / 1000;
			# printf "%d[%d]: %d - %d  = %d ms\n", $i, $pid, $endtime{$pid}, $starttime{$pid}, $endtime{$pid} - $starttime{$pid};
		}

		my $sum = 0;
		foreach my $pid (keys %starttime)
		{
			$sum += $endtime{$pid} - $starttime{$pid};
		    # printf "[%d]: %d ms (%d, %d)\n", $pid, $endtime{$pid} - $starttime{$pid}, $starttime{$pid}, $endtime{$pid};
		}

		push(@results, $sum / $nprocs);

		# kill referers if any (and dummy process)
		unlink($trg_file);
		while (wait() == 0) {}
		my $nrefs = 0;
		my $refcount = 0;
		while(<$refresrd>)
		{
			chomp;
			$refcount += $_;
			$nrefs++;
		}
		close($refresrd);
		
		push (@refresults, $refcount / $nrefs) if ($nrefs > 0);
	}

	# calculate stdev
	(my $updmean, my $updstdev) = stdev(@results);
	(my $refmean, my $refstdev) = stdev(@refresults);

	printf "$test_name (l:%d, d:%d, t:%d, i:%d, tr:%d): %.2f ms (stdev %.2f) / %d updprocs, %.2f refs (stdev %.2f) / %d refprocs\n", $loops, $ndbs, $ntbls, $childiter, $childtrlen, $updmean, $updstdev, $nprocs, $refmean, $refstdev, $nreferers;
}

sub stdev
{
	my $sum = 0;
	my $sqsum = 0;
	my $count = $#_ + 1;

	return (0, 0) if $count == 0;

	foreach my $el (@_)
	{
		$sum += 1.0 * $el;
		$sqsum += 1.0 * $el * $el;
	}
	my $mean = $sum / $count;
	my $stdev = sqrt($sqsum / $count - $mean * $mean);

	return ($mean, $stdev);
}