leijurv created an issue (osm2pgsql-dev/osm2pgsql#2505)
Hello,
Thanks for
[pointing](https://github.com/osm2pgsql-dev/osm2pgsql/issues/2504#issuecomment-5855868028)
me to [Project CODA](https://osm2pgsql.org/project-coda/). It made me curious
to what extent the hybrid disk sort-join could be extended to also create a
compact middle format on disk. I have a prototype to share, along the lines of
your CODA requirements page, in the hope that you might consider it useful. If
it's helpful / interesting then great but if it doesn't fit what you plan to do
then that's also fine!
Some stats about the prototype:
- The planet middle takes 81.3GB, as compared to 95.0 for the PBF, or ~400 for
the current middle if you include the flat nodes.bin)
- To build this middle from the planet takes 15 minutes. Peak RAM usage is
11.5GB. Peak disk usage is 86GB. (That peak is less than the PBF because it
streamed it directly from S3 while converting to the middle (optional feature))
- To apply osmchanges to it, an hourly diff takes 8.5s median, and a minutely
diff takes 0.4s median
- Measurements taken on a `m9g.2xlarge`. 8 CPU cores, 32GB RAM, disk with 16k
IOPS, 500MB/sec. The 15 minute time is with that IOPS; with the baseline gp3
(3k IOPS) it takes 31 minutes.
The middle has complete information so it's used to fill output tables
afterward. (the "Populating an osm2pgsql Database from Middle" extra on the
[requirements page](https://osm2pgsql.org/project-coda/requirements.html)).
| From the middle (no PBF) to OSM Carto flex style | time |
|---|---:|
| nodes | 4m 17s |
| ways | 26m 26s |
| relations | 8m 19s |
| **Table load total** | **39m 2s** |
| + creating the indexes (skipped CLUSTER) | 1h 01m |
| **Total**<br/>(if middle is already built, and not including CLUSTER) | **1h
40m** |
| **Grand total** <br/>(including middle, but excluding CLUSTER) | **1h 55m** |
Disk use: Carto tables 516 GB, plus 81 GB for the middle, 602 GB final size.
Peak was about 60gb higher while creating indexes. With clustering, which adds
many more hours, the peak would be ~220gb higher. So, total time to go from
nothing to building the middle then getting a working Carto (which is the
default for `osm2pgsql -O flex -S ... --middle-coda=DIR planet.osm.pbf` for
efficiency), is **under 2 hours** on this machine.
**Brief outline**
- The file format is LMDB. There are 8 tables in the file: `ways`, `nodes`,
`rels` as you'd expect, then the indexes `n2w` `n2r` `w2r` `r2r` (for applying
osmchanges), and a tiny one `meta` (similar to the current properties table).
- All the keys are integers, and they are all `osm_id >> shift`. For example,
`ways` is keyed by `way_id >> 10`, meaning (up to) 1024 ways are stored
together.
- Only nodes that have tags, nodes in relations, and nodes not attached to any
way are stored in `nodes`. That's <3% of all nodes. The rest are stored in
`ways`. This allows for significant compression: the x,y are delta encoded
sequentially along the way, then some bit tricks, then zstd is applied,
resulting in nodes taking 3.4 bytes on average.
- The child-ID-to-parent-ID indexes are stored separately, run length encoded
then zstd. The only big one is `n2w` (node-to-way), ~10gb, which is 0.86 bytes
per entry.
- Tags use the
[`osm2pgsql-middle-experiments`](https://github.com/osm2pgsql-dev/osm2pgsql-middle-experiments)
keys and tag lists first to abbreviate/tokenize the most common tags / keys,
then zstd with a trained dictionary runs on that result. Way tags average 3.5
bytes per way.
- Osmchanges are applied using the various indexes, not really any additional
tricks. Edited blocks are rewritten in a LMDB transaction.
<details>
<summary><b>Table sizes (click to expand)</b></summary>
| table | key | count | useful size | size on disk
after LMDB overhead |
|---|---|---|---|---|
| ways | `way_id >> 10` | 1.49M | 59.4 GB |
62.4 GB |
| nodes | `node_id >> 12` | 3.18M | 4.1 GB | 5.4 GB |
| rels | `rel_id >> 6` | 298k | 0.79 GB |
1.1 GB |
| n2w | `node_id >> 14` | 858k | 10.6 GB |
12.3 GB |
| n2r | `node_id >> 16` | 202k | 0.06 GB |
0.08 GB |
| w2r | `way_id >> 16` | 24k | 0.27 GB |
0.32 GB |
| r2r | `rel_id >> 16` | 328 | 0.002 GB |
0.003 GB |
Within ways table:
- 41GB for node locations
- 13GB for node IDs
- 4GB for way tags
- about 1GB for other overhead like headers, counts, flags, etc
Within nodes table:
- 2.4GB for tags
- 1.5GB for locations
- 0.3GB for IDs, headers, etc
</details>
<details>
<summary><b>LMDB overhead over time (click to expand)</b></summary>
LMDB's free list gets fragmented over time, but it's not so bad. Starting with
the June 29 planet and replaying hourly diffs, as you can see it plateaus at a
few percent of overhead. (not a big deal even if it got bigger, because a
reimport is fast, or you could vacuum it by writing to a new file with free
pages compacted away)
<img width="1800" height="680" alt="Image"
src="https://github.com/user-attachments/assets/42d4380e-c7b0-4113-99f3-34d618a939dc";
/>
<img width="1800" height="800" alt="Image"
src="https://github.com/user-attachments/assets/82bd91e8-859f-4ad4-9e74-6426299d64a6";
/>
</details>
Prototype code:
https://github.com/leijurv/osm2pgsql/commit/8441bd96c15f0ba40cde78083ba89616c82078cc.
It adds `--middle-coda=DIR` as a third middle, in `src/middle-coda.cpp`,
`coda-format.cpp`, `coda-store.cpp`, `coda-build.cpp` and `coda-source.cpp`
(about 5k lines of code), plus LMDB in `contrib/`.
--
Reply to this email directly or view it on GitHub:
https://github.com/osm2pgsql-dev/osm2pgsql/issues/2505
You are receiving this because you are subscribed to this thread.
Message ID: <osm2pgsql-dev/osm2pgsql/issues/[email protected]>
_______________________________________________
Tile-serving mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/tile-serving
