Preloading or Caching the table in PostgreSQL is a tough task, because PostgreSQL doesnt offer a Single big synchronize-level memory managment. All the memories are independent. Caching is possible with the third party tools like memcached.

pgmemcache is a set of PostgreSQL user-defined functions(API’s) that provide an interface to memcached. pgmemcache, pre-requisites recommends to have libmemcached, however its also recommended to install memcached along with it. My presentation consist of installation/caching/monitoring using pgmemcache API’s. As am not the Developer or Hacker :), so my way of implementation is in very simple method.

Points:

• Stores value in cache on the basis of Key/Value means, keeping table with primary key/unique key is recommended.
• No Data redundancy - If memcached goes down or runs out of space, new records and updates will be lost.
• Supports all memcached commands (set/get(single/multi)/delete/replace/incr/stats)
• After keeping the data into memcached and if you drop the table from backend,  memcached won’t  throw any errors. Its all your management how you maintain it.
• No ability to iterate over data or determine what keys have been stored.
• You can never bring a memcached server down or add a new one to the pool while people are playing or connected.
• If the background updating process stops for any reason, updates do not occur and there is a possiblity that the memcached server could fill up.
• Every PostgreSQL backend has to bind to memcached port before accessing the data.
• Memcached runs on default port 11211

Pre-requisites:

1. PostgreSQL 8.4. or above
2. libevent
3. memcached
4. libmemcached
5. pgmemcache
6. Monitoring-Tools (monitoring-tools,damemtop,etc.,)

Installation:
Step 1. (libevent)

Libevent API is important when configuring pgmemcache, I prefer to have libraries as first step of installation. So lets start with libevent library configuring in default location.

Download link for libevent:
http://www.monkey.org/~provos/libevent-2.0.12-stable.tar.gz
tar -xvf libevent-2.0.12-stable.tar.gz
cd libevent-2.0.12-stable
./configure 
make
make install

Step 2 (memcached)

Install memcached by enabling the libevent.

Download link for memcached:
http://memcached.googlecode.com/files/memcached-1.4.6.tar.gz
cd /usr/local/src/memcached-1.4.6
------on 32-bit
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib
------on 64-bit
export LD_LIBRARY_PATH=/usr/lib64:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
./configure --prefix=/opt/PostgreSQL/9.0/bin/ --with-libevent=/usr/lib64
make
make install

Step 3. (libmemcached)

pgmemcache is built on top of libmemcached. Libmemcached looks for memcache binary location, so set the path to memcached binaries before proceeding it.

export PATH=/opt/PostgreSQL/9.0/bin/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
Download link:
http://launchpad.net/libmemcached/1.0/0.50/+download/libmemcached-0.50.tar.gz
cd libmemcached-0.50
./configure
make
make install

Step 4 (pgmemcache)

pgmemcache API will help in, interacting with memcached like caching/retreiving data.

Download link:
http://pgfoundry.org/frs/download.php/3018/pgmemcache_2.0.6.tar.bz2
cd pgmemcache
PATH=/opt/PostgreSQL/9.0/bin:$PATH make USE_PGXS=1 install
or 
make
make install

Installation will create pgmemcache.sql file with all API’s to interact with memcache under PG contrib location. To create pgmemcache API’s, just exectute pgmemcache.sql file in all the database.

psql -p PGPORT -d PGDATABASE -f /opt/PostgreSQL/9.0/share/postgresql/contrib/pgmemcache.sql

pgmemcache API’s list:

Note: While executing .sql file you may face error like “ISTFATAL: could not load library “/opt/PostgreSQL/9.0/lib/postgresql/pgmemcache.so”: libmemcached.so.8: cannot open shared object file: No such file or directory”. Means, PG instance didnt loaded with newly created library. Resolution, set the PATH and LD_LIBRARY_PATH and restart the instance to recognize the libraries.

Eg:-
export PATH=/opt/PostgreSQL/9.0/bin/bin:$PATH
export LD_LIBRARY_PATH=/usr/lib:/opt/PostgreSQL/9.0/lib:$LD_LIBRARY_PATH
$pg_ctl -D $PGDATA restart

If you want to load the pgmemcache as default to your PG instance, edit the postgresql.conf file and change the following parameters and restart the cluster.

shared_preload_libraries='pgmemcache'
custom_variable_classes='pgmemcache'

Configuration:
Step 1.

For caching data, first you need to initialize the memory, once the memory is allotted, later PG backends responsibility to bind and push the data into the cache. Here, I have started my memcache on localhost with 512MB on default port 11211. -d means start the daemon. All my exercise is  on localhost.

$./memcached -d -m 512 -u postgres -l localhost -p 11211

Note: To retreive data from the cache, every PostgreSQL backend should first bind and retreive the data.

Step 2.

Bind the instance to the running memcache port. After binding, checkout for the memcached statistics.

Step 3.

Now, its time to cache data into memcached, Memcached uses keys/value to reside data in its memory, so make sure your table has Primary/Unique key so retrieving will be easy. As mentioned, there are very good API’s to play around on keeping the value and accessing them, in my example, I use memcache_set() to keep the value and memcache_get() to retrive data.

Once the value is set in the memcached, now its your responsibility to bind your backend to memcached and with the help of pgmemcache API’s you can access the data. Each Postgres backend must bind before accessing. Please find the example below.

Getting data from cache

Monitoring

If you are very good in linux you can pull maximum information on memcached memory, however there are few tools which come along with memcached source pack like monitoring-tools,damemtop etc.,. Am using monitoring-tools utility for monitoring memcache.

usage:-
memcached-tool localhost display
memcached-tool localhost dump
memcached-tool localhost stats | grep bytes

Example:

A small effort from my side to setup pgmemcache and understand the basics. Hope it was helpful. Keep posting your comments or suggestion which are highly appreciated.

–Raghav