Subject: Re: Memory size
To: dangal <danielito.gallo@gmail.com>, pgsql-performance@postgresql.org
References: <1520772521740-0.post@n3.nabble.com>
 <CAMkU=1xu3YhU=7Pit-2xM3EMP+n0A+o6A2s-QNRy0C4spgSbXA@mail.gmail.com>
 <1520789622976-0.post@n3.nabble.com>
From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
Message-ID: <7a228fb5-cf57-851e-e528-2835d628ca8f@2ndquadrant.com>
Date: Sun, 11 Mar 2018 19:12:36 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.4.0
MIME-Version: 1.0
In-Reply-To: <1520789622976-0.post@n3.nabble.com>
Content-Type: text/plain; charset=utf-8
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

On 03/11/2018 06:33 PM, dangal wrote:
> jeff thank you very much for your time, I tell you, they are the same queries
> with the same parameters, I take 3 minutes for example, but I execute it and
> it takes me seconds, that's why I suspect it is the shared buffer
> The server had 16 GB and we increased it to 24, but I really do not know if
> it should continue to increase since they are not our own resources, we have
> to ask for them and justify them
> 

It's not very clear if your question is about shared_buffers or amount
of RAM in general. In any case, it looks like the performance difference
is due to having to do I/O on the first execution, while the second
execution gets served from RAM. If that's the case, increasing shared
buffers is not going to help, in fact it's going to make matters worse
(due to double buffering etc.).

You should be able to confirm this by analyzing system metrics,
particularly I/O and CPU time. There should be a lot of I/O during the
first execution, and almost nothing during the second one.

So it seems you need to add more RAM, but it's unclear how much because
we don't know what part of the data is regularly accessed (I really
doubt it's the whole 750GB). That is something you have to determine by
analyzing your workload. All we know is data needed by this query likely
fit into RAM, but then get pushed out by other queries after a while.

An alternative would be to use better storage system, although that will
not give you the same performance, of course.

FWIW it's also possible something is going wrong at the hypervisor level
(e.g. contention for storage cache used by multiple VMs). It's hard to
say, considering you haven't even shared an explain analyze of the
queries. Try EXPLAIN (ANALYZE, BUFFERS) both for the slow and fast
executions, and show us the results.

FWIW you might also read this first:
https://wiki.postgresql.org/wiki/Slow_Query_Questions

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services