Subject: memory and performance under 4.0.1

From: David Feldcamp <[email-masked]>
Date: Wed, 2 Sep 92 12:50:39 -0700

I have once again tried to shift my application to the new version of Postgres
(this time 4.0.1) from V3.1.  It seems to run correctly, but the performance is two to three
times slower (subjective) and even worse the backend is consuming gigantic amounts of memory
(e.g., after my set of queries to setup my front end on start up, ps reports that just
sitting idle the backend is holding > 13MB of virtual memory).

Can anyone explain this behavior and/or suggest ways of preventing it?

Thx.

---

From: <[email-masked]>
Date: Wed, 02 Sep 92 12:59:27 PDT

> Can anyone explain this behavior and/or suggest ways of preventing it?

what is the query mix you're running to set up the database?  if you're
copying in a very large number of tuples, postgres will allocate and
hold memory for all of them until the end of transaction.  unless your
system supports negative sbrk (i know of none that do), this memory will
remain allocated to the backend after the transaction completes, even
though postgres calls free() on it.  however, it shouldn't be resident;
that is, your RSS should be reasonable, even if the total amount of
virtual memory the os is reserving for you is large.

we're aware of modest slowdowns in 4.0.1 over 3.1, but nothing approaching
a factor of two.  can you quantify this more precisely?

one way to speed up execution after loading all your data is to vacuum
the database.  earlier versions of postgres failed to take transaction
commit time into account when computing the visibility of tuples in
concurrent backends.  this has been fixed in 4.0.1.  if you don't vacuum
your database regularly, this will lead to an extra log lookup per tuple
examined.  this may be contributing to the slowdown you're seeing.

					mike olson
					project sequoia 2000
					uc berkeley
					[email-masked]