pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAre there tuning parameters that don't take effect immediately?
4+ messages / 3 participants
[nested] [flat]
* Are there tuning parameters that don't take effect immediately?
@ 2015-06-12 20:37 Michael Nolan <htfoot@gmail.com>
0 siblings, 2 replies; 4+ messages in thread
From: Michael Nolan @ 2015-06-12 20:37 UTC (permalink / raw)
To: pgsql-performance
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.
The parameters I was working with were:
effective_cache_size
shared_buffers
temp_buffers
work_mem
maintenance_work_mem
Looking at the free command, I see a lot more memory being used for
buffer/cache today. (Centos 7.)
--
Mike Nolan
nolan@tssi.com
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Are there tuning parameters that don't take effect immediately?
@ 2015-06-12 20:52 David G. Johnston <david.g.johnston@gmail.com>
parent: Michael Nolan <htfoot@gmail.com>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2015-06-12 20:52 UTC (permalink / raw)
To: Michael Nolan <htfoot@gmail.com>; +Cc: pgsql-performance
On Fri, Jun 12, 2015 at 4:37 PM, Michael Nolan <htfoot@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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Are there tuning parameters that don't take effect immediately?
@ 2015-06-12 21:00 Joshua D. Drake <jd@commandprompt.com>
parent: Michael Nolan <htfoot@gmail.com>
1 sibling, 0 replies; 4+ messages in thread
From: Joshua D. Drake @ 2015-06-12 21:00 UTC (permalink / raw)
To: Michael Nolan <htfoot@gmail.com>; pgsql-performance
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.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Are there tuning parameters that don't take effect immediately?
@ 2015-06-12 21:13 Michael Nolan <htfoot@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Nolan @ 2015-06-12 21:13 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-performance
On Fri, Jun 12, 2015 at 4:52 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:
> On Fri, Jun 12, 2015 at 4:37 PM, Michael Nolan <htfoot@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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2015-06-12 21:13 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2015-06-12 20:37 Are there tuning parameters that don't take effect immediately? Michael Nolan <htfoot@gmail.com>
2015-06-12 20:52 ` David G. Johnston <david.g.johnston@gmail.com>
2015-06-12 21:13 ` Michael Nolan <htfoot@gmail.com>
2015-06-12 21:00 ` Joshua D. Drake <jd@commandprompt.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