osm2pgsql speed benchmark commands

osm2pgsql_speed_commands.sh

sudo apt-get update && sudo apt-get -y dist-upgrade && sudo shutdown -r now

sudo -i
cat > /etc/apt/sources.list.d/pgdg.list <<EOF
deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
#deb-src http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main
EOF
wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | apt-key add -
apt-get --no-install-recommends install -y python-software-properties
add-apt-repository -y ppa:chris-lea/node.js
add-apt-repository -y ppa:mapnik/v2.2.0
apt-get update
apt-get --no-install-recommends install -y postgresql-9.3-postgis-2.1 postgresql-contrib-9.3 \
git unzip apache2.2-bin apache2.2-common apache2-mpm-worker munin-node munin \
munin-plugins-extra libdbd-pg-perl sysstat iotop ptop \
nodejs gdal-bin libgdal1-dev libmapnik mapnik-utils python-mapnik libmapnik-dev \
libxml2-dev libgeos-dev libgeos++-dev libpq-dev libbz2-dev libproj-dev protobuf-c-compiler \
libprotobuf-c0-dev autoconf automake libtool make g++ \
protobuf-compiler libprotobuf-lite7 libprotobuf-dev \
libgdal1-dev


sed -i "s|Allow from.*|Allow from all|" /etc/munin/apache.conf
service apache2 reload

tee /etc/sysctl.d/60-overcommit.conf <<EOF
# Overcommit settings to allow faster osm2pgsql imports
vm.overcommit_memory=1
EOF
#sed -i 's/#*kernel.shmmax = .*/kernel.shmmax = 2147483648/' /etc/sysctl.d/30-postgresql-shm.conf
#sed -i 's/#*kernel.shmall = .*/kernel.shmmax = 524288/' /etc/sysctl.d/30-postgresql-shm.conf
sysctl -p
sysctl -w vm.dirty_ratio=75
sysctl -w vm.zone_reclaim_mode=0
sysctl -w vm.overcommit_memory=1

PGCONF="/etc/postgresql/9.3/main/postgresql.conf"
sed -i 's/#*shared_buffers = .*/shared_buffers = 1GB/' $PGCONF
sed -i 's/#*effective_cache_size = .*/effective_cache_size = 28GB/' $PGCONF

# If you have the RAM, 1GB is good
sed -i 's/#*maintenance_work_mem = .*/maintenance_work_mem = 1GB/' $PGCONF

# More helps speed up rendering queries
sed -i 's/#*work_mem = .*/work_mem = 64MB/' $PGCONF

# Suggested parameters for bulk loading
sed -i 's/#*checkpoint_segments = .*/checkpoint_segments = 256/' $PGCONF
sed -i 's/#*checkpoint_completion_target = .*/checkpoint_completion_target = 0.9/' $PGCONF

# Parameter tuning
sed -i 's/#*random_page_cost = .*/random_page_cost = 2.0/' $PGCONF
# On older PG versions increase cpu_tuple_cost to 0.05-0.10

# Autovacuum tuning to minimize bloat
sed -i 's/#*autovacuum_vacuum_scale_factor = .*/autovacuum_vacuum_scale_factor = 0.04/' $PGCONF
sed -i 's/#*autovacuum_analyze_scale_factor = .*/autovacuum_analyze_scale_factor = 0.02/' $PGCONF

# Move postgres data dir
# /etc/init.d/postgresql stop
# umount /mnt
# mkfs.ext4 /dev/xvdb
# mount /mnt
# mv /var/lib/postgresql /mnt
# chown -R postgres:postgres /mnt/postgresql
# sed -i "s/data_directory = '\/var\/lib\/postgresql\/9.3\/main'/data_directory = '\/mnt\/postgresql\/9.3\/main'/" $PGCONF
# /etc/init.d/postgresql start

# Make data dirs for osm2pgsql stuff
# mkdir /mnt/flat
# mkdir /mnt/planet
# chown ubuntu:ubuntu /mnt/flat /mnt/planet

# exit root
exit

mkdir ~/osm
cd ~/osm

sudo -u postgres createuser -s ubuntu
createdb gis
psql -d gis -c "CREATE EXTENSION hstore; CREATE EXTENSION postgis;"
sudo munin-node-configure --sh | sudo sh
sudo service munin-node restart

sudo apt-get --no-install-recommends -y install lua5.2 liblua5.2-dev curl
# either from packages or source...
# add-apt-repository -y ppa:kakrueger/openstreetmap
# apt-get update
# apt-get --no-install-recommends install -y osm2pgsql

# or ....
git clone https://github.com/openstreetmap/osm2pgsql.git
cd osm2pgsql
# I'm probably working with a different branch here, so some git stuff
./autogen.sh && ./configure && make