Re: Re: Heaps of read() syscalls by the postmaster

I attac perl script which I run to benchmark postgres.
NOTICE: you should create 'pgtest' database.
It uses DBI, DBD::Pg and Benchmark modules.
There are obvious parameters you can play with.
Regards,
    Oleg

On Fri, 19 May 2000, Tom Lane wrote:

> Date: Fri, 19 May 2000 14:46:13 -0400
> From: Tom Lane <tgl@sss.pgh.pa.us>
> To: Matthias Urlichs <smurf@noris.net>
> Cc: pgsql-hackers@postgresql.org
> Subject: Re: [HACKERS] Re: Heaps of read() syscalls by the postmaster 
> 
> "Matthias Urlichs" <smurf@noris.net> writes:
> > NB: The same benchmark revealed that CREATE TABLE (or maybe it's CREATE
> > INDEX) leaks about 2k of memory.
> 
> Following up on this other point: this could simply be the new table's
> relcache entry (2K seems high though).  Currently the relcache doesn't
> have any procedure for discarding uninteresting entries, so once a
> table is referenced by a backend that relcache entry will be there until
> the backend quits or has some specific reason for flushing the entry.
> 
> I wouldn't expect a CREATE TABLE / DELETE TABLE cycle to show any memory
> leak, since the DELETE would flush the relcache entry.  But creating a
> few thousand tables in a row would start to eat up memory a little bit.
> What is the benchmark doing exactly?
> 
> We could add a mechanism for aging relcache entries out of the cache
> when they haven't been touched recently, but so far it hasn't seemed
> worth the trouble...
> 
>             regards, tom lane
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83
#!/usr/local/bin/perl

use strict;


use DBI;
use DBD::Pg;

use Benchmark;

# Change this 
my $DSN = 'DBI:Pg:dbname=pgtest';
my $TABLE = 'bench';

my($i);
sub TimeMe ($$$$) {   my($startMsg, $endMsg, $code, $count) = @_;   printf("\n%s\n", $startMsg);   my($t1) =
Benchmark->new();  $@ = '';   eval {for ($i = 0;  $i < $count;  $i++) {    &$code;}   };   if ($@) {print "Test failed,
message:$@\n";   } else {my($td) = Benchmark::timediff(Benchmark->new(), $t1);my($dur) = $td->cpu_a;printf($endMsg,
$count,$dur, $count / $dur);print "\n";   }
 
}


TimeMe("Testing empty loop speed ...",      "%d iterations in %.1f cpu+sys seconds (%d per sec)",      sub {      },
100000);


my($dbh);
TimeMe("Testing connect/disconnect speed ...",      "%d connections in %.1f cpu+sys seconds (%d per sec)",      sub {
$dbh= DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });   $dbh->disconnect();      },   2000);
 


$dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 0 });
$dbh->do("DROP TABLE $TABLE");
$dbh->disconnect();

$dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });
TimeMe("Testing CREATE/DROP TABLE speed ...",      "%d files in %.1f cpu+sys seconds (%d per sec)",      sub {
$dbh->do("CREATETABLE $TABLE (id INT4, name CHAR(40),"                   . " firstname CHAR(40), address CHAR(40),"
             . " zip CHAR(10), city CHAR(40), email CHAR(40))");          $dbh->do("DROP TABLE $TABLE");      },
1000);
$dbh->disconnect();

my $dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });
$dbh->do("CREATE TABLE $TABLE (id INT4, name CHAR(40),"   . " firstname CHAR(40), address CHAR(40),"   . " zip
CHAR(10),city CHAR(40), email CHAR(40))");
 
my(@vals) = (0 .. 499);
my($num);
TimeMe("Testing INSERT speed ...",      "%d rows in %.1f cpu+sys seconds (%d per sec)",      sub {          ($num) =
splice(@vals,int(rand(@vals)), 1);          $dbh->do("INSERT INTO $TABLE VALUES (?, 'Wiedmann', 'Jochen',"
    . " 'Am Eisteich 9', '72555', 'Metzingen',"                   . " 'joe\@ispsoft.de')", undef, $num);      },
500);

$dbh->disconnect();

my(@vals) = (0 .. 499);
$dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });
TimeMe("Testing UPDATE speed ...",      "%d rows in %.1f cpu+sys seconds (%d per sec)",      sub {          ($num) =
splice(@vals,int(rand(@vals)), 1);          $dbh->do("UPDATE $TABLE SET name='NnamdeiW' WHERE id=$num");      },
500);

$dbh->disconnect();

my $dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });
my($sth);
TimeMe("Testing SELECT speed ...",      "%d single rows in %.1f cpu+sys seconds (%.1f per sec)",      sub {
$num= int(rand(500));          $sth = $dbh->prepare("SELECT * FROM $TABLE WHERE id = $num");          $sth->execute();
       $sth->fetch() or die "Expected result for id = $num";      },   100);
 

$sth->finish;
$dbh->disconnect();

$dbh = DBI->connect($DSN, undef, undef,{ 'RaiseError' => 1 });

TimeMe("Testing SELECT speed (multiple rows) ...",      "%d times 100 rows in %.1f cpu+sys seconds (%.1f per sec)",
sub {          $num = int(rand(400));          $sth = $dbh->prepare("SELECT * FROM $TABLE WHERE id >= $num"
                 . " AND id < " . ($num+100));          $sth->execute();          ($sth->rows() == 100)              or
die"Expected 100 rows for id = $num, got " . $sth->rows();          while ($sth->fetch()) {          }      },   100);
 

$sth->finish;
$dbh->disconnect();


exit;