[Tile-serving] [osm2pgsql-dev/osm2pgsql] osm2pgsql should not perform analyze by itself (Issue #2094)

[Adrien nayrat]

Hello,

At openstreetmap france, we noticed osm2pgsql perform ``ANALYZE`` on some tables.  Unfortunately, this task cancel autovacuum :

```
2023-10-16 00:00:37.086 UTC [1643288-1]: db=osm,user=osm2pgsql,app=osm2pgsql,client=[local],xid=0 LOG:  process 1643288 still waiting for ShareUpdateExclusiveLock on relation 18197 of database 16402 after 1000.160 ms
2023-10-16 00:00:37.086 UTC [1643288-2]: db=osm,user=osm2pgsql,app=osm2pgsql,client=[local],xid=0 DETAIL:  Process holding the lock: 1640734. Wait queue: 1643288.
2023-10-16 00:00:37.086 UTC [1643288-3]: db=osm,user=osm2pgsql,app=osm2pgsql,client=[local],xid=0 STATEMENT:  ANALYZE "planet_osm_ways"
2023-10-16 00:00:37.086 UTC [1640734-1]: db=,user=,app=,client=,xid=0 ERROR:  canceling autovacuum task
2023-10-16 00:00:37.086 UTC [1640734-2]: db=,user=,app=,client=,xid=0 CONTEXT:  while vacuuming index "planet_osm_ways_nodes_bucket_idx" of relation "public.planet_osm_ways"
        automatic vacuum of table "osm.public.planet_osm_ways"
2023-10-16 00:00:37.086 UTC [1643288-4]: db=osm,user=osm2pgsql,app=osm2pgsql,client=[local],xid=0 LOG:  process 1643288 acquired ShareUpdateExclusiveLock on relation 18197 of database 16402 after 1000.476 ms
2023-10-16 00:00:37.086 UTC [1643288-5]: db=osm,user=osm2pgsql,app=osm2pgsql,client=[local],xid=0 STATEMENT:  ANALYZE "planet_osm_ways"
```

 We have an issue with a big table (`planet_osm_ways`). Postgres try to perform an autovacuum on this table, but the task is canceled by osm2pgsql. As the table is big, we can't perform autovacuum before osm2pgsql start analyze.
Thus, the table is never vacuumed and Postgres trying to vacuum it consume many I/Os.

We should rely on Postgres autovacuum, since pg 13, it is even able to start vacuum/analyze on append only table. osm2pgsql do not have to execute ANALYZE by tself.

Regards,


-- 
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2094
You are receiving this because you are subscribed to this thread.

Message ID: <osm2pgsql-dev/osm2pgsql/issues/2094 at github.com>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20231016/6f9c8f3a/attachment.htm>