Re: question about memory usage

[klo uo <klonuo@xxxxxxxxx>]

Tomas, thanks for the heads up!

I certainly didn't know what this setting means, except the obvious name. Your links helped.

I just can't find where was this setting suggested, but IIRC it was in a guide for migrating OSM to PostGIS, as other tables were just created by GDAL OGR.

I had this line in my `postgresql.conf`:

max_locks_per_transaction = 50000        # 10000

that's why I thought that 10000 is the default, but it may be that commented value was entered by me, and not the real default value.

I've set it now to 64.

Thanks again

On Wed, Jul 23, 2014 at 4:10 PM, Tomas Vondra <tv@xxxxxxxx> wrote:

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