Thread: Are there tuning parameters that don't take effect immediately?
Last night I was doing some tuning on a database The longest query I was running was taking around 160 seconds. I didn't see much change in the running time for that query, even after restarting PG.
Today, with roughly the same system load (possibly even a bit heavier load), that query is running about 40 seconds.
Are there tuning parameters in postgresql.conf that don't take effect right away, even after a restart of PG? The only thing I can come up that's happened since last night was that we ran the nightly vacuum analyze on that database, but I did not change the statistics target. Today, with roughly the same system load (possibly even a bit heavier load), that query is running about 40 seconds.
The only thing I can come up that's happened since last night was that we ran the nightly vacuum analyze on that database, but I did not change the statistics target.
The answer to your question is no, parameters changes are worse would take effect after a reboot - though most are used on the very next query that runs.
The vacuum would indeed likely account for the gains - there being significantly fewer dead/invisible rows to have to scan over and discard while retrieving the live rows that fulfill your query.
David J.
On 06/12/2015 01:37 PM, Michael Nolan wrote: > Last night I was doing some tuning on a database The longest query I > was running was taking around 160 seconds. I didn't see much change in > the running time for that query, even after restarting PG. > > Today, with roughly the same system load (possibly even a bit heavier > load), that query is running about 40 seconds. Sounds like some of the relations are cached versus not. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing "I'm offended" is basically telling the world you can't control your own emotions, so everyone else should do it for you.
On Fri, Jun 12, 2015 at 4:52 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
The only thing I can come up that's happened since last night was that we ran the nightly vacuum analyze on that database, but I did not change the statistics target.The answer to your question is no, parameters changes are worse would take effect after a reboot - though most are used on the very next query that runs.The vacuum would indeed likely account for the gains - there being significantly fewer dead/invisible rows to have to scan over and discard while retrieving the live rows that fulfill your query.David J.
I wouldn't have said there was much activity in those tables since the previous day's vacuum, maybe a couple hundred rows changed or added in a table that has nearly 900,000 rows, and the other tables involved probably even less than that. There may be one table with more activity, perhaps 20,000 row updates and maybe a few dozen new rows in a table that has 400,000 rows. Maybe I need to manually analyze that table more often?
Vacuum analyze verbose generate way too much output, is there a way to get some more straight forward numbers from an analyze?
I'm definitely not complaining about the improvement, I'm just trying to get a handle on what really caused it and whether I can improve it even further.
--
Mike Nolan