Here is the 9.0 versionand yes I meant maintenance_work_mem # Postgresql Memory Configuration and Sizing Script # By: James Morton # Last Updated 05/16/2012 # # Note This script is meant to be used with by the postgres user with a configured .pgpass file # It is for Postgres version 9 running on Linux and only tested on Centos 5 # # Reference - http://eee.postgresql.org/docs/9.1/static/kernel-resources.html # # This script should be run after changing any of the following in the postgresconf.sql # # maximum_connections # block_size # wal_block_size # wal_buffers # max_locks_per_transaction # max_prepared_transactions # shared_buffers # # or after changing the following OS kernel values # # SHMMAX # SHMALL # SHMMNI # SEMMNS # SEMMNI # SEMMSL #!/bin/bash #Input Variables DBNAME=$1 USERNAME=$2 clear echo echo "Postgresql Shared Memory Estimates" echo echo echo "Local Postgres Configuration settings" echo #Postgresql Version PSQL="psql "$DBNAME" -U "$USERNAME PG_VERSION=$($PSQL --version) echo "PG_VERSION:"$PG_VERSION #Postgresql Block Size PG_BLKSIZ=$($PSQL -t -c "show block_size;") echo "PG_BLKSIZ:"$PG_BLKSIZ #Maximum Connections PG_MAXCON=$($PSQL -t -c "show max_connections;") echo "PG_MAXCON:"$PG_MAXCON #Maximum Locks per Tansaction PG_MAXLPT=$($PSQL -t -c "show max_locks_per_transaction;") echo "PG_MAXLPT:"$PG_MAXLPT #Maximum Prepared Transactions, 2 phase commit, might not configured in postresql.conf let PG_MAXPRT=0 PG_MAXPRT=$($PSQL -t -c "show max_prepared_transactions;") echo "PG_MAXPRT:"$PG_MAXPRT #Shared Buffers PG_SHABUF=$($PSQL -t -c "show shared_buffers;") PG_SHABUF=$(echo $PG_SHABUF | sed s/MB//) echo "PG_SHABUF:" $PG_SHABUF PG_SHABUF_IN_B=$(( $PG_SHABUF * 1024 * 1024 )) echo "PG_SHABUF_IN_B:"$PG_SHABUF_IN_B PG_SHABUF_NUMOFBUF=$(($PG_SHABUF_IN_B / $PG_BLKSIZ)) echo "PG_SHABUF_NUMOFBUF:"$PG_SHABUF_NUMOFBUF #WAL Block Size PG_WALBLK=$($PSQL -t -c "show wal_block_size;") echo "PG_WALBLK:"$PG_WALBLK #WAL Buffers PG_WALBUF=$($PSQL -t -c "show wal_buffers;") PG_WALBUF=$(echo $PG_WALBUF | sed s/MB//) echo "PG_WALBUF:" $PG_WALBUF PG_WALBUF_IN_B=$(( $PG_WALBUF * 1024 * 1024 )) echo "PG_WALBUF_IN_B:"$PG_WALBUF_IN_B PG_WALBUF_NUMOFBUF=$(($PG_WALBUF_IN_B / $PG_WALBLK)) echo "PG_WALBUF_NUMOFBUF:"$PG_WALBUF_NUMOFBUF #Autovacuum workers PG_ATVWRK=$($PSQL -t -c "show autovacuum_max_workers;") echo "PG_ATVWRK:"$PG_ATVWRK #maintainance_work_mem PG_MNTWKM=$($PSQL -t -c "show maintenance_work_mem;") PG_MNTWKM=$(echo $PG_MNTWKM | sed s/MB//) echo "PG_MNTWKM:"$PG_MNTWKM #effective_cache_size PG_EFCHSZ=$($PSQL -t -c "show effective_cache_size;") echo "PG_EFCHSZ:"$PG_EFCHSZ echo echo "OS Memory settings" echo PAGE_SIZE=$(getconf PAGE_SIZE) echo "PAGE_SIZE:"$PAGE_SIZE PHYS_PAGES=$(getconf _PHYS_PAGES) echo "PHYS_PAGES:"$PHYS_PAGES TOTAL_MEM_IN_MB=$(( ((PAGE_SIZE * PHYS_PAGES) / 1024) / 1024 )) echo "TOTAL_MEM_IN_MB:"$TOTAL_MEM_IN_MB echo echo "Current Kernel Shared Memory Settings" echo #get os mem settings into vars CUR_SHMMAX_IN_B=$(cat /proc/sys/kernel/shmmax) echo "CUR_SHMMAX_IN_B:"$CUR_SHMMAX_IN_B CUR_SHMMAX_IN_MB=$(( (CUR_SHMMAX_IN_B / 1024) / 1024 )) echo "CUR_SHMMAX_IN_MB:"$CUR_SHMMAX_IN_MB CUR_SHMALL=$(cat /proc/sys/kernel/shmall) echo "CUR_SHMALL:" $CUR_SHMALL CUR_SHMALL_IN_MB=$(( (CUR_SHMALL / 1024) / 1024 )) echo "CUR_SHMALL_IN_MB:"$CUR_SHMALL_IN_MB CUR_SHMMNI=$(cat /proc/sys/kernel/shmmni) echo "CUR_SHMMNI:" $CUR_SHMMNI echo echo "Current Kernel Semaphore Settings" echo CUR_SEMMNI=$( cat /proc/sys/kernel/sem | awk '{print $4}' ) echo "CUR_SEMMNI:"$CUR_SEMMNI CUR_SEMMNS=$( cat /proc/sys/kernel/sem | awk '{print $2}' ) echo "CUR_SEMMNS:"$CUR_SEMMNS CUR_SEMMSL=$( cat /proc/sys/kernel/sem | awk '{print $1}' ) echo "CUR_SEMMSL:"$CUR_SEMMSL PG_RECSET_SEMMNI=$(printf "%.0f" $(echo "scale=2;($PG_MAXCON+$PG_ATVWRK+4) / 16" | bc)) echo "PG_RECSET_SEMMNI:"$PG_RECSET_SEMMNI PG_RECSET_SEMMNS=$(printf "%.0f" $(echo "scale=2;(($PG_MAXCON+$PG_ATVWRK+4) / 16)*17" | bc)) echo "PG_RECSET_SEMMNS:"$PG_RECSET_SEMMNS echo echo "Estimate SHMMAX per Postgresql 9.1 Doc - Table 17-2" echo SHMMAX_MAXCON=$(( PG_MAXCON * (1800 + 270 * PG_MAXLPT) )) echo "SHMMAX_MAXCON:"$SHMMAX_MAXCON SHMMAX_ATVWRK=$(( PG_ATVWRK * (1800 + 270 * PG_MAXLPT) )) echo "SHMMAX_ATVWRK:"$SHMMAX_ATVWRK SHMMAX_MAXPRT=$(( PG_MAXPRT * (770 + 270 * PG_MAXLPT) )) echo "SHMMAX_MAXPRT:"$SHMMAX_MAXPRT SHMMAX_SHABUF=$(( PG_SHABUF_NUMOFBUF * (PG_BLKSIZ + 208) )) echo "SHMMAX_SHABUF:"$SHMMAX_SHABUF SHMMAX_WALBUF=$(( PG_WALBUF_NUMOFBUF * (PG_WALBLK + 8) )) echo "SHMMAX_WALBUF:"$SHMMAX_WALBUF PG_REC_SHMMAX_TOTAL_B=$(( 788480 + SHMMAX_MAXCON + SHMMAX_ATVWRK + SHMMAX_MAXPRT + SHMMAX_SHABUF + SHMMAX_WALBUF )) echo "PG_REC_SHMMAX_TOTAL_B:"$PG_REC_SHMMAX_TOTAL_B PG_REC_SHMMAX_TOTAL_MB=$(( (PG_REC_SHMMAX_TOTAL_B / 1024) / 1024 )) echo "PG_REC_SHMMAX_TOTAL_MB:"$PG_REC_SHMMAX_TOTAL_MB echo echo "-checking ipcs -m, postgres should be running" CUR_IPCS_PG_SHAMEMSEG=$(ipcs -m | grep postgres | awk '{print $5}') CUR_IPCS_PG_SHAMEMSEG_MB=$(( $CUR_IPCS_PG_SHAMEMSEG / 1024 / 1024 )) echo "CUR_IPCS_PG_SHAMEMSEG_MB:" $CUR_IPCS_PG_SHAMEMSEG_MB "Should be equal or very close to the recommended SHMMAX" echo echo "Shared Memory Kernel Checks" echo #SHMMAX if [ "$PG_REC_SHMMAX_TOTAL_B" -lt "$CUR_SHMMAX_IN_B" ]; then echo "SHMMAX is within Postgresql's needs" elif [ "$PG_REC_SHMMAX_TOTAL_B" -ge "$CUR_SHMMAX_IN_B" ]; then echo "SHMMAX should be set greater than $PG_REC_SHMMAX_TOTAL_B" else echo "SHHMAX setting cannot be determined" fi #SHMALL - note: SHMALL on CENTOS is in Bytes if [ "$PG_REC_SHMMAX_TOTAL_B" -lt "$CUR_SHMALL" ]; then echo "SHMALL is within Postgresql's needs" elif [ "$PG_REC_SHMMAX_TOTAL_B" -ge "$CUR_SHMALL" ]; then echo "SHMALL should be set greater than $PG_REC_SHMMAX_TOTAL_B" else echo "SHMALL setting cannot be determined" fi #SHMMNI if [ "$CUR_SHMMNI" -ge 1 ]; then echo "SHMMNI is within Postgresql's needs" elif [ "$CUR_SHMMNI" -lt 1 ]; then echo "SHMMNI should be set greater than 1" else echo "SHMMNI setting cannot be determined" fi #SEMMNI if [ "$CUR_SEMMNI" -ge "$PG_RECSET_SEMMNI" ]; then echo "SEMMNI is within Postgresql's needs" elif [ "$CUR_SEMMNI" -lt "$PG_RECSET_SEMMNI" ]; then echo "SEMMNI should be set greater than or equal to $PG_RECSET_SEMMNI" else echo "SEMMNI setting cannot be determined" fi #SEMMNS if [ "$CUR_SEMMNS" -ge "$PG_RECSET_SEMMNS" ]; then echo "SEMMNS is within Postgresql's needs" elif [ "$CUR_SEMMNS" -lt "$PG_RECSET_SEMMNS" ]; then echo "SEMMNS should be set greater than or equal to $PG_RECSET_SEMMNS" else echo "SEMMNS setting cannot be determined" fi #SEMMSL if [ "$CUR_SEMMSL" -ge 17 ]; then echo "SEMMSL is within Postgresql's needs" elif [ "$CUR_SEMMSL" -lt 17 ]; then echo "SEMMSL should be set greater than or equal to 17" else echo "SEMMSL setting cannot be determined" fi echo echo "Estimate Total Memory Sizing" echo echo "Note: Postgres should be running for these numbers to be meaningful" echo echo $TOTAL_MEM_IN_MB "MB Total Physical System RAM" echo CUR_IPCS_SHAMEM=$(ipcs -m | grep postgres | awk '{print $5}') CUR_IPCS_SHAMEM_IN_MB=$(( $CUR_IPCS_SHAMEM / 1024 / 1024 )) #echo "CUR_IPCS_SHAMEM_IN_MB:"$CUR_IPCS_SHAMEM_IN_MB SHABUF_RECSET_IN_MB=$(printf "%.0f" $(echo "scale=2;$TOTAL_MEM_IN_MB*.25" | bc)) #echo "SHABUF_RECSET_IN_MB:"$SHABUF_RECSET_IN_MB SHABUF_RECSET_IN_MB_MAX=$(printf "%.0f" $(echo "scale=2;$TOTAL_MEM_IN_MB*.4" | bc)) #echo "SHABUF_RECSET_IN_MB_MAX:"$SHABUF_RECSET_IN_MB_MAX echo shared_buffers: $PG_SHABUF" MB current postgresql.conf setting" echo shared_buffers: $SHABUF_RECSET_IN_MB" MB Normal recomended setting (25% of Physical Ram)" echo shared_buffers: $SHABUF_RECSET_IN_MB_MAX" MB Agressive setting (40% of Physical Ram)" echo MNTWKM_RECSET_IN_MB=$(printf "%.0f" $(echo "scale=2;$TOTAL_MEM_IN_MB*.05" | bc)) #echo "MNTWKM_RECSET_IN_MB:"$MNTWKM_RECSET_IN_MB echo maintainance_worker_mem: $PG_MNTWKM "MB current postgresql.conf setting" echo maintainance_worker_mem: $MNTWKM_RECSET_IN_MB "MB Normal recommended setting (5% of physical Ram)" #OS_MEM_FREE=$(cat /proc/meminfo | grep MemFree | awk '{print $2}') #echo "OS_MEM_FREE:"$OS_MEM_FREE #OS_MEM_CACHED=$(cat /proc/meminfo | grep Cached | awk '{print $2}' | head -n1) #echo "OS_MEM_CACHED:"$OS_MEM_CACHED #EFCHSZ_RECSET=$(( (OS_MEM_FREE + OS_MEM_CACHED) / 1024 + PG_SHABUF )) #echo effective_cache_size: -- View this message in context: http://postgresql.1045698.n5.nabble.com/Would-my-postgresql-8-4-12-profit-from-doubling-RAM-tp5721879p5724078.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
