pg_optimizations.py (9b49ec0) - pg_optimizations

pg_optimizations.py

#!/usr/bin/python

import subprocess

p = subprocess.Popen(['getconf','PAGE_SIZE'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
out, err = p.communicate()

page_size=int(out.strip())

p = subprocess.Popen(['getconf','_PHYS_PAGES'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
out, err = p.communicate()

phys_pages=int(out.strip())

all_mem=phys_pages*page_size

def pretty_size(size):
    size=float(size)
    for x in ['B', 'KB', 'MB']:
        if size < 1024.0:
            return "%i%s" % (size, x)
        size /= 1024.0
    return "%i%s" % (size*1024, x)

print "Total server memory : %s (Pages : %s / Page size : %sB" % (pretty_size(all_mem),phys_pages,page_size)

pg_ratio=None
while pg_ratio is None:
	pg_ratio=int(raw_input("How many for PG (0-95) ? "))
	if pg_ratio<1 or pg_ratio>95:
		print "Invalid value"
		pg_ratio=None

print "Memory allocated to PG : %s" % pretty_size(all_mem*pg_ratio/100)

pg_max_con=None
while pg_max_con is None:
	pg_max_con=int(raw_input("How many simultaneous connections to PG ? "))
	if pg_max_con<=0:
		print "Invalid value"
		pg_max_con=None

shmall=int((phys_pages*pg_ratio/100)*1.2)
if shmall>all_mem:
	shmall=all_mem
shmmax=int(shmall*page_size)

print "\nTo put in /etc/sysctl.d/30-postgresql-shm.conf :\n"
print "\tkernel.shmall = %s" % shmall
print "\tkernel.shmmax = %s" % shmmax
print "\nAnd restart procps service to apply (on Debian) :\n\n\tservice procps restart"

pg_mem=int(all_mem*pg_ratio/100)
shared_buffers=int(pg_mem/4)
work_mem=int(pg_mem / (pg_max_con + (pg_max_con * 0.1)))
effective_cache_size=pg_mem

maintenance_work_mem=0.9*pg_mem

print "\nTo put in postgresql.conf :\n"
print "\t# Memory related parameters"
print "\t# PG allocated memory : %s" % pretty_size(pg_mem)
print "\tmax_connections = %s" % pg_max_con
print "\t# shared_buffers = 25% of PG allocated memory"
print "\tshared_buffers = %s" % pretty_size(shared_buffers)
print "\t# Memory used for sorting result for each connection"
print "\t# work_mem = PG allocated memory / (max_connexions + 10%)"
print "\twork_mem = %s" % pretty_size(work_mem)
print "\t# Memory used for vacuum, create index, ..."
print "\t# maintenance_work_mem = 90% of PG allocated memory"
print "\tmaintenance_work_mem = %s " % pretty_size(maintenance_work_mem)
print "\t# Available memory for PG (use for internal calculation)"
print "\teffective_cache_size = %s" % pretty_size(effective_cache_size)
print "\n\t# Checkpoints management"
print "\tcheckpoint_segments = 8"
print "\tcheckpoint_timeout = 5min"
print "\tcheckpoint_completion_target = 0.7"
print "\n\t# Log all request > 1sec"
print "\tlog_min_duration_statement = 1000"

print "\nAnd restart PG service to apply :\n\n\tservice postgresql restart"

pg_optimizations

Initial commit