Tuning Postgres on MacOS

A fresh postgres installation on a development machine will probably be fine for most apps. However, when playing with big data, doing heavy aggregations as we do at adjust.io (even in development) can produce completely different performance than on a production server.

Here’s how to tune your local postgres installation to be more production-like.

postgresql.conf

Postgres comes with rather conservative defaults settings on your machine (hopefully you have a good admin for your production environment who knows how to tune your database server!) If you want to get the most out of your local postgres on MacOS you need to tune your postgresql.conf, typically found at

 /usr/local/var/postgres/postgresql.conf           

There are a lot of settings but here are a few of the most important:

maintenance_work_mem = 1GB

Larger settings might improve performance for vacuuming and for restoring database dumps as well as CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY. Since you normally don’t have many of these operations running concurrently it’s safe to set this value quite high.

checkpoint_segments = 16

This setting improves the transaction write performance. Setting checkpoint_segments to 16 means that a checkpoint occurs for every 256MB of data written. Setting this to a larger value will increase write performance, but also increase recovery time. Write-heavy systems can have checkpoint_segments set as high as 256 (checkpoints every 4GB) but hey, we’re talking about development machines, right?

checkpoint_completion_target = 0.9

For PostgreSQL 8.3 and newer, the useful maximum for this is 0.9. See here for more information.

effective_cache_size = 12GB

This can be set to up to 3/4 of your physical memory. It also tells the query planner how much memory you expect to be available.

work_mem = 1GB

Increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts, which, unsurprisingly, will be faster than the disk-based alternative. This setting is applied to each and every sort done for all users.

shared_buffers = 4GB

The shared_buffers configuration parameter determines how much memory is dedicated for PostgreSQL to use for caching data. This is probably the most important setting. Setting it to 1/4 of RAM should be OK for modern systems. However, it requires reconfiguration of your kernel (see below)

wal_buffers = 16MB

Should be 1/32 of the size of shared_buffers with an upper limit of 16MB.

max_connections = 80

The maximum number of client connections allowed. On a development machine you will most likely only need a few connections simultaneously.

Setup & Restart

For my MacBook Pro with 16GB RAM I use the following settings:

maintenance_work_mem = 1GB    
checkpoint_segments = 16        
checkpoint_completion_target = 0.9
effective_cache_size = 12GB
work_mem = 1GB    
shared_buffers = 4GB    
wal_buffers = 16MB
max_connections = 80    

If you have installed postgres with homebrew you can restart it with

launchctl unload -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

But you should also have

tail -f /usr/local/var/postgres/server.log

running in a separate terminal, as you will likely get an error:

FATAL:  could not create shared memory segment: Invalid argument
DETAIL:  Failed system call was shmget(key=5432001, size=4417863680, 03600).
HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.  You can either reduce the request size or reconfigure the kernel with larger SHMMAX.  To reduce the request size (currently 4417863680 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

That’s because the default kernel settings of MacOS are also rather conservative. With 16GB of RAM, setting ‘shared memory segment’ (shmmax) to 5GB should be ok. Keep in mind that it must be set in bytes with a multiplier of 4096. So

ceil(5 * 1024 * 1024 * 1024 / 4096) * 4096 = 5368709120

You can play around with shmmax with

sudo sysctl -w kern.sysv.shmmax=5368709120 
kern.sysv.shmmax: 4194304 -> 5368709120

shmall (‘Total amount of shared memory available’) must be shmmax / pagesize. You can get the pagesize with

pagesize
=> 4096

And try it with

sudo sysctl -w kern.sysv.shmall=1310720   

As long as postgres and MacOS are still up and running (!), you can save your kernel setting permanently to

sudo vim /etc/sysctl.conf

And restart your system.

TL;DR

Get more out of postgres on your development machine.

For my 16GB RAM MacBook pro I use the following settings

/usr/local/var/postgres/postgresql.conf

maintenance_work_mem = 1GB    
checkpoint_segments = 16        
checkpoint_completion_target = 0.9
effective_cache_size = 12GB
work_mem = 1GB    
shared_buffers = 4GB    
wal_buffers = 16MB
max_connections = 80 

/etc/sysctl.conf

kern.sysv.shmmax=5368709120
kern.sysv.shmmin=1
kern.sysv.shmmni=256
kern.sysv.shmseg=64
kern.sysv.shmall=1310720

Further reading

For an in-depth look on postgres high performance checkout this book or this wiki

Comments