Обсуждение: [MASSMAIL]Performance after restart/reboot pre-Analyze
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
On Thu, Mar 28, 2024 at 8:44 AM David Fogarty <dfogarty@icsusa.com> 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.
David J.
David Fogarty <dfogarty@icsusa.com> writes:
> It seems like whenever I restart my database server performance is quite bad until an Analyze has been performed. I
expectthis 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
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)