Re: Would my postgresql 8.4.12 profit from doubling RAM?

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.