Speeding up import and reducing disk space

General talk
1

Hi!

First of all: this is not a question, but a “report of my experience”…
Since I had many problem with very slow import (see here) and with disk space too (see here), I looked for a solution and I found it.
So, I’d like to share my solution with the community, hoping it helps someone else…

Caveat: this solution worked for me, since I just need an import of the OSM data once per year (so every year I destroy the previous database and import the data to a new one)! If you need regular updates, it will not works!

  1. Preparing the hardware
    Since the import and the querying of the data need quick access, I used an SSD instead of an HDD. 512GB is enough for whole Europe. 256GB can be enough, if you don’t need extra index.

  2. Preparing the database
    To speed up the import, autovacuum should be turned off. In postgresql.conf change to:

autovacuum = off
  1. Start the import
    I decided to use a separated node cache (created in the SSD, ~70GB für Europe), to disable the parallel indexing, to drop the slim mode tables after completing import and to use unlogged tables for storing data.
    So my commands are:
sudo touch /var/lib/postgresql/nodes.cache ; sudo chmod a+w /var/lib/postgresql/nodes.cache
time sudo -u _renderd osm2pgsql -d gis --disable-parallel-indexing --drop --unlogged \
   --flat-nodes /var/lib/postgresql/nodes.cache --create --slim  -G --hstore --tag-transform-script \
   ~/src/osm-icao/osm-icao.lua -C 12000 --number-processes 12 -S ~/src/osm-icao/osm-icao.style \
   ~/data/europe-latest.osm.pbf
sudo rm /var/lib/postgresql/nodes.cache

/var/lib/postgresql is the mount point of my SSD.

Result:

Processing: Node(2875001k 930.1k/s) Way(348143k 7.94k/s) Relation(5895530 239.70/s)  parse time: 71530s

As I said, this solution cannot be applyd be regular updates, since some data are missing:


gis=# \d+
                                     Liste der Relationen
 Schema |                Name                 |   Typ   | Eigentümer |  Größe  | Beschreibung 
--------+-------------------------------------+---------+------------+---------+--------------
 public | external_data                       | Tabelle | _renderd   | 16 kB   | 
 public | geography_columns                   | Sicht   | postgres   | 0 bytes | 
 public | geometry_columns                    | Sicht   | _renderd   | 0 bytes | 
 public | planet_osm_line                     | Tabelle | _renderd   | 35 GB   | 
 public | planet_osm_nodes                    | Tabelle | _renderd   | 0 bytes | 
 public | planet_osm_point                    | Tabelle | _renderd   | 13 GB   | 
 public | planet_osm_polygon                  | Tabelle | _renderd   | 12 GB   | 
 public | planet_osm_rels                     | Tabelle | _renderd   | 2946 MB | 
 public | planet_osm_roads                    | Tabelle | _renderd   | 5540 MB | 
 public | planet_osm_ways                     | Tabelle | _renderd   | 80 GB   | 
 public | raster_columns                      | Sicht   | postgres   | 0 bytes | 
 public | raster_overviews                    | Sicht   | postgres   | 0 bytes | 
 public | spatial_ref_sys                     | Tabelle | _renderd   | 4616 kB | 
(13 Zeilen)

Hope to help someone! :wink:
Luca