pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedMemory size
8+ messages / 3 participants
[nested] [flat]
* Memory size
@ 2018-03-11 12:48 dangal <danielito.gallo@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: dangal @ 2018-03-11 12:48 UTC (permalink / raw)
To: pgsql-performance
Dear some consultation, I have a base of about 750 GB in size and we are
having problem of slowness in certain views of the application, so I have
been seeing it is apparently a memory problem because if I run again the
view runs fast, the base is in a virtual server with 24 GB of RAM and 8 GB
of shared buffer, with this information how much would you recommend to put
a memory in the server
thank you very much
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-11 16:59 Jeff Janes <jeff.janes@gmail.com>
parent: dangal <danielito.gallo@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2018-03-11 16:59 UTC (permalink / raw)
To: dangal <danielito.gallo@gmail.com>; +Cc: pgsql-performance
On Sun, Mar 11, 2018 at 5:48 AM, dangal <danielito.gallo@gmail.com> wrote:
>
> Dear some consultation, I have a base of about 750 GB in size and we are
> having problem of slowness in certain views of the application, so I have
> been seeing it is apparently a memory problem because if I run again the
> view runs fast, the base is in a virtual server with 24 GB of RAM and 8 GB
> of shared buffer, with this information how much would you recommend to put
> a memory in the server
>
There is no way to answer that with the information you provide.
Are the "certain views" run with different supplied parameters on different
executions, or are they run with no parameters or unchanging ones?
How long can you wait between the first run and the second run before the
second run is no longer fast?
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-11 17:33 dangal <danielito.gallo@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: dangal @ 2018-03-11 17:33 UTC (permalink / raw)
To: pgsql-performance
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
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-11 18:12 Tomas Vondra <tomas.vondra@2ndquadrant.com>
parent: dangal <danielito.gallo@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Tomas Vondra @ 2018-03-11 18:12 UTC (permalink / raw)
To: dangal <danielito.gallo@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-11 19:11 dangal <danielito.gallo@gmail.com>
parent: Tomas Vondra <tomas.vondra@2ndquadrant.com>
0 siblings, 0 replies; 8+ messages in thread
From: dangal @ 2018-03-11 19:11 UTC (permalink / raw)
To: pgsql-performance
thank you very much Tomas, tomorrow at work I will see to capture plans of
ejcucion to see if you can give me a hand, I am really helping me a lot with
their advice
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-11 23:16 Jeff Janes <jeff.janes@gmail.com>
parent: dangal <danielito.gallo@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Jeff Janes @ 2018-03-11 23:16 UTC (permalink / raw)
To: dangal <danielito.gallo@gmail.com>; +Cc: pgsql-performance
On Sun, Mar 11, 2018 at 10:33 AM, dangal <danielito.gallo@gmail.com> 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
>
If that is the only query that you have trouble with, it might be easiest
just to set up a cron job to run it periodically just to keep that data set
in cache. Not very elegant, but it can be effective.
Cheers,
Jeff
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-12 01:43 dangal <danielito.gallo@gmail.com>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: dangal @ 2018-03-12 01:43 UTC (permalink / raw)
To: pgsql-performance
With several views, Jeff is following us
Tomorrow I will see if I can provide more data to see if you can guide me a
bit
Thank you so much everyone
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Memory size
@ 2018-03-12 15:08 dangal <danielito.gallo@gmail.com>
parent: dangal <danielito.gallo@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: dangal @ 2018-03-12 15:08 UTC (permalink / raw)
To: pgsql-performance
I was seeing thanks to your recommendations and I found the following, to
see what you think
cache hit rate 0.99637443599712620769
We have the default values 5 minutes
total checkpoint minutes beetween checkpoint
26927 0.358545045634493
temp_files temp_size (in 10 days)
16870 171 GB
believe that the problem may come here?
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2018-03-12 15:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2018-03-11 12:48 Memory size dangal <danielito.gallo@gmail.com>
2018-03-11 16:59 ` Jeff Janes <jeff.janes@gmail.com>
2018-03-11 17:33 ` dangal <danielito.gallo@gmail.com>
2018-03-11 18:12 ` Tomas Vondra <tomas.vondra@2ndquadrant.com>
2018-03-11 19:11 ` dangal <danielito.gallo@gmail.com>
2018-03-11 23:16 ` Jeff Janes <jeff.janes@gmail.com>
2018-03-12 01:43 ` dangal <danielito.gallo@gmail.com>
2018-03-12 15:08 ` dangal <danielito.gallo@gmail.com>
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