Thread: Memory size
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
På søndag 11. mars 2018 kl. 13:48:41, skrev dangal <danielito.gallo@gmail.com>:
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
The rest of the memory Andreas, 16 gb -- Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html
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
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
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
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
På søndag 11. mars 2018 kl. 14:57:52, skrev dangal <danielito.gallo@gmail.com>:
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
Attachment
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
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
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