Daylight Ingest and Slow Deletions #2088

pyoung-sc · 2023-10-02T22:14:18Z

pyoung-sc
Oct 2, 2023

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!

Answered by pyoung-sc

Oct 5, 2023

@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 1…

View full answer

pnorman · 2023-10-02T22:37:51Z

pnorman
Oct 2, 2023
Maintainer

Can you provide your osm2pgsql output and version?

3 replies

pyoung-sc Oct 2, 2023
Author

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 Oct 3, 2023
Maintainer

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 Oct 3, 2023
Author

Will do!

pyoung-sc · 2023-10-05T13:03:18Z

pyoung-sc
Oct 5, 2023
Author

@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.

15 replies

pyoung-sc Oct 26, 2023
Author

@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 Oct 27, 2023
Author

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 Oct 28, 2023

@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 Oct 28, 2023
Maintainer

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 Oct 29, 2023

@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 · 2023-10-08T08:16:05Z

mboeringa
Oct 8, 2023

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?

5 replies

mboeringa Oct 8, 2023

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 Oct 8, 2023
Author

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 Oct 8, 2023

@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 Oct 8, 2023
Author

I used it directly, just worked!

mboeringa Oct 11, 2023

@pyoung-sc

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

mboeringa · 2023-10-15T08:33:23Z

mboeringa
Oct 15, 2023

@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?

1 reply

pyoung-sc Oct 17, 2023
Author

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 · 2023-10-22T10:00:11Z

mboeringa
Oct 22, 2023

@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.

1 reply

lonvia Oct 23, 2023
Maintainer

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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Daylight Ingest and Slow Deletions #2088

{{title}}

Replies: 5 comments 25 replies

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{editor}}'s edit

{{editor}}'s edit

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Daylight Ingest and Slow Deletions #2088

Replies: 5 comments · 25 replies

pnorman Oct 2, 2023 Maintainer

pyoung-sc Oct 2, 2023 Author

pnorman Oct 3, 2023 Maintainer

pyoung-sc Oct 3, 2023 Author

pyoung-sc Oct 5, 2023 Author

pyoung-sc Oct 26, 2023 Author

pyoung-sc Oct 27, 2023 Author

pnorman Oct 28, 2023 Maintainer

pyoung-sc Oct 8, 2023 Author

pyoung-sc Oct 8, 2023 Author

pyoung-sc Oct 17, 2023 Author

lonvia Oct 23, 2023 Maintainer

Replies: 5 comments 25 replies

pnorman
Oct 2, 2023
Maintainer

pyoung-sc Oct 2, 2023
Author

pnorman Oct 3, 2023
Maintainer

pyoung-sc Oct 3, 2023
Author

pyoung-sc
Oct 5, 2023
Author

pyoung-sc Oct 26, 2023
Author

pyoung-sc Oct 27, 2023
Author

pnorman Oct 28, 2023
Maintainer

pyoung-sc Oct 8, 2023
Author

pyoung-sc Oct 8, 2023
Author

pyoung-sc Oct 17, 2023
Author

lonvia Oct 23, 2023
Maintainer