Re: Dramatic change in memory usage with version 9.1

Le 19 décembre 2011 16:04, Rafael Martinez <r(dot)m(dot)guerrero(at)usit(dot)uio(dot)no> a écrit :
> -----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.

Can you report what is filling the cache and the swap ?

--
Cédric Villemain +33 (0)6 20 30 22 52
http://2ndQuadrant.fr/
PostgreSQL: Support 24x7 - Développement, Expertise et Formation