Home > bash, Linux, PostgreSQL, Slackware > Install and configure pgbouncer version 1.5 at Slackware 12.0.0 and PostgreSQL 8.3.7

Install and configure pgbouncer version 1.5 at Slackware 12.0.0 and PostgreSQL 8.3.7

I had an issue with suddent spikes at the parallel db connections, which caused the Linux swap and memory out
Solution – sql optimisation, PostgreSQL tunning and pgbouncer implementation

postgresql.conf changes

root@darkwater:[Tue Feb 21 23:21:00]:[/opt/pgsql/data]$ diff postgresql.conf postgresql.conf.2012-02-20.22.59.32
106,107c106,107
< # shared_buffers = 32MB
< shared_buffers = 16MB			# min 128kB or max_connections*16kB
---
> 
> shared_buffers = 32MB			# min 128kB or max_connections*16kB
115c115
< maintenance_work_mem = 16MB		# min 1MB
---
> #maintenance_work_mem = 16MB		# min 1MB
166,168d165
< # 2012-02-20
< wal_buffers = 8MB
< synchronous_commit = off
213,214d209
< # 2012-02-20 
< effective_cache_size = 1024MB
240,243c235,236
< # 
< #log_destination = 'csvlog'		# Valid values are combinations of
< # 2012-02-20
< log_destination = 'stderr'		# Valid values are combinations of
---
> #
> log_destination = 'csvlog'		# Valid values are combinations of
422,425c415,416
< # 
< # 2012-02-20
< #autovacuum_vacuum_cost_delay = 20	# default vacuum cost delay for
< autovacuum_vacuum_cost_delay = 100	# default vacuum cost delay for
---
> #
> autovacuum_vacuum_cost_delay = 20	# default vacuum cost delay for
root@darkwater:[Tue Feb 21 23:21:11]:[/opt/pgsql/data]$ 

SQL optimization .. just explain analyze usage along with pg_stat_activity data.
usefull links :
http://www.revsys.com/writings/postgresql-performance.html
http://www.anchor.com.au/hosting/dedicated/Tuning_PostgreSQL_on_your_Dedicated_Server

pgbouncer install, the short version, it took me a bit longer to fix the dependencies :)
anyway, it was easy as the Linux is Slackware .. eveything works here if you have the proper source

cd /opt/installs
wget  http://ignum.dl.sourceforge.net/project/asciidoc/asciidoc/8.6.6/asciidoc-8.6.6.tar.gz
tar xvfz asciidoc-8.6.6.tar.gz
cd asciidoc-8.6.6/
./configure ; make -j3 install

wget --no-check-certificate  https://github.com/downloads/libevent/libevent/libevent-2.0.17-stable.tar.gz
tar xvfz libevent-2.0.17-stable.tar.gz
cd libevent-2.0.17-stable/
./configure; make -j3 install

wget  http://pgfoundry.org/frs/download.php/3197/pgbouncer-1.5.tar.gz
tar xvfz pgbouncer-1.5.tar.gz
cd pgbouncer-1.5/
./autogen.sh
./configure
make -j3 install

mkdir /etc/pgbouncer
cp /opt/installs/pgbouncer-1.5/etc/* /etc/pgbouncer/
chown -R dragkh:users /etc/pgbouncer/

#example working configuration 
 root@darkwater:[Tue Feb 21 23:32:03]:[/opt/installs]$ cat /etc/pgbouncer/pgbouncer.ini | grep -v "^;" | sed '/^ *$/d'
[databases]
injected = host=127.0.0.1 port=5432 dbname=dragkh
nondefaultdb = pool_size=50 reserve_pool=10
[pgbouncer]
logfile = pgbouncer.log
pidfile = pgbouncer.pid
listen_addr = 127.0.0.1
listen_port = 6543
user = dragkh 
auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = dune, dragkhhhh
stats_users = dune, dragkhhhh
pool_mode = statement
server_reset_query = DISCARD ALL
max_client_conn = 100
default_pool_size = 20
reserve_pool_size = 5
log_pooler_errors = 1
server_lifetime = 1200
server_idle_timeout = 60
server_connect_timeout = 15
server_login_retry = 15
query_wait_timeout = 10
client_login_timeout = 20
autodb_idle_timeout = 3600

#Slackware rc.d start script based on the provided bu the pgboncer debian scrip/opt/installs/pgbouncer-1.5/etc/example.debian.init.sh

root@darkwater:[Tue Feb 21 23:30:17]:[/opt/installs]$ cat /etc/rc.d/rc.pgbouncer
#!/bin/bash
#
# pgbouncer	Start the PgBouncer PostgreSQL pooler.
#
# The variables below are NOT to be changed.  They are there to make the
# script more readable.

NAME=pgbouncer
DAEMON=/usr/local/bin/pgbouncer
PIDFILE=/var/run/$NAME.pid
CONF=/etc/pgbouncer/$NAME.ini
OPTS="-d $CONF"
# note: SSD is required only at startup of the daemon.
SSD=`which start-stop-daemon`
export PATH=${PATH}::/opt/kannel.before.smpp/sbin
ENV="env -i LANG=C PATH=/bin:/usr/bin:/usr/local/bin"

trap "" 1

# Check if configuration exists
test -f $CONF || exit 0

case "$1" in
  start)
    echo -n "Starting server: $NAME"
    $ENV $SSD --start --pidfile $PIDFILE --exec $DAEMON -- $OPTS > /dev/null
    ;;

  stop)
    echo -n "Stopping server: $NAME"
    start-stop-daemon --stop --pidfile $PIDFILE 
    ;;

  reload | force-reload)
    echo -n "Reloading $NAME configuration"
    start-stop-daemon --stop --pidfile $PIDFILE --signal HUP
    ;;

  restart)
    $0 stop
    $0 start
    ;;

  *)
    echo "Usage: /etc/rc.d/$NAME {start|stop|reload|restart}"
    exit 1
    ;;
esac

if [ $? -eq 0 ]; then
	echo .
	exit 0
else
	echo " failed"
	exit 1
fi

#edited pgsql rc.d start/stop script to include the pgbouncer

root@darkwater:[Tue Feb 21 23:36:15]:[/opt/installs]$ cat /etc/rc.d/rc.pgsqld 
#! /bin/sh

# chkconfig: 2345 98 02
# description: PostgreSQL RDBMS

# This is an example of a start/stop script for SysV-style init, such
# as is used on Linux systems.  You should edit some of the variables
# and maybe the 'echo' commands.
#
# Place this file at /etc/init.d/postgresql (or
# /etc/rc.d/init.d/postgresql) and make symlinks to
#   /etc/rc.d/rc0.d/K02postgresql
#   /etc/rc.d/rc1.d/K02postgresql
#   /etc/rc.d/rc2.d/K02postgresql
#   /etc/rc.d/rc3.d/S98postgresql
#   /etc/rc.d/rc4.d/S98postgresql
#   /etc/rc.d/rc5.d/S98postgresql
# Or, if you have chkconfig, simply:
# chkconfig --add postgresql
#
# Proper init scripts on Linux systems normally require setting lock
# and pid files under /var/run as well as reacting to network
# settings, so you should treat this with care.

# Original author:  Ryan Kirkpatrick <pgsql@rkirkpat.net>

# $PostgreSQL: pgsql/contrib/start-scripts/linux,v 1.7 2004/10/01 18:30:21 tgl Exp $

## EDIT FROM HERE

# Installation prefix
prefix=/opt/pgsql

# Data directory
PGDATA="/opt/pgsql/data"

# Who to run the postmaster as, usually "postgres".  (NOT "root")
PGUSER=postgres

# Where to keep a log file
PGLOG="$PGDATA/serverlog"

## STOP EDITING HERE

# Check for echo -n vs echo \c
if echo '\c' | grep -s c >/dev/null 2>&1 ; then
    ECHO_N="echo -n"
    ECHO_C=""
else
    ECHO_N="echo"
    ECHO_C='\c'
fi

# The path that is to be used for the script
PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin

# What to use to start up the postmaster (we do NOT use pg_ctl for this,
# as it adds no value and can cause the postmaster to misrecognize a stale
# lock file)
DAEMON="$prefix/bin/postmaster"

# What to use to shut down the postmaster
PGCTL="$prefix/bin/pg_ctl"

port=5432

set -e

# Only start if we can find the postmaster.
test -x $DAEMON || exit 0

seikvars="-B 2000 -N 500"
# Parse command line parameters.
case $1 in
  start)
	$ECHO_N "Starting PostgreSQL: "$ECHO_C
	su - $PGUSER -c "$DAEMON ${seikvars} -p ${port} -D '$PGDATA' &" >>$PGLOG 2>&1
	echo "ok"
	/etc/rc.d/rc.pgbouncer start
	;;
  stop)
	echo -n "Stopping PostgreSQL: "
	su - $PGUSER -c "$PGCTL -p ${port} stop -D '$PGDATA' -s -m fast"
	echo "ok"
	/etc/rc.d/rc.pgbouncer stop
	;;
  restart)
	echo -n "Restarting PostgreSQL: "
	/etc/rc.d/rc.pgbouncer stop
	su - $PGUSER -c "$PGCTL -p ${port} stop -D '$PGDATA' -s -m fast -w"
	su - $PGUSER -c "$DAEMON ${seikvars} -p ${port} -D '$PGDATA' &" >>$PGLOG 2>&1
	/etc/rc.d/rc.pgbouncer start
	echo "ok"
	;;
  reload)
        echo -n "Reload PostgreSQL: "
        su - $PGUSER -c "$PGCTL  -p ${port} reload -D '$PGDATA' -s"
        echo "ok"
        ;;
  status)
	su - $PGUSER -c "$PGCTL  -p ${port} status -D '$PGDATA'"
	/etc/rc.d/rc.pgbouncer status
	;;
  *)
	# Print help
	echo "Usage: $0 {start|stop|restart|reload|status}" 1>&2
	exit 1
	;;
esac

exit 0

# pgbouncer consile usage:
dragkhhhh@[local]:6543::pgbouncer=[Tue Feb 21 23:39:50 EET 2012]# show help;                                                           
NOTICE:  Console usage
DETAIL:  
	SHOW HELP|CONFIG|DATABASES|POOLS|CLIENTS|SERVERS|VERSION
	SHOW STATS|FDS|SOCKETS|ACTIVE_SOCKETS|LISTS|MEM
	SHOW DNS_HOSTS|DNS_ZONES
	SET key = arg
	RELOAD
	PAUSE [<db>]
	RESUME [<db>]
	KILL <db>
	SUSPEND
	SHUTDOWN
SHOW

dragkhhhh@[local]:6543::pgbouncer=[Tue Feb 21 23:50:16 EET 2012]# show stats;
-[ RECORD 1 ]----+-----------
database         | pgbouncer
total_requests   | 3
total_received   | 0
total_sent       | 0
total_query_time | 0
avg_req          | 0
avg_recv         | 0
avg_sent         | 0
avg_query        | 0
-[ RECORD 2 ]----+-----------
database         | dragkh
total_requests   | 450333
total_received   | 216309096
total_sent       | 178535626
total_query_time | 2820410959
avg_req          | 5
avg_recv         | 2562
avg_sent         | 2314
avg_query        | 2230

Time: 0.119 ms


dragkhhhh@[local]:6543::pgbouncer=[Tue Feb 21 23:41:33 EET 2012]# show mem;
     name     | size | used | free | memtotal 
--------------+------+------+------+----------
 user_cache   |  156 |    5 |  100 |    16380
 db_cache     |  120 |    3 |  133 |    16320
 pool_cache   |  260 |    2 |   61 |    16380
 server_cache |  232 |    2 |   68 |    16240
 client_cache |  232 |   11 |   59 |    16240
 iobuf_cache  | 2060 |    1 |   49 |   103000
(6 rows)

Enjoy pgbouncer …:)

Slackware4Life

  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: