public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: HORDER Philip <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: Restore of a reference database kills the auto analyze processing.
Date: Tue, 21 May 2024 08:55:03 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
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
[email protected]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Restore of a reference database kills the auto analyze processing.
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox