I completed some benchmarking of osm2pgsql with various ZFS filesystem
settings for use in my new server, and wrote up a blog post. I believe
the results to be generally applicable for large OSM-based databases on
ZFS storage and are not confined to osm2pgsql.
http://paulnorman.ca/blog/2014/11/zfs-settings-for-osm2pgsql/
The short version of the ZFS tuning is
- Use an 8K recordsize for the tablespace, always
- Use lz4 compression for anything except a write-only workload with COPY
- CPU usage of compression is not significant
I am reluctant to extend the results beyond ZFS to other compression
schemes, but switching geometry columns from MAIN to EXTENDED on systems
where the underlying filesystem is not compressed may be worth
investigating. This would impact a rendering workload, not an import
workload.
I also did some basic postgresql.conf tuning, documented in
http://paulnorman.ca/blog/2014/11/new-server-postgresql-tuning/
This brought the initial import time from 46 hours to 25 hours, mainly
due to a much faster GIN index build. Tuning is important, particularly
maintenance_work_mem for GIN index creation.
My fastest time for a full --slim import without --drop was 21.5 hours,
which is pretty good for 7200 RPM HDDs as the underlying storage. As
always, if you are not planning on updating, --drop will save much of
that time, probably about half of it.
_______________________________________________
dev mailing list
[email protected]
https://lists.openstreetmap.org/listinfo/dev
