Dramatic change in memory usage with version 9.1 - Mailing list pgsql-performance

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hello

I am sending this email to ask if anyone has noticed a change in how
a server running postgreSQL 9.1 uses and allocates memory compared to
older versions.

We upgraded all our systems from 8.3 to 9.1 a couple of weeks ago, and
we have experienced a radical change in how our servers make use of
memory. How memory is allocated has become more unstable and the swap
usage has increased dramatically.

The pattern that we have started seeing is:

* Sudden decrease of swap when running backup/vacuum+analyze jobs
* Full use of cached memory when running backup/vacuum+analyze jobs
* Sudden increase of swap and unused memory when backup/vacuum+analyze
jobs are finnished.
* Progressive decrease of swap during the day.


Here is a list of things about this upgrade to version 9.1 that can be
interesting when analyzing this change of behavior:

* The servers are running the samme OS version and linux kernel as
with 8.3.

* We are running the same values for parameters related to memory
allocation as we used in 8.3.

* We are running the same backups and maintenance jobs as with version
8.3. These jobs are running at the exactly same time as with 8.3.

* Backups (PITR, pg_dumps) and maintenances (vacuum, analyze) jobs are
executed between midnight and early morning.

* We run several postgreSQL clusters per server, running in different
IPs and disks.

* We have not seen any significant change in how databases are
used/accessed after the upgrade to 9.1.

* We upgraded in the first time from 8.3.12 to 9.1.2, but because this
bug: http://archives.postgresql.org/pgsql-bugs/2011-12/msg00068.php
we had to downgrade to 9.1.1. We thought in the begynning that our
memory problems were related to this bug, but everything is the same
with 9.1.1.

* A couple of days ago we decreased the values of maintenance_work_mem
and work_mem over a 50% in relation to values used with 8.3. The only
change we have seen is even more unused memory after backup/vacuum
+analyze jobs are finnished.

Here you have some graphs that can help to get a picture about what we
are talking about:

* Overview of how memory use changed in one of our servers after the
upgrade in the begynning og week 49:
http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-month.png
http://folk.uio.no/rafael/upgrade_to_9.1/server-1/memory-year.png

* We could think that all this happens because we are running to much
in one server. Here are some graphs from a server with 30GB+ running
only one postgres cluster (shared_memory = 6GB,
maintenance_work_memory = 512MB, work_mem = 32MB) for a couple of days:

http://folk.uio.no/rafael/upgrade_to_9.1/server-2/memory-week.png

The memory pattern is the same even when running only one postgres
cluster in a server with enough memory.

Any ideas about why this dramatic change in memory usage when the only
thing apparently changed from our side is the postgres version?

Thanks in advance for any help.

regards,
- --
Rafael Martinez Guerrero
Center for Information Technology
University of Oslo, Norway

PGP Public Key: http://folk.uio.no/rafael/
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAk7vUpYACgkQBhuKQurGihTvjACff5J08pNJuRDgkegYdtQ5zp52
GeoAnRaaU+F/C/udQ7lMl/TkvRKX2WnP
=VcDk
-----END PGP SIGNATURE-----