Re: [OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
> Sounds similar to this issue. How is your RAM usage? Perhaps the new -C0 flag > will help, I dunno. > https://github.com/openstreetmap/osm2pgsql/issues/946 > j I had memory problems first after upgrading to PostgreSQL 11, but I disabled JIT on PostgreSQL 11 and then it worked fine again - not sure if it was the main culprit. I only have a 16GB machine, but during append I don't see any memory problems. SWAP is empty, and I'm using 2GB memory at the moment (14% memory consumption) - so I don't think it is a memory problem. Nevertheless I will try the -C0 flag on the next round/week > if you are using the flatnodes option (which you should for a world-wide > import) then the node import step will mainly hit the flatnodes file and only > have relatively limited PostgreSQL interaction. It therefore sounds unlikely > that the PostgreSQL upgrade could be at fault. Yes, I use flatnodes (also on SSD) - the strange thing is: I don't see many IO during node processing - I have near constant resource consumption (CPU/Memory/IOwait...): Overall CPU: 25% (12% user, 12% system), system seems a bit high.. Overall Memory: 14% IOwait: 0.2% or most of the time less local network: constant 17.8Mb in and 17.8Mb out. It looks like those are postgres UDP packets sent/received (according to lsof), googling it seems those are from the stats collector. Perhaps this is my problem - I will disable stats on the next run. I then have mostly two postgres processes consuming CPU 19% CPU on "main: gis planet [local] SELECT" the queries on the DB 13% CPU on "/usr/lib/postgresql/11/bin/postgres" the binary itself 1-4% CPU on osm2pgsql Not sure, where my bottleneck is. Neither CPU, Memory or IO seem saturated... and initial import was processing fast/as-expected... But according to your feedback, no PostgreSQL 11 problems are "known", this is good to know. So it probably has something todo with my setup :-) kind regards and many thanks! Michael ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
[OSM-dev] PostgreSQL 11 - osm2pgsql performance problems during --append?
Hi I'm running my osm2pgsql setup for a couple of years now by processing the whole planet and doing "--append" updates on a weekly basis. I have a Debian server and 1.5 TB SSD storage for the planet and run with flat-nodes configuration. I keep my system up-to-date and recently switched to PostgreSQL 11 and added a SSD disk to my SSD-RAID. Since this change I see a large drop on nodes processing by approx a factor of 50x (initial import is doing fine - just when I apply a diff with --append, I now see a processing rate of about 0.1k/s, it was in the 5k/s before), way and relation processing is still ok, faster even! Before: Processing: Node(20644k 4.7k/s) Way(2943k 0.59k/s) Relation(65680 29.95/s) After: Processing: Node(30299k 0.1k/s) Way(4609k 1.15k/s) Relation(81710 40.57/s) So now the whole update process takes longer as if I drop the complete planet database and start a fresh import, which is sub-optimal :-) It probably boils down to this: - Either my new SSD has some performance problem? - Or PostgreSQL 11 has some regressions? - osm2pgsql does not work well with PostgreSQL 11? I also don't see any I/O bottleneck (iowait is very low <1%), CPU overall is steady at 25% (12% user, 12% system) I also updated osm2pgsql to the newest 1.0 release, but it did not change anything. Now, before I downgrade the DB or replace the disk: Does anybody else run PostgreSQL 11 and sees similar problems? kind regards Michael ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Osmosis RuntimeException: Pipeline entities are not sorted
Hi Brett I tried with --sort-change, but had the same result. It looks like I need to re-import the full planet again and then go on with daily updates - I just hope the error does not get propagated further along in the daily-diffs. many thanks for your investigation! Michael > On 12.05.2016, at 12:33, Brett Henderson <br...@bretth.com> wrote: > > > Actually, on closer inspection of the error it looks like the same node > (257882) is appearing twice with the same version. I doubt if sorting will > help. I don't think Osmosis has a way to fix this issue. The simplify > change task could probably cope with it, but the input validation may be too > strict. It may be necessary to fix the file manually ... > > On Thu, 12 May 2016 at 20:30 Brett Henderson <br...@bretth.com > <mailto:br...@bretth.com>> wrote: > Okay, so the --simplify-change task is failing because it requires sorted > input. > > Can you try this? It will sort the changes before attempting to simplify > them. > osmosis —read-replication-interval workingDirectory=/import/data/diffs/ > --sort-change --simplify-change --write-xml-change > /import/data/diffs/data/changes.osc.gz > > > On Thu, 12 May 2016 at 19:43 Michael Kussmaul <kussmaul.l...@nix.ch > <mailto:kussmaul.l...@nix.ch>> wrote: > Hi Brett > > Sure, here is my stack trace: > > May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run > INFO: Osmosis Version 0.44.1 > May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run > INFO: Preparing pipeline. > May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run > INFO: Launching pipeline execution. > May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run > INFO: Pipeline executing, waiting for completion. > May 11, 2016 1:48:18 PM > org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager > waitForCompletion > SEVERE: Thread for task 1-read-replication-interval failed > org.openstreetmap.osmosis.core.OsmosisRuntimeException: Pipeline entities are > not sorted, previous entity type=Node, id=257882, version=5 current entity > type=Node, id=257882, version=5. > at > org.openstreetmap.osmosis.core.sort.v0_6.SortedHistoryChangePipeValidator.process(SortedHistoryChangePipeValidator.java:59) > at > org.openstreetmap.osmosis.set.v0_6.ChangeSimplifier.process(ChangeSimplifier.java:50) > at > org.openstreetmap.osmosis.core.sort.v0_6.ChangeSorter.complete(ChangeSorter.java:73) > at > org.openstreetmap.osmosis.replication.v0_6.ReplicationDownloader.processComplete(ReplicationDownloader.java:116) > at > org.openstreetmap.osmosis.replication.v0_6.BaseReplicationDownloader.runImpl(BaseReplicationDownloader.java:313) > at > org.openstreetmap.osmosis.replication.v0_6.BaseReplicationDownloader.run(BaseReplicationDownloader.java:383) > at java.lang.Thread.run(Thread.java:744) > > May 11, 2016 1:48:18 PM org.openstreetmap.osmosis.core.Osmosis main > SEVERE: Execution aborted. > org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks > failed. > at > org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146) > at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92) > at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > at java.lang.reflect.Method.invoke(Method.java:606) > at > org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330) > at > org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238) > at > org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) > at > org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) > at org.codehaus.classworlds.Launcher.main(Launcher.java:47) > > > >> On 12.05.2016, at 05:15, Brett Henderson <br...@bretth.com >> <mailto:br...@bretth.com>> wrote: >> >> Can you provide more of the stack trace? It would be helpful to see exactly >> which task in the pipeline is failing. It *may* be possible to workaround >> it it with an intermediate sort task. >> >> On Wed, 11 May 2016 at 21:43 Michael Kussmaul <kussmaul.l...@nix.ch >> <mailto:kussmaul.l...@nix.ch>> wrote: >> Yes, I see the same problem, I’m using osmosis to keep my local planet file >> up-to-da
Re: [OSM-dev] Osmosis RuntimeException: Pipeline entities are not sorted
Hi Brett Sure, here is my stack trace: May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Osmosis Version 0.44.1 May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Preparing pipeline. May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Launching pipeline execution. May 11, 2016 1:34:36 PM org.openstreetmap.osmosis.core.Osmosis run INFO: Pipeline executing, waiting for completion. May 11, 2016 1:48:18 PM org.openstreetmap.osmosis.core.pipeline.common.ActiveTaskManager waitForCompletion SEVERE: Thread for task 1-read-replication-interval failed org.openstreetmap.osmosis.core.OsmosisRuntimeException: Pipeline entities are not sorted, previous entity type=Node, id=257882, version=5 current entity type=Node, id=257882, version=5. at org.openstreetmap.osmosis.core.sort.v0_6.SortedHistoryChangePipeValidator.process(SortedHistoryChangePipeValidator.java:59) at org.openstreetmap.osmosis.set.v0_6.ChangeSimplifier.process(ChangeSimplifier.java:50) at org.openstreetmap.osmosis.core.sort.v0_6.ChangeSorter.complete(ChangeSorter.java:73) at org.openstreetmap.osmosis.replication.v0_6.ReplicationDownloader.processComplete(ReplicationDownloader.java:116) at org.openstreetmap.osmosis.replication.v0_6.BaseReplicationDownloader.runImpl(BaseReplicationDownloader.java:313) at org.openstreetmap.osmosis.replication.v0_6.BaseReplicationDownloader.run(BaseReplicationDownloader.java:383) at java.lang.Thread.run(Thread.java:744) May 11, 2016 1:48:18 PM org.openstreetmap.osmosis.core.Osmosis main SEVERE: Execution aborted. org.openstreetmap.osmosis.core.OsmosisRuntimeException: One or more tasks failed. at org.openstreetmap.osmosis.core.pipeline.common.Pipeline.waitForCompletion(Pipeline.java:146) at org.openstreetmap.osmosis.core.Osmosis.run(Osmosis.java:92) at org.openstreetmap.osmosis.core.Osmosis.main(Osmosis.java:37) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.codehaus.plexus.classworlds.launcher.Launcher.launchStandard(Launcher.java:330) at org.codehaus.plexus.classworlds.launcher.Launcher.launch(Launcher.java:238) at org.codehaus.plexus.classworlds.launcher.Launcher.mainWithExitCode(Launcher.java:415) at org.codehaus.plexus.classworlds.launcher.Launcher.main(Launcher.java:356) at org.codehaus.classworlds.Launcher.main(Launcher.java:47) > On 12.05.2016, at 05:15, Brett Henderson <br...@bretth.com> wrote: > > Can you provide more of the stack trace? It would be helpful to see exactly > which task in the pipeline is failing. It *may* be possible to workaround it > it with an intermediate sort task. > > On Wed, 11 May 2016 at 21:43 Michael Kussmaul <kussmaul.l...@nix.ch > <mailto:kussmaul.l...@nix.ch>> wrote: > Yes, I see the same problem, I’m using osmosis to keep my local planet file > up-to-date and now see the same error as Jan Michel: > > org.openstreetmap.osmosis.core.OsmosisRuntimeException: Pipeline entities are > not sorted, previous entity type=Node, id=257882, version=5 current entity > type=Node, id=257882, version=5. > > Again node 257882. > > My osmosis command: > osmosis —read-replication-interval workingDirectory=/import/data/diffs/ > --simplify-change --write-xml-change /import/data/diffs/data/changes.osc.gz > > kind regards > Michael > ___ > dev mailing list > dev@openstreetmap.org <mailto:dev@openstreetmap.org> > https://lists.openstreetmap.org/listinfo/dev > <https://lists.openstreetmap.org/listinfo/dev> ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Osmosis RuntimeException: Pipeline entities are not sorted
Yes, I see the same problem, I’m using osmosis to keep my local planet file up-to-date and now see the same error as Jan Michel: org.openstreetmap.osmosis.core.OsmosisRuntimeException: Pipeline entities are not sorted, previous entity type=Node, id=257882, version=5 current entity type=Node, id=257882, version=5. Again node 257882. My osmosis command: osmosis —read-replication-interval workingDirectory=/import/data/diffs/ --simplify-change --write-xml-change /import/data/diffs/data/changes.osc.gz kind regards Michael ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] dev Digest, Vol 105, Issue 11
I have a very similar setup: A cheap machine ($600 - 16GB RAM) and some SSDs - although I also started with a single 200GB SSD. I have this machine in production for a year now and produce weekly updates to my data - it's not super fast, but a weekly diff import takes 1-2 h. Full import still takes approx. 48 h the last time I tried. 1.) Why not buy a second SSD? You could then either strip those two SSD together on operating-system level (e.g. RAID-0 or mdadm stripe) or just use several table-spaces on those different disks. 2.) Table-spaces: PostgreSQL and osm2pgsql supports table-spaces - this basically means you can decide on table granularity which tables you want to store on normal disk or SSD. You can even migrate those tables later from one tablespace to another. In osm2pgsql take a look at the --tablespace- parameters. Additionally use the flat-node mode, as this uses less disk-space. My osm2pgsql command for initial import looks like this: ./osm2pgsql -r pbf --create --latlong --database planet --username gis --prefix planet --slim --style /home/map/import/tools/hstore.style --cache 12000 --number-processes 4 --tablespace-main-index vertex --tablespace-main-data vertex --tablespace-slim-index samsung --tablespace-slim-data samsung --hstore --flat-nodes /media/vertex/import_index/node.cache planet.osm.pbf (I have two SSDs and created two tablespaces: vertex and samsung - to reflect my SSDs) - of course if you place more data on normal disk, the import-time will suffer. 3.) Perhaps you can take a look at http://imposm.org - an alternative to osm2pqsql - have not tried it yet though... Michael Hello, on my day-job I recently had to solve the Problem of setting up a Postgis database contaning a full-planet extract using one of those cheap 180GB SSD on a semi-potent machine (only 8GB of RAM). I first tried to use osm2pgsql for this purpose which is almost impossible for a couple of reasons: * I would have needed more than 180GB of disk space (at least during import) * The import would take a _very_ long time (several days rather than hours) So I had to look for a backup strategy! I found one which proved to be that good, that I would like to discuss it here as an alternative way for rendering-database setup. The first thing I discovered is the fact, that we currently use roughly twice the disk-space for intermediate tables (or file in case of flatnode) than for the processed data itself. Here is how this looks like on tile.openstreetmap.de: relation | total_size ---+ public.planet_osm_line| 61 GB public.planet_osm_polygon | 59 GB public.planet_osm_point | 10 GB public.planet_osm_roads | 8 GB public.planet_osm_ways| 174 GB public.planet_osm_rels| 4 GB + flatnode.dat 20 GB processed data: 61GB+59GB+10GB+8GB=138GB intermediate data: 20GB+174GB+4GB=202GB The most annoying part is the 174GB planet_osm_ways table. So what I did to solve my problem was using pg_dump for the processed data tables only and setting up my target database using pg_restore. Advantages: * Very fast data import even on machines where import using osm2pgsql would be practically impossible * Decent size of database dump (32GB in case of a --hstore-match-only database which is about the same size as a planet dump) Disadvantages: * Currently no update strategy available * Will need a master osm2pgsql database * Will inherit table scheme from master database Conclusion: IMO the disadvantages can be resolved or at least mitigated in the following way: * we generate a downloadable dump of the database on tile.openstreetmap.de (e.g. weekly) * osm2pgsql needs to be patched to output the changes to the processed data tables as SQL commands which can then be used to replicated the slave databases * We already use --hstore-match-only database format. So flexibility in table-layout is not that much of a concern as views to hstore column can be used for rendering instead of tables. I would like to hear your comments to this proposial. Regards Sven -- Threading is a performance hack. (The Art of Unix Programming by Eric S. Raymond) /me is giggls@ircnet, http://sven.gegg.us/ on the Web -- Thinking of using NT for your critical apps? Isn't there enough suffering in the world? (Advertisement of Sun Microsystems in Wall Street Journal) /me is giggls@ircnet, http://sven.gegg.us/ on the Web ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev ___ dev mailing list dev@openstreetmap.org https://lists.openstreetmap.org/listinfo/dev
[OSM-dev] osm2pgsql way_area is 0 for small areas
Hi I'm using osm2pgsql in hstore+latlon configuration and experienced a problem with the calculated way_area. For small areas (parks, buildings) the value stored in the database is always 0. Looking at the code output_pgsql.c (line 931, 1162, 1215), I saw we simply use %f to format the calculated area - obviously this results in a rounded 0.00 for very small areas. I was wondering if there would be any side-effects in OSM toolchains if we change this to an %g formatter? This would enable exponential notation for very small numbers (e.g. 1e-10) - do you think this will break existing software? I changed osm2pgsql localy to use the %g formatter for my usecase - but I would be grateful if such a change could be incorporated into mainstream, so I do not need to patch the file for every update. What do you think? My other question would be: How could I disable way_area calculation in a hstore configuration? I understand I can add way_area in a normal default.style config file, but when using a hstore.style, I was not capable of disabling the calculation of the way_area. many thanks and kind regards Michael ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] osm2pgsql: going over pending relations number larger than relations?
Hi I have imported the full planet last November and now keep the db up-to-date weekly (based on daily diffs I fetch every week with osmosis). It worked nicely so far, but in the current import I see something strange: Reading in file: /Users/map/import/data/changes.osc.gz Processing: Node(21177k) Way(2326k) Relation(34k) parse time: 283207s Node stats: total(21177902), max(1145198079) Way stats: total(2326039), max(98985030) Relation stats: total(34113), max(1418290) Going over pending ways processing way (1544k) Going over pending relations processing relation (40k) So from my understanding, the diff contained 34K relations, but currently it processes relation 40K in the Going over pending relations step. Is this possible/normal? I guess if I now stop the import I have to start over with a full planet? kind regards Michael PS: I use PostgreSQL 8.4 and osm2pgsql from SVN-revision 24244 (Nov 7, 2010) ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] osm2pgsql diff mechanics
Hi, I plan to do osm2pgsql diffs on my PostgreSQL setup. For my use-case it is important that all geometry data is valid, so after the initial import I do a cleaning of all non-valid geometry data (self-intersection, ...). Now my question: If I cleaned a geometry (e.g. forest) and a diff specifies a change in this geometry, what will happen? 1.) Will the geometry completely be replaced by the new geometry as specified in the diff. 2.) Will the geometry be messed up, because the diff expects a different original geometry and now just applies a patch. 3.) Diff will fail, because it expects something different in the original geometry. 4.) ... For my use-case 1.) would be the best, because I could then do a re-cleaning after each diff-import. Many thanks Michael ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] compile error on osm2pgsql for Macosx + Patch
Hi, Not sure how to report this - when compiling the current SVN-version of osm2pgsql for macosx, I receive a compiler error (malloc.h: No such file or directory). It seems including malloc.h is anyway unnecessary, as stdlib.h already takes care to get the correct malloc implementation - so after removing the line #include malloc.h in the file middle-pgsql.c, everything went fine. thanks Michael ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
Re: [OSM-dev] Reference .osm for render testing?
Great - I was searching for something like this! many thanks Michael On 17.11.2009, at 12:05, Patrick Petschge Kilian wrote: Hi, Is there an artificial reference .osm available, which shows different rendering scenarios? Especially for those building a rendering engine, it could be useful to have a simple OSM, which just contains all OSM map-feature, special cases (like multipolygons with holes, streets above another, ...) and a reference rendering (e.g. Mapnik, Osmarender) - as otherwise we have to hunt for special regions on the whole world.osm. Osmarender has some test data which come close. You can look at them at: http://trac.openstreetmap.org/browser/applications/rendering/osmarender/testdata The file named comprehensive.osm probably comes closest to what you want. I'm not aware of the availability of rendered images of those datafile. Neither for osmarender nor for mapnik. HTH and HAND, Patrick Petschge Kilian ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
[OSM-dev] Reference .osm for render testing?
Hi Is there an artificial reference .osm available, which shows different rendering scenarios? Especially for those building a rendering engine, it could be useful to have a simple OSM, which just contains all OSM map-feature, special cases (like multipolygons with holes, streets above another, ...) and a reference rendering (e.g. Mapnik, Osmarender) - as otherwise we have to hunt for special regions on the whole world.osm. kind regards Michael ___ dev mailing list dev@openstreetmap.org http://lists.openstreetmap.org/listinfo/dev
