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
secs.
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";
$dbh->disconnect;
exit;
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;
$vartext++;
}
}
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) {
$dbh->commit;
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
