On Tuesday 09 September 2003 14:33, Mindaugas Riauba wrote:
> > Well, try it without the trigger. If performance improves markedly, it
> might
> > be worth rewriting in C.
>   Nope. Execution time is practically the same without trigger.

OK - no point in rewriting it then.

> > If not, you're probably saturating the disk I/O - using iostat/vmstat
> > will
> let
> > you see what's happening. If it is your disks, you might see if moving
> > the WAL onto a separate drive would help, or check the archives for
> > plenty of discussion about raid setups.
>   Bottleneck in this case is CPU. postmaster process uses almost 100% of
> CPU.

>   Disk I/O should not be a problem in this case. vmstat shows ~300kb/s
> write activity.

Hmm - I must admit I wasn't expecting that. Closest I can get on my test 
machine here: AMD 400MHz / 256MB / IDE disk / other stuff running is about 20 

I've attached the perl script I used - what sort of timings does it give you?

  Richard Huxton
  Archonet Ltd
#!/usr/bin/perl -w
use DBI;
use Benchmark;

# How many clients, how many inserts each
my $num_upd = 10000;
my $num_rows = 1000;

my $dbname = "richardh";
my $dbh = DBI->connect("dbi:Pg:dbname=$dbname", "", "");

# Comment out create_table after the first run.
create_table($dbh, $num_rows);
my $t0 = new Benchmark;
run_updates($dbh, $num_rows, $num_upd);
my $t1 = new Benchmark;
$td = timediff($t1, $t0);
print "the code took:",timestr($td),"\n";


sub create_table {
	my $dbh = shift;
	my $num_rows = shift;

	my $sth = $dbh->prepare("CREATE TABLE foo (a int4, v varchar(100), t timestamp with time zone, PRIMARY KEY (a))");
	$sth->execute or die $dbh->errstr;

	$sth = $dbh->prepare("INSERT INTO foo (a,v,t) VALUES (?,?,now())");
	my $vartext = 'AAAAAAAA';
	for (my $i=0; $i<$num_rows; $i++) {
		$sth->execute($i,$vartext) or die $dbh->errstr;

sub run_updates {
	my $dbh = shift;
	my $num_rows = shift;
	my $num_upd  = shift;
	my $batchsize = 500;

	my $sth = $dbh->prepare("UPDATE foo SET t=now() WHERE a=?");
	for (my $i=0; $i<$num_upd; $i++) {
		if ($i % $batchsize==0) { $dbh->begin_work; }
		if ($i % $batchsize==499) {
			print "update $i\n";
			$dbh->do("VACUUM foo;");
		$sth->execute() or die $dbh->errstr;
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to