Feedback-ID: i76984098:Fastmail
Message-ID: <501b644f-ecb1-44b5-9822-bb322c801cd4@aklaver.com>
Date: Tue, 21 May 2024 08:55:03 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Restore of a reference database kills the auto analyze
 processing.
To: HORDER Philip <Phil.Horder@uk.thalesgroup.com>,
 "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.org>
References: <c4115724cd554dc981773c0ccfd3e8d9@uk.thalesgroup.com>
 <e90161be-b5fa-49ed-98c4-35d79092b7fa@aklaver.com>
 <7343f73e08294d4b997494f7b072ced6@uk.thalesgroup.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <7343f73e08294d4b997494f7b072ced6@uk.thalesgroup.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/501b644f-ecb1-44b5-9822-bb322c801cd4%40aklaver.com>
Precedence: bulk

On 5/21/24 06:00, HORDER Philip wrote:
> 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.

This is where I am getting confused. In your original post you had:

To apply an update, we:
   stop the applications that use LFM,
   set the user (LFU) to NOLOGIN
   kill any left-over connections: select 
pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE 
pg_stat_activity.datname = 'lfm' and usename = 'lfu';
   drop the existing reference database using the dropDb utility.
   reload the new file using pg_restore and the postgres super user.
   set the user (LFU) to LOGIN

In other words DROP DATABASE then CREATE DATABASE and reload the schema 
objects and associated data.

Yet your int stats output.txt file has things like the following:

-[ RECORD 3 ]-------+--------------------------------------------------
relid               | 923130055
schemaname          | a
relname             | cr_pt_e_202405020000_202405030000
seq_scan            | 1264
seq_tup_read        | 8800722491
idx_scan            | 4601405
idx_tup_fetch       | 4415621
n_tup_ins           | 3851400
n_tup_upd           | 15790
n_tup_del           | 0
n_tup_hot_upd       | 0
n_live_tup          | 7166325
n_dead_tup          | 0
n_mod_since_analyze | 0
n_ins_since_vacuum  | 0
last_vacuum         |
last_autovacuum     | 2024-05-03 09:03:44.810654+00
last_analyze        | 2024-05-09 08:44:37.725674+00
last_autoanalyze    | 2024-05-03 09:03:58.838664+00
vacuum_count        | 0
autovacuum_count    | 1
analyze_count       | 1
autoanalyze_count   | 1


I am having a hard time figuring out how both of the above can be true. 
Dropping and recreating the database would wipe out the statistics.


> 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.

The above is confusing also. In your original post you only referred to 
the postgres and lfm databases.

What other databases are you referring to?

As to partitions are you referring to partitions of tables in the lfm 
database or something else?


> 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
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com