adjust_pg_conf

gistfile1.py

import os
import re
import sys
import wmi

def get_memory_info(): 
    computer_system = wmi.WMI().Win32_ComputerSystem() 
    op_system = wmi.WMI().Win32_OperatingSystem() 
   
    total_mem = int(computer_system[0].TotalPhysicalMemory)/1024/1024 
    free_mem = int(op_system[0].FreePhysicalMemory)/1024
    
    return total_mem, free_mem

def adjust_pg_mem_parameters(total_mem, free_mem):
    '''https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server'''
    max_effective_cache_size = 10240
    min_effective_cache_size = 256
    
    max_shared_buffers = 5120
    min_shared_buffers = 128
    
    effective_cache_size = free_mem / 2
    shared_buffers = free_mem / 4
    
    if effective_cache_size > max_effective_cache_size:
         effective_cache_size = max_effective_cache_size
    elif effective_cache_size < min_effective_cache_size:
        effective_cache_size = min_effective_cache_size
        print '[WARING]:free mem only %sMB! You MUST increase the memory!' % free_mem
    
    if shared_buffers > max_shared_buffers:
         shared_buffers = max_shared_buffers
    elif shared_buffers < min_shared_buffers:
        shared_buffers = min_shared_buffers
        print '[WARING]:free mem only %sMB! You MUST increase the memory!' % free_mem
    
    return effective_cache_size, shared_buffers
    
def auto_modify_pg_conf(filepath):
    if not os.path.exists(filepath):
        print 'postgresql.conf not exist : %s' % filepath
        return False
    
    pg_conf = ''
    with open(filepath) as f:
        pg_conf = f.read()
        
    total_mem, free_mem = get_memory_info()
    effective_cache_size, shared_buffers = adjust_pg_mem_parameters(total_mem, free_mem)
    
    effective_cache_size_conf = 'effective_cache_size = %sMB' % effective_cache_size
    p = re.compile('#?effective_cache_size\s*?=.+?MB')
    pg_conf, sub_number = p.subn(effective_cache_size_conf, pg_conf)
    
    cpu_processes_conf = 'shared_buffers = %sMB' % shared_buffers
    p = re.compile('#?shared_buffers\s*?=.+?MB')
    pg_conf, sub_number = p.subn(cpu_processes_conf, pg_conf)
    
    with open(filepath, 'w') as f:
         f.write(pg_conf)
         
    return True

if __name__ == "__main__":
    if len(sys.argv) > 1:
        filepath = sys.argv[1]
        auto_modify_pg_conf(filepath)
    else:
        print 'input "postgresql.conf" file path'