Call Memory Cache Functionality from Within Stored Procedure

I apologize for my cancelled post titled “Linux System Programming in C”. It is supposed to be posted under other category.

To alleviate database load, my first pick is pgpool-II with memory_cache_enabled turned on. Unfortunately, it does not cache query result from stored procedure.

The alternative is pgmemcache. It provides interface functions to memcached and can be called from within stored procedure.

Thanks to pgpool-II idea, SQL command and result is cached into memcached hash:

  • Taking md5 of SQL as key.
  • Forming set of records to string as value.

Assumption:

  • memcahed server is up and running.
  • pgmemcahe extension is installed in postgersql server.
  • postgresql.conf has been set accordingly.

My case is query result for chart of account (coa) table joining 3 tables:

SELECT
  coa.id,
  tipe.label AS type_label,
  coa.kode,
  coa.label AS coa_label,
  dbcr.label AS dbcr_label
FROM acct.coa coa
INNER JOIN acct.tipe tipe ON coa.tipe=tipe.id
INNER JOIN acct.dbcr dbcr ON coa.dbcr=dbcr.id
OFFSET 0 LIMIT 25;

I wrap it insided plpgsql stored procedure. Here is my custom data type:

CREATE TYPE acct.coa_cache AS
(i_id integer,
t_tipe text,
t_kode text,
t_label text,
t_dbcr text);

Stored Procedure:

CREATE OR REPLACE FUNCTION acct.coa_query_page_cache(OUT d acct.coa_cache)
RETURNS SETOF acct.coa_cache AS
$BODY$
DECLARE
  _sql text;
  _key text;
  _v text;
  _ds acct.coa_cache[];
BEGIN
  _sql:= 'SELECT coa.id'
    || ',tipe.label'
    || ',coa.kode'
    || ',coa.label'
    || ',dbcr.label'
    || ' FROM acct.coa coa'
    || ' INNER JOIN acct.tipe tipe ON coa.tipe=tipe.id'
    || ' INNER JOIN acct.dbcr dbcr ON coa.dbcr=dbcr.id'
    || ' OFFSET 0 LIMIT 10';
  _key:=md5(_sql); --taking md5 as key
  _v:=memcache_get(_key); --get hash value by key
  IF _v IS NOT NULL THEN --hit
    raise notice 'hit';
    _ds:=NULLIF(_v,'')::acct.coa_cache[];--convert string to acct.coa_cache array
    RETURN QUERY SELECT * FROM UNNEST(_ds);
  ELSE --miss
    raise notice 'miss';
    FOR d IN EXECUTE _sql
    LOOP
      _ds:= _ds || d;
      RETURN NEXT;
    END LOOP;
    PERFORM memcache_set(_key, _ds::text);--register key/value to memcached
  END IF;
  RETURN;
END;$BODY$
  LANGUAGE plpgsql STABLE SECURITY DEFINER;

Let us check how it works with psql.

psql# \timing on
Timing is on.

First, check the raw SQL performance:

psql# SELECT coa.id,
tipe.label AS tipe_label,
coa.kode,
coa.label,
dbcr.label AS dbcr_label
FROM acct.coa coa
INNER JOIN acct.tipe tipe ON coa.tipe=tipe.id
INNER JOIN acct.dbcr dbcr ON coa.dbcr=dbcr.id
OFFSET 0 LIMIT 10;
id | tipe_label | kode | label | dbcr_label
----+------------+-----------+-----------------------------+------------
1 | AKTIVA | 1 | AKTIVA | DEBET
2 | AKTIVA | 1.1 | AKTIVA LANCAR | DEBET
3 | AKTIVA | 1.1.1 | KAS | DEBET
4 | AKTIVA | 1.1.1.1 | KAS DANA ZAKAT | DEBET
5 | AKTIVA | 1.1.1.2 | Kas Dana Infaq | DEBET
6 | AKTIVA | 1.1.1.2.1 | Kas Dana Infaq Umum | DEBET
7 | AKTIVA | 1.1.1.2.2 | Kas Dana Infaq Yatim Dhuafa | DEBET
8 | AKTIVA | 1.1.1.2.3 | KAS DANA INFAQ QURBAN | DEBET
9 | AKTIVA | 1.1.1.3 | Kas Dana Kemanusiaan | DEBET
10 | AKTIVA | 1.1.1.3.1 | Kas Dana Kemanusiaan Umum | DEBET
(10 rows)

Time: 6.652 ms

Second run: 1.290 ms
Third run: 1.248 ms
Fourth run: 1.238 ms
Fifth run: 1.263 ms
Looks it get saturated at 1.2 ms for long run.

How it looks like for memcached version:

psql# select * from acct.coa_query_page_cache();
NOTICE: miss
i_id | t_tipe | t_kode | t_label | t_dbcr
------+--------+-----------+-----------------------------+--------
1 | AKTIVA | 1 | AKTIVA | DEBET
2 | AKTIVA | 1.1 | AKTIVA LANCAR | DEBET
3 | AKTIVA | 1.1.1 | KAS | DEBET
4 | AKTIVA | 1.1.1.1 | KAS DANA ZAKAT | DEBET
5 | AKTIVA | 1.1.1.2 | Kas Dana Infaq | DEBET
6 | AKTIVA | 1.1.1.2.1 | Kas Dana Infaq Umum | DEBET
7 | AKTIVA | 1.1.1.2.2 | Kas Dana Infaq Yatim Dhuafa | DEBET
8 | AKTIVA | 1.1.1.2.3 | KAS DANA INFAQ QURBAN | DEBET
9 | AKTIVA | 1.1.1.3 | Kas Dana Kemanusiaan | DEBET
10 | AKTIVA | 1.1.1.3.1 | Kas Dana Kemanusiaan Umum | DEBET
(10 rows)

Time: 3.488 ms
siazcenter=# select * from acct.coa_query_page_cache();
NOTICE: hit
i_id | t_tipe | t_kode | t_label | t_dbcr
------+--------+-----------+-----------------------------+--------
1 | AKTIVA | 1 | AKTIVA | DEBET
2 | AKTIVA | 1.1 | AKTIVA LANCAR | DEBET
3 | AKTIVA | 1.1.1 | KAS | DEBET
4 | AKTIVA | 1.1.1.1 | KAS DANA ZAKAT | DEBET
5 | AKTIVA | 1.1.1.2 | Kas Dana Infaq | DEBET
6 | AKTIVA | 1.1.1.2.1 | Kas Dana Infaq Umum | DEBET
7 | AKTIVA | 1.1.1.2.2 | Kas Dana Infaq Yatim Dhuafa | DEBET
8 | AKTIVA | 1.1.1.2.3 | KAS DANA INFAQ QURBAN | DEBET
9 | AKTIVA | 1.1.1.3 | Kas Dana Kemanusiaan | DEBET
10 | AKTIVA | 1.1.1.3.1 | Kas Dana Kemanusiaan Umum | DEBET
(10 rows)

Time: 0.846 ms

Third run: 0.826 ms (hit)
Fourth run: 0.838 ms (hit)
Fifth run: 0.815 ms (hit)

The longest 3.488 ms is for the first run when I miss memcached key and start register query key/value. Next run is faster, saturated at 0.8 ms for long run. The more important thing is that my database load is reduced in write once, read many times scenario.

Invalidation
How to invalidate memcached hash when underlying tables are updated. In my case, I write statement trigger and assign it to underlying tables. It will delete key/value from memcached whenever any underlying tables are modified.

Advertisements
2 comments
  1. Sounds good, yet you have a race condition on cache invalidation.

    The trigger will be fired when the data is changed but not yet committed. Let’s call this session A.

    If before this transaction commits you get another request — let’s call this session B — session B will check memcache, get a miss and query the database. Because session A’s transaction hasn’t yet been committed, session B will see the old values. At this point I presume your code will put the queried values in memcache — but they are still the old values.

    What you need is the classic “on commit trigger”… which is a difficult problem to solve and many DBMSes don’t support in a simple way.

  2. abdulyadi said:

    Thanks for spotting the issue. I am interested in RegisterXactCallback(custom_callback, NULL); and provides handler for event XACT_EVENT_COMMIT.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: