public inbox for [email protected]
help / color / mirror / Atom feedPerformance after restart/reboot pre-Analyze
4+ messages / 4 participants
[nested] [flat]
* Performance after restart/reboot pre-Analyze
@ 2024-03-28 15:43 David Fogarty <[email protected]>
2024-03-28 15:50 ` Re: Performance after restart/reboot pre-Analyze David G. Johnston <[email protected]>
2024-03-28 15:52 ` Re: Performance after restart/reboot pre-Analyze Tom Lane <[email protected]>
2024-03-28 17:32 ` Re: Performance after restart/reboot pre-Analyze Alvaro Herrera <[email protected]>
0 siblings, 3 replies; 4+ messages in thread
From: David Fogarty @ 2024-03-28 15:43 UTC (permalink / raw)
To: [email protected]
Hello,
It seems like whenever I restart my database server performance is quite bad until an Analyze has been performed. I expect this is due to the query planner needing new stats, but that leads me to three questions:
1. Is there a way to have Analyze stats/query planner "plans" persist through a restart? Like store them somehow after an Analyze and tell Postgres where to pull them from?
2. If not, is there a technical reason/limitation as to why?
3. Are there general configuration settings/optimizations for improving performance after a restart before an Analyze has finished?
If this would be better directed toward a different mailing list, let me know. I appreciate any guidance on this.
Also, if it affects anything, the environments are on AWS RDS, we're currently on 14 and 15 but will be upgrading to 16 very soon. I've only recently been brought into this, so I'm not fully aware of what kinds of queries we mostly handle or how the various tables/views are indexed.
Thanks,
Dave
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance after restart/reboot pre-Analyze
2024-03-28 15:43 Performance after restart/reboot pre-Analyze David Fogarty <[email protected]>
@ 2024-03-28 15:50 ` David G. Johnston <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: David G. Johnston @ 2024-03-28 15:50 UTC (permalink / raw)
To: David Fogarty <[email protected]>; +Cc: [email protected]
On Thu, Mar 28, 2024 at 8:44 AM David Fogarty <[email protected]> wrote:
>
> It seems like whenever I restart my database server performance is quite
> bad until an Analyze has been performed. I expect this is due to the query
> planner needing new stats,
>
Nope, statistics are WAL logged.
> but that leads me to three questions:
>
> 1. Is there a way to have Analyze stats/query planner "plans" persist
> through a restart?
>
Plans are session-scoped.
What you are observing is the shared buffers being empty. There is a
contrib library, pg_prewarm, designed to help alleviate this problem.
https://www.postgresql.org/docs/current/pgprewarm.html
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance after restart/reboot pre-Analyze
2024-03-28 15:43 Performance after restart/reboot pre-Analyze David Fogarty <[email protected]>
@ 2024-03-28 15:52 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2024-03-28 15:52 UTC (permalink / raw)
To: David Fogarty <[email protected]>; +Cc: [email protected]
David Fogarty <[email protected]> writes:
> It seems like whenever I restart my database server performance is quite bad until an Analyze has been performed. I expect this is due to the query planner needing new stats, but that leads me to three questions:
A simple restart shouldn't invalidate statistics. I'd lay some small
change on your actual problem being not that, but the need to
repopulate the server's buffers from disk files. If so, pg_prewarm
might help you.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Performance after restart/reboot pre-Analyze
2024-03-28 15:43 Performance after restart/reboot pre-Analyze David Fogarty <[email protected]>
@ 2024-03-28 17:32 ` Alvaro Herrera <[email protected]>
2 siblings, 0 replies; 4+ messages in thread
From: Alvaro Herrera @ 2024-03-28 17:32 UTC (permalink / raw)
To: David Fogarty <[email protected]>; +Cc: [email protected]
On 2024-Mar-28, David Fogarty wrote:
> It seems like whenever I restart my database server performance is
> quite bad until an Analyze has been performed. I expect this is due to
> the query planner needing new stats, but that leads me to three
> questions:
How exactly are you restarting the database server? Statistics
collected by ANALYZE are lost on crashes or when recovery is run, so if
you're just hard-crashing your server, then ANALYZE is indeed necessary.
If that's what's happening, try not doing that -- use fast shutdown (not
immediate) and wait until it's finished before turning power off.
If you do need to crash your server (but why risk your data?), then
maybe vacuumdb --analyze-in-stages would be helpful.
Otherwise, a cold cache as others mentioned is the most likely
explanation.
--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"Doing what he did amounts to sticking his fingers under the hood of the
implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-03-28 17:32 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-28 15:43 Performance after restart/reboot pre-Analyze David Fogarty <[email protected]>
2024-03-28 15:50 ` David G. Johnston <[email protected]>
2024-03-28 15:52 ` Tom Lane <[email protected]>
2024-03-28 17:32 ` Alvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox