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 : https://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 : https://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= Assume identity of
-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