I recently compared the osm2pgsql performance of PostgreSQL 9.3 and 9.4
with my standard import workload and consuming updates and found 9.4
offered a solid performance improvement.
The 130904 planet PBF was imported on a SSD-based i7-4700 machine with
32GB RAM with PostgreSQL 9.3.5 and 9.40. PostgreSQL was tuned in
accordance with
http://www.paulnorman.ca/blog/2014/11/new-server-postgresql-tuning/
except full_page_writes was not set. osm2pgsql options were
--number-processes 8 --flat-nodes --slim --cache 20000. Hourly diffs
from 000008555 to 000008854 were consumed in blocks of 100 with a 10
second sleep between each diff. osm2pgsql options were
--number-processes 8 --flat-nodes --slim --cache 2000.
Initial import time decreased by 2.2% from 7 hours 31 minutes to 7 hours
21 minutes. The time to create the large ways nodes GIN index decreased
by 6% from 3 hours 31 minutes to 3 hours 18 minutes. The time to perform
the other parts of the import did not change significantly.
Total update time for 100 hours of diffs decreased by 2.5% from 4 hours
8 minutes to 4 hours 1 minute. It was not possible to separate by
osm2pgsql stage.
The size of the ways nodes GIN index after importing and updating
remained at 100GB, which was not the expected result. GIST geometry
index sizes differed slightly, but GIST index creation is
non-deterministic and the variations were well within the bounds of what
is observed by simply reindexing repeatedly.
The latest (150202) planet has increased in size by 25%, so import times
would be expected to increase by the same for both 9.3 and 9.4. The size
of 28 daily diffs has increased in size by 33% from 1133 MiB to 1512
MiB, so update times would be expected to similarly increase.
The system used for benchmarking has significantly faster random IO than
many. Most systems, particularly those with mechanical HDDs, would have
a greater relative and absolute speed increase than this. It would have
saved 1 to 3 hours on one of my servers.
When not updating --drop should be used for increased speed. These
imports do not generate the GIN index and would be no faster with 9.4.
Although a rendering stack can run on versions as old as PostgreSQL 8.4
and 1.5, a minimum of 9.1 + 2.0 is suggested, with gains seen from
9.3+2.1.1, and this combination is successfully run on many production
servers. I will be recommending 9.4+2.1 for new installs.
For Debian based OSes, including Ubuntu, 9.4 is packaged at
apt.postgresql.org with packages that are drop-in replacements for the
system ones.
_______________________________________________
dev mailing list
dev@openstreetmap.org
https://lists.openstreetmap.org/listinfo/dev
