problems - Mailing list pgsql-performance

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;
}




--