Sunday, July 24, 2011

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.

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.

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.