pgmemcache是一系列的PostgreSQL函数, 用于memcache的读写操作.
通过pgmemcache以及PostgreSQL的触发器可以方便的对数据库中的数据进行缓存.
当然缓存的操作也可以挪至应用程序自己来处理. pgmemcache只是一种选择.
本文涉及的环境如下 :
CentOS 5.7 x64PostgreSQL 9.2.0libevent 2.0.20-stablememcached 1.4.15libmemcache 0.48pgmemcache 2.0.6
首先介绍一下pgmemcache的安装 :
pgmemcache 依赖 libmemcache和PostgreSQLlibmemcache依赖libevent和memcachememcache依赖libevent.
所以首先要安装的是libevent :
wget https://github.com/downloads/libevent/libevent/libevent-2.0.20-stable.tar.gztar -zxvf libevent-2.0.20-stable.tar.gzcd libevent-2.0.20-stable./configuremakemake install
安装完后可以看到安装好后的libevent动态库文件.
updatedblocate 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/libldconfig
确认已经生效 :
ldconfig -p|grep libeventlibevent_pthreads-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_pthreads-2.0.so.5libevent_openssl-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_openssl-2.0.so.5libevent_extra-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_extra-2.0.so.5libevent_core-2.0.so.5 (libc6,x86-64) => /usr/local/lib/libevent_core-2.0.so.5libevent-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.gztar -zxvf memcached-1.4.15.tar.gzcd memcached-1.4.15./configure --help./configure --prefix=/opt/memcached-1.4.15 --enable-sasl --enable-64bitmakemake installcd /opt/memcached-1.4.15/share/man/man1man ./memcached.1
接下来安装libmemcached (找一个合适的版本, 例如CentOS 5.7 用到的是 libmemcached-0.48 ) :
wget http://download.tangent.org/libmemcached-0.48.tar.gztar -zxvf libmemcached-0.48.tar.gzcd libmemcached-0.48./configure --prefix=/opt/libmemcached-0.48 --with-memcached=/opt/memcached-1.4.15/bin/memcachedmakemake install
修改动态库配置文件, 并使之生效 :
vi /etc/ld.so.conf/opt/libmemcached-0.48/libldconfig
查看新增的动态库
是否生效 :
ldconfig -p|grep libmemcachelibmemcachedutil.so.1 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedutil.so.1libmemcachedutil.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedutil.solibmemcachedprotocol.so.0 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedprotocol.so.0libmemcachedprotocol.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcachedprotocol.solibmemcached.so.6 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcached.so.6libmemcached.so (libc6,x86-64) => /opt/libmemcached-0.48/lib/libmemcached.solibhashkit.so.0 (libc6,x86-64) => /opt/libmemcached-0.48/lib/libhashkit.so.0libhashkit.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.bz2tar -jxvf pgmemcache_2.0.6.tar.bz2cd 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 MakefileSHLIB_LINK = -lmemcached -lsasl2
但是没有指定库目录, 所以需要修改一下
vi MakefileSHLIB_LINK = -L/opt/libmemcached-0.48/lib -lmemcached -lsasl2
接下来编译安装就可以了.
gmakegmake install
安装好pgmemcache后, 需要做的是修改PostgreSQL的配置文件以及重启数据库, 在需要用到pgmemcache函数的数据库中执行pgmemcache.sql.
这里假设172.16.3.150上已经启动了memcached.
su - pg9.2.0cd $PGDATAvi postgresql.confshared_preload_libraries = 'pgmemcache'pgmemcache.default_servers = '172.16.3.150:11211' #多个memcached可以用逗号隔开配置.pgmemcache.default_behavior = 'BINARY_PROTOCOL:1' #多个配置可以用逗号隔开配置.
重启数据库 :
pg_ctl stop -m fastpg_ctl start
在加载pgmemcache.sql前, 需要对这个脚本修改一下, 否则会报语法错误.
cd $PGHOME/share/contribvi 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--------------------tdigoal=> 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 $$BEGINIF OLD.pwd != NEW.pwd THENPERFORM 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 $$BEGINPERFORM 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 $$BEGINPERFORM 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 $$declarev_input_pwd_md5 text;v_user_pwd_md5 text;beginv_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 <> '' ) thenraise notice 'hit in memcache.';if (v_input_pwd_md5 = v_user_pwd_md5) thenreturn true;elsereturn false;end if;elseselect pwd into v_user_pwd_md5 from tbl_user_info where userid=i_userid;if found thenraise notice 'hit in table.';if (v_input_pwd_md5 = v_user_pwd_md5) thenreturn true;elsereturn false;end if;elsereturn false;end if;end if;exceptionwhen others thenreturn 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 sentERROR: canceling statement due to user requestCONTEXT: 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 memcachepg9.2.0 1918 1 0 Nov18 ? 00:01:06 ./memcached -d -u pg9.2.0root 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 FAILURECONTEXT: 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 requestCONTEXT: 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 $$BEGINPERFORM memcache_set('tbl_user_info_' || NEW.userid || '_pwd', NEW.pwd);RETURN NEW;EXCEPTION when others thenPERFORM memcache_delete('tbl_user_info_' || NEW.userid || '_pwd');END;$$ LANGUAGE 'plpgsql';
经过测试只对第一条tuple有效.
3. 除了pgmemcache以外, pgredis也是类似的项目, 只不过是redis的一些封装好的API.
【参考】
-- $1 = server hostname - TEXTCREATE OR REPLACE FUNCTION memcache_server_add(TEXT) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_add' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_add(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_add_absexpire' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_add(TEXT, TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_add' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_get(TEXT) RETURNS TEXTAS '$libdir/pgmemcache', 'memcache_get' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_get(BYTEA) RETURNS TEXTAS '$libdir/pgmemcache', 'memcache_get' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_get_multi(IN TEXT[], OUT key TEXT, OUT value TEXT) RETURNS SETOF recordAS '$libdir/pgmemcache', 'memcache_get_multi' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_get_multi(IN BYTEA[], OUT key TEXT, OUT value TEXT) RETURNS SETOF recordAS '$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 INT4AS '$libdir/pgmemcache', 'memcache_decr' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_decr(TEXT) RETURNS INT4AS '$libdir/pgmemcache', 'memcache_decr' LANGUAGE C;-- $1 = key - TEXT-- $2 = increment - INT4, default 1 (opt)CREATE OR REPLACE FUNCTION memcache_incr(TEXT, INT4) RETURNS INT4AS '$libdir/pgmemcache', 'memcache_incr' LANGUAGE C STRICT;CREATE OR REPLACE FUNCTION memcache_incr(TEXT) RETURNS INT4AS '$libdir/pgmemcache', 'memcache_incr' LANGUAGE C STRICT;-- $1 = key - TEXT-- $2 = hold timer - INTERVAL (opt)CREATE OR REPLACE FUNCTION memcache_delete(TEXT, INTERVAL) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_delete' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_delete(TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_delete' LANGUAGE C;-- Flush all servers in the clusterCREATE OR REPLACE FUNCTION memcache_flush_all() RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_replace' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_replace(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_replace_absexpire' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_replace(TEXT, TEXT) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_set(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_set_absexpire' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_set(TEXT, TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_set(BYTEA, TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_set' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_set(TEXT, BYTEA) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_append_absexpire' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_prepend' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_prepend(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$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 BOOLAS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT, TIMESTAMPTZ) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_append_absexpire' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_append(TEXT, TEXT) RETURNS BOOLAS '$libdir/pgmemcache', 'memcache_append' LANGUAGE C;CREATE OR REPLACE FUNCTION memcache_stats() RETURNS TEXTAS '$libdir/pgmemcache', 'memcache_stats' LANGUAGE C;