CentOS 5.7 x64PostgreSQL 9.2.0libevent 2.0.20-stablememcached 1.4.15libmemcache 0.48pgmemcache 2.0.6
pgmemcache 依赖 libmemcache和PostgreSQLlibmemcache依赖libevent和memcachememcache依赖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
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
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
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
wget http://pgfoundry.org/frs/download.php/3018/pgmemcache_2.0.6.tar.bz2tar -jxvf pgmemcache_2.0.6.tar.bz2cd pgmemcache
. /home/pg9.2.0/.bash_profile
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 ./
less MakefileSHLIB_LINK = -lmemcached -lsasl2
vi MakefileSHLIB_LINK = -L/opt/libmemcached-0.48/lib -lmemcached -lsasl2
gmakegmake install
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
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
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 +
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
digoal=> insert into tbl_user_info select generate_series(1,10000000), md5(clock_timestamp()::text);INSERT 0 10000000
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();
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();
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();
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;
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
-M return error on memory exhausted (rather than removing items)digoal=> select count(*) from tbl_user_info ;count----------10000001(1 row)
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)
[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
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
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
digoal=> select memcache_get('tbl_user_info_10000001_pwd');memcache_get----------------------------------07e975fb5fc9a308760cb7711681635b(1 row)
--enable-64bit build 64bit version
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';
-- $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;