pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedproblems
2+ messages / 2 participants
[nested] [flat]
* problems
@ 2003-04-20 18:09 Ian Knopke <knopke@music.mcgill.ca>
0 siblings, 1 reply; 2+ messages in thread
From: Ian Knopke @ 2003-04-20 18:09 UTC (permalink / raw)
To: 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;
}
--
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: problems
@ 2003-04-20 18:36 Tom Lane <tgl@sss.pgh.pa.us>
parent: Ian Knopke <knopke@music.mcgill.ca>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2003-04-20 18:36 UTC (permalink / raw)
To: Ian Knopke <knopke@music.mcgill.ca>; +Cc: pgsql-performance
Ian Knopke <knopke@music.mcgill.ca> writes:
> 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.
AFAICT you are just showing us kernel disk cache expanding to fill
unused memory. This is normal and not a cause for alarm.
> Eventually the system moves to using swap and things really slow down.
Disk cache can't cause swapping --- the kernel will just throw away
cached pages when the memory is needed for something else. It could
be that you have growth in the number of Postgres processes, or the
sizes of individual processes, but vmstat isn't very helpful for
determining that (good ol' top would be more useful). In any case
you haven't actually shown us any data from the state where the system
is slow, so it's a bit hard to conjecture about what's going on.
Some other important information that you haven't let us in on is the
platform and the Postgres version you're using.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-04-20 18:36 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-04-20 18:09 problems Ian Knopke <knopke@music.mcgill.ca>
2003-04-20 18:36 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox