Here is a bash script I wrote to print out mem config ffrom postgresconf.sql and os (centos 5.5 in this case). According to Gregory Smith in Postgresql 9.0 shared buffers should be appx 25-40% of avail Physical RAM. Also considerPostgres uses the OS Buffer as it access the physical data and log files and while doing so has the potential to double buffer blocks. WorkMEM is suggested at 5% but if you do alot of CLUSTER/ANALYZE/VACUUM you will want to up this, I usually round off to the highest power of 2 is 5% is 328mb i'll set it to 512. Most of the conversions are done in the script and the Shared Memory checks are just that, checks, a modern OS should be way above pg required kernel settings. also look at your ipcs -m this will show you the shared memory in use and is you have other processes aside from postgres using shared memory. I also have a 9.0 script if anyone wants it. # # Postgresql Memory Configuration and Sizing Script # By: James Morton # Last Updated 06/18/2012 # # Note This script is meant to be used with by the postgres user with a configured .pgpass file # It is for Postgres version 8 running on Linux and only tested on Centos 5 # # Reference - http://www.postgresql.org/docs/8.0/static/kernel-resources.html # # This script should be run after changing any of the following in the postgresconf.sql # # maximum_connections # block_size # 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 #Shared Buffers PG_SHABUF=$($PSQL -t -c "show shared_buffers;") echo "PG_SHABUF:" $PG_SHABUF #maintainance_work_mem PG_MNTWKM=$($PSQL -t -c "show maintenance_work_mem;") echo "PG_MNTWKM:"$PG_MNTWKM #work_mem PG_WRKMEM=$($PSQL -t -c "show work_mem;") echo "PG_WRKMEM:"$PG_WRKMEM echo echo echo "Kernel Shared Memory Settings" echo 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 #Estimate SHMMAX per Postgresql 8.0 table 16-2 SHMMAX_MAXCON=$(( PG_MAXCON * 14541 )) #echo "SHMMAX_MAXCON:" $SHMMAX_MAXCON SHMMAX_SHABUF=$(( PG_SHABUF * 9832 )) #echo "SHMMAX_SHABUF:" $SHMMAX_SHABUF PG_REC_SHMMAX_TOTAL_B=$(( 256000 + SHMMAX_MAXCON + SHMMAX_SHABUF )) #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 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 echo CUR_SHMALL=$(cat /proc/sys/kernel/shmall) #note: SHMALL on CENTOS is in Bytes #echo "CUR_SHMALL:" $CUR_SHMALL CUR_SHMALL_IN_MB=$(( (CUR_SHMALL / 1024) / 1024 )) echo "CUR_SHMALL_IN_MB:" $CUR_SHMALL_IN_MB 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 echo CUR_SHMMNI=$(cat /proc/sys/kernel/shmmni) echo "CUR_SHMMNI:" $CUR_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 echo echo echo "Kernel Semaphore Settings" echo CUR_SEMMNI=$( cat /proc/sys/kernel/sem | awk '{print $4}' ) echo "CUR_SEMMNI:" $CUR_SEMMNI PG_RECSET_SEMMNI=$(printf "%.0f" $(echo "scale=2;($PG_MAXCON) / 16" | bc)) echo "PG_RECSET_SEMMNI:" $PG_RECSET_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 echo CUR_SEMMNS=$( cat /proc/sys/kernel/sem | awk '{print $2}' ) echo "CUR_SEMMNS:" $CUR_SEMMNS PG_RECSET_SEMMNS=$(printf "%.0f" $(echo "scale=2;(($PG_MAXCON) / 16)*17" | bc)) echo "PG_RECSET_SEMMNS:" $PG_RECSET_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 echo CUR_SEMMSL=$( cat /proc/sys/kernel/sem | awk '{print $1}' ) echo "CUR_SEMMSL:" $CUR_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 echo echo "Memory Sizing" echo OS_PAGE_SIZE=$(getconf PAGE_SIZE) echo "OS_PAGE_SIZE:" $OS_PAGE_SIZE OS_PHYS_PAGES=$(getconf _PHYS_PAGES) echo "OS_PHYS_PAGES:" $OS_PHYS_PAGES OS_TOTAL_MEM_IN_MB=$(( ((OS_PAGE_SIZE * OS_PHYS_PAGES) / 1024) / 1024 )) echo "OS_TOTAL_MEM_IN_MB:" $OS_TOTAL_MEM_IN_MB echo CUR_SHABUF_MB=$(( PG_SHABUF * 8192 / 1024 / 1024)) echo "CUR_SHABUF_MB:" $CUR_SHABUF_MB SHABUF_RECSET_IN_MB=$(printf "%.0f" $(echo "scale=2;$OS_TOTAL_MEM_IN_MB*.25" | bc)) echo "SHABUF_RECSET_IN_MB:"$SHABUF_RECSET_IN_MB SHABUF_RECSET=$(( SHABUF_RECSET_IN_MB * 1024 / 8 )) echo "SHABUF_RECSET:" $SHABUF_RECSET echo PG_MNTWKM_IN_MB=$(( PG_MNTWKM / 1024 )) echo "PG_MNTWKM_IN_MB:" $PG_MNTWKM_IN_MB RECSET_MNTWKM_MB=$(printf "%.0f" $(echo "scale=2;$OS_TOTAL_MEM_IN_MB*.05" | bc)) echo "RECSET_MNTWKM_MB:" $RECSET_MNTWKM_MB RECSET_MNTWKM=$(( RECSET_MNTWKM_MB * 1024 )) echo "RECSET_MNTWKM:" $RECSET_MNTWKM echo IPCS_STRING=$(ipcs -m | grep postgres | awk '{print $5}') let CUR_IPCS_PG_SHAMEMSEG_MB=0 for token in $IPCS_STRING; do CUR_IPCS_PG_SHAMEMSEG_MB=$(( CUR_IPCS_PG_SHAMEMSEG_MB + token ));done; CUR_IPCS_PG_SHAMEMSEG_MB=$(( $CUR_IPCS_PG_SHAMEMSEG_MB / 1024 / 1024 )) echo "CUR_IPCS_PG_SHAMEMSEG_MB:" $CUR_IPCS_PG_SHAMEMSEG_MB POTENTIAL_WORKMEM_MB=$(( PG_WRKMEM * PG_MAXCON / 1024 )) echo "POTENTIAL_WORKMEM_MB:" $POTENTIAL_WORKMEM_MB echo PG_TOTAL_CUR_MEM_MB=$(( CUR_IPCS_PG_SHAMEMSEG_MB + POTENTIAL_WORKMEM_MB + PG_MNTWKM_IN_MB )) echo "PG_TOTAL_CUURENT_POTENTIAL_MEM_MB:" $PG_TOTAL_CUR_MEM_MB PG_TOTAL_RECSET_MEM_MB=$(( SHABUF_RECSET_IN_MB + POTENTIAL_WORKMEM_MB + RECSET_MNTWKM_MB )) echo "PG_TOTAL_RECSET_POTENTIAL_MEM_MB:" $PG_TOTAL_RECSET_MEM_MB -- View this message in context: http://postgresql.1045698.n5.nabble.com/Would-my-postgresql-8-4-12-profit-from-doubling-RAM-tp5721879p5722887.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
