From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: dangal <danielito.gallo@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Memory size
Date: Sun, 11 Mar 2018 19:12:36 +0100
Message-ID: <7a228fb5-cf57-851e-e528-2835d628ca8f@2ndquadrant.com> (raw)
In-Reply-To: <1520789622976-0.post@n3.nabble.com>
References: <1520772521740-0.post@n3.nabble.com>
<CAMkU=1xu3YhU=7Pit-2xM3EMP+n0A+o6A2s-QNRy0C4spgSbXA@mail.gmail.com>
<1520789622976-0.post@n3.nabble.com>
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
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-performance@postgresql.org
Cc: tomas.vondra@2ndquadrant.com, danielito.gallo@gmail.com
Subject: Re: Memory size
In-Reply-To: <7a228fb5-cf57-851e-e528-2835d628ca8f@2ndquadrant.com>
* 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