Re: Dramatic change in memory usage with version 9.1

-----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(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.
>>
[.......]
>
> 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-----