Sunday, July 24, 2011

pgmemcache Setup and Usage

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

7 comments :

Helper said...

very nice and good information.. :), Can you please provide some db monitoring tools to find the top 5 queries using high cpu or more time to execute

Raghavendra said...

Thank you.

http://pgfoundry.org/
pgfoundry, is the place where you get most of the third party tools related to PostgreSQL. However, am aware of quite a few which I use as best.

1. check_postgres
http://bucardo.org/wiki/Check_postgres

2. pgFouine
http://pgfouine.projects.postgresql.org/

3. Munin
http://munin-monitoring.org/

4. pg_top (there is no current release)
http://ptop.projects.postgresql.org/screenshots/

Regards
Raghav

Amit jain - helloamit5@gmail.com said...

Nice post. But need to add one more point here.

It is often more convenient to specify a list of memcached servers to connect to in postgresql.conf, rather than calling memcache_server_add() in each new client connection. This can be done as follows:

In postgresql.conf Set the "pgmemcache.default_servers" custom GUC variable to a comma-separated list of 'host:port' pairs (the port is optional).

Raghavendra said...

Thanks....
Agreed, I should have added it. Thanks for adding point to this thread.

--Raghav

anand vamsi said...

Hi Raghvan.

Im in a process of installing memcahed_function_mysql

Here is the doc i have followed
http://igstan.ro/posts/2010-11-25-how-to-install-memcached-functions-for-mysql.html

in the last step after executing
mysql> source ./install_functions.sql

im getting error like :

mysql> source ./install_functions.sql
ERROR 1126 (HY000): Can't open shared library 'libmemcached_functions_mysql.so' (errno: 22 libmemcached.so.3: cannot open shared object file: No such file or directory)

can u help me in resolving this.

Raghavendra said...

Am not familiar much with MySQL, however ERROR indicates that .so file is not loaded in MySQL library. You need to set the MySQL Library path with this .so file and restart the server. Then you can give a try.

Raghav

Anonymous said...

if it will be a big help you can add doing this with windows platform

Post a Comment

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License