pgmemcache installation-CSDN博客

pgmemcache是一系列的PostgreSQL函数, 用于memcache的读写操作.

通过pgmemcache以及PostgreSQL的触发器可以方便的对数据库中的数据进行缓存.

当然缓存的操作也可以挪至应用程序自己来处理. pgmemcache只是一种选择.

本文涉及的环境如下 :

CentOS 5.7 x64
PostgreSQL 9.2.0
libevent 2.0.20-stable
memcached 1.4.15
libmemcache 0.48
pgmemcache 2.0.6

首先介绍一下pgmemcache的安装 :

pgmemcache 依赖 libmemcache和PostgreSQL
libmemcache依赖libevent和memcache
memcache依赖libevent.

所以首先要安装的是libevent :

wget https://github.com/downloads/libevent/libevent/libevent-2.0.20-stable.tar.gz
tar -zxvf libevent-2.0.20-stable.tar.gz
cd libevent-2.0.20-stable
./configure
make
make install

安装完后可以看到安装好后的libevent动态库文件.

updatedb
locate libevent-2.0.so
/usr/local/lib/libevent-2.0.so.5
/usr/local/lib/libevent-2.0.so.5.1.8

将这个目录加入动态库配置文件 :

vi /etc/ld.so.conf
/usr/local/lib
ldconfig

确认已经生效 :

ldconfig -p|grep libevent
        libevent_pthreads-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_pthreads-2.0.so.5
        libevent_openssl-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_openssl-2.0.so.5
        libevent_extra-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_extra-2.0.so.5
        libevent_core-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_core-2.0.so.5
        libevent-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent-2.0.so.5

接下来要安装memcached :

wget http://memcached.googlecode.com/files/memcached-1.4.15.tar.gz
tar -zxvf memcached-1.4.15.tar.gz
cd memcached-1.4.15
./configure --help
./configure --prefix=/opt/memcached-1.4.15 --enable-sasl --enable-64bit
make
make install
cd /opt/memcached-1.4.15/share/man/man1
man ./memcached.1

接下来安装libmemcached (找一个合适的版本, 例如CentOS 5.7 用到的是 libmemcached-0.48 ) :

wget http://download.tangent.org/libmemcached-0.48.tar.gz
tar -zxvf libmemcached-0.48.tar.gz
cd libmemcached-0.48
./configure --prefix=/opt/libmemcached-0.48 --with-memcached=/opt/memcached-1.4.15/bin/memcached
make
make install

修改动态库配置文件, 并使之生效 :

vi /etc/ld.so.conf
/opt/libmemcached-0.48/lib
ldconfig

查看新增的动态库是否生效 :

ldconfig -p|grep libmemcache
        libmemcachedutil.so.1 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedutil.so.1
        libmemcachedutil.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedutil.so
        libmemcachedprotocol.so.0 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedprotocol.so.0
        libmemcachedprotocol.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedprotocol.so
        libmemcached.so.6 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcached.so.6
        libmemcached.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcached.so
        libhashkit.so.0 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libhashkit.so.0
        libhashkit.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libhashkit.so

最后要安装pgmemcache_2.0.6.tar.bz2

wget http://pgfoundry.org/frs/download.php/3018/pgmemcache_2.0.6.tar.bz2
tar -jxvf pgmemcache_2.0.6.tar.bz2
cd pgmemcache

需要用到pg_config, 所以需要加入到PATH中.

. /home/pg9.2.0/.bash_profile

pgmemcache的头文件中包含了libmemcached的一些头, 如下, 所以需要将这些头文件拷贝到pgmemcache的目录中来.

less pgmemcache.h
#include <libmemcached/sasl.h>
#include <libmemcached/memcached.h>
#include <libmemcached/server.h>
#include <sasl/sasl.h>

拷贝这些头文件到本地目录中,

cp -r /opt/libmemcached-0.48/include/libhashkit ./
cp -r /opt/libmemcached-0.48/include/libmemcached ./

同时编译时需要用到libmemcached.so, 如下Makefile :

less Makefile
SHLIB_LINK = -lmemcached -lsasl2

但是没有指定库目录, 所以需要修改一下

vi Makefile
SHLIB_LINK = -L/opt/libmemcached-0.48/lib -lmemcached -lsasl2

接下来编译安装就可以了.

gmake
gmake install

安装好pgmemcache后, 需要做的是修改PostgreSQL的配置文件以及重启数据库, 在需要用到pgmemcache函数的数据库中执行pgmemcache.sql.

这里假设172.16.3.150上已经启动了memcached.

su - pg9.2.0
cd $PGDATA
vi postgresql.conf
shared_preload_libraries = 'pgmemcache'
pgmemcache.default_servers = '172.16.3.150:11211'  #多个memcached可以用逗号隔开配置.
pgmemcache.default_behavior = 'BINARY_PROTOCOL:1'  #多个配置可以用逗号隔开配置.

重启数据库 :

pg_ctl stop -m fast
pg_ctl start

在加载pgmemcache.sql前, 需要对这个脚本修改一下, 否则会报语法错误.

cd $PGHOME/share/contrib
vi pgmemcache.sql
:%s/LANGUAGE\ 'C'/LANGUAGE\ C/g
:x!

在需要的库中执行脚本 :

psql -h 127.0.0.1 -U postgres digoal -f ./pgmemcache.sql

好了简单的测试一下pgmemcache的函数 :

digoal=> select memcache_set('key1', '1');
 memcache_set 
--------------
 t
(1 row)

digoal=> select memcache_get('key1');
 memcache_get 
--------------
 1
(1 row)

digoal=> select memcache_incr('key1',99);
 memcache_incr 
---------------
           100
(1 row)

digoal=> select memcache_incr('key1',99);
 memcache_incr 
---------------
           199
(1 row)

digoal=> select memcache_stats();
        memcache_stats        
------------------------------
                             +
 Server: 172.16.3.150 (11211)+
 pid: 1918                   +
 uptime: 13140               +
 time: 1353222576            +
 version: 1.4.15             +
 pointer_size: 64            +
 rusage_user: 0.999          +
 rusage_system: 0.1999       +
 curr_items: 1               +
 total_items: 3              +
 bytes: 72                   +
 curr_connections: 6         +
 total_connections: 10       +
 connection_structures: 7    +
 cmd_get: 1                  +
 cmd_set: 1                  +
 get_hits: 1                 +
 get_misses: 0               +
 evictions: 0                +
 bytes_read: 207             +
 bytes_written: 3196         +
 limit_maxbytes: 67108864    +
 threads: 4                  +


digoal=> select memcache_flush_all();
 memcache_flush_all 
--------------------
 t

digoal=> select memcache_get('key1');
 memcache_get 
--------------
 
(1 row)

digoal=> select memcache_stats();
        memcache_stats        
------------------------------
                             +
 Server: 172.16.3.150 (11211)+
 pid: 1918                   +
 uptime: 13165               +
 time: 1353222601            +
 version: 1.4.15             +
 pointer_size: 64            +
 rusage_user: 0.999          +
 rusage_system: 0.1999       +
 curr_items: 0               +
 total_items: 3              +
 bytes: 0                    +
 curr_connections: 6         +
 total_connections: 11       +
 connection_structures: 7    +
 cmd_get: 2                  +
 cmd_set: 1                  +
 get_hits: 1                 +
 get_misses: 1               +
 evictions: 0                +
 bytes_read: 276             +
 bytes_written: 4278         +
 limit_maxbytes: 67108864    +
 threads: 4                  +

【cache应用场景举例】

1. 测试表

digoal=> create table tbl_user_info (userid int8 primary key, pwd text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "tbl_user_info_pkey" for table "tbl_user_info"
CREATE TABLE

2. 测试数据

digoal=> insert into tbl_user_info select generate_series(1,10000000), md5(clock_timestamp()::text);
INSERT 0 10000000

3. 更新触发器(不安全)

CREATE OR REPLACE FUNCTION tbl_user_info_upd() RETURNS TRIGGER AS $$
        BEGIN
        IF OLD.pwd != NEW.pwd THEN
                PERFORM memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd);
        END IF;
        RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER tbl_user_info_upd AFTER UPDATE ON tbl_user_info FOR EACH ROW EXECUTE PROCEDURE tbl_user_info_upd();

3. 插入触发器(不安全)

CREATE OR REPLACE FUNCTION tbl_user_info_ins() RETURNS TRIGGER AS $$
        BEGIN
        PERFORM memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd);
        RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER tbl_user_info_ins AFTER INSERT ON tbl_user_info FOR EACH ROW EXECUTE PROCEDURE tbl_user_info_ins();

4. 删除触发器(安全, 因为无法命中cache是安全的, 但是cache数据和table数据不一致是不安全的)

CREATE OR REPLACE FUNCTION tbl_user_info_del() RETURNS TRIGGER AS $$
BEGIN
        PERFORM memcache_delete('tbl_user_info_' || NEW.userid || '_pwd');
        RETURN OLD;
END;
$$ LANGUAGE 'plpgsql';
CREATE TRIGGER tbl_user_info_del AFTER DELETE ON tbl_user_info FOR EACH ROW EXECUTE PROCEDURE tbl_user_info_del();

5. 密码匹配函数 :

CREATE OR REPLACE FUNCTION auth (i_userid int8, i_pwd text) returns boolean as $$
declare
  v_input_pwd_md5 text;
  v_user_pwd_md5 text;
begin
  v_input_pwd_md5 := md5(i_pwd);
  select memcache_get('tbl_user_info_' || i_userid || '_pwd') into v_user_pwd_md5;
  if (v_user_pwd_md5 <> '' ) then
    raise notice 'hit in memcache.';
    if (v_input_pwd_md5 = v_user_pwd_md5) then
      return true;
    else
      return false;
    end if;
  else
    select pwd into v_user_pwd_md5 from tbl_user_info where userid=i_userid;
    if found then
      raise notice 'hit in table.';
      if (v_input_pwd_md5 = v_user_pwd_md5) then
        return true;
      else
        return false;
      end if;
    else
      return false;
    end if;
  end if;
exception
when others then
  return false;
end;
$$ language plpgsql;

6. 再次插入测试数据, 并且手动退出请求. 这样将导致PG回滚这条SQL, 但是memcache已经被操作了.

digoal=> insert into tbl_user_info select generate_series(10000001,11000001), md5(clock_timestamp()::text);
Cancel request sent
ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd)"
PL/pgSQL function tbl_user_info_ins() line 3 at PERFORM

不要以为memcache也回滚了, 因为memcache启动时没有启用-M, 超出后将 removing items , 所以我们查询这些KEY都不存在.

-M            return error on memory exhausted (rather than removing items)
digoal=> select count(*) from tbl_user_info ;
  count   
----------
 10000001
(1 row)

这些KEY查不到数据, 不是因为memcache的操作回滚了, 而是发生了 removing items 的情况, 如上所述.

digoal=> select memcache_get('tbl_user_info_10000001_pwd');
 memcache_get 
--------------
 
(1 row)
digoal=> select memcache_get('tbl_user_info_10000002_pwd');
 memcache_get 
--------------
 
(1 row)

重启memcache, 并限制8MB的内存和开启-M.

[root@db-172-16-3-150 ~]# ps -ewf|grep memcache
pg9.2.0   1918     1  0 Nov18 ?        00:01:06 ./memcached -d -u pg9.2.0
root     20612 20581  0 08:43 pts/7    00:00:00 grep memcache
[root@db-172-16-3-150 ~]# kill 1918

[root@db-172-16-3-150 bin]# ./memcached -d -u pg9.2.0 -M -m 8

再次插入时, 内存使用完就开始报warning了, 但是SQL语句照常执行, 并没有回滚掉. 所以说是不安全的.

digoal=> insert into tbl_user_info select generate_series(10000001,11000001), md5(clock_timestamp()::text);
WARNING:  MEMORY ALLOCATION FAILURE
CONTEXT:  SQL statement "SELECT memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd)"
PL/pgSQL function tbl_user_info_ins() line 3 at PERFORM

手工的CACNEL SQL请求, 这时PG将发生回滚, 但是这里注意memcache的数据已经写入了.

ERROR:  canceling statement due to user request
CONTEXT:  SQL statement "SELECT memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd)"
PL/pgSQL function tbl_user_info_ins() line 3 at PERFORM

从这里看, memcache的数据已经写入了.

digoal=> select memcache_get('tbl_user_info_10000001_pwd');
           memcache_get           
----------------------------------
 07e975fb5fc9a308760cb7711681635b
(1 row)

【小结】

1. memcache可以选择是否编译64位版本.

   --enable-64bit          build 64bit version

64bit 对应pointer_size : 64, 所以将占用更多的空间. 如果没有超过20亿的key, 使用32位就够了.

2. 事务安全性

memcache_get是安全的.

但是memcache_set, replace在事务中是不安全的. 因为事务回滚时, 这些函数不会回滚. 也就是说对memcache的更改无法回滚.

在trigger function写exception就可以解决了么?

例如:

CREATE OR REPLACE FUNCTION tbl_user_info_ins() RETURNS TRIGGER AS $$
        BEGIN
        PERFORM memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd);
        RETURN NEW;
EXCEPTION when others then
  PERFORM memcache_delete('tbl_user_info_' || NEW.userid || '_pwd');
END;
$$ LANGUAGE 'plpgsql';

经过测试只对第一条tuple有效.

3. 除了pgmemcache以外, pgredis也是类似的项目, 只不过是redis的一些封装好的API.

参考: https://github.com/siavashg/pgredis

【参考】

1. http://docs.libmemcached.org

2. http://memcached.org/

3. http://download.tangent.org/

4. http://pgfoundry.org/frs/?group_id=1000052

5. http://libmemcached.org/

6. http://www.monkey.org/~provos/libevent/

7. http://code.google.com/p/memcached/wiki/Clients

8. http://libevent.org/

9. pgmemcache functions :

-- $1 = server hostname - TEXT
CREATE OR REPLACE FUNCTION memcache_server_add(TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_server_add' LANGUAGE C STRICT;

-- $1 = key - TEXT
-- $2 = val - TEXT (opt)
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
-- $4 = flags - INT4 (opt)
CREATE OR REPLACE FUNCTION memcache_add(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_add' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_add(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_add_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_add(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_add' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_get(TEXT) RETURNS TEXT
AS '$libdir/pgmemcache', 'memcache_get' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_get(BYTEA) RETURNS TEXT
AS '$libdir/pgmemcache', 'memcache_get' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_get_multi(IN TEXT[], OUT key TEXT, OUT value TEXT) RETURNS SETOF record
AS '$libdir/pgmemcache', 'memcache_get_multi' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_get_multi(IN BYTEA[], OUT key TEXT, OUT value TEXT) RETURNS SETOF record
AS '$libdir/pgmemcache', 'memcache_get_multi' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = decrement - INT4, default 1 (opt)
CREATE OR REPLACE FUNCTION memcache_decr(TEXT, INT4) RETURNS INT4
AS '$libdir/pgmemcache', 'memcache_decr' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_decr(TEXT) RETURNS INT4
AS '$libdir/pgmemcache', 'memcache_decr' LANGUAGE C;


-- $1 = key - TEXT
-- $2 = increment - INT4, default 1 (opt)
CREATE OR REPLACE FUNCTION memcache_incr(TEXT, INT4) RETURNS INT4
AS '$libdir/pgmemcache', 'memcache_incr' LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION memcache_incr(TEXT) RETURNS INT4
AS '$libdir/pgmemcache', 'memcache_incr' LANGUAGE C STRICT;


-- $1 = key - TEXT
-- $2 = hold timer - INTERVAL (opt)
CREATE OR REPLACE FUNCTION memcache_delete(TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_delete' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_delete(TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_delete' LANGUAGE C;

-- Flush all servers in the cluster
CREATE OR REPLACE FUNCTION memcache_flush_all() RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_flush_all0' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_replace(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_replace' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_replace(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_replace_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_replace(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_replace' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_set(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_set(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_set_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_set(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_set(BYTEA, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_set(TEXT, BYTEA) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_prepend_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_prepend_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;

-- $1 = key - TEXT
-- $2 = val - TEXT
-- $3 = expire - INTERVAL or TIMESTAMPTZ (opt)
CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, INTERVAL) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append_absexpire' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT) RETURNS BOOL
AS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;

CREATE OR REPLACE FUNCTION memcache_stats() RETURNS TEXT
AS '$libdir/pgmemcache', 'memcache_stats' LANGUAGE C;