From: Tom Lane <tgl@sss.pgh.pa.us>
To: Joel Benelli <joel.benelli@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: effective_cache_size
Date: Mon, 08 Oct 2018 10:49:49 -0400
Message-ID: <27080.1539010189@sss.pgh.pa.us> (raw)
In-Reply-To: <CAAX2mCERNLqmWWLPYBs7YZGky0Sf925LJb9CB2U9cfOTAuEqLQ@mail.gmail.com>
References: <CAAX2mCERNLqmWWLPYBs7YZGky0Sf925LJb9CB2U9cfOTAuEqLQ@mail.gmail.com>
Joel Benelli <joel.benelli@gmail.com> writes:
> We have a Linux server 16GB RAM and HD SSD with Postgres 9.5 and
> configuration:
> ...
> effective_cache_size = 12GB
> With these settings the memory usage is between 10 ~ 13GB and when running
> a dump occur errors of "out of memory" preventing new requests. By changing
> the effective_cache_size setting to 6GB, the memory usage is between 8 ~
> 12GB, without errors during the dump.
That seems *very* odd. The only thing effective_cache_size actually
affects is the planner's opinion of the cost of indexscans [1],
so that it might choose different query plans after you change the setting.
But there's no obvious reason why that would lead to memory problems
in one case and not the other.
I'd suggest looking in the postmaster log to discover exactly which query
is failing, and then manually EXPLAIN'ing that query with both settings
to see what plans we are talking about. That might yield some more
insight.
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, joel.benelli@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: effective_cache_size
In-Reply-To: <27080.1539010189@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox