Published on Sun, Jul 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.
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.
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.
Bind the instance to the running memcache port. After binding, checkout for the memcached statistics.
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
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
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.