search

osm2pgsql-dev / osm2pgsql

OpenStreetMap data to PostgreSQL converter
https://osm2pgsql.org
GNU General Public License v2.0
1.48k stars 473 forks source link

Long import of Europe data file #676

Closed Koles500 closed 7 years ago

Koles500 commented 7 years ago

We want to import Europe PBF file to database (from http://download.geofabrik.de). osm2pgsql is running now on our server for ~ 24 hours already processing Europe data.. Processing: Node(1827652k 131.7k/s) Way(11431k 0.20k/s) Relation(0 0.00/s) Command I used:

C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\europe-latest.osm.pbf

Why is it so slow? I am a little worried that this may take very long time and who knows if this will work at the end or end up with some fucking error. We need this work to be done as soon as possible. It is running in slim mode. I had a look into Postgres database in PgAdmin III and I don't see any tables created like osm_nodes nor planet_osm_ways nor planet_osm_rels. Should they be there is place already? I clicked "refresh" on "Tables" so I think they should be there... or not ? Before importing Europe I succeeded with importing a small irish town but this took just a moment. I am really impatient about this, because previous imports failed with Europe file, aldough at that time I used older version of osm2pgsql. Please help me find out if this is working well or how to speed it up. Thanks.

ImreSamu commented 7 years ago

check the https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks

openstreetmap-tiles commented 7 years ago

On Sun, Jan 22, 2017 at 12:22:32PM -0800, Koles500 wrote:

We want is to import Europe PBF file to database (from http://download.geofabrik.de). osm2pgsql is running now on our server for ~ 24 hours already processing Europe data.. Processing: Node(1827652k 131.7k/s) Way(11431k 0.20k/s) Relation(0 0.00/s) Command I used: PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\europe-latest.osm.pbf Why is it so slow? 

several possible reasons that I can quickly think of:

I am a little worried that this may take very long time 

when I was using an HDD, I stopped the import on an HDD after a

week, bought an SSD, and reimported in less than two days in a machine with 16GiB of ram.

and who knows if this will work at the end or end up with some fucking error. 

I understand that you might be frustrated with unexpected

performance, but there's no need to use such strong words.

We need this work to be done as soon as possible. 

I can relate to that too, but cursing is not going to help.

I had a look into Postgres database in PgAdmin III and I don't see any tables created like osm_nodes nor planet_osm_ways nor planet_osm_rels. Should they be there is place already?

not sure about this, but maybe they're being created in a transaction

and until commit time you won't see them in another session. maybe try with --unlogged.

Before importing Europe I succeeded with importing a small irish town but this took just a moment. I am really impatient about this, because previous imports failed with Europe file, aldough at that time I used older version of osm2pgsql. Please help me find out if this working well or how to speed it up. Thanks.

this run took ~17h:

http://www.grulic.org.ar/~mdione/glob/posts/optimizing-mapnik-rendering-with-metatiles/

the .pbf was in a HDD, the flat nodes and DB in the SSD. notice that

the volume of data of a small town in Ireland WRT Europe's is several orders of magnitude, which is the difference between fitting all the processed and processing data in disk cache (RAM) or not.

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

lonvia commented 7 years ago
Koles500 commented 7 years ago

Our hard disk is double WD2000FYYZ-01UL1B2 and 32 GB RAM Current progress: Processing: Node(1827652k 131.7k/s) Way(58646k 0.49k/s) Relation(0 0.00/s) Is it worth restarting this now or is it better to wait?

Don't use the -E parameter, use -m. -E will cause the projection library to be used which is slower than the hand-crafted mercator conversation.

Will this have any impact on the data or just speed of importing? Could you explain what is "hand-crafted mercator conversation" ?

pnorman commented 7 years ago

Our hard disk is double WD2000FYYZ-01UL1B2 and 32 GB RAM

These are 7200 RPM drives, so won't have great IO performance.

Is it worth restarting this now or is it better to wait?

Probably best to restart now. Both flat nodes and cache will give speed gains. In particular, larger cache will will avoid the need for so much IO on the slow drives, and flat nodes tends to work well on HDDs.

Will this have any impact on the data or just speed of importing?

It might result in some rounding differences, but shouldn't impact the data.

Could you explain what is "hand-crafted mercator conversation" ?

It doesn't call GEOS, but instead uses code internal to osm2pgsql.

Koles500 commented 7 years ago

@lonvia , could you please confirm that it is worth to restart it now? I need to be convinced so we won't waste almost 2 days of processing so far. Thank you.

It doesn't call GEOS, but instead uses code internal to osm2pgsql.

I didn't understand you answer here... ?

woodpeck commented 7 years ago

The fastest way to complete this import is buying a 512 GB SSD drive with next-day delivery, putting that into your server tomorrow, and then restarting the import. Re. the "hand-crafted Mercator conversion", OSM data needs to be converted into "sphercial Mercator" coordinates when imported. "-m" uses built-in C/C++ code to make this conversion, whereas "-E 3857" uses an external library (proj4) to do the same, hence it will be slightly slower. But frankly I don't believe it will make a difference in your case since you've got plenty of CPU idly waiting for the disk to complete an operation.

Koles500 commented 7 years ago

Thanks but buying SSD is not an option as it is remote server we access through remote desktop. So the question is - is it worth restarting on current hardware configuration... ?

lonvia commented 7 years ago

@Koles500 If you don't trust the advice of the osm2pgsql maintainers, how about having a look at the benchmarks @ImreSamu posted. Compare the import speeds for ways to decide what to do next. Entirely up to you.

There are too many moving parts involved in an import of OSM data to give you a 100% guaranteed-to-work answer. And as mentioned before, you have a moving part that very few people have experience with, which is Windows. You'll have to tweak your system for your use case but there is tons of good advice in this issue to get you on the right track.

Koles500 commented 7 years ago

Well I didn't know that @woodpeck is osm2pgsql maintainer. Thank you for your responses.

Koles500 commented 7 years ago

Out current progress:

Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(591650 9.77/s)

When Relation is finished, then how much % approximately is done from your experience?

openstreetmap-tiles commented 7 years ago

On Wed, Jan 25, 2017 at 01:33:18PM -0800, Koles500 wrote:

When Relation is finished, then how much % approximately is done from your experience?

We gave you several examples, and told you several times: this

depends a lot on your setup. In my case (Europe import in June 2014, 16GiB of RAM, 256GiB SSD, slim, flat nodes, 2GiB of cache) it was 4h20 of processing, 9h30 of pending ways, ~9h15 for indexes for a total of 17h26. But again, your mileage might vary, a lot. Cheers,

-- Marcos.

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

Koles500 commented 7 years ago

How many "Relation" are there usualy? It seems to me there are quite more than in examples I found online. Can you tell me how many, aproximately, should be there in comparison to "Node" and "Way" ? My current progress:

Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(2936300 15.46/s)

woodpeck commented 7 years ago

Last night there were 3333405 relations in the Europe file.

Koles500 commented 7 years ago

Great, then this part is almost over. And when these 3 are done, then how do you think - how much % of total progress is done aproximately ? AFAIR I found somewhere that this is just about 50%, am I correct ?

woodpeck commented 7 years ago

Latest benchmark on https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks shows a parse time of 36485 seconds and a total time of 152995 seconds. But YMMV.

Koles500 commented 7 years ago

"Relation" is done, it is continuing , but some strange TopologyException-s were throws. Could you please say something more about these exceptions ? Is it still running at all? It is consuming about between 1 to 3% of processor, but I don't see any other "helper-processes" going on.

PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\eur
ope-latest.osm.pbf
osm2pgsql version 0.92.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: osm_point
Setting up table: osm_line
Setting up table: osm_polygon
Setting up table: osm_roads
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, scale=100 cache=800
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels

Reading in file: C:\OSM\Data\europe-latest.osm.pbf
Using PBF parser.
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s)
Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666
499.7699999996
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s)
Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717
07.1900000004
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s)  parse time: 507324s
Node stats: total(1827652739), max(4604487172) in 13878s
Way stats: total(223763746), max(465494091) in 287105s
Relation stats: total(3316871), max(6875737) in 206341s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        165944299 ways are pending

Using 8 helper-processes
openstreetmap-tiles commented 7 years ago

On Fri, Jan 27, 2017 at 10:52:34AM -0800, Koles500 wrote:

"Relation" is done, it is continuing , but some strange exceptions were throws. Could you please say something more about these exceptions ?

`PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\eur ope-latest.osm.pbf osm2pgsql version 0.92.0 (64 bit id space)

Using built-in tag processing pipeline Using projection SRS 3857 (Spherical Mercator) Setting up table: osm_point Setting up table: osm_line Setting up table: osm_polygon Setting up table: osm_roads Allocating memory for sparse node cache Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9 Mid: pgsql, scale=100 cache=800 Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels

Reading in file: C:\OSM\Data\europe-latest.osm.pbf Using PBF parser. Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s) Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666 499.7699999996 Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s) Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717 07.1900000004 Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s) parse time: 507324s Node stats: total(1827652739), max(4604487172) in 13878s Way stats: total(223763746), max(465494091) in 287105s Relation stats: total(3316871), max(6875737) in 206341s Committing transaction for osm_point Committing transaction for osm_line Committing transaction for osm_polygon Committing transaction for osm_roads Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline Setting up table: osm_nodes Setting up table: osm_ways Setting up table: osm_rels Using built-in tag processing pipeline

Going over pending ways... 165944299 ways are pending

Using 8 helper-processes`

I don't see anything wrong, it's just 8 processes printing the same

message.

if just importing the data took almost 6 days (!), I have very bad

news for you: you just passed the mostly sequential part of the import. Like Harmut said, then comes the index processing and that will kill your current performance. Are you sure you want the whole Europe? I would definitely make all efforts to buy the cheapest, even 2nd hand 500 MiB SSD I could if I were really, really pressed to finish soon.

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

Koles500 commented 7 years ago

Did you really read my answer? First I wrote that there are some TopologyException-s - why? Second why would it "kill my current performance" - osm2pgsql process is taking only 1-3% of processor. Also please do not quote such huge code block, it makes scrolling more difficult. Thanks.

Koles500 commented 7 years ago

Anyway we will probably do just british islands after I tweak Postgres and use new options that were suggested above.

openstreetmap-tiles commented 7 years ago

On Fri, Jan 27, 2017 at 11:27:56AM -0800, Koles500 wrote:

First I wrote that there are some TopologyException-s - why? 

no, sorry, I didn't. dunno what they are. it would have helped if you

isolated a bit the lines you were refering to.

Second why would it "kill my current performance" - osm2pgsql process is taking only 1-3% of processor.

performance is not measured in cpu ussage but in units of data

processed in time. if you checked your IO instead, you'll see is not being really saturated either; most of the time your disk will be seeking between sections to read and sections to write. that's why hard disks and filesystems usually publish both benchmarks: secuential and random access (read and write).

Also please do not quote such huge code block, it makes scrolling more difficult. Thanks.

It was *your* block :)

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

openstreetmap-tiles commented 7 years ago

On Fri, Jan 27, 2017 at 11:43:40AM -0800, Koles500 wrote:

Anyway we will probably do just british islands after I tweak Postgres and use new options that were suggested above.

at only 1GiB of data (against the 18.2 for Europe) it should go way,

way faster.

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

Koles500 commented 7 years ago

It looks like the process has stopped, because it is using 0% of processor (and ~3,3 GB RAM). Could you please say something more about these messages : ?

PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\eur
ope-latest.osm.pbf
osm2pgsql version 0.92.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: osm_point
Setting up table: osm_line
Setting up table: osm_polygon
Setting up table: osm_roads
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, scale=100 cache=800
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels

Reading in file: C:\OSM\Data\europe-latest.osm.pbf
Using PBF parser.
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s)
Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666
499.7699999996
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s)
Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717
07.1900000004
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s)  parse time: 507324s
Node stats: total(1827652739), max(4604487172) in 13878s
Way stats: total(223763746), max(465494091) in 287105s
Relation stats: total(3316871), max(6875737) in 206341s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        165944299 ways are pending

Using 8 helper-processes
Finished processing 165944299 ways in 200997 s

165944299 Pending ways took 200997s at a rate of 825.61/s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads

Going over pending relations...
        0 relations are pending

Using 8 helper-processes
Finished processing 0 relations in 0 s

Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Sorting data and creating indexes for osm_point
Sorting data and creating indexes for osm_roads
Sorting data and creating indexes for osm_polygon
Sorting data and creating indexes for osm_line
Stopping table: osm_nodes
Stopped table: osm_nodes in 0s
Stopping table: osm_ways
Building index on table: osm_ways
Stopping table: osm_rels
Building index on table: osm_rels
Copying osm_roads to cluster by geometry finished
Creating geometry index on osm_roads
Creating osm_id index on osm_roads
Creating indexes on osm_roads finished
All indexes on osm_roads created in 13205s
Completed osm_roads
Copying osm_point to cluster by geometry finished
Creating geometry index on osm_point
Creating osm_id index on osm_point
Creating indexes on osm_point finished
All indexes on osm_point created in 30703s
Completed osm_point
Stopped table: osm_rels in 51844s
Copying osm_line to cluster by geometry finished
Creating geometry index on osm_line
Creating osm_id index on osm_line
Creating indexes on osm_line finished
All indexes on osm_line created in 103561s
Completed osm_line`
openstreetmap-tiles commented 7 years ago

On Tue, Jan 31, 2017 at 02:31:04PM -0800, Koles500 wrote:

It looks like the process has stopped, because it is using 0% of processor (and ~3,3 GB RAM).

check the I/O, indexing does not take much CPU but the IO you can

give it. As I said previously, CPU and RAM are not the only resources this tool uses. at least is not using network...

Could you please say something more about these messages : ?

Reading in file: C:\OSM\Data\europe-latest.osm.pbf Using PBF parser. Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s) Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666 499.7699999996 Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s) Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717 07.1900000004 Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s) parse time: 507324s

These exceptions we already talked about, I have no idea, sorry.

Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress Committing transaction for osm_point WARNING: there is no transaction in progress Committing transaction for osm_line WARNING: there is no transaction in progress Committing transaction for osm_polygon WARNING: there is no transaction in progress Committing transaction for osm_roads WARNING: there is no transaction in progress

This is "normal", it just means it did nothing in this step (although

I agree it shouldn't eve try to commit an empty/non existant transaction).

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

lonvia commented 7 years ago

Side location message: see https://github.com/openstreetmap/osm2pgsql/issues/644

Closing this issue because it's steering far from the initial question, which has been established is due to a suboptimal configuration.

Let me add a friendly reminder that is an issue tracker for bugs and feature requests. General usage questions are best directed at help.osm.org or the tile-serving mailing list.

Koles500 commented 7 years ago

@openstreetmap-tiles , how do I check I/O of a process on Windows ? It is moving ... 

All indexes on osm_line created in 103561s
Completed osm_line
Copying osm_polygon to cluster by geometry finished
Creating geometry index on osm_polygon
Creating osm_id index on osm_polygon
Creating indexes on osm_polygon finished
All indexes on osm_polygon created in 324884s
Completed osm_polygon
Koles500 commented 7 years ago

Alright, it has finished and first generated tiles look good ! Below is full log. Took almost 15 days ;0 :)

PS C:\OSM\rendering> osm2pgsql -U postgres -m -d osm -p osm -E 3857 -s -S C:\OSM\osm2pgsql\default.style C:\OSM\Data\eur
ope-latest.osm.pbf
osm2pgsql version 0.92.0 (64 bit id space)

Using built-in tag processing pipeline
Using projection SRS 3857 (Spherical Mercator)
Setting up table: osm_point
Setting up table: osm_line
Setting up table: osm_polygon
Setting up table: osm_roads
Allocating memory for sparse node cache
Node-cache: cache=800MB, maxblocks=12800*65536, allocation method=9
Mid: pgsql, scale=100 cache=800
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels

Reading in file: C:\OSM\Data\europe-latest.osm.pbf
Using PBF parser.
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3148140 15.84/s)
Standard exception processing relation id=6540291: TopologyException: side location conflict at -85245.029999999999 6666
499.7699999996
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3254340 15.98/s)
Standard exception processing relation id=6752256: TopologyException: side location conflict at 538047.93000000005 56717
07.1900000004
Processing: Node(1827652k 131.7k/s) Way(223763k 0.78k/s) Relation(3316870 16.07/s)  parse time: 507324s
Node stats: total(1827652739), max(4604487172) in 13878s
Way stats: total(223763746), max(465494091) in 287105s
Relation stats: total(3316871), max(6875737) in 206341s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline
Setting up table: osm_nodes
Setting up table: osm_ways
Setting up table: osm_rels
Using built-in tag processing pipeline

Going over pending ways...
        165944299 ways are pending

Using 8 helper-processes
Finished processing 165944299 ways in 200997 s

165944299 Pending ways took 200997s at a rate of 825.61/s
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads
Committing transaction for osm_point
Committing transaction for osm_line
Committing transaction for osm_polygon
Committing transaction for osm_roads

Going over pending relations...
        0 relations are pending

Using 8 helper-processes
Finished processing 0 relations in 0 s

Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Committing transaction for osm_point
WARNING:  there is no transaction in progress
Committing transaction for osm_line
WARNING:  there is no transaction in progress
Committing transaction for osm_polygon
WARNING:  there is no transaction in progress
Committing transaction for osm_roads
WARNING:  there is no transaction in progress
Sorting data and creating indexes for osm_point
Sorting data and creating indexes for osm_roads
Sorting data and creating indexes for osm_polygon
Sorting data and creating indexes for osm_line
Stopping table: osm_nodes
Stopped table: osm_nodes in 0s
Stopping table: osm_ways
Building index on table: osm_ways
Stopping table: osm_rels
Building index on table: osm_rels
Copying osm_roads to cluster by geometry finished
Creating geometry index on osm_roads
Creating osm_id index on osm_roads
Creating indexes on osm_roads finished
All indexes on osm_roads created in 13205s
Completed osm_roads
Copying osm_point to cluster by geometry finished
Creating geometry index on osm_point
Creating osm_id index on osm_point
Creating indexes on osm_point finished
All indexes on osm_point created in 30703s
Completed osm_point
Stopped table: osm_rels in 51844s
Copying osm_line to cluster by geometry finished
Creating geometry index on osm_line
Creating osm_id index on osm_line
Creating indexes on osm_line finished
All indexes on osm_line created in 103561s
Completed osm_line
Copying osm_polygon to cluster by geometry finished
Creating geometry index on osm_polygon
Creating osm_id index on osm_polygon
Creating indexes on osm_polygon finished
All indexes on osm_polygon created in 324884s
Completed osm_polygon
Stopped table: osm_ways in 562079s
node cache: stored: 52428801(2.87%), storage efficiency: 50.00% (dense blocks: 0, sparse nodes: 52428801), hit rate: -6.
78%

Osm2pgsql took 1270744s overall
openstreetmap-tiles commented 7 years ago

On Mon, Feb 06, 2017 at 10:06:45AM -0800, Koles500 wrote:

Alright, it has finished and first generated tiles look good ! Below is full log. Took almost 15 days ;0 :)

could you add it to this wiki page:

https://wiki.openstreetmap.org/wiki/Osm2pgsql/benchmarks

with the following info:

-- (Not so) Random fortune: Terrorism isn't a crime against people or property. It's a crime against our minds, using the death of innocents and destruction of property to make us fearful. -- Bruce Schneier

Koles500 commented 7 years ago

I tried to edit that page, but I got this error: "Something went wrong" "Unknown" Tried 3 times. Maybe you can add? Here is my text:

Europe import on Windows Server 2012 HDD Whole process took only ~ 15 days :-) Date of import: 21.01.2017 (Europe pbf file downloaded few days before from main page of http://download.geofabrik.de/) CPU: Xenon E3-1246 v3 3.5 GHz (8 cores) Memory: 32 GB Hard drive: double HDD (!) WDC WD2000FYYZ-01UL1B2 (WD 2TB 7200 RPM 64MB Cache SATA 6.0Gb/s) OS: Windows Server 2012 64-bit osm2pgsql : Newest version downloaded from https://ci.appveyor.com/api/buildjobs/iisabxffssilv7gl/artifacts/osm2pgsql_Release.zip

AND below my log from console which I posted above in previous post.