Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
Stephan Knauss stephans-server.de> writes: > A while ago I did import the planet on my windows machine (i7 920, 12G). > I had used -C 3800. So you could give it a bit more ram. > I have no exact data, but I think it did run for two or three days. Thank you Stephan! We will give this a try. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
On 29.08.2011 17:47, Andrei wrote: Are we trying to do the impossible by importing the whole planet with just 12Gb of RAM? Is there some trickery we can use to do the import on this machine, or maybe another method we can use for the import? A while ago I did import the planet on my windows machine (i7 920, 12G). I had used -C 3800. So you could give it a bit more ram. I have no exact data, but I think it did run for two or three days. Other steps you did look fine. If the point of your out-of-memory does correlate with the -C option is out of my knowledge. In case you do not get any better answer you could try it with this setting. Stephan ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
Martijn van Oosterhout gmail.com> writes: > > On 8 June 2011 17:11, Martijn van Oosterhout gmail.com> wrote: > > Actually, it looks like it's breaking on the pending_ways query. It's > > possible there's a lot of those, perhaps it's just running out of > > memory there. The code was designed that pending would only be for > > changes, and I wouldn't have expected that code to be used for an > > initial import. but I haven't look at the code recently so maybe that > > changed? > > > > If it is the intention that this code is used for initial imports it > > really needs to be changed to use cursors. > > Actually, it occurred to me that the system is optimised to the case > where the file contains nodes, then ways, then relations. What you're > seeing could be caused by the file having the ways first, then the > nodes. Can you examine the file to see how it looks? > > Have a nice day, Hi, We're having a problem importing planet file into our PostGIS database. The import ran for a few days and then terminated with an "out of memory" error (full details below). After much googling and trying to fix things ourselves, we've finally admitted defeat and need to ask for some help! Are we trying to do the impossible by importing the whole planet with just 12Gb of RAM? Is there some trickery we can use to do the import on this machine, or maybe another method we can use for the import? Thanks in advance for any advice, Andrei & Doug MARSS Full details of our setup below: OpenStreetMap importing planet issue task: Importing planet.osm.bz2 file into a PostgreSQL 8.4 + PostGis database. hardware used: Intel Xeon CPU I7(2.67Ghz), with 12GB of RAM process used (from weait.com blog - "build your own OSM server" (http://weait.com/content/build-your-own-openstreetmap-server)): 1. downloaded planet file (17GB) 2. prepared the postGIS database - installed postGIS and postGIS extensions for PostreSQL sudo apt-get install postgresql-8.4-postgis postgresql-contrib-8.4 sudo apt-get install postgresql-server-dev-8.4 sudo apt-get install build-essential libxml2-dev libtool sudo apt-get install libgeos-dev libpq-dev libbz2-dev proj 3. installed osm2pgsql (latest version (0.70.5)) from the SVN repository cd ~/bin svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/ cd osm2pgsql ./autogen.sh ./configure make 4. configured the PostGIS database shared_buffers = 128MB # 16384 for 8.1 and earlier checkpoint_segments = 20 maintenance_work_mem = 256MB # 256000 for 8.1 and earlier autovacuum = off 5. edited kesrnel parameter shmmax to increase maximum size of shared memory. sudo sysctl -w kernel.shmmax=268435456 sudo sysctl -p /etc/sysctl.conf 6. restarted postgres to enable the changes sudo /etc/init.d/postgresql-8.4 restart 7. created a database sudo -u postgres -i createuser username # answer yes for superuser createdb -E UTF8 -O username gis_planet createlang plpgsql gis exit 8. have set up PostGIS on the postresql database. psql -f /usr/share/postgresql/8.4/contrib/postgis-1.5/postgis.sql -d gis 9. have set the tables owners to the same user-name as mapnik will use to generate tiles echo "ALTER TABLE geometry_columns OWNER TO username; ALTER TABLE spatial_ref_sys OWNER TO username;" | psql -d gis 10. have set the Spatial Reference Identifier (SRID) on the new database. psql -f ~/bin/osm2pgsql/900913.sql -d gis 11. loaded planet into the database with osm2pgsql ./osm2pgsql -S default.style --slim -d gis -C 2048 ~/planet/planet.osm.bz2 error encountered: Node stats: total(1137985868), max(1350873143) Way stats: total(100214404), max(120480121) Relation stats: total(1037036), max(1652480) Going over pending ways OpenStreetMap importing planet issue task: Importing planet.osm.bz2 file into a PostgreSQL 8.4 + PostGis database. hardware used: Intel Xeon CPU I7(2.67Ghz), with 12GB of RAM process used (from weait.com blog - "build your own OSM server" (http://weait.com/content/build-your-own-openstreetmap-server)): 1. downloaded planet file (17GB) 2. prepared the postGIS database - installed postGIS and postGIS extensions for PostreSQL sudo apt-get install postgresql-8.4-postgis postgresql-contrib-8.4 sudo apt-get install postgresql-server-dev-8.4 sudo apt-get install build-essential libxml2-dev libtool sudo apt-get install libgeos-dev libpq-dev libbz2-dev proj 3. installed osm2pgsql (latest version (0.70.5)) from the SVN repository cd ~/bin svn co http://svn.openstreetmap.org/applications/utils/export/osm2pgsql/ cd osm2pgsql ./autogen.sh ./configure make 4. configured the PostGIS database shared_buffers = 128MB # 16384 for 8.1 and earlier checkpoint_segments = 20 maintenance_work_mem = 256MB # 256000 for 8.1 and earlier autovacuum = off 5. edited kesrnel parameter shmmax to increase maximum size of shared memory. sudo sysctl -w kernel.shmmax=268435456 sudo sysctl -p /etc/sysctl.conf 6. restarted postgres to enable the changes sudo /etc/init.d/postgresql-
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
Martijn van Oosterhout gmail.com> writes: > > On 9 June 2011 08:38, Saphy Mo yahoo.com> wrote: > > The file is the same Europe.osm.bz2 directly downloaded from geofabrik. I > > will decomprese it and try to see the order of Nodes, Ways and relation. > > But the error happens while pending_ways query. > > It occurs to me you can see this during import, by seeing which > counters go up first. The osm2pgsql wiki http://wiki.openstreetmap.org/wiki/Osm2pgsql#Optimization says "it is smart to increase the value of checkpoint_segments". I have a feeling that it is compulsory, at least I was not able to import the Finnish excerpt at all with the PostgreSQL 9.0 default value. But perhaps you have increased it already. -Jukka Rahkonen- ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
On 9 June 2011 08:38, Saphy Mo wrote: > The file is the same Europe.osm.bz2 directly downloaded from geofabrik. I > will decomprese it and try to see the order of Nodes, Ways and relation. > But the error happens while pending_ways query. It occurs to me you can see this during import, by seeing which counters go up first. The reason that this is a problem is that in a normal import while importing the nodes there are no way so pending_ways is empty. Since yours is not empty the question is why. There are two possibilities: 1. The database wasn't empty to start with, But with create mode that seems unlikely. 2. The ways come before the nodes, which means all ways get marked as pending when the nodes are imported. And it barfs on this query when there are a lot of them. Note you can als get a quick answer with just: $ bzcat |head Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
On 8 June 2011 17:11, Martijn van Oosterhout wrote: > Actually, it looks like it's breaking on the pending_ways query. It's > possible there's a lot of those, perhaps it's just running out of > memory there. The code was designed that pending would only be for > changes, and I wouldn't have expected that code to be used for an > initial import. but I haven't look at the code recently so maybe that > changed? > > If it is the intention that this code is used for initial imports it > really needs to be changed to use cursors. Actually, it occurred to me that the system is optimised to the case where the file contains nodes, then ways, then relations. What you're seeing could be caused by the file having the ways first, then the nodes. Can you examine the file to see how it looks? Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
On 8 June 2011 16:44, Saphy Mo wrote: > Dear friend, > I am trying to import whole Europe.os.bz2 in a Postgis database named > EuropeLl as you see in the script below. > CPU: Dual Coe 3 Ghy > RAM: 3 GB > HDD: 1 TB > osm2pgsql.exe -d EuropeLL --latlong --slim -c -C 500 -H localhost -P 5432 -U > osm -W -S default.style europe.osm.bz2 How much memory did you give postgresql? How much is it using? Actually, it looks like it's breaking on the pending_ways query. It's possible there's a lot of those, perhaps it's just running out of memory there. The code was designed that pending would only be for changes, and I wouldn't have expected that code to be used for an initial import. but I haven't look at the code recently so maybe that changed? If it is the intention that this code is used for initial imports it really needs to be changed to use cursors. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
Re: [OSM-talk] Slim mode in osm2pgsql and out of memory error
On Wed, Jun 8, 2011 at 10:44 AM, Saphy Mo wrote: > Dear friend, > I am trying to import whole Europe.os.bz2 in a Postgis database named > EuropeLl as you see in the script below. > CPU: Dual Coe 3 Ghy > RAM: 3 GB > HDD: 1 TB > osm2pgsql.exe -d EuropeLL --latlong --slim -c -C 500 -H localhost -P 5432 -U > osm -W -S default.style europe.osm.bz2 > -- > It reads Nodes(495155k), Ways(57513k), relations(669k) properly. > Exception Caught processings ways id=1479960 > Processing: Node(495155k),Ways(57513k), relations(718k) > Node State: total (495155996),max(1281994925) > Way state: total(57513025), max(158054) > going over pending ways > pending_ways faild : out of memory for query result > (7) > error occurred cleaning up > > > Please let me know, what I shoud modify and change to get it working. > Shoudl I use a PC with more than 8 or 16 GB -C 500 # limits osm2pgsql to 500MB. Default is 800MB iirc. Try that, or more. ___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
[OSM-talk] Slim mode in osm2pgsql and out of memory error
Dear friend, I am trying to import whole Europe.os.bz2 in a Postgis database named EuropeLl as you see in the script below. CPU: Dual Coe 3 Ghy RAM: 3 GB HDD: 1 TB osm2pgsql.exe -d EuropeLL --latlong --slim -c -C 500 -H localhost -P 5432 -U osm -W -S default.style europe.osm.bz2 -- It reads Nodes(495155k), Ways(57513k), relations(669k) properly. Exception Caught processings ways id=1479960 Processing: Node(495155k),Ways(57513k), relations(718k) Node State: total (495155996),max(1281994925) Way state: total(57513025), max(158054) going over pending ways pending_ways faild : out of memory for query result (7) error occurred cleaning up Please let me know, what I shoud modify and change to get it working. Shoudl I use a PC with more than 8 or 16 GB regards, Saphy___ talk mailing list talk@openstreetmap.org http://lists.openstreetmap.org/listinfo/talk
