Replies: 5 comments 14 replies
-
|
Nearly 9 CPU hours in
|
Beta Was this translation helpful? Give feedback.
0 replies
-
|
The z13 building merge (and other polygon merges) are very expensive, 14.5 CPU hours are spent in When called from
|
Beta Was this translation helpful? Give feedback.
0 replies
-
|
Mbtiles writing doesn't take a very long time (12 minutes), but it can't be parallelized so turns into a single-threaded bottleneck that we haven't been able to improve. I thought that #219 or #245 might improve things, but the time ends up remaining unchanged. Does anyone else have any ideas to reduce the sqlite write time? Here are the connection settings we use currently: And the table schemas we use for compact mode: planetiler/planet-logs/v0.5.0-planet-c6gd-128gb.txt Lines 675 to 699 in 74b7474 |
Beta Was this translation helpful? Give feedback.
8 replies
-
|
Yeah I assume we're bumping up against the limits of sqlite here... The only further ideas I had would break compatibility with the mbtiles spec and require readers to make changes, for example using a simple numeric tile ID instead of z/x/y (~50% speedup) or have N mbtiles writer threads that each write their output to a separate file and clients would need to know which file to route requests to. |
Beta Was this translation helpful? Give feedback.
-
|
I did some prototyping on how a new mbtiles with a single 32bit index may look like -- https://gist.github.com/nyurik/73b1c2bafbb107c5685ab1d8ac883ce8 -- it uses computed (virtual) fields to simplify debugging (relatively new sqlite feature), but this is clearly non-compatible with the existing mbtiles without an additional index (see code). |
Beta Was this translation helpful? Give feedback.
-
|
Cool, I tried something similar with generated columns while keeping it compatible: #219 (comment) . The idea was that write performance would improve but despite the comment, after benchmarking it, it was slower. But it obviously was slower because of the generated columns that had to be indexed. So your non-compatible version should probably be faster. I think there are many ways the output could be improved - iff someone's use-case/application would allow it. Or you would even want to output it to some other DB (postgres, ...) for some reason. So I wonder if the output generator should be swappable using some plugin mechanism like e.g. ServiceLoader. |
Beta Was this translation helpful? Give feedback.
-
|
The single numeric tile ID index does speed up inserts by about 50%. Too bad there's no way we can tell sqlite "to resolve this z,x,y coordinate, fetch this tile ID" so we could still adhere to the mbtiles spec without materializing the z/x/y index...
I was thinking about this too, right now you specify output location with |
Beta Was this translation helpful? Give feedback.
-
|
What about a --db-mode? with options being "mbtiles", "mbtiles-compact", "sqlite-comapct" (<- single numeric tile ID index). --db-mode would be relevant for any DB output. |
Beta Was this translation helpful? Give feedback.
-
|
I have to say, performance is already miles ahead of any previous solution that I'm aware of, so don't sweat it too much.... Still, more performance is always nice. Out of interest, I looked up pricing for the EC2 instance you use, and you've gotten to the point where exporting the complete MBTiles out of Amazon costs far more than the compute time needed to generate the tiles in the first place! |
Beta Was this translation helpful? Give feedback.
1 reply
-
|
Thanks! I'm planning to take a break from the performance improvements for a bit so wanted to leave this info to collect ideas or if others wanted to pursue it further in the short term. |
Beta Was this translation helpful? Give feedback.
-
|
Another approach if people wanted to get the generation time down by spending more money would be to add This doesn't seem like a great option since 4x-ing the cost to only gets you from 38 to 16 minutes, and at the end you're left with 4 mbtiles files that need to be merged or have a tile server that knows which file to route each request to but maybe that's a tradeoff someone wants to make 🤷♂️ Also this would only be beneficial after implementing #119 so that each time we run, we use up-to-the-minute OSM input. |
Beta Was this translation helpful? Give feedback.
5 replies
-
|
Could sharding help reduce memory requirements instead? It would be neat to be able to generate the planet on my old linux box (with only 8GB RAM), even if I had to leave it overnight. |
Beta Was this translation helpful? Give feedback.
-
|
Using It's a bit hard to shard the geographic data since you don't know what tiles features will end up in until the very end and if you try to guess early on, you might miss data that shows up at low zooms, or in relations that span large geographic areas... |
Beta Was this translation helpful? Give feedback.
-
|
@smellman also has run successfully on machine with 16GB ram + 512gb swap with -Xmx20g + storage=mmap in ~6 hours to generate the data for https://tile.openstreetmap.jp/ |
Beta Was this translation helpful? Give feedback.
-
|
@msbarry, Is there any specific configuration for allocating swap size, beyond -Xmx20g + storage=mmap? What are the vcpu requirements? Would a t2.2xlarge with 8 vCPU, 32GiB of memory and 512GB disk suffice? With these performance improvements - ~6 hours on a 8vCPU/32GB - I am not sure why one would pay 6x-8x the price to do it in half the time. Updating the Readme with a suggestion for optimum ec2 instance and params might be helpful. |
Beta Was this translation helpful? Give feedback.
-
|
I don't think swap would help unless you have less than 32gb of ram. That setup should work just fine. 2 cores is probably the bare minimum but the more you have the faster it will go. Maybe we should start a discussion for people to post their best runs - either cheapest or fastest and what settings they used? |
Beta Was this translation helpful? Give feedback.
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
-
I addressed some obvious single-threaded bottlenecks in the 0.5.0 release and am planning to take a break from performance improvements for a bit. Here's a summary of where the remaining time goes when processing the planet, in case others had further ideas they wanted to try?
These performance charts show generating the planet on a c6gd.16xlarge EC2 instance with 64 cpus and 128GB
ram with z13 building merge (53m, left) and without (38m, right). The details below include the z13 building merge.
VisualVM Profile
See this gist or load the profile for a detailed breakdown of where the CPU time goes in each thread, but here's a summary of the hot spots:
com.onthegomap.planetiler.render.FeatureRenderer.renderLineOrPolygon: 15 hourscom.onthegomap.planetiler.geo.GeoUtils.snapAndFixPolygon: 8 hourscom.onthegomap.planetiler.render.TiledGeometry.sliceIntoTiles: 2.65 hourscom.onthegomap.planetiler.render.TiledGeometry.sliceY: 1 hourcom.onthegomap.planetiler.render.TiledGeometry.sliceX: 0.9 hourcom.onthegomap.planetiler.render.TiledGeometry.addShapeToResults: 0.65 hourscom.onthegomap.planetiler.collection.FeatureGroup.encodeValue: 1.37 hourscom.onthegomap.planetiler.geo.DouglasPeuckerSimplifier.simplify: 1 hourcom.onthegomap.planetiler.expression.MultiExpression$KeyIndex.getMatchesWithTriggers: 1 hourcom.onthegomap.planetiler.VectorTile.encodeGeometry: 0.7 hourscom.onthegomap.planetiler.reader.osm.PbfDecoder.decode: 0.7 hourscom.onthegomap.planetiler.FeatureMerge.mergeNearbyPolygons: 14 hours, 13 of those fromBuilding.postProcesscom.onthegomap.planetiler.util.Gzip.gzip: 1.4 hours gzipping tilescom.onthegomap.planetiler.FeatureMerge.mergeLineStrings: 1.2 hours, 0.85 hours fromTransportation.postProcesscom.onthegomap.planetiler.geo.GeoUtils.snapAndFixPolygon: 0.85 hourscom.onthegomap.planetiler.VectorTile.encode: 0.75 hourscom.onthegomap.planetiler.collection.FeatureGroup$TileFeatures.decodeVectorTileFeature: 0.71 hourscom.onthegomap.planetiler.mbtiles.Mbtiles$BatchedTableWriterBase.flush: 11.9 minutes, 11.5 of that inorg.sqlite.jdbc3.JDBC3PreparedStatement.executeThoughts?
Beta Was this translation helpful? Give feedback.
All reactions