PostgreSQL Memory Tuner

psql_mem_tuner.py

total_memory_percentage = 20.0
total_memory_max_megabytes = 8000

memory_params_percentage = {
    'shared_buffers': 25.0,
    'work_mem': 1.0,
    'maintenance_work_mem': 12.0,
    'effective_cache_size': 62.0,
}

## For example if system has 32 GB of RAM
## and total_memory_percentage is set to 50.0
## and total_memory_max_megabytes is set to a large number (like 1000000000)
## and memory_params_percentage['shared_buffers'] is set to 25.0
## then, psql's shared_buffers is going to be 4096MB = (32*1024 * 50/100) * 25/100
## but if total_memory_max_megabytes is set to 8000
## then, psql's shared_buffers is going to be 1000MB = (8000 * 50/100) * 25/100


## default psql configurations
## shared_buffers          128MB # min 128kB
## work_mem                  4MB # min 64kB
## maintenance_work_mem     64MB # min 1MB
## effective_cache_size      4GB


######################################

import os
from os.path import join
import shutil


def readSysMemInfo():
    info = {}
    for line in open('/proc/meminfo'):
        if not line:
            continue
        try:
            key, value = line.split(':')
        except ValueError:
            print 'unknown meminfo line: %s'%line
            continue
        key = key.strip()
        value = value.strip()
        try:
            value_num, value_unit = value.split(' ')
        except ValueError:
            value_num = value
        else:
            if value_unit != 'kB':
                print 'unknown meminfo line: %s'%line
                continue
        try:
            value_num = int(value_num)
        except ValueError:
            print 'unknown meminfo line: %s'%line
            continue
        info[key] = value_num
    return info


def calcPgMemParams():
    sys_mem_info = readSysMemInfo()
    sys_mem_total = sys_mem_info['MemTotal'] / 1024.0 ## megabytes
    pg_mem_total = min(
        total_memory_max_megabytes,
        sys_mem_total * total_memory_percentage / 100.0,
    )
    ###
    params = {}
    for key, percentage in memory_params_percentage.items():
        mem = pg_mem_total * percentage / 100.0
        mem_str = '%dMB'%mem ## FIXME
        params[key] = mem_str
    ###
    return params


def writeNewPgConfFile(confPath, newConfPath):
    params = calcPgMemParams()
    lines = []
    for line in open(confPath):
        line = line.rstrip()
        if line.startswith('#'):
            ucLine = line[1:]
        else:
            ucLine = line
        ###
        ucLineParts = ucLine.split('#')
        ucLine, comment = ucLineParts[0], '#'.join(ucLineParts[1:])
        comment = comment.strip()
        ###
        try:
            key, value = ucLine.split(' = ')
        except ValueError:
            pass
        else:
            try:
                newValue = params[key]
            except KeyError:
                pass
            else:
                line = '%s = %s'%(key, newValue)
                if comment:
                    line = line + ' # ' + comment
        lines.append(line)
    ###
    newConfText = '\n'.join(lines) + '\n'
    open(newConfPath, 'w').write(newConfText)


def findAndModifyPgConf():
    rootDir = '/etc/postgresql'
    for version in os.listdir(rootDir):
        confPath = join(rootDir, version, 'main', 'postgresql.conf')
        writeNewPgConfFile(confPath, confPath)


if __name__=='__main__':
    findAndModifyPgConf()