Expert Software Engineering Services
With the setup of a nice little postgres replication cluster (or specifically, a cluster of clusters!), all that we have really achieved is a fancy database backup and redundancy system. Absolutely nothing wrong with that! Should the master go down, we can have a slave take over and with a little tweaking, even become the master itself. However we are still putting the entire load of connections on one database (the master), all the while three other databases are sitting there twiddling their thumbs. What would be really nice is to spread out the responsibilities to all of the databases in the cluster so they are all performing selects. I am also a miserly sort and want to reuse my connections to said databases rather than constantly creating a new connection for every request. What we need is some kind of connection pooling or load balancing ...
This is a different ask than say a message broker or queue manager. What we want for now is a connection cacher and pooler which would manage connections to databases, order selects and return results, distributing the requests across available nodes rather than storing a stack of queries and farming them out one by one (an example would be celery with redis / rabbitmq etc.). There are a couple of options, pgbouncer, pgpool II, HAProxy.
I decided to go with pgpool II since after a lot of research and investigation, it fit my requirements the best (load balancing, connection pooling, and perhaps in the future parallel queries for stupid large data requests). Pgbouncer seems more tuned to the connection pooling aspect and HAProxy is more of a general purpose load balancer. Obviously this is up to your personal taste, requirements and patience (none of these have the friendly bootstrappy documentation and get used to reading forum posts and ticket threads). A popular setup is a combination of pgbouncer and HAProxy to distribute queries and reuse connections. However I am primarily doing this as a learning experience and out of a desire to make sure my resources are being used (note, not going for efficiency per se. right now, mostly to make sure processes aren't always idle). Pgpool II also has it's own replication, streaming and failover functionality should you want that.
Something to note here would be that the setup I intend to use might not really be a good setup for a production environment. The application (nginx webserver) is on machine A, the pooler / balancer (Pgpool II) are on machine B, and database servers (postgresql 9.5) are on machines A, B, C and D. All the machines have a wired connection through a router. An application (for example a website) connecting to a remote pooler server entails more overheads than a pooler on the same server as the application as well as having other unintended and hard to trace issues (for example potentially invalidating MAC addresses etc. for connection caching).
So what is connection pooling? If you have an application that connects to a database, there is a bunch of stuff that happens under the hood in order for that connection to occur. Think about it like picking up a package (for a SELECT) from a big, inner city apartment building. You first have to create a bike (connection object), ride to the building (the server), pass a doorman (SSH, SSL, firewall), go to the particular apartment, knock on the door, state your name (Dave’s not here, man (pg_hba)), give the password, pick up the package, leave the building, and then destroy the bike you rode to the building on. If your application is doing this every time it needs something from the database, that can use a lot of memory and take a lot of time. A better way is to do this once every now and then, keep the door open, and request data from an open, existing connection. You can also have many of these open doors running concurrently, so that if one door has someone in front of it, that request can just move to the next open door. A lot of web frameworks have support for this functionality built in, however many don't, need special configuration or have patchy support for it (django being one of them in past versions). There is a difference between a persistent connection and connection pooling, where a persistent connection is mostly just one open connection, whereas a pool is just that, a bag of open connections that an application can choose from to use to retrieve data from. Something to note here is that the web framework and postgresql native pooling functionality is probably fine under small load, so as always premature optimisation (some love for c2) is worth considering.
What is load balancing? Well in this context, load balancing is making sure that all nodes or servers in the cluster aren't being overloaded, or in more nuanced situations, specifying which 'weights' each node should receive in terms of burden. We want to distribute all the 'apartment package pickups' depending on which apartments are most efficient handing them over. For example if node 1 is a super machine and node 2 is a bit more 'out to pasture' but still capable, one can specify that node 1 take 75% of the requests and the rest go to the second node. This is very useful in times of high load since one database doesn't become overwhelmed with requests. If the nodes are of equal prowess then a load balancer can work both nodes equally through 'round robin' or random database selection (as far as I understand it, Pgpool II uses a randomised node selection with the weights taken into consideration).
Ok, all the philosophy aside, time to configure and set this up. First of all, we should create a pgpool user to run the pgpool process as. This allows us to control the hba authentication to the postgres database servers through the pgpool user rather than root.
sudo useradd -d /home/pgpool -m pgpool and then sudo passwd pgpool. Remember this password, since you will want to use it for each pgpool user (the postgresql pgpool login etc.).
Pgpool2 is kind of tricky to configure and run. The machine that I am going to install and run it from is running linux mint, so you'll want to update, upgrade and clean up the OS as much as possible. Obviously the aptitude repository is horribly out of date and there has been (as of this post) a new release of Pgpool II to 3.7.2. Change user to pgpool, cd to the pgpool user home directory and then wget and untar that sucker from here.
Cd into the extracted folder and begin the build phase. Depending on your situation, there are a number of things that can go wrong with this process. Pgpool requires GNU make 3.80 + so check for your Make version:
Update your gcc compiler, and you may need to install build-essential, libpq and libpq-dev. If you don't have postgresql on the machine, pgpool acts basically the same as a postgres database, so install postgresql (I have 9.5), postgresql-contrib, postgresql-client, postgresql-common. You'll also want openssh-server (since pgpool will ssh tunnel to databases on other machines). You will also need libmemcached in order since pgpool uses memcached for it's in memory caching. If you are wanting to use the local postgresql database with Pgpool, don't forget to hook this database up to the master / slave streaming replication system.
So inside the extracted directory run the configure + make + make install sequence. I did not supply any options to the configure command. This installs pgpool to /usr/local/etc. You can delete the extracted and tar files from the /home/pgpool directory should you want to, however you might want to hang on to it until you are sure that your set up is working fully first. chown -R the /usr/local/etc directory to pgpool. In my case there was nothing else in there, but if you don't like that idea season to taste.
The doc is a little light here, but this step is required for your Pgpool II installation (it creates or amends some pgpool ctl database function concerning recovery). I had to do this by actually copying the template as SQL and running that directly on the master server because of the streaming replication setup.
I am not using the replication mode of pgpool, so am not installing the locking mechanism.
So this part is a little subtle and sent me in a circle for a while. There isn't a default pgpool.conf but rather a set of sample configuration files depending on what your requirements are for your set up. For example should you want streaming replication, there is a pgpool.conf.sample-stream which you can use straight off the bat by copy paste (cp /usr/local/etc/pgpool.conf.sample-stream /usr/local/etc/pgpool.conf). I wanted to use the base vanilla conf (pgpool.conf.sample) and edit that to my taste, but didn't do this step. Starting pgpool without setting this conf had me chasing my tail for a little while since the conf file just didn't 'take'. Even if you find a default pgpool.conf you may still need to do the copy paste ‘activation’ of the configuration file.
Now there is a whole administrative portion of Pgpool II, that also includes a whole web site and interface. I don't tend to use GUI's for this sort of thing (the feedback from them for issues is often cryptic or hard to find) and I feel like the commands and functionality is often not granular enough for me. I prefer a command line interface where I can get good feed back and drill down to the exact things that I want in the exact order I want it. This is a matter of taste of course and GUI's can often prevent you from doing silly things by 'validating' the processes and functionality order for you. I find that most of the time the button click is just calling a command line command anyway so why not cut out the middle man? All that aside, there is a GUI to administer the pgpool process and should you want to use that, you can set that up here.
Even if you don't use the web administration functionality, you will need to set up the pcp.conf and pcppass files and set the OS permissions on them so that the pgpool user can access them. This will enable you to remotely start, stop, attach etc. pgpool and pgpool's nodes (more on all this in the next post).
Fortunately I am using the most common use of pgpool which is load balancing and connection pooling, with postgresql handling the master / slave replication, so configuring the pgpool process is pretty easy. There are four ways to use Pgpool II (which can get a bit more bespoke), but we will concentrate on setting up the streaming replication mode. So open the copy pasted pgpool.conf file from before in your favourite text editor:
Pgpool acts basically as a postrgresql database, so you can set the connection settings as if you were setting up a postgresql server with the same listen addresses as '*' (or whatever your needs are) and instead of the 5432 convention port, use the 9999 convention port. I set the socket dir to the home directory in case of a downed server, /tmp would need to have permissions reset unless I set permissions for the user on that dir.
The pcp connection settings are for the pcp administration interface, and also the connection settings for remote commands to be run upon.
So this is the fun bit - here you specify your 'nodes' or database servers from which pgpool will use to load balance queries from. I like to use the /etc/hosts aliases as hostnames, but you can use IP addresses. You can also play with the weighting if you have faster servers than others; all my servers are old pastured laptops, so I give them all equal weighting. You can see the various settings here.
Authentication - enable the hba so you can control who can connect to the pgpool instance (the same format as the postgres hba configuration). I set the pool_passwd to empty, it seems a little wonky at the moment trying to get it to work with the /usr/local/etc/pool_passwd file.
The load balancing settings: originally I struggled a little with this bit, since I had set up postgres to manage replication master / slave functionality, so I had set it to off here, thinking that pgpool would interfere with my postgres streaming replication. However for load balancing in a master slave environment, pgpool needs to know which node to write to - the primary - since it cannot write to slave databases. Otherwise you would have to set up a specific node (the master) for writes (in your application) and then your application could only read from pgpool. Set master slave to on and load balance to on.
Since we are using postgresql for replication, don't use the pgpool replication functionality. The rest of the configuration is process monitoring etc. I left as is since I don't need it, and is a little bit out of the scope of what I required.
Now since pgpool acts pretty much like a postgresql database, all the HBA files need to be set up. Pgpool needs the following to enable communication between servers:
1. Pgpool OS user needs keyless SSH access to the server nodes.
2. Pgpool postgresql user (the pgpool login to the postgres table) needs to be on all databases in order to perform connections (luckily we can just create on the master and presto, pgpool user everywhere!).
3. Pool_hba.conf needs auth loopback from the other postgresql databases - a little counter intuitive, but the communication goes both ways.
* NOTE: md5 authentication seems a bit wonky in this version of pgpool, so I use trust as the authentication method.
So first off, do the ssh-keygen ssh-copy-id dance to ALL the node servers.
Then create a postgres pgpool user on the master database which has all the privileges required to facilitate your requirements. Use the same password as the pcp.conf / pgpool user password. I did this through pgAdmin III.
Set up the pool_hba.conf file and enter in the allowed node addresses (this is on the server with pgpool running):
Now on each of the nodes add a authentication setting for pgpool to the postgres database (I also have bench_replication as well for testing):
For every database as well you will need to add an entry - for example if you have a web application 'www.example.com' with a database of 'example' you would need to add pgpool as a login to that database:
host example pgpool IP OF PGPOOL SERVER trust
This should be pretty much everything you need in terms of configuration. As I said before Pgpool II can take a bit of patience to get going, so this is not exhaustive by any means. In the next post we will actually fire this up, automate it through our nuclear control panel and test it out!