Comments (4)
@rouen-sk I agree and we are taking steps toward doing something like that. But it is a) a far bigger project than anything suggested here, b) does not work for all use cases, because for some you need that data in the database. So improving what we have is still an issue. Lets stick to that discussion here.
See also https://osm2pgsql.org/contribute/project-ideas.html#non-postgresql-middle
from osm2pgsql.
- From the numbers here it seems to be significantly faster to create the table without the primary key constraint and add that later. But in another test where I also generated output tables, this did not make a difference. The reason is probably that osm2pgsql was busy so often doing other things, that PostgreSQL had the time to update the indexes while the import ran. So it might be possible to get some improvement here in a real situation, but it is not quite as clear-cut as the numbers here suggest.
We should move to creating the UNIQUE index after loading the data. It might not have sped up your test, but I believe it would on some hardware with a different number of threads. Additionally, the resulting index is properly balanced without dead tuples in it.
- It looks like using COPY FREEZE can improve the performance. For this to work we have to create the table in the same transaction as we do the COPY. This is not easily possible with the current code, but it is a change we could do.
This would rule out ever having multiple threads writing to the middle at the same time. Do we want to do that?
from osm2pgsql.
I believe using postgres as node cache was bad choice in the first place, and minor optimizations wont "save us" here.
Postgres has way too much overhead for this purpose, in terms of time, storage space and IOPS. Simple high-performance key-value store library, such as LevelDB, would outperform it significantly.
For example, Imposm is using LevelDB for node cache, and on my machine (cloud VM, so poor IOPS) building node cache takes about 1 hour, and 160 GB of disk space. osm2pgsql slim mode (with 50GB RAM cache) takes several hours, and I honestly don't know how much disk space, because I tried the latest planet yesterday, and it crashed on low disk space, with about 550 GB free at the start.
from osm2pgsql.
I believe using postgres as node cache was bad choice in the first place, and minor optimizations wont "save us" here. Postgres has way too much overhead for this purpose, in terms of time, storage space and IOPS. Simple high-performance key-value store library, such as LevelDB, would outperform it significantly.
For example, Imposm is using LevelDB for node cache, and on my machine (cloud VM, so poor IOPS) building node cache takes about 1 hour, and 160 GB of disk space. osm2pgsql slim mode (with 50GB RAM cache) takes several hours, and I honestly don't know how much disk space, because I tried the latest planet yesterday, and it crashed on low disk space, with about 550 GB free at the start.
You are aware of the --flat-nodes option that osm2pgsql has supported for years to store nodes outside the database in an efficient and fast way?
On my machine (non-cloud), it takes only 34m to import all +12B nodes of Facebook's Daylight distribution of OpenStreetMap using the --flat-nodes
option, and the resulting file should be some 160GB as well. That still leaves the ways and relations stored in PostgreSQL for middle, but I am not sure if imposm doesn't store them as well in PostgreSQL?
550GB free space for a Planet import is becoming very tight. Planet is just to big nowadays, and honestly, with good 4TB NVMe PCIe 3-5.x drives now as low as maybe $200-300, shortage of disk space or IOPS should be no excuse for a failed import.
from osm2pgsql.
Related Issues (20)
- osm2pgsql should not perform analyze by itself HOT 15
- `object:as_multipolygon()` does not take `object.members` into account. HOT 2
- highway=rest_area treatet as line HOT 2
- free(): invalid size Aborted Core dumped HOT 4
- North America import fails HOT 1
- nlohmann-json is missing from the Alpine build dependencies command
- Error in reprocessing of ways in relation, if osmc_symbols-tag of the relation contains the word 'backslash' HOT 6
- Deprecating -i,--tablespace-index? HOT 5
- Out of bounds tile coordinates produced HOT 3
- as_linestring() and likely other geometry transform functions can only be called from the process_way() function, not custom functions. HOT 5
- When Planet osm data import, way showing 0 and not started HOT 6
- osm2pgsql using too much memory for expire lists HOT 14
- Windows CI broken HOT 2
- cmake on ubuntu 22.04-lts requires libboost-dev, libboost-system-dev, libboost-filesystem-dev to be installed HOT 2
- Replication breakage
- Run update in transaction
- osm2pgsql-replication returns code 0 when output says "Error during diff download" HOT 3
- Flex: call `:get_bbox()` on geometry, not on parameter table (object) HOT 6
- master CMakeLists.txt lists version as 1.11.0 HOT 2
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from osm2pgsql.