Re: question about memory usage

On 23 Červenec 2014, 15:56, klo uo wrote:
> Bill, thanks for your reply.
>
> "shared_buffers" is set to "128MB".
>
> Now that you mention config file, the only thing I did change there, and
> was suggested to me while I made some on my databases was
> "max_locks_per_transaction = 50000" (which has default value 10000).
>
> After resetting "max_locks_per_transaction" to default value and
> restarting
> the server, memory occupied in working set reduced linearly to around 200
> MB.
>
> I guess this is it.

The default value for max_locks_per_transaction is 64, not 10000. Values
this high are quite insane, and suggest that either you don't know what
the value means (and increased it just in case, because "more is always
better") or that the application does something wrong (eventually
requiring so many locks).

You really need to check this (notice how the amount of shared memory
depends on max_locks_per_transaction):

http://www.postgresql.org/docs/9.0/static/kernel-resources.html#SHARED-MEMORY-PARAMETERS

and this (which explains what max_locks_per_transaction does):

http://www.databasesoup.com/2012/06/postgresqlconf-maxlockspertransaction.html

regards
Tomas