Обсуждение: Dramatic change in memory usage with version 9.1

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

Wow, upgrading 3 major releases at a go. :) It would probably be
useful to use the helpful:

http://wiki.postgresql.org/wiki/Guide_to_reporting_problems

to get the information that is needed to the right people.

Regards,
Ken

On Mon, Dec 19, 2011 at 04:04:54PM +0100, Rafael Martinez wrote:
> -----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.

On Mon, Dec 19, 2011 at 17:04, Rafael Martinez <r.m.guerrero@usit.uio.no> wrote:
> * Sudden decrease of swap when running backup/vacuum+analyze jobs

Do you know for certain that this memory use is attributed to
vacuum/analyze/backup, or are you just guessing? You should isolate
whether it's the vacuum or a backup process/backend that takes this
memory.

Do you launch vacuum/analyze manually or are you just relying on autovacuum?
How many parallel vacuum jobs are there?
What's your autovacuum_max_workers set to?
How large is your database?
How did you perform the upgrade -- via pg_upgrade or pg_dump?

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

Well, for one, there have been many planner changes that make it use
memory more aggressively, these probably being the most significant:
* Materialize for nested loop queries in 9.0:
http://rhaas.blogspot.com/2010/04/materialization-in-postgresql-90.html
* Hash join usage for RIGHT and FULL OUTER JOINs in 9.0

However, none of these would apply to vacuum, analyze or backups.

Regards,
Marti

Le 19 décembre 2011 16:04, Rafael Martinez <r.m.guerrero@usit.uio.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

On 19/12/2011 11:04 PM, Rafael Martinez wrote:
> Any ideas about why this dramatic change in memory usage when the only
> thing apparently changed from our side is the postgres version?
>
It'd be interesting to know how much of your workload operates with
SERIALIZABLE transactions, as the behavior of those has changed
significantly in 9.1  and they _are_ more expensive in RAM terms now.

--
Craig Ringer

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

On 12/20/2011 12:15 PM, Cédric Villemain wrote:
> Le 19 décembre 2011 16:04, Rafael Martinez <r.m.guerrero@usit.uio.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.
>>
[.......]
>
> Can you report what is filling the cache and the swap ?
>

Hello

We are running RHEL4 with a 2.6.9 kernels and we do not know how to
check how much swap a particular process is using. It looks like with
kernels > 2.6.16 you can get this informaton via /proc/PID/smaps.

We have been able to run some tests and we think we have found a reason
for the change in memory usage with version 9.1

It looks like it is a combination of how pg_dump works now and how the
operative system manages memory.

What we have found out is that the server process attending to pg_dump
uses much more memory with 9.1 than with 8.3 dumping the same database.

This is the test we have done with 8.3 and 9.1:

* Clean reboot of the server.
* Clean start of postgres server
* One unique process running against postgres:
pgdump -c --verbose <dbname> | gzip > dump_file.dump.gz

* DBsize = 51GB+
* shared_buffers = 2GB
* work_mem = 16MB
* maintenance_work_mem = 256MB
* Total server memory = 8GB

* We have collected data via /proc of how the system has been using
memory and VSIZE, RSS and SHARE memory values for all postgres processes.

Some graphs showing what happens during the dump of the database with
9.1 and 8.3 can be consulted here:

http://folk.uio.no/rafael/upgrade_to_9.1/test/

As you can see, the server process with 9.1 memory usage grows more than
the dobbel of the value defined with shared_buffers. With 8.3 is half of
this.

What we have seen in these tests corresponds with what we have seen in
production Ref:[1]. The 'cached' memory follows the 'inactive' memory
when this one gets over a certain limit. And 'active' and 'inactive'
memory cross their paths and exchange roles.

We have not experienced the use of swap under these tests as we do in
production probably because we are not running several jobs in parallel.

So the drop in 'cached' memory we see in production is not related to
the termination of a backup or maintenance job, it is related to how
much 'inactive' memory the system has. It looks like some kernel limit
is reached and the kernel starts to reallocate how the memory is used.

What it's clear is that:

* Running pg_dump needs/uses much more memory with 9.1 than with 8.3
(33% more). The same job takes 15min.(18%) more with 9.1 than 8.3

* With 9.1 the assignation the system does of wich memory is 'active'
and wich one is 'inactive' has changed Ref:[2].

We still has some things to find out:

* We are not sure why swap usage has increased dramatically. We have in
theory a lot of memory 'cached' that could be used instead of swap.

* We still do not understand why the assignation of which memory is
'active' and which one is 'inactive' has such an impact in how memory is
managed.

* We are trying to find out if the kernel has some memory parameters
that can be tunned to change the behavior we are seeing.

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

Thanks in advance to anyone trying to find an explanation.

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 v2.0.14 (GNU/Linux)

iEYEARECAAYFAk7yGjYACgkQBhuKQurGihTeHwCggv0yjskln8OkW2g5Kj6T4YGR
jekAn3FhUbCUR0RjXS+LLJpyzAGNQjys
=lBqa
-----END PGP SIGNATURE-----

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

On 12/21/2011 12:48 AM, Craig Ringer wrote:
> On 19/12/2011 11:04 PM, Rafael Martinez wrote:
>> Any ideas about why this dramatic change in memory usage when the only
>> thing apparently changed from our side is the postgres version?
>>
> It'd be interesting to know how much of your workload operates with
> SERIALIZABLE transactions, as the behavior of those has changed
> significantly in 9.1  and they _are_ more expensive in RAM terms now.
>

Hello

As long as I know, all the databases are using the default, "read
committed".

We have almost 500 databases across all our servers, but we are only
dbas. We provide the infrastructure necessary to run this and help users
when they need it but we have not 100% control over how they are using
the databases ;-)

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 v2.0.14 (GNU/Linux)

iEYEARECAAYFAk7yHHAACgkQBhuKQurGihQz1gCdGJY6vk89lHKMldkYlkxOeJYJ
GSMAoKDRCRo1UpqlUgItzCm/XV9aCbb8
=7f6R
-----END PGP SIGNATURE-----

On Wed, Dec 21, 2011 at 10:50 AM, Rafael Martinez
<r.m.guerrero@usit.uio.no> wrote:
> As long as I know, all the databases are using the default, "read
> committed".

Note that backups run in serializable mode.

Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Wed, Dec 21, 2011 at 10:50 AM, Rafael Martinez
> <r.m.guerrero@usit.uio.no> wrote:
>> As long as I know, all the databases are using the default, "read
>> committed".
>
> Note that backups run in serializable mode.

In 9.1 they default to running in "repeatable read".  You can choose
the --serializable-deferrable option, which runs at the serializable
transaction isolation level, sort of.  It does that by waiting for a
"safe" snapshot and then running the same as a repeatable read
transaction -- so either way you have none of the overhead of the
new serializable transactions.

Besides that, almost all of the additional RAM usage for the new
serializable implementation is in shared memory.  As you can see in
the graphs from Rafael, the difference isn't very dramatic as a
percentage of a typical production configuration.

-Kevin

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

On 12/22/2011 12:29 AM, Havasvölgyi Ottó wrote:
> Hello,
>
> Can you find some relation between the memory usage and insert
> statements? 9.1.2 has memory problems with inserts (even the simplest
> ones) on Linux and Windows too, I could produce it. Using pgbench also
> shows it. Some memory is not reclaimed.
> I could produce it also with 8.4.9 on Linux, I haven't tried 8.4.10 yet.
>
[...]

Hello

Are you thinking about this bug?:
http://archives.postgresql.org/pgsql-bugs/2011-12/msg00068.php

Our problem should not have anything to do with this bug (it was
introduced in 9.1.2)

We could not finish a full import of some of our databases with 9.1.2
because all ram+swap was used in a matter of minuttes. We are using
9.1.1 and we haven't seen the 9.1.2 behavior.

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 v2.0.14 (GNU/Linux)

iEYEARECAAYFAk7y8aUACgkQBhuKQurGihTD8gCgk0Frrd/mEjQrIgG9K0dzhNxN
HzcAnRiQKWBgwZaNSmY+zrGjYSJFva9o
=zcv3
-----END PGP SIGNATURE-----