public inbox for [email protected]
help / color / mirror / Atom feedRestore of a reference database kills the auto analyze processing.
8+ messages / 2 participants
[nested] [flat]
* Restore of a reference database kills the auto analyze processing.
@ 2024-05-02 15:52 HORDER Philip <[email protected]>
2024-05-02 16:59 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2024-05-02 18:41 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: HORDER Philip @ 2024-05-02 15:52 UTC (permalink / raw)
To: [email protected] <[email protected]>
Running Postgres 15.3 with PostGIS 3.3
On Windows 10 (yes, I know)
It's a single node db with no replication, topping out at about 200GB.
We have a schema (A) in the default 'postgres' maintenance database, which our software services connect to, with one set of users (SU)
We have another database, let's call it LFM, which contains reference data for some COTS software. I don't know what's in it, we just get given updates for it in pg_backup binary files, about 2MB each.
This is accessed by a different postgres user (LFU) supplied to the COTS tool.
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
Other services connecting to the default db, with SU users should keep running with no dropouts.
This works, some of the time.
If I repeat the update process, somewhere around run #4 the auto analyzer stops working, and only analyzes tables in the new db at the point of reload, then shuts off again.
All vacuum and analyze operations on the 'postgres' database just stops, even though there is still data processing into it.
With log_autovacuum_min_duration = 0, we are logging all vacuum & analyze operations, so we can see when the entries shut off in the Postgres log files, e.g.
2024-05-02 14:52:01.597 GMT [6896]: [23-1] db=,user=,app=,client= LOG: automatic analyze of table "lfm.pg_catalog.pg_trigger"
The only way I can find of getting the analyzer back is to restart Postgres.
We've narrowed the cause down to the pg_restore, but have no idea where to go from here.
Can anyone help stand the anaylzer back up please?
Most configs are left at default, (apart from memory settings) but we currently have
autovacuum_max_workers = 10
log_autovacuum_min_duration = 0
thanks,
Phil Horder
Database Mechanic
Thales Land & Air Systems
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
@ 2024-05-02 16:59 ` Adrian Klaver <[email protected]>
2024-05-07 09:30 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-05-02 16:59 UTC (permalink / raw)
To: HORDER Philip <[email protected]>; [email protected] <[email protected]>
On 5/2/24 8:52 AM, HORDER Philip wrote:
> Running Postgres 15.3 with PostGIS 3.3
>
> On Windows 10 (yes, I know)
>
> It’s a single node db with no replication, topping out at about 200GB.
>
> We have a schema (A) in the default 'postgres' maintenance database,
> which our software services connect to, with one set of users (SU)
>
> We have another database, let’s call it LFM, which contains reference
> data for some COTS software. I don't know what's in it, we just get
> given updates for it in pg_backup binary files, about 2MB each.
Do you mean pg_basebackup, pg_dump or something else?
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 16:59 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
@ 2024-05-07 09:30 ` HORDER Philip <[email protected]>
0 siblings, 0 replies; 8+ messages in thread
From: HORDER Philip @ 2024-05-07 09:30 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Sorry, pg_dump.
Phil Horder
Database Mechanic
-----Original Message-----
From: Adrian Klaver <[email protected]>
Sent: 02 May 2024 17:59
To: HORDER Philip <[email protected]>; [email protected]
Subject: [EXTERNAL EMAIL] Re: Restore of a reference database kills the auto analyze processing.
On 5/2/24 8:52 AM, HORDER Philip wrote:
> Running Postgres 15.3 with PostGIS 3.3
>
> On Windows 10 (yes, I know)
>
> It’s a single node db with no replication, topping out at about 200GB.
>
> We have a schema (A) in the default 'postgres' maintenance database,
> which our software services connect to, with one set of users (SU)
>
> We have another database, let’s call it LFM, which contains reference
> data for some COTS software. I don't know what's in it, we just get
> given updates for it in pg_backup binary files, about 2MB each.
Do you mean pg_basebackup, pg_dump or something else?
--
Adrian Klaver
[email protected]
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
@ 2024-05-02 18:41 ` Adrian Klaver <[email protected]>
2024-05-07 09:38 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
1 sibling, 1 reply; 8+ messages in thread
From: Adrian Klaver @ 2024-05-02 18:41 UTC (permalink / raw)
To: HORDER Philip <[email protected]>; [email protected] <[email protected]>
On 5/2/24 08:52, HORDER Philip wrote:
> Running Postgres 15.3 with PostGIS 3.3
>
> On Windows 10 (yes, I know)
>
> It’s a single node db with no replication, topping out at about 200GB.
>
> We have a schema (A) in the default 'postgres' maintenance database,
> which our software services connect to, with one set of users (SU)
This above is probably not a good idea, The 'postgres' database is
generally taken to be a throw away database for establishing an initial
connection. Many utilities/tools use it for that purpose, having your
data in it exposes that data.
> This works, some of the time.
>
> If I repeat the update process, somewhere around run #4 the auto
> analyzer stops working, and only analyzes tables in the new db at the
> point of reload, then shuts off again.
>
> All vacuum and analyze operations on the 'postgres' database just stops,
> even though there is still data processing into it.
Is there enough data processing?
Autovacuum has thresholds for turning on, are you sure those thresholds
are just not being met?
>
> With log_autovacuum_min_duration = 0, we are logging all vacuum &
> analyze operations, so we can see when the entries shut off in the
> Postgres log files, e.g.
>
> 2024-05-02 14:52:01.597 GMT [6896]: [23-1] db=,user=,app=,client= LOG:
> automatic analyze of table "lfm.pg_catalog.pg_trigger"
Except the above shows it working.
What is the evidence it is not?
>
> The only way I can find of getting the analyzer back is to restart Postgres.
Did you wait to see if activity after the pg_restore crossed the
autovacuum thresholds?
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 18:41 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
@ 2024-05-07 09:38 ` HORDER Philip <[email protected]>
2024-05-07 15:24 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
0 siblings, 1 reply; 8+ messages in thread
From: HORDER Philip @ 2024-05-07 09:38 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Thanks for your time Adrian
> Is there enough data processing?
Yes, one table is receiving upwards of 20 million rows daily.
We noticed the problem when fetch performance on this table degraded after updates.
> Autovacuum has thresholds for turning on, are you sure those thresholds are just not being met?
Yes we're sure. Our biggest table is set for a fixed number of rows rather than a percentage, this gets an auto analyse about every 15 minutes.
After an update this just stops, and there are no analyse entries in the log file. None at all, for any table.
When we restart Postgres the auto analyse restarts and catches up with the backlog.
Phil Horder
Database Mechanic
Thales
Land & Air Systems
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 18:41 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2024-05-07 09:38 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
@ 2024-05-07 15:24 ` Adrian Klaver <[email protected]>
2024-05-07 15:28 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2024-05-15 08:08 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
0 siblings, 2 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-05-07 15:24 UTC (permalink / raw)
To: HORDER Philip <[email protected]>; [email protected] <[email protected]>
On 5/7/24 02:38, HORDER Philip wrote:
> Thanks for your time Adrian
>
>
>> Is there enough data processing?
>
> Yes, one table is receiving upwards of 20 million rows daily.
> We noticed the problem when fetch performance on this table degraded after updates.
>
>> Autovacuum has thresholds for turning on, are you sure those thresholds are just not being met?
>
> Yes we're sure. Our biggest table is set for a fixed number of rows rather than a percentage, this gets an auto analyse about every 15 minutes.
>
> After an update this just stops, and there are no analyse entries in the log file. None at all, for any table.
1) What is the exact pg_restore command you are using?
2) From earlier post: '... only analyzes tables in the new db at the
point of reload, then shuts off again.' Provide that sequence of events
from the Postgres log.
3) Also statistics from
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW
for that table after the reload.
>
> When we restart Postgres the auto analyse restarts and catches up with the backlog.
>
>
> Phil Horder
> Database Mechanic
>
> Thales
> Land & Air Systems
>
> 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.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 18:41 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2024-05-07 09:38 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-07 15:24 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
@ 2024-05-07 15:28 ` Adrian Klaver <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: Adrian Klaver @ 2024-05-07 15:28 UTC (permalink / raw)
To: HORDER Philip <[email protected]>; [email protected] <[email protected]>
On 5/7/24 08:24, Adrian Klaver wrote:
> On 5/7/24 02:38, HORDER Philip wrote:
>> Thanks for your time Adrian
>>
>>
>
> 1) What is the exact pg_restore command you are using?
>
> 2) From earlier post: '... only analyzes tables in the new db at the
> point of reload, then shuts off again.' Provide that sequence of events
> from the Postgres log.
>
> 3) Also statistics from
>
> https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW
>
> for that table after the reload.
4) The autovacuum settings you have in effect.
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 8+ messages in thread
* RE: Restore of a reference database kills the auto analyze processing.
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 18:41 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
2024-05-07 09:38 ` RE: Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-07 15:24 ` Re: Restore of a reference database kills the auto analyze processing. Adrian Klaver <[email protected]>
@ 2024-05-15 08:08 ` HORDER Philip <[email protected]>
1 sibling, 0 replies; 8+ messages in thread
From: HORDER Philip @ 2024-05-15 08:08 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; [email protected] <[email protected]>
Classified as: {OPEN}
Backups of this db are created with:
pg_dump --file=fsm.dmp -Fc --blobs --oids --dbname=lfm --host=localhost --port=nnnn --username=superuser
Restore is run with:
dropdb --port=nnnn --maintenance-db=postgres --username=superuser --if-exists lfm
pg_restore -Fc --create --dbname=postgres --port=nnnn --username=superuser
fsm.dmp
-------------
> 2) From earlier post: '... only analyzes tables in the new db at the point of reload, then shuts off again.' Provide that sequence of events from the Postgres log.
Log file extract is attached, with object names obfuscated.
> 3) Also statistics from
https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-ALL-TABLES-VIEW
for that table after the reload.
Well, 'that' table is everything, I'll add an entry for a table that obviously needs stats collection.
From yesterday, current stats for table a.accp, from pg_STAT_all_tables:
"811486381""airscape""accp"16458538988177871456553503047055581967135016364880294000"2024-05-14 14:51:37.158892+00""2024-05-09 08:27:45.328468+00""2024-05-14 13:15:31.999198+00"01815170101653
This table has a low row count, but high content turnover.
It usually gets auto-analyzed every minute.
For today, this hasn't been auto analysed since the update at 3am.
> 4) The autovacuum settings you have in effect:
vacuum_cost_limit = 2000
log_autovacuum_min_duration = 0
autovacuum_max_workers = 10
all other vacuum settings are defaults.
------------
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.
Attachments:
[application/octet-stream] postgresql-05-02 - obfuscated extract.log (42.2K, 2-postgresql-05-02%20-%20obfuscated%20extract.log)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2024-05-15 08:08 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-05-02 15:52 Restore of a reference database kills the auto analyze processing. HORDER Philip <[email protected]>
2024-05-02 16:59 ` Adrian Klaver <[email protected]>
2024-05-07 09:30 ` HORDER Philip <[email protected]>
2024-05-02 18:41 ` Adrian Klaver <[email protected]>
2024-05-07 09:38 ` HORDER Philip <[email protected]>
2024-05-07 15:24 ` Adrian Klaver <[email protected]>
2024-05-07 15:28 ` Adrian Klaver <[email protected]>
2024-05-15 08:08 ` HORDER Philip <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox