Re: [GENERAL] Would my postgresql 8.4.12 profit from doubling RAM?
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=$(
Re: [GENERAL] max_connections
According to http://www.postgresql.org/docs/8.3/static/kernel-resources.html The maximum shared memory usage of a connection in bytes is 1800 + 270 * max_locks_per_transaction max_locks_per_transaction default is 64 19080 Bytes or .018 mb's per connection or 1.819 mb at 100 default connections With a Gig of Phsical Ram setting Shared Buffers to use 25% - 256 mb dedicated to Postgres default is using roughly 0.75% for connections You can extrapolate this out taking into consideration all your specific variables, total physical RAM, postgresql.conf settings etc but I wouldn;t run off to use pgpool unless your in an extremly connection heavy environment as it does add an additional layer within the client server connection and is another component to config and admin. -- View this message in context: http://postgresql.1045698.n5.nabble.com/max-connections-tp5722890p5722899.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "Too far out of the mainstream"
MySQL doesn't even support self referential updates like update t1 set c1 ='value' where t1.id not in (select id from t1 where id > 100); Nor is it fully ACID compliant. And its online documentation is a nightmare. PgAdmin is infintely better than mysql workbench, heck anything is better than MySQLWorkbench Postgres as of 9 will do pretty much anything Oracle or mssql will do minus robust tools (where mssql is a clear winner with ssrs and ssis and ssms). Oracles tools are coming around with developer, modeler, and analytics but really oracle is for when you need serious distributed transaction balancing via RAC. Honestly if your not using RAC there is no reason to use Oracle. So There is not one reason to go with MySQL over Postgres and tons of reason to use Postgres over MySQL, arrays, ORM, Tools, Documentation, Cross-Language Support, Faster, ACID compliant, etc And if you want a really rich toolset and you have bought into the .NET library model, which once you start digging is quite cool, go read petzolds DotNETZero, then go with mssql. And if your running a transaction volume to rival Amazon and want a db that can come as close to a true parrallel load balancing as RAC then fork aout the shiny and go with Oracle. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Too-far-out-of-the-mainstream-tp5722177p5722878.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables
How does postgres figure this out to throw the error msg?
create table test_table
(
column1 char(10),
column2 varchar(20)
) without oids;
create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql
select test1('1234567890','ABCDEFGHIJKLMNOPQRST')
select * from test_table;
-- 1234567890, ABCDEFGHIJKLMNOPQRST
select test1('this is way way longer than 10 characters','this is way way
way way way way way way way way way way longer than 20 characters')
ERROR: value too long for type character(10)
CONTEXT: SQL statement "insert into test_table values ($1, $2)"
PL/pgSQL function "test1" line 3 at SQL statement
** Error **
ERROR: value too long for type character(10)
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722876.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables
This is what I meant to post
drop table test_table;
create table test_table
(
column1 char(20),
column2 varchar(40)
) without oids;
drop function test1(char(10), varchar(20));
create or replace function test1(c1 char(10), c2 varchar(20))
returns void as
$$
BEGIN
insert into test_table values ($1, $2);
END
$$
language plpgsql
select
test1('12345678900123456789','ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD')
select * from test_table;
12345678900123456789, ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789ABCD
Just showing that it does indeed not use the length in at all, and this just
seems wrong. I can definetly see situations where someone would put a length
on a in put var and get an an unexpected result, like the one above.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722881.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables
Duh never mind I call brain cloud on that one, and thanks for all the help. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722880.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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" ]; the
[GENERAL] Re: Where is the char and varchar length in pg_catalog for function input variables
Yeah thats what I was starting to wonder if those lengths basically mean nothing. I am writing a ton of functions to unit test all of the functions in our app and am generating random strings and would like to pass the lengths to my random string generator so if it's varchar 50 I am generating a string between 0 and 50 length but since I can't find the length value I guess I am just going to put an arbitrary length in. Would be nice to know what exactly is going on when you have a length specified on an input variable in pg_catalog. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845p5722850.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Where is the char and varchar length in pg_catalog for function input variables
I have searched and searched and just cannot find the maximum lengths for
input variables in a function
i.e.
CREATE FUNCTION test(input1 char(5), input2 varchar(50))
RETURNS void AS
$$RAISE NOTICE('%,%'), $1, $2;$$
LANGUAGE plpgsql;
Where do I find the 5 and the 50 it has to be somewhere I have searched
through
pg_proc
pg_type
pg_attribute (whose attlen only relates to tables)
pg_type
and all possible manner of joining these tables.
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Where-is-the-char-and-varchar-length-in-pg-catalog-for-function-input-variables-tp5722845.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
