Our team has been excited ever since IBM and Enterprise DB
announced availability of Postgres Plus Advanced Server on PowerLinux.
Our team recently started engaging with IBM to evaluate the scalability of PPAS on PowerLinux and to determine how to fine-tune such setups.
(It's a work-in-progress and I am not sure how much I can
share here. But if you are thinking of migrating your IT infrastructure to a
much more cost effective stack of Power PC running RHEL and using EnterpriseDB
PPAS/PostgreSQL as database please contact me or write to success@ashnik.com).
Though I really welcome this new announcement from
EnterpriseDB, one thing which disappointed me was non-availability of pgBouncer
in the rpm installers provided by EnterpriseDB for PPAS on PowerLinux.
I have always preferred pgBouncer as a connection pooling
tool over pgpool. It's light weight and quite easy to configure. In the past I had
done some benchmarking study to see how PPAS scales with and without connection
pooling. I had seen some significant gain and hence decided to install
pgBouncer before I could to proceed with the performance measurement of PPAS on
PowerLinux.
I faced a few issues but could finally install it and get it
running. So I decided to share my experience of the same (which can hopefully help
someone, as more and more people try to move away from costly hardware and
proprietary database to PPAS on PowerLinux).
First thing that one should do is, make sure you have IBM
Advanced ToolChain installed on your PowerLinux box.
As per my understanding, the compiler which is part IBM
Advanced ToolChain is supposed to be optimized for Power PC. So it's preferred
for code compilation instead of using gcc compiler shipped with Red Hat.
Generally it will be installed in /opt/atXX
So I set my path to include Advanced Toolchain:
[root@pflex75 pgbouncer]#
export PATH=/opt/at7.0/bin:$PATH
I just wanted to be sure about my version of gcc compiler
[root@pflex75 pgbouncer]# which gcc
/opt/at7.0/bin/gcc
[root@pflex75 pgbouncer]# gcc --version
gcc (GCC) 4.8.2 20130819 (Advance-Toolchain-at7.0)
[ibm/gcc-4_8-branch, revision: 201854 merged from gcc-4_8-branch, revision
200989]
Copyright (C) 2013 Free Software Foundation, Inc.
This is free software; see the source for copying conditions. There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR
PURPOSE.
I downloaded the latest version of pgbouncer (for me its
1.5.4) from pgfoundries site and extracted the content of the tar file
[root@pflex75 pgbouncer]# tar -xzvf pgbouncer-1.5.4.tar.gz
Now was the time to configure the binaries and install them
as per the instruction in README file which is extracted from above tar. So I
fired below commands:
[root@pflex75 pgbouncer]# cd pgbouncer-1.5.4
[root@pflex75 pgbouncer-1.5.4]# ./configure --prefix=/usr/local
--with-libevent=libevent-prefix
[root@pflex75 pgbouncer-1.5.4]# make
[root@pflex75 pgbouncer-1.5.4]# make install
[root@pflex75 pgbouncer-1.5.4]# cd ..
Now I had to setup the pgBouncer and start it.
So I coped the pgbouncer.ini file to a location of my choice
(/etc/pgbouncer)
[root@pflex75 pgbouncer]# mkdir -p /etc/pgbouncer
[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf
/etc/pgbouncer
[root@pflex75 pgbouncer]# cp
/usr/local/share/doc/pgbouncer/pgbouncer.ini /etc/pgbouncer/pgbouncer.ini
Then I created a userlist.txt in a location of my choice (once
again /etc/pgbouncer):
[root@pflex75 pgbouncer]#
cat /etc/pgbouncer/userlist.txt
"enterprisedb"
"password"
Now I followed all the standard steps to edit the pgbouncer
to add all the address and databases. Then I went ahead to set pool size etc
and then set path for userlist file.
In pgbouncer.ini file, I checked the value for logfile and
pidfile. I had to ensure that these locations are present and owned by proper
user (the user which I will be using for running pgbouncer must have
permissions to run them).
logfile
= /var/log/pgbouncer/pgbouncer.log
pidfile
= /var/run/pgbouncer/pgbouncer.pid
[root@pflex75 pgbouncer]# mkdir -p /var/log/pgbouncer;
[root@pflex75 pgbouncer]# touch /var/log/pgbouncer/pgbouncer.log;
[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf
/var/log/pgbouncer
[root@pflex75 pgbouncer]# mkdir -p /var/run/pgbouncer/
[root@pflex75 pgbouncer]# chown enterprisedb:enterprisedb -Rf
/var/run/pgbouncer
But once I tried to run pgbouncer, I got below issue
[root@pflex75 pgbouncer]# pgbouncer
-d /etc/pgbouncer/pgbouncer.ini
pgbouncer: error while loading shared libraries: libevent-2.0.so.5:
cannot open shared object file: No such file or directory
To resolve (I followed below steps, but may be one can just
resolve it by setting LD_LIBRARY):
First thing I did was removing the existing installation:
[root@pflex75 pgbouncer]# rm
/usr/local/bin/pgbouncer
[root@pflex75 pgbouncer]# rm
/usr/local/share/man/man5/pgbouncer.5
[root@pflex75 pgbouncer]# rm
/usr/local/share/man/man1/pgbouncer.1
[root@pflex75 pgbouncer]# rm -rf /usr/local/share/doc/pgbouncer
Then I removed the installer I had extracted and then extracted
it afresh:
[root@pflex75 pgbouncer-1.5.4]# cd .. ; rm -rf pgbouncer-1.5.4
[root@pflex75 pgbouncer]# tar -xzvf pgbouncer-1.5.4.tar.gz
I made sure you have latest libevent was installed:
[root@pflex75 pgbouncer]# yum info libevent
Loaded
plugins: product-id, refresh-packagekit, security, subscription-manager
This
system is not registered to Red Hat Subscription Management. You can use
subscription-manager to register.
Installed
Packages
Name : libevent
Arch : ppc64
Version : 1.4.13
Release :
4.el6
Size : 335 k
Repo : installed
From
repo :
anaconda-RedHatEnterpriseLinux-201301301505.ppc64
Summary : Abstract asynchronous event notification
library
URL : http://monkey.org/~provos/libevent/
License : BSD
Description
: The libevent API provides a mechanism to execute a callback function
: when a specific event occurs on a file
descriptor or after a timeout
: has been reached. libevent is meant to
replace the asynchronous event
: loop found in event driven network
servers. An application just needs
: to call event_dispatch() and can then add
or remove events dynamically
: without having to change the event loop.
Available
Packages
Name : libevent
Arch : ppc
Version : 1.4.13
Release : 4.el6
Size : 66 k
Repo : local
Summary : Abstract asynchronous event notification
library
URL : http://monkey.org/~provos/libevent/
License : BSD
Description
: The libevent API provides a mechanism to execute a callback function
: when a specific event occurs on a file
descriptor or after a timeout
: has been reached. libevent is meant to
replace the asynchronous event
: loop found in event driven network
servers. An application just needs
: to call event_dispatch() and can then add
or remove events dynamically
: without having to change the event loop.
Then I set the value for LD_LIBRARY_PATH parameter to look
at /usr/lib64 (for power64 system it will have the libevent)
[root@pflex75 pgbouncer-1.5.4]# export
LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH
I created an appropriate Softlink for Libevent (I believe
this step was not needed)
[root@pflex75 pgbouncer-1.5.4]# ln ln -s /usr/lib64/libevent-1.4.so.2 /usr/local/lib
[root@pflex75 pgbouncer-1.5.4]# ./configure --prefix=/usr/local
--with-libevent=/usr/lib64
[root@pflex75 pgbouncer-1.5.4]# make
[root@pflex75 pgbouncer-1.5.4]# make install
[root@pflex75 ~]# pgbouncer --help
Usage: pgbouncer [OPTION]... config.ini
-d, --daemon Run in background (as a daemon)
-R, --restart Do a online restart
-q, --quiet Run quietly
-v, --verbose Increase verbosity
-u,
--user=<username> Assume identity
of <username>
-V, --version Show version
-h, --help Show this help screen and exit
Now switch user to enterprisedb (or any user which you wish
to use for running pgbouncer)
[root@pflex75 ~]# su - enterprisedb
-bash-4.1$ pgbouncer -d
/etc/pgbouncer/pgbouncer.ini
pgbouncer: error while loading shared libraries: libevent-2.0.so.5:
cannot open shared object file: No such file or directory
Ahhhha!!! Isn’t that the same error I just resolved? So I
added below line in .bash_profile:
export
LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH
Now apply the same for current shell environment
-bash-4.1$ export LD_LIBRARY_PATH=/usr/lib64:$LD_LIBRARY_PATH
Try again:
-bash-4.1$ pgbouncer -d
/etc/pgbouncer/pgbouncer.ini
2013-10-25 19:31:40.325 11412 LOG File descriptor limit: 1024
(H:4096), max_clie nt_conn: 200, max fds
possible: 250
-bash-4.1$ ps -ef | grep -i pgbouncer
495 14384 1 0
19:41 ? 00:00:00 pgbouncer -d
/etc/pgbouncer/pgbouncer.ini
Wow!!! Issue resolved!!! :-)
A few more steps which won't harm (just to ensure
permissions)
-bash-4.1$ chown enterprisedb:enterprisedb -Rf
/usr/local/share/man/man1/pgbouncer.1
-bash-4.1$ chown enterprisedb:enterprisedb -Rf
/usr/local/share/man/man5/pgbouncer.5
-bash-4.1$ chown enterprisedb:enterprisedb -Rf
/usr/local/share/doc/pgbouncer
