Memory size

2018-03-11 Thread dangal 

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

Sv: Memory size

2018-03-11 Thread Andreas Joseph Krogh 
På søndag 11. mars 2018 kl. 13:48:41, skrev dangal mailto:[email protected]>>:

 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
 
What is effective_cache_size ?
 

https://www.postgresql.org/docs/10/static/runtime-config-query.html#GUC-EFFECTIVE-CACHE-SIZE
 
-- Andreas Joseph Krogh
 

Re: Sv: Memory size

2018-03-11 Thread dangal 
The rest of the memory Andreas, 16 gb



--
Sent from: 
http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

Re: Memory size

2018-03-11 Thread Jeff Janes 
On Sun, Mar 11, 2018 at 5:48 AM, dangal  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

Re: Memory size

2018-03-11 Thread dangal 
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

Re: Memory size

2018-03-11 Thread Tomas Vondra 
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

Re: Memory size

2018-03-11 Thread dangal 
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

Sv: Re: Sv: Memory size

2018-03-11 Thread Andreas Joseph Krogh 
På søndag 11. mars 2018 kl. 14:57:52, skrev dangal mailto:[email protected]>>:
The rest of the memory Andreas, 16 gb
 
Then I'd blame it on the virtual environment. It's common at least in the 
VMWare-world to have a 8GB disk-cache and reads going beond that are slow. 
You've not told us anything about table/index-size but I believe reading those 
from disk is the culprit here.
 
-- Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
[email protected] 
www.visena.com 
 


 

Re: Memory size

2018-03-11 Thread Jeff Janes 
On Sun, Mar 11, 2018 at 10:33 AM, 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
>

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

Re: Memory size

2018-03-11 Thread dangal 
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