[OSM-dev] Tuning ZFS and osm2pgsql

[Paul Norman]

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.