Re: question about memory usage

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(at)fuzzy(dot)cz> 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
>
>