Daylight Ingest and Slow Deletions

[pyoung-sc]

Hello!

I've been running an ingest of the Daylight v1.30 release (including a buildings sidecar that has been merged into the planet file )which has a ton more buildings in it than previous releases.

From what I can tell, the data has been inserted into postgres, but when I look at what queries are still going, I see a handful of idle queries like this

 pid | duration | query | state ------+------------------------+----------------------------------------------------------------------------------------------+------- 3252 | 3 days 06:06:02.449547 | ANALYZE "planet_osm_rels" | idle 3256 | 5 days 04:29:04.091938 | PREPARE get_wkb(bigint) AS SELECT "way" FROM "public"."planet_osm_point" WHERE "osm_id" = $1 | idle 3257 | 3 days 06:05:56.190388 | CREATE INDEX ON "public"."planet_osm_line" USING BTREE (osm_id) | idle 3258 | 3 days 04:45:54.122027 | CREATE INDEX ON "public"."planet_osm_polygon" USING BTREE (osm_id) | idle 3259 | 3 days 04:26:22.868305 | CREATE INDEX ON "public"."planet_osm_roads" USING BTREE (osm_id) | idle 

and these slow running deletes

3255 | 00:30:10.531937 | DELETE FROM "public"."planet_osm_line" WHERE osm_id IN (1229976490,1229977130,...) 

These delete queries change as time goes on, but each one takes quite a while to finish, which I presume is holding up the idle queries from above.

I was curious if anyone knew what part of the workflow makes these deletes get issued, and if there's something to be done to speed things up?

The call to osm2pgsql looks like

osm2pgsql --slim --hstore-all -C 61440 -S osm2pgsql.lua -O flex --flat-nodes flat.nodes --number-processes 16 ... 

where the lua file is from tilezen.

Many thanks for any info/suggestions!

[pnorman]

Can you provide your osm2pgsql output and version?

[pyoung-sc]

It is

osm2pgsql version 1.5.1 (1.5.1) Build: Release Compiled using the following library versions: Libosmium 2.17.0 Proj [API 6] 8.2.1 Lua 5.3.6 

and so far in the logs I see

2023-09-29 15:58:16 Reading input files done in 166987s (46h 23m 7s). 2023-09-29 15:58:16 Processed 12349015866 nodes in 2790s (46m 30s) - 4426k/s 2023-09-29 15:58:16 Processed 1770379046 ways in 37266s (10h 21m 6s) - 48k/s 2023-09-29 15:58:16 Processed 10285732 relations in 126931s (35h 15m 31s) - 81/s 2023-09-29 15:58:16 Reprocess marked ways (stage 2)... 2023-09-29 15:58:16 Creating id index on table 'planet_osm_line'... 2023-09-29 17:18:19 Creating id index on table 'planet_osm_polygon'... 2023-09-29 17:37:50 Creating id index on table 'planet_osm_roads'... 2023-09-29 18:52:45 Creating id indexes took 10469s (2h 54m 29s) 2023-09-29 18:52:45 There are 3011391 ways to reprocess... 

It is on my list to try the latest release in the next go around...

[pnorman]

Can you try with 1.9.2 and report back? I believe this was fixed with 7be6f0c, which is present in 1.7.0 onward.

[pyoung-sc]

Will do!

[pyoung-sc]

@pnorman Good news, with 1.9.2 it got past where 1.5.1 was stuck!

2023-10-05 03:31:38 Reading input files done in 167714s (46h 35m 14s). 2023-10-05 03:31:38 Processed 12349015866 nodes in 2817s (46m 57s) - 4384k/s 2023-10-05 03:31:38 Processed 1770379046 ways in 37281s (10h 21m 21s) - 47k/s 2023-10-05 03:31:38 Processed 10285732 relations in 127616s (35h 26m 56s) - 81/s 2023-10-05 03:31:39 Reprocess marked ways (stage 2)... 2023-10-05 03:31:44 Creating id index on table 'planet_osm_line'... 2023-10-05 04:52:38 Creating id index on table 'planet_osm_polygon'... 2023-10-05 05:12:10 Creating id index on table 'planet_osm_roads'... 2023-10-05 06:27:12 Creating id indexes took 10532s (2h 55m 32s) 2023-10-05 06:27:12 There are 3011391 ways to reprocess... 2023-10-05 09:41:30 Done postprocessing on table 'planet_osm_nodes' in 0s 2023-10-05 09:41:30 Building index on table 'planet_osm_rels' 2023-10-05 09:41:30 Building index on table 'planet_osm_ways' 2023-10-05 09:41:32 Clustering table 'planet_osm_point' by geometry... 2023-10-05 09:41:35 Clustering table 'planet_osm_roads' by geometry... 2023-10-05 09:41:35 Clustering table 'planet_osm_polygon' by geometry... 2023-10-05 09:41:35 Clustering table 'planet_osm_line' by geometry... 2023-10-05 10:16:40 Creating index on table 'planet_osm_point' ("way")... 2023-10-05 10:28:23 Creating id index on table 'planet_osm_point'... 2023-10-05 10:29:50 Analyzing table 'planet_osm_point'... 

The bad news is there was a Segmentation fault (core dumped)... after that last line.

[pyoung-sc]

@mboeringa sounds like they've got a fix:
We have found the cause of the collision and will be fixing it ASAP. The issue is that the Admin OSC was not numbered correctly. If you "osmium renumber" the admin osc before applying it then it should fix the issue until we get the root cause fixed internally.

[pyoung-sc]

Using the parameters recommended in the manual trimmed off about 12 hours:

Reading input files done in 109127s (30h 18m 47s). 2023-10-26 06:23:01 Processed 12427938028 nodes in 1899s (31m 39s) - 6544k/s 2023-10-26 06:23:01 Processed 1779505585 ways in 23308s (6h 28m 28s) - 76k/s 2023-10-26 06:23:01 Processed 10383411 relations in 83920s (23h 18m 40s) - 124/s 2023-10-26 06:23:01 Reprocess marked ways (stage 2)... 2023-10-26 06:23:04 Creating id index on table 'planet_osm_line'... 2023-10-26 07:41:11 Creating id index on table 'planet_osm_polygon'... 2023-10-26 07:59:33 Creating id index on table 'planet_osm_roads'... 2023-10-26 09:13:59 Creating id indexes took 10258s (2h 50m 58s) 2023-10-26 09:14:00 There are 3064634 ways to reprocess... 2023-10-26 11:22:59 Clustering table 'planet_osm_point' by geometry... 2023-10-26 11:23:02 Done postprocessing on table 'planet_osm_nodes' in 0s 2023-10-26 11:23:02 Building index on table 'planet_osm_ways' 2023-10-26 11:23:02 Building index on table 'planet_osm_rels' 2023-10-26 11:23:04 Clustering table 'planet_osm_line' by geometry... 2023-10-26 11:23:04 Clustering table 'planet_osm_roads' by geometry... 2023-10-26 11:23:04 Clustering table 'planet_osm_polygon' by geometry... 2023-10-26 11:40:25 Creating index on table 'planet_osm_point' ("way")... 2023-10-26 11:50:59 Creating id index on table 'planet_osm_point'... 2023-10-26 11:52:39 Analyzing table 'planet_osm_point'... 2023-10-26 15:19:06 Creating index on table 'planet_osm_polygon' ("way")... 2023-10-26 16:59:31 Creating id index on table 'planet_osm_polygon'... 2023-10-26 17:16:44 Analyzing table 'planet_osm_polygon'... 2023-10-26 21:00:37 Done postprocessing on table 'planet_osm_ways' in 34654s (9h 37m 34s) 2023-10-26 21:00:37 Done postprocessing on table 'planet_osm_rels' in 262s (4m 22s) 2023-10-26 21:00:37 All postprocessing on table 'planet_osm_point' done in 1784s (29m 44s). 2023-10-27 11:01:31 Creating index on table 'planet_osm_roads' ("way")... 2023-10-27 11:40:20 Creating index on table 'planet_osm_line' ("way")... 2023-10-27 17:53:36 Creating id index on table 'planet_osm_roads'... 2023-10-27 18:49:15 Analyzing table 'planet_osm_roads'... 2023-10-27 18:58:35 Creating id index on table 'planet_osm_line'... 2023-10-27 19:55:28 Analyzing table 'planet_osm_line'... 2023-10-27 19:55:30 All postprocessing on table 'planet_osm_line' done in 117155s (32h 32m 35s). 2023-10-27 19:55:30 All postprocessing on table 'planet_osm_polygon' done in 21231s (5h 53m 51s). 2023-10-27 19:55:30 All postprocessing on table 'planet_osm_roads' done in 113181s (31h 26m 21s). 2023-10-27 19:55:30 Storing properties to table '"public"."osm2pgsql_properties"'. 2023-10-27 19:55:30 osm2pgsql took 244276s (67h 51m 16s) overall. 

[mboeringa]

@pyoung-sc , apart from possible latency issues as Paul hinted on, I do think the current recommendations especially for work_mem (50MB) are still very conservative in the manual, even taking into account the 64GB RAM limit that is presumed there. They definitely do not represent recommended settings for a machine with 256GB, like you seem to have been using last.

My current postgresql settings on a 512GB machine (actually only 420GB assigned to the VM, + another 100GB swap), are visible in this benchmark

As you can see, I have work_mem set in the GB, not MB.

[pnorman]

The benchmark page is incorrect. The global synchronous_commit setting has no impact, because osm2pgsql sets for its transactions.

I do not recommend relying on that page, as it has significant amounts of misinformation. The manual settings are good recommendations for a standard osm2pgsql import, although some of the settings have no measurable impact.

[mboeringa]

@pnorman. Thanks for correcting the Wiki page, but my remark about synchronous_commit was more in general, also taking into account likely post-processing done on the data, where it may have effect.

I think it is a bit over-stated that the page "has significant amounts of misinformation". Benchmarks are benchmarks, it is what you measure, so the majority of the information on the page, apart from possible issues with specific statements about specific PostgreSQL configuration settings, is real.

[mboeringa]

I've been running an ingest of the Daylight v1.30 release (including a buildings sidecar that has been merged into the planet file )which has a ton more buildings in it than previous releases.

Ah thanks, I'll try again with more (previous run was on a r5.8xl with 256 GB RAM).

@pyoung-sc Not to side track this discussion, but could you explain how you merged your 1.2 billion building sidecar (99 GB) into the planet file? The official daylight page recommends using osmium. However, I tried to do this on a machine with >400 GB RAM available on Ubuntu 22.04, and osmium crashed, including the Terminal window I ran it in, likely when it hit the maximum RAM available, as I saw it go over >300 GB RAM last time I checked.

This makes me wonder how you managed to merge those gigantic files on a system with just 256 GB?

[mboeringa]

I now noticed in the osmium manual, that the Meta suggested apply-changes option of osmium, will sort the input files, meaning the entire pbf file needs to be read in memory, which according to the same manual page will likely cost about 10x the amount of RAM as the size of the input compressed file.

No wonder I ran out of RAM, that means at least 1TB(!) of RAM needed for the 99GB gzipped changeset file... The suggestion by Meta seems wrong with the current new gigantic sidecar file with 1.2B buildings, few if any people in the OSM community will have a server with that much RAM. Even for the Cloud providers, an instance with that much RAM is rare.

Don't know if it will work, but will now try to:

• Convert the osc.gz file to osm.pbf using the osmium cat command, which so far uses negligible memory.
• Merge the two pbfs (Planet + building sidecar) using osmium merge, which also should have low memory footprint according to the manual pages (I don't know though, if osmium merge can handle the negative IDs that appear to be present in the sidecar file according to Meta documentation...).
• Renumber the pbf using osmium renumber.
• Try to import it using osm2pgsql.

[pyoung-sc]

Hi,

Yeah I used osmium merge and it worked just fine on a reasonable machine; there was some discussion in the OSM slack about exactly this recently!

https://osmus.slack.com/archives/CSPDYAY1F/p1695420281157739?thread_ts=1695051501.111779&cid=CSPDYAY1F

[mboeringa]

@pyoung-sc .

Thanks, just one more question: did you merge the osc.gz file directly with the Planet osm.pbf file? I assumed they needed to be of the same format, hence the described step of first using cat to convert the OSM change file to pbf.

But I guess this is not necessary?

[pyoung-sc]

I used it directly, just worked!

[mboeringa]

@pyoung-sc

https://daylightmap.org/2023/10/11/file-format-change.html

[mboeringa]

@pyoung-sc ,

I now imported the latest v1.32 of Daylight. However, I am seeing a serious issue with the country / administrative boundaries, as added to the PBF using the osmium apply-changes option per Facebook instructions (I used osmium merge for the buildings).

As you can see, the country boundaries (admin_level=2) are garbled, and only Australia and a few other countries come out more or less unscathed. The majority of boundaries is missing.

Admittedly, the result you see is based on a custom generalization workflow I developed myself, but I have thoroughly battle tested this workflow over the last couple of years importing country and official OpenStreetMap Planet downloads, and never before seen this particular issue, hence I am pretty sure the issue is not on my side.

Another indication that there may have been a processing error at Facebook's side, is that the link to the matching coastlines-v1.32.gz file, is broken and ends up on an XML error page (https://daylightmap.org/). This is not the case for the previous Daylight v1.30.

Before I contact Meta, can you confirm this issue?

[pyoung-sc]

Will do on checking the borders.

The coastlines file is at s3://daylight-map-distribution/release/v1.32/coastlines-v1.32.tgz, with the file name changes maybe the html didn't get quite set correctly!

[mboeringa]

@pyoung-sc

Any news regarding the boundary issue?

Just to be sure, I have re-imported the existing combined PBF I created with osmium, and re-run my workflow. I still see the issue.

I have not yet re-downloaded all the data. I did notice that Facebook seems to have fixed the download link for the coastlines, but if that also means they fixed anything else, IDK.

I would still appreciate if you could confirm this issue before I go through the whole process of re-downloading and re-processing huge datasets that may - or may not - remain broken.

[lonvia]

We are getting way off topic here. This is a forum for osm2pgsql. You might want to find a forum for daylight data for further discussion.