Subject: Re: Restore of a reference database kills the auto analyze processing.

From: HORDER Philip <[email-masked]>
Date: Thu, 16 May 2024 15:59:03 +0000

Classified as: {OPEN}

Adrian,

> Still your contention was that autovacuum quit running after the initial restore and that is not the case....

This Postgres server has been restarted a few times since 2nd May most recently on Tuesday 14th, hence the more recent analyze status.

We've had some problems with our data feeds on this integration system, but these are now running again.
I'm planning to leave it all alone until I'm back in the office on Tuesday, and run this query again for a few tables and send you an update.
I'm expecting no further stats analysis, (and the performance to be appalling).

Thanks for your time.

Phil Horder
Database Mechanic

Thales
Land & Air Systems

{OPEN}
The information contained in this e-mail is confidential. It is intended only for the stated addressee(s) and access to it by any other person is unauthorised. If you are not an addressee, you must not disclose, copy, circulate or in any other way use or rely on the information contained in this e-mail. Such unauthorised use may be unlawful. If you have received this e-mail in error, please inform the originator immediately and delete it and all copies from your system.

Thales UK Limited. A company registered in England and Wales. Registered Office: 350 Longwater Avenue, Green Park, Reading, Berks RG2 6GF. Registered Number: 868273

Please consider the environment before printing a hard copy of this e-mail.

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 16 May 2024 09:13:29 -0700

> This Postgres server has been restarted a few times since 2nd May most recently on Tuesday 14th, hence the more recent analyze status.

Assuming clean shutdowns the statistics will survive restarts. They 
would be wiped when you drop a database and start over, have an unclean 
shutdown or you use one of the reset functions from here:

https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-FUNCTIONS

28.2.25. Statistics Functions

> I'm expecting no further stats analysis, (and the performance to be appalling).

 From here:

https://www.postgresql.org/docs/current/runtime-config-logging.html

log_autovacuum_min_duration

In addition, when this parameter is set to any value other than -1, a 
message will be logged if an autovacuum action is skipped due to a 
conflicting lock or a concurrently dropped relation.

-- 
Adrian Klaver
[email-masked]

---

From: HORDER Philip <[email-masked]>
Date: Tue, 21 May 2024 13:00:33 +0000

Classified as: {OPEN}

> Assuming clean shutdowns the statistics will survive restarts. They would be wiped when you drop a database and start over, have an unclean shutdown or you use one of the reset functions...

Yes, stats are permanent, but are not being updated.
We don't use any of the pg_stat_reset functions.

-------------------------------------
I've left the system alone over the weekend.
Here's the timeline:

14th May:
Postgres working ok, 1767 log entries for "automatic analyze", mostly in database postgres.
03:30 Jenkins deployed an update, resulting in reload of lfm database.

15th May:
Postgres working ok, with 257 logged "automatic analyze" events, up until 03:30
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Log of the auto analyse around that update is attached.
No further auto analyse logged after 03.30

16th May:
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Only 3 logged "automatic analyze" in the whole file, timed at 03:30, for lfm.public and lfm.pg_catalog tables.

Test data feed restarts at 2024-05-16 14:54
Daily partitions are created for this data, and each partition from here is showing no vacuum or analyze timestamps

17th May:
03:30 Jenkins deployed an update, resulting in reload of lfm database.
Only the reloaded database shows log entries for "automatic analyze", at 03:30, then nothing more.

18th - 21st may:
As per 17th

Stats output for a sample of tables is attached.
You can see that the partitions were auto analysed on the day they were created, and not since.
And that new partitions haven’t been analysed at all.
(accp does get a manual analyze occasionally, from an SQL function somewhere, but not enough to stop auto analyze from running as well)

Summary: since the reload of lfm database on 15th May, the only "automatic analyze" events logged have been for the lfm database, at the point of reload.
No other stats analyze seems to have taken place, on any database on the server since that point, even partitions with over a million rows.
Apart from that, Postgres appears to be working normally.

I'm sure that another restart of Postgres will restore the auto analyze, until the next pg_restore of lfm.
So what's going on?  How is it that we're breaking this important function that we shouldn't be able to have any effect on?

Thanks for looking,
Phil Horder
Database Mechanic

Thales
Land & Air Systems

{OPEN}