LinuxQuestions.org
Download your favorite Linux distribution at LQ ISO.
Go Back   LinuxQuestions.org > Blogs > Musings on technology, philosophy, and life in the corporate world
User Name
Password

Notices



Hi. I'm a Unix Administrator, mathematics enthusiast, and amateur philosopher. This is where I rant about that which upsets me, laugh about that which amuses me, and jabber about that which holds my interest most: Unix.
Rate this Entry

PostgreSQL reconfigure script

Posted 02-10-2010 at 10:54 AM by rocket357
Updated 03-04-2011 at 10:42 AM by rocket357 (Added Version changelog to script)

UPDATE: Script has been revised and is available here

The following script is something I've written up to ease my duties as the PostgreSQL DBA for a large hosting company. It configures PostgreSQL according to whack-a-mole, and it auto-detects various resources such as system RAM, shared memory settings, etc...

The script currently supports Linux and OpenBSD (FreeBSD coming soon!).

Code:
#!/bin/sh

# configPG.sh:
#
# This script automatically detects system RAM and other resources
# and modifies $PG_DATA_DIR/postgresql.conf to configure the server
# for one of three uses:  web, oltp, or data warehousing.
# 
# Author: rocket357@users.sourceforge.net
# License: BSD
#
# This script uses the conventions found at:
# http://www.slideshare.net/oscon2007/...e-whack-a-mole
#
# The following comment is taken from the slide
# "What Color Is My Application" found at the above url...
#
# web = web application backend
#       1) DB smaller than RAM
#       2) 90% or more simple read queries
# oltp = online transaction processing
#	1) db slightly larger than RAM, up to 1 TB
#	2) 20-40% small data write queries
#	3) some long transactions
# dw = data warehousing
#	1) large to huge databases (100 GB to 100 TB)
#	2) large complex report queries
#	3) large bulk loads of data
#	4) also called "Decision Support" or "Business Intelligence"

# CHANGELOG
# v0.1 - Initial post to LQ.org

set -e # bomb out if something goes wrong...

if [ ! `whoami` = 'root' ]; then
	echo "This script needs to run as root because"
	echo "it alters shm{max,mni,all}"
	exit
fi

if [ -z "$1" ]; then
	echo "Usage:  ./setupPG.sh [template]"
	echo "Where [template] is one of:"
	echo "	'web'  (web backend server)"
	echo "	'oltp' (online transaction processing)"
	echo "	'dw'   (data warehouse)"
	echo "See http://www.slideshare.net/oscon2007/performance-whack-a-mole"
	echo "for further explanation."
	exit
fi

###################################
### USER CONFIGURABLE VARIABLES ###
###################################

PGHOMEDIR=/var/lib/postgresql
PGDATADIR=$PGHOMEDIR/data
CONFIG_FILE=$PGDATADIR/postgresql.conf
TEMP_FILE=$PGDATADIR/postgresql.conf.TMP

# These two are taken from performance-whack-a-mole (see link in header comments)
SHARED_BUFFER_RATIO=0.25
EFFECTIVE_CACHE_RATIO=0.67

if [ "$1" = "web" ]; then # web backend server
	NUM_CONN=400
	WORK_MEM=512 # kB
	CHECKPOINT_SEG=8
	MAINT_WORK_MEM=128MB
elif [ "$1" = "oltp" ]; then # online transaction processing
	NUM_CONN=200
	WORK_MEM=2048 # kB
	CHECKPOINT_SEG=16
	MAINT_WORK_MEM=128MB
elif [ "$1" = "dw" ]; then # data warehousing
	NUM_CONN=100
	WORK_MEM=131072 # kB
	CHECKPOINT_SEG=64
	MAINT_WORK_MEM=1024MB
fi

#######################################
### END USER CONFIGURABLE VARIABLES ###
#######################################

# first let's locate the configuration file...
if [ -e $CONFIG_FILE ]; then
	echo "Backing up original config file to $CONFIG_FILE.BACKUP"
	cp $CONFIG_FILE $CONFIG_FILE.BACKUP
	echo "Backing up /etc/sysctl.conf to $PGHOMEDIR/sysctl.conf.BACKUP"
	cp /etc/sysctl.conf $PGHOMEDIR/sysctl.conf.BACKUP
fi


OS_TYPE=`uname -s`

### LINUX
if [ "$OS_TYPE" = "Linux" -o "$OS_TYPE" = "GNU/Linux" ]; then

	SYSCTL_KERNEL_NAME="kernel"
	MAX_MEM=`grep MemTotal /proc/meminfo | sed -e 's/^[^0-9]*//' | cut -d' ' -f1`
	OS_PAGE_SIZE=`getconf PAGE_SIZE`

### OPENBSD
elif [ "$OS_TYPE" = "OpenBSD" ]; then

	SYSCTL_KERNEL_NAME="kern.shminfo"
	MAX_MEM=$(echo "scale=0; `dmesg | grep \"real mem\" | cut -d\"=\" -f2 | cut -d\"(\" -f1`/1024" | bc -l) # convert to kB
	OS_PAGE_SIZE=`sysctl hw.pagesize | cut -d'=' -f2`

### UNKNOWN?
else
	echo "$OS_TYPE isn't supported!  Please send an e-mail to rocket357@users.sourceforge.net to have this OS added!"
	exit
fi

echo "Done!"

# make sure work_mem isn't greater than total memory divided by number of connections...
WORK_MEM_KB=$(echo "scale=0; $MAX_MEM/$NUM_CONN" | bc -l)
if [ $WORK_MEM_KB -gt $WORK_MEM ]; then WORK_MEM_KB=$WORK_MEM; fi
WORK_MEM=$(echo "scale=0; $WORK_MEM_KB/1024" | bc -l)MB

# OS settings
HOSTNAME=`hostname`

# shm{mni,all,max} are critical to PostgreSQL starting.  
# They must be high enough for these settings:
# 	max_connections
# 	max_prepared_transactions
# 	shared_buffers
# 	wal_buffers
# 	max_fsm_relations
# 	max_fsm_pages

echo -n "Checking the current kernel's shared memory settings..."


# SHMMAX
#
# (BLOCK_SIZE + 208) * ((MAX_MEM * 1024) / PAGE_SIZE) * $SHARED_BUFFER_RATIO)

SHMMAX=`sysctl $SYSCTL_KERNEL_NAME.shmmax | cut -d'=' -f2`
OPTIMAL_SHMMAX=`echo "scale=0; (8192 + 208) * (($MAX_MEM * 1024) / $OS_PAGE_SIZE) * $SHARED_BUFFER_RATIO" | bc -l | cut -d'.' -f1`
if [ $SHMMAX -lt $OPTIMAL_SHMMAX ]; then
	sysctl $SYSCTL_KERNEL_NAME.shmmax=$OPTIMAL_SHMMAX
	echo "$SYSCTL_KERNEL_NAME.shmmax=$OPTIMAL_SHMMAX" >> /etc/sysctl.conf
fi


# SHMMNI
#
# 4096 - 8192

SHMMNI=`sysctl $SYSCTL_KERNEL_NAME.shmmni | cut -d'=' -f2`
OPTIMAL_SHMMNI=8192
if [ $SHMMNI -lt $OPTIMAL_SHMMNI ]; then
	sysctl $SYSCTL_KERNEL_NAME.shmmni=$OPTIMAL_SHMMNI
	echo "$SYSCTL_KERNEL_NAME.shmmni=$OPTIMAL_SHMMNI" >> /etc/sysctl.conf
fi


# SHMALL
#
# SHMMAX / PAGE_SIZE

SHMALL=`sysctl $SYSCTL_KERNEL_NAME.shmall | cut -d'=' -f2`
OPTIMAL_SHMALL=$(echo "scale=0; $SHMMAX / $OS_PAGE_SIZE" | bc -l | cut -d'.' -f1)
if [ $SHMALL -lt $OPTIMAL_SHMALL ]; then
	sysctl $SYSCTL_KERNEL_NAME.shmall=$OPTIMAL_SHMALL
	echo "$SYSCTL_KERNEL_NAME.shmall=$OPTIMAL_SHMALL" >> /etc/sysctl.conf
fi


# convert MAX_MEM to MB
MAX_MEM=$(echo "scale=0; $MAX_MEM/1024" | bc -l)
SHARED_BUFFERS=$(echo "scale=0; $MAX_MEM * $SHARED_BUFFER_RATIO" | bc -l | cut -d'.' -f1)MB


WAL_BUFFERS="1MB"
EFFECTIVE_CACHE_SIZE=$(echo "scale=0; $MAX_MEM * $EFFECTIVE_CACHE_RATIO" | bc -l | cut -d'.' -f1)MB


### NOW THE FUN STUFF!!
echo "Applying system configuration settings to the server..."


echo "This system appears to have $MAX_MEM MB maximum memory..."

if [ -e $CONFIG_FILE ]; then
	echo "Setting SHARED_BUFFERS to:       $SHARED_BUFFERS"
	echo "Setting WORK_MEM to:             $WORK_MEM"
	echo "Setting EFFECTIVE_CACHE_SIZE to: $EFFECTIVE_CACHE_SIZE"
	echo "Setting CHECKPOINT_SEGMENTS to:  $CHECKPOINT_SEG"
	echo "Setting MAINTENANCE_WORK_MEM to: $MAINT_WORK_MEM"
	echo "Setting WAL_BUFFERS to:          $WAL_BUFFERS"

	sed \
-e "s/[#]*shared_buffers = .*/shared_buffers = $SHARED_BUFFERS/" \
-e "s/[#]*work_mem = .*/work_mem = $WORK_MEM/" \
-e "s/[#]*effective_cache_size = .*/effective_cache_size = $EFFECTIVE_CACHE_SIZE/" \
-e "s/[#]*checkpoint_segments = .*/checkpoint_segments = $CHECKPOINT_SEG/" \
-e "s/[#]*maintenance_work_mem = .*/maintenance_work_mem = $MAINT_WORK_MEM/" \
-e "s/[#]*wal_buffers = .*/wal_buffers = $WAL_BUFFERS/" \
-e "s/[#]*cpu_tuple_cost = .*/cpu_tuple_cost = 0.002/" \
-e "s/[#]*cpu_index_tuple_cost = .*/cpu_index_tuple_cost = 0.0002/" \
-e "s/[#]*cpu_operator_cost = .*/cpu_operator_cost = 0.0005/" \
$CONFIG_FILE > $TEMP_FILE
	mv $TEMP_FILE $CONFIG_FILE

else
	echo "Unable to locate the PostgreSQL config file!  Can't continue!"
	exit 1
fi 

echo "Done!"
And the corresponding Linux init script:

Code:
#!/bin/bash

export PGDATA=/var/lib/postgresql/data

case "$1" in
	start)
		echo "Starting the PostgreSQL Server"
		su --command="/usr/bin/pg_ctl start -D $PGDATA " postgres
		if [ -f $PGDATA/postmaster.pid -a -w /proc/`cat $PGDATA/postmaster.pid`/oom_adj ]; then
			echo -17 >>/proc/`cat $PGDATA/postmaster.pid`/oom_adj
		fi
		sysctl -w vm.overcommit_memory=2
		;;
	stop)
		echo "Stopping the PostgreSQL Server"
		su --command="/usr/bin/pg_ctl stop -D $PGDATA" postgres
		;;
	force-stop)
                echo "Stopping the PostgreSQL Server"
                su --command="/usr/bin/pg_ctl stop -D $PGDATA -m fast" postgres
                ;;
	restart)
		echo "Restarting the PostgreSQL Server"
		su --command="/usr/bin/pg_ctl restart -D $PGDATA " postgres
		if [ -f $PGDATA/postmaster.pid -a -w /proc/`cat $PGDATA/postmaster.pid`/oom_adj ]; then
			echo -17 >>/proc/`cat $PGDATA/postmaster.pid`/oom_adj
		fi
		sysctl -w vm.overcommit_memory=2
		;;
	reload)
		echo "Reloading the PostgresSQL config"
		su --command="/usr/bin/pg_ctl reload" postgres
		;;
	status)
		echo "Status of PostgreSQL Server:"
		su --command="/usr/bin/pg_ctl status" postgres
		;;
	*)
		echo "Usage: /etc/init.d/postgresql {start|stop|force-stop|restart|reload|status}"
		;;
esac
exit 0
Views 1821 Comments 2
« Prev     Main     Next »
Total Comments 2

Comments

  1. Old Comment
    thank you!
    Posted 02-11-2010 at 04:54 AM by Web31337 Web31337 is offline
  2. Old Comment
    You're welcome, Web31337.
    Posted 02-11-2010 at 09:34 AM by rocket357 rocket357 is offline
 

  



All times are GMT -5. The time now is 11:56 AM.

Main Menu
Advertisement

Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration