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