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
