Very slow Loading of LinkedGeoData in PostgreSql

Question

I have installed and tuned my PostgreSql Database and I downloaded LinkedGeoData files from here and then I have executed the line lgd-createdb -h localhost -d databasename -U user -W password -f bremen-latest.osm.pbf (12MB) and the same for saarland-latest.osm.pbf (21.6 MB) and worked fine and under 15 Minutes but I tried to load a heavier file like Mecklenburg-Vorpommern-latest.osm.pbf (54MB) and it didn't react very good, system executes that line but I wait for result since yesterday.

The values of my PostgreSql's conf File postgresql.conf are

shared_buffers               = 2GB 
effective_cache_size         = 4GB
checkpoint_segments          = 256
checkpoint_completion_target = 0.9
autovacuum                   = off 
work_mem                     = 256MB
maintainance_work_mem        = 256MB

My PostgreSql Version is 9.1 under Debian Machine.

How can I solve this issue?

I thank you in advance.

Answer 1

I am the developer of the lgd-createdb script, and I just tried to reproduce the problem using postgresql 9.3 (via ubuntu 14.04) on a notebook with quad core I7, SSD and 8GB RAM - and for me the Mecklenburg-Vorpommern-latest.osm.pbf file was loaded in less than 10 minutes.

My settings were:

shared_buffers = 2GB
temp_buffers = 64MB
work_mem = 64MB
maintenance_work_mem = 256MB
checkpoint_segments = 64
checkpoint_completion_target = 0.9
checkpoint_warning = 30s
effective_cache_size = 2GB

so quite similar to yours.

I even created a new version of the LGD script (not in the repo yet), where osmosis is configured to first load the data into the "snapshot" schema and afterwards convert it to the "simple" schema. Osmosis is optimized for the former schema, and indeed on a single run (using the CompactTempFile option) it was a slightly faster (8min snapshot vs 8:30min simple).

Do you have SSDs? The latter loading strategy might be significantly faster on non-SSDs (although it shouldn't be hours for a 50MB file). Maybe a system load indicator such as htop or indicator-multiload could help you reveal resource problems (such as running out of RAM or high disk I/O by another process).