Set up Pgpool-II Query Caching with Azure Database for PostgreSQL

[BenjaminSatzger]

In our previous blog post, we showed how the performance of repeating SELECT queries can be improved by leveraging an external cache. As shown in the figure below, in our simple experiment, the response time of a repeating SELECT query improved by 86% with around 20% degradation in first time execution of cacheable queries, which seems an acceptable trade-off for workloads with often repeating queries.

 
In this blog post, we provide steps and scripts describing how to install and setup Pgpool on an Ubuntu VM to act as a connection pooler and query cache to an Azure Database for PostgreSQL server. Memcached is used as the backing cache store as shown below. At the end of the blog post, we share a Quickstart ARM template which allows you to quickly provision an Ubuntu VM with Pgpool-II preconfigured with an Azure Database for PostgreSQL server for quick proof-of-concept testing.
 

In our Quickstart template below, Pgpool query cache is configured with default settings. One exception is using Memcached instead of shared memory option as the backing cache. You can fine-tune additional query caching settings based on your workload and expertise.

Note that if you don't require Pgpool's unique features like query caching, we recommend using a simpler connection pooler like PgBouncer with Azure Database for PostgreSQL.

The steps below represent an example scenario that you can modify to your needs by changing configuration settings in pgpool.conf (on Ubuntu, the Pgpool config file is located at /etc/pgpool2/pgpool.conf), followed by a Pgpool restart to pick them up: 

service pgpool2 restart

 

Steps to manually setup Pgpool on Ubuntu VM

Steps below provide a setup example. The complete bash script for the steps below can be download from our Github repository. (If you want to use the quick ARM template instead, scroll to the next section).

We assume the existence of three environment variables:

SERVERNAME: Short name of Azure PostgreSQL server, e.g. pgtestsrv
USERNAME: Short username (without @servername) of Azure PostgreSQL server, e.g. pguser
PASSWORD: Password of Azure PostgreSQL user

# Install Pgpool II and Memcached
apt-get -y install pgpool2 memcached
 
# Pgpool query cache requires a work directory to record table oids
mkdir -p /var/log/pgpool/oiddir
chown postgres:postgres /var/log/pgpool/oiddir
 
# Before modifying Pgpool and Memcached config files, save them 
cp /etc/pgpool2/pgpool.conf  /etc/pgpool2/pgpool.conf.save
cp /etc/pgpool2/pool_hba.conf /etc/pgpool2/pool_hba.conf.save
cp /etc/memcached.conf /etc/memcached.conf.save
 
# Create self-signed certificate to allow connecting to Pgpool using SSL
# Note: Adjust this step to meet your security requirements
openssl req -new -newkey rsa:4096 -days 3650 -nodes -x509 -subj "/C=US/ST=Denial/L=Seattle/O=Dis/CN=www.pgpooltest.org" -keyout /etc/pgpool2/server.key  -out /etc/pgpool2/server.crt
 
# Modify Pgpool config
# Turn SSL on
sed -i 's/ssl = off/ssl = on/g' /etc/pgpool2/pgpool.conf
 
# Use SSL key and cert created above
sed -i "s/#ssl_key = '.\/server.key'/ssl_key = '\/etc\/pgpool2\/server.key'/g" /etc/pgpool2/pgpool.conf
sed -i "s/#ssl_cert = '.\/server.cert'/ssl_cert = '\/etc\/pgpool2\/server.crt'/g" /etc/pgpool2/pgpool.conf
 
# Accept all incoming connections 
sed -i "s/listen_addresses = 'localhost'/listen_addresses = '*'/g" /etc/pgpool2/pgpool.conf
 
# Set Azure PostgreSQL backend
sed -i "s/backend_hostname0 = 'localhost'/backend_hostname0 = '$SERVERNAME.postgres.database.azure.com'/g" /etc/pgpool2/pgpool.conf 
 
# Use pool_hba.conf for client authentication
sed -i 's/enable_pool_hba = off/enable_pool_hba = on/g' /etc/pgpool2/pgpool.conf
 
# Enable query cache
sed -i "s/memory_cache_enabled = off/memory_cache_enabled = on/g" /etc/pgpool2/pgpool.conf 
 
# Use Memcached as query cache store
sed -i "s/memqcache_method = 'shmem'/memqcache_method = 'memcached'/g" /etc/pgpool2/pgpool.conf
 
# Require all clients connecting to Pgpool to authenticate with password (scram-sha-256 in this case)
echo "host all all 0.0.0.0/0 scram-sha-256" > /etc/pgpool2/pool_hba.conf
 
# Write pool_passwd used by Pgpool for password authentication with Azure PostgreSQL backend
echo $USERNAME@$SERVERNAME:$PASSWORD > /etc/pgpool2/pool_passwd
chmod 600 /etc/pgpool2/pool_passwd
chown postgres:postgres /etc/pgpool2/*
 
# Modify Memcached config
# Allow Memcached to use 1024 MB of memory
sed -i "s/m 64/m 1024/g" /etc/memcached.conf
 
# Start Pgpool and Memcached services
# Start Memcached
service memcached restart
 
# Set defaults for Pgpool service
echo "PGPOOL_CONFIG_FILE=/etc/pgpool2/pgpool.conf" >> /etc/default/pgpool2
echo "PGPOOL_HBA_CONFIG_FILE=/etc/pgpool2/pool_hba.conf" >> /etc/default/pgpool2
echo "PGPOOL_PCP_CONFIG_FILE=/etc/pgpool2/pcp.conf" >> /etc/default/pgpool2
echo "PGPOOL_PID_FILE=/var/run/postgresql/pgpool.pid" >> /etc/default/pgpool2
update-rc.d pgpool2 defaults
 
# Start Pgpool
service pgpool2 restart

 

Deploy Pgpool using Quickstart ARM template

If you would like to skip the above steps and want to quickly setup and test Pgpool with Azure DB for PostgreSQL, you can click on Deploy button below. It will take you to an ARM template form. Once you provide all the parameter values, it will provision an Ubuntu VM with Pgpool-II installed, configured and running, connected to an Azure Database for PostgreSQL server. Note that the template uses a self-signed certificate to enable SSL connections to Pgpool, which is something you might want to revisit for production use. The template is available in our GitHub repository.
 
 
 
Once you have deployed the ARM Template successfully without any errors, you will be able to see the hostname, sshCommand and psqlCommand as part of Deployment Outputs.  
 
 
hostname is the Public DNS for the Ubuntu VM hosting Pgpool and Memcached 
sshCommand provides the ssh command to connect to the Ubuntu VM hosting Pgpool and Memcached
psqlCommand provides the psql command to connect the PostgreSQL Server   
psql "host={dnsLabelPrefix}.{region}.cloudapp.azure.com port=5432 dbname={your_database} user={your_username}"
For Example: psql "host=pgpoolvm.westus2.cloudapp.azure.com port=5432 dbname=postgres user=pguser@pgtestsrv" 
 
Hope this helps!

Benjamin Satzger
Principal Software Engineer, Microsoft

Parikshit Savjani
Senior Program Manager, Microsoft