Tune some system-specific PostgreSQL parameters.

pg_tuner.py

#!/usr/bin/env python
# vim: set fileencoding=utf-8

from __future__ import print_function

from fabric.api import env, run, sudo
from fabric.colors import cyan, green, yellow
from fabric.state import output


env.user = "ubuntu"
env.forward_agent = True
output['everything'] = False

conf = "/etc/postgresql/9.2/main/postgresql.conf"


def pg_import_tune():
    """Tune PostgreSQL for bulk imports."""
    print(cyan("Tuning PostgreSQL for bulk import."))

    print(yellow("  • Stopping PostgreSQL."))
    sudo("service postgresql stop")

    memsize = int(run("free -b | grep ^Mem | awk '{print $2}'").strip())
    memsize_mb = int(memsize / 1048576)

    # Set the maximum amount of allocatable SysV shared memory to
    # 80% of the total system RAM.
    print(yellow("  • Setting shmmax to 80% of total RAM."))
    shmmax_b = int(memsize * 0.80)
    sudo("echo 'kernel.shmmax={}' >> /etc/sysctl.conf".format(shmmax_b))
    sudo("echo 'kernel.shmall={}' >> /etc/sysctl.conf".format(shmmax_b))
    sudo("sysctl -p /etc/sysctl.conf")

    print(yellow("  • Setting shared_buffers to 1/3 of RAM."))
    shared_bufs = int(memsize_mb * 0.33)
    sudo("sed -i 's/#*shared_buffers = [0-9]*MB/shared_buffers = {}MB/' {}"
         .format(shared_bufs, conf))

    print(yellow("  • Setting maintenance_work_mem to 1/3 of RAM."))
    maintenance_mem = int(memsize_mb * 0.33)
    sudo("sed -i 's/#*maintenance_work_mem = [0-9]*MB/"
         "maintenance_work_mem = {}MB/' {}".format(maintenance_mem, conf))

    print(yellow("  • Disabling fsync."))
    sudo("sed -i 's/#*fsync = on/fsync = off/' {}".format(conf))

    print(yellow("  • Increasing WAL checkpoint settings."))
    sudo("sed -i 's/#*checkpoint_segments = [0-9]*/"
         "checkpoint_segments = 256/' {}".format(conf))

    sudo("sed -i 's/#*checkpoint_timeout = 5min/"
         "checkpoint_timeout = 1h/' {}".format(conf))

    print(yellow("  • Starting PostgreSQL."))
    sudo("service postgresql start")

    print(green("✓ Tuning complete!"), end="\n\n")


def pg_mem_tune():
    """Tune PostgreSQL to make effective use of available RAM."""
    print(cyan("Tuning PostgreSQL memory settings."))

    print(yellow("  • Stopping PostgreSQL."))
    sudo("service postgresql stop")

    memsize = int(run("free -b | grep ^Mem | awk '{print $2}'").strip())
    memsize_mb = int(memsize / 1048576)

    # Set the maximum amount of allocatable SysV shared memory to
    # 80% of the total system RAM.
    print(yellow("  • Setting shmmax to 80% of total RAM."))
    shmmax_b = int(memsize * 0.80)
    sudo("echo 'kernel.shmmax={}' >> /etc/sysctl.conf".format(shmmax_b))
    sudo("echo 'kernel.shmall={}' >> /etc/sysctl.conf".format(shmmax_b))
    sudo("sysctl -p /etc/sysctl.conf")

    # The shared_buffers parameter determines how much memory Postgres can use
    # for caching data (this is in addition to the OS buffer cache). Although
    # the docs suggest that this is aggressive, set it to 75% of the total
    # avaialble shared memory.
    print(yellow("  • Setting shared_buffers to 15% of RAM."))
    shared_bufs = int(memsize_mb * 0.15)
    sudo("sed -i 's/#*shared_buffers = [0-9]*MB/shared_buffers = {}MB/' {}"
         .format(shared_bufs, conf))

    # The work_mem parameter determines how much memory each process can use
    # for intermediate results. Set this to memsize / max_connections.
    max_conn = int(run("grep --color=never -Po "
                       "'(?<=^max_connections = )\d+' {}".format(conf)))
    work_mem = int(memsize_mb / max_conn)
    print(yellow("  • Setting work_mem to {}MB.".format(work_mem)))
    sudo("sed -i 's/#*work_mem = [0-9]*MB/work_mem = {}MB/' {}"
         .format(work_mem, conf))

    # The maintenance_work_mem parameter determines how much memory is used by
    # maintenance queries such as "VACCUM, CREATE INDEX", etc. Set this to
    # memsize / 8.
    maintenance_mem = int(memsize_mb / 8)
    print(yellow("  • Setting maintenance_work_mem to {}MB."
                 .format(maintenance_mem)))
    sudo("sed -i 's/#*maintenance_work_mem = [0-9]*MB/"
         "maintenance_work_mem = {}MB/' {}".format(maintenance_mem, conf))

    # The effective_cache_size parameter is used by the query planner to
    # figure out whether plans it is considering can fit in RAM or not. Set
    # this to most of available system RAM.
    print(yellow("  • Setting effective_cache_size to 75% of available RAM."))
    cache_size = int(run("free -m | grep ^Mem | awk '{print $4+$7}'").strip())
    cache_size = int(cache_size * 0.75)
    sudo("sed -i 's/#*effective_cache_size = [0-9]*MB/"
         "effective_cache_size = {}MB/' {}".format(cache_size, conf))

    print(yellow("  • Starting PostgreSQL."))
    sudo("service postgresql start")

    print(green("✓ Tuning complete!"), end="\n\n")