problems

Hello,

I'm not sure if this is really a problem. I'm working on a distributed web
crawling system which uses several clients on different machines. Results
are logged to a central Postgres system. When I start it, it works fine,
but seems to slow down drastically after several hours/days. To test the
database, I wrote a short Perl script which makes up random strings and
inserts them, and writes the benchmark times to a logfile.

Comparing the beginning and end times from the log, it seems to take the
same amount of time to insert at the beginning of the process as after
about twenty minutes. However, I also logged the input from vmstat, which
shows the amount of memory available shrinking rapidly.

Before running test program:
total used free shared buffers cached
Mem: 516136 120364 395772 0 4776 75884
-/+ buffers/cache: 39704 476432
Swap: 248996 0 248996
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 0 395768 4776 75888 0 0 124 27 124 236 13 2 86 0

The first 20 lines of vmstat output (3 seconds apart each, 512M total):
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 0 0 267676 5116 194136 0 0 74 143 123 825 32 4 64 0
3 0 0 266244 5116 195532 0 0 0 0 105 2343 85 15 0 0
1 0 0 264816 5120 196932 0 0 0 0 104 2182 89 11 0 0
2 0 0 263324 5120 198308 0 0 0 299 126 2299 90 10 0 0
1 0 0 261856 5120 199744 0 0 0 0 101 2482 92 8 0 0
1 0 0 260376 5124 201188 0 0 1 683 114 2484 93 7 0 0
2 0 0 259152 5124 202392 0 0 0 640 119 2336 91 9 0 0
3 0 0 257880 5128 203628 0 0 0 0 102 2414 86 14 0 0
2 0 0 256772 5128 204712 0 0 0 640 116 2378 92 8 0 0

Eventually the system moves to using swap and things really slow down.
Interestingly, when I stop the program and shut down postgres, only some
of the memory comes back. Here is the current state of my system with
postgres shut down, after running the test: procs
-----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 0 252 63396 5468 373792 0 0 29 318 133 1131 37 5 58 0

I seem to be missing some memory. However, I might not understand the
results from vmstat properly. Does anyone know what is going on or how I
can solve this?

Ian Knopke

Test program:
#############################################################
#!/usr/bin/perl -w
##test.pl - Program to test postgres performance

use DBI;
use Time::HiRes qw(gettimeofday);
use IO::Socket;
use IO::File;
use Number::Format;

my $str='abcdefghijklmnopqrstuvwxyz';
my @str=split('',$str);

$SIG{INT}=\&int_stoproutine;
open (LOGFILE,">testlog.txt") or die "Can't open logfile\n";

my $fmt=Number::Format->new(DECIMAL_DIGITS => 0);
my $dbh=DBI->connect("DBI:Pg:dbname=inserttests",,) or die "Can't connect: $DBI::errstr\n";

my $counter=0;
while(1) {

my $starttime=gettimeofday();
print LOGFILE "COUNTER: $counter ";
print LOGFILE "START: $starttime ";
my $str=&genmystr();
print LOGFILE "STR: $str ";
my $donetime=gettimeofday();
print LOGFILE "STRTIME: $donetime ";

my $query_string="insert into tablea (tablea_term) values(\'$str\')";
$sth=$dbh->prepare($query_string);
my $error_code=$sth->execute();

my $endtime=gettimeofday();
print LOGFILE "END: $endtime ";
my $difftime=$endtime-$starttime;
print LOGFILE "DIFF: $difftime\n";
$counter++;

}

close(LOGFILE);

sub int_stoproutine {
exit;
}

sub genmystr{
my $str='';
foreach(1 .. 8) {

my $a=rand(25);
my $b=$fmt->round($a);
$str=$str.$str[$b];
}
return $str;
}

--