Re: Restore of a reference database kills the auto analyze processing. - Mailing list pgsql-general

Classified as: {OPEN}

> Just for confirmation your settings are still?:

> autovacuum_max_workers = 10
> log_autovacuum_min_duration = 0

Yes.

> You said previously:
> "The only way I can find of getting the analyzer back is to restart Postgres."
>
> To be clear this means:
> 1) The lfm database is dropped/created.
Yes, using dropdb and pg_restore

> 2) There is a round of autovacuum immediately after the lfm is restored.
Yes, some tables in the lfm database, but not all, an apparently random selection, anywhere between 2 and 21 tables,
acrossthe lfm schemas, public & pg_catalog.
 

> 3) autovacuum then goes silent.
Yes. Dead in a ditch. But with no errors.

> 4) Before the next drop/create lfm you restart the Postgres server and autovacuum starts again.
I haven't restarted in a week, and the pattern remains, with a bit of analyze at each reload of lfm, and then nothing.

> What is in the logs when you do the restart?
Nothing notable:
1) denied connections, while restarting
2) authorized connections
3) auto analyze going into overdrive:
See below

> Is there some process that runs shortly after the drop/create lfm cycle?
Not that I can see.

Extract of postgres log for a typical restart :
2024-05-13 05:56:19.151 GMT [4688]: [99-1] db=,user=,app=,client= LOG:  shutting down
2024-05-13 05:56:19.162 GMT [4688]: [100-1] db=,user=,app=,client= LOG:  checkpoint starting: shutdown immediate
2024-05-13 05:56:19.751 GMT [344]: [1-1] db=[unknown],user=[unknown],app=[unknown],client=11.22.33.44 LOG:  connection
received:host=11.22.33.44 port=54730
 
2024-05-13 05:56:19.752 GMT [344]: [2-1] db=postgres,user=a_a,app=[unknown],client=11.22.33.44 FATAL:  the database
systemis shutting down
 
2024-05-13 05:56:19.843 GMT [4324]: [1-1] db=[unknown],user=[unknown],app=[unknown],client=11.22.33.44 LOG:  connection
received:host=11.22.33.44 port=54731
 
2024-05-13 05:56:19.845 GMT [4324]: [2-1] db=lfm2,user=fs_admin,app=[unknown],client=11.22.33.44 FATAL:  the database
systemis shutting down
 
...
2024-05-13 05:56:20.319 GMT [4688]: [101-1] db=,user=,app=,client= LOG:  checkpoint complete: wrote 326 buffers (0.1%);
0WAL file(s) added, 0 removed, 0 recycled; write=0.073 s, sync=1.006 s, total=1.168 s; sync files=33, longest=0.281 s,
average=0.031s; distance=588 kB, estimate=26425 kB
 
2024-05-13 05:57:13.889 GMT [4220]: [1-1] db=,user=,app=,client= LOG:  database system was shut down at 2024-05-13
05:56:20GMT
 
2024-05-13 05:57:13.921 GMT [4236]: [1-1] db=[unknown],user=[unknown],app=[unknown],client=11.22.33.44 LOG:  connection
received:host=11.22.33.44 port=54863
 
2024-05-13 05:57:13.922 GMT [4236]: [2-1] db=postgres,user=a_a,app=[unknown],client=11.22.33.44 FATAL:  the database
systemis starting up
 
...
2024-05-13 05:57:14.572 GMT [4868]: [1-1] db=[unknown],user=[unknown],app=[unknown],client=11.22.33.44 LOG:  connection
received:host=11.22.33.44 port=54869
 
2024-05-13 05:57:14.608 GMT [4868]: [2-1] db=lfm2,user=fs_admin,app=[unknown],client=11.22.33.44 LOG:  connection
authenticated:identity="fs_admin" method=md5 (E:/PostgreSQL/15/data/pg_hba.conf:108)
 
2024-05-13 05:57:14.608 GMT [4868]: [3-1] db=lfm2,user=fs_admin,app=[unknown],client=11.22.33.44 LOG:  connection
authorized:user=fs_admin database=lfm2
 
...
2024-05-13 05:57:29.305 GMT [6048]: [1-1] db=,user=,app=,client= LOG:  automatic analyze of table "lfm.fs.ij"
avg read rate: 7.813 MB/s, avg write rate: 0.000 MB/s
buffer usage: 350 hits, 5 misses, 0 dirtied
system usage: CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.00 s
2024-05-13 05:57:44.209 GMT [6140]: [1-1] db=,user=,app=,client= LOG:  automatic vacuum of table "lfm2.fs.ij": index
scans:0
 
pages: 0 removed, 2 remain, 2 scanned (100.00% of total)
tuples: 1 removed, 2 remain, 0 are dead but not yet removable
removable cutoff: 597176608, which was 0 XIDs old when operation ended
new relfrozenxid: 597176605, which is 372 XIDs ahead of previous value
index scan not needed: 0 pages from table (0.00% of total) had 0 dead item identifiers removed
avg read rate: 7.481 MB/s, avg write rate: 5.611 MB/s
buffer usage: 37 hits, 4 misses, 3 dirtied
WAL usage: 3 records, 2 full page images, 15749 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s
2024-05-13 05:57:59.312 GMT [6064]: [1-1] db=,user=,app=,client= LOG:  automatic vacuum of table "postgres.a.acap":
indexscans: 1
 
pages: 0 removed, 1 remain, 1 scanned (100.00% of total)
tuples: 56 removed, 24 remain, 0 are dead but not yet removable
removable cutoff: 597176633, which was 0 XIDs old when operation ended
new relfrozenxid: 597176559, which is 14186 XIDs ahead of previous value
index scan needed: 1 pages from table (100.00% of total) had 56 dead item identifiers removed
index "app_ctxt_area_pk": pages: 6 in total, 0 newly deleted, 3 currently deleted, 3 reusable
index "acap_area_hix": pages: 27 in total, 0 newly deleted, 0 currently deleted, 0 reusable
index "acap_process_id_ix": pages: 2 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 18.881 MB/s, avg write rate: 6.743 MB/s
buffer usage: 104 hits, 14 misses, 5 dirtied
WAL usage: 1 records, 1 full page images, 7577 bytes
system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s


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
itby any other person is unauthorised. If you are not an addressee, you must not disclose, copy, circulate or in any
otherway use or rely on the information contained in this e-mail. Such unauthorised use may be unlawful. If you have
receivedthis 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.