Depending on the specification of your server; you may want to adjust the postgres configuration to optimize performance, especially if you are planning on a ‘Whole World’ install.

Please note - this guide is based on a Contabo VPS XL SSD with 60Gb Ram and is based on values that worked for me.

Update postgresql.conf

sudo nano /etc/postgresql/12/main/postgresql.conf

First the shared_buffer default setting is too small. The rule of thumb is to set it to 25% of your total RAM (excluding swap space).

shared_buffers = 15GB

Bump work_mem to 1GB and maintenance_work_mem to 8GB

work_mem = 1GB                        # min 64kB
maintenance_work_mem = 8GB            # min 1MB

If you have lots of ram you can increase the effective_cache_size

effective_cache_size = 20GB

Save and close the file. Restart PostgreSQL for the changes to take effect.

sudo systemctl restart postgresql

Huge Pages

By default, PostgreSQL will try use huge pages in RAM. However, Linux by default does not allocate huge pages. Check the process ID of PostgreSQL.

sudo head -1 /var/lib/postgresql/12/main/postmaster.pid

Sample output:

33918

Then check the VmPeak value of this process ID.

grep ^VmPeak /proc/33918/status

Sample output:

VmPeak:	16188612 kB

This is the peak memory size that will be used by PostgreSQL. Now check the size of huge page in Linux.

cat /proc/meminfo | grep -i huge

Sample output:

AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:               0 kB

We can calculate how many huge pages we need. Divide the VmPeak value by the size of huge page: 16188612 kB / 2048 kB = 7904. Edit /etc/sysctl.conf file.

sudo nano /etc/sysctl.conf

Add the following line to allocate 7950 huge pages.

vm.nr_hugepages = 7904

Save and close the file. Then apply the changes.

sudo sysctl -p

If you check the meminfo again,

cat /proc/meminfo | grep -i huge

We can see there are 7904 huge pages available.

AnonHugePages:         0 kB
ShmemHugePages:        0 kB
FileHugePages:         0 kB
HugePages_Total:    7904
HugePages_Free:     7904
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
Hugetlb:        16187392 kB

Restart PostgreSQL to use huge pages.

sudo systemctl restart postgresql

Conclusion

What have we done?

1. Made some basic changes to postgresql.conf to increase the amount of memory it can use.
2. Configured huge pages for

With the changes we’ve made the import time for OSM should be improved.

Based on https://www.linuxbabe.com/ubuntu/openstreetmap-tile-server-ubuntu-18-04-osm