Last active
March 7, 2024 06:20
-
-
Save ilius/a061bb1acfe63f711980 to your computer and use it in GitHub Desktop.
PostgreSQL Memory Tuner
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment