Hi. I'm jon.404, a Unix/Linux/Database/Openstack/Kubernetes Administrator, AWS/GCP/Azure Engineer, 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: *nix.
PostgreSQL reconfigure script
Posted 02-10-2010 at 09:54 AM by rocket357
Updated 03-04-2011 at 09:42 AM by rocket357 (Added Version changelog to script)
Updated 03-04-2011 at 09: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!).
And the corresponding Linux init script:
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!"
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
Total Comments 2
Comments
-
thank you!
Posted 02-11-2010 at 03:54 AM by Web31337 -
You're welcome, Web31337.
Posted 02-11-2010 at 08:34 AM by rocket357