开发者学堂课程【PostgreSQL快速入门：6 PostgreSQL 连接池，本地高速缓存，异地高速缓存 】学习笔记与课程紧密联系，让用户快速学习知识
课程地址：https://developer.aliyun.com/learning/course/16/detail/92
6 PostgreSQL 连接池，本地高速缓存，异地高速缓存
内容介绍：
一、连接池及数据库高速缓存
二、连接池
三、数据库高速缓存
四、本地高速缓存pgfincore
五、练习
一、连接池及数据库高速缓存
1、postgresql本身是cs结构，相当于每个客户端连接都会有对应的服务端进行交互，对于短连接postgresql对应的连接开销比较大，比较适合长连接。
2、短连接需要第三方的插件，以pgbouncer为例，理解数据库连接池在短连接环境下的好处，连接池的几种模式和使用场景，pgbouncer是线程模式，客户端连接过来，不需要重新fock进程，对于短连接的效率比较高，后面也会进行测试。
3、几种外部高速缓存的介绍，本地的高速缓存，如oS层缓存pgfincore，外部的高速缓存，K-V缓存pgmemcached的使用。
二、连接池
1、为什么要使用连接池?
（1）理由一，由于PostgreSQL是进程模式，短连接会带来性能问题，看几个测试结果:
pg93@db-172-16-3-150-> vi test.sql
select 1;
短连接模式的tps。
pg93@db-172-16-3-150-> pgbench -M extended-n-r -f ./test.qI-c 16-j4-C-T30 进行30秒测试，C每次连接重新发起的意思
每个都要fock连接，可以不断的看见有进程被创建的过程
transaction type: Custom query
scaling factor: 1
query mode: extended
number of clients: 16
number of threads: 4
duration: 30s
number of transactions actually processed: 36100
tps = 1203.128160 (including connections establishing)
tps = 97264.142873 (excluding connections establishing)
statement latencies in milliseconds:
9，993634 select 1;
可以看到结果是每秒钟的tps有一千多，每次执行select 1都是连接上再断开，再连接再断开，16个变形，4个线程，线程指的是pgbench线程，不是postgresql线程，可以看到相当于postgresql每个连接都要重新fock过来，从postgre主进程fock子进程，再跟线程进行交互，这就是短连接带来的坏处，postgre相当于每个连接都要fock进程进行交互，短连接断开之后需要再次fock，达到系统极限。如果排除掉连接时间，可以达到九万多的tps，不使用C，现在就是长连接的测试，没有新的进程出来，使用短连接达到的是1173的tps，如果用长连接，30秒后，使用长连接可以达到8万，相当于差了80倍，性能差很多。对于短连接，只有外部加连接池使用。
使用prepared模式的tps
pg93@db-172-16-3-l50->pgbench-M prepared -n-r -f ./test.sql-c 16-j4-T 30
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 30 s
number of transactions actually processed: 3695465
tps = 123176.163882 (including connections establishing)
tps = 123233.120481 (excluding connections establishing)
statement latencies in milliseconds:
0.128769 Select 1;
（2）理由二，当客户端非常多时，大多数连接可能空闲，但是长时间占据一个连接，可能导致连接数超出数据库最大连接数配置，正常发起的请求
无法获得连接。使用prepared能后达到121130的tps，性能非常好，postgresql默认值默认是一百，可以配置两千。连接池有好处，客户端发起，中间架连接池到应用程序之间有两千或者三千的连接，但是到服务端postgresql可能只有一百个连接。这样可以省掉很多的连接。
（3）理由三，连接池可以挡掉一些非法请求，例如非法的访问非业务数据库的请求。连接池对于应用程序就是数据库，连接池里面没有配置的数据库，是没有办法访问的。
（4）理由四，连接池位于数据库和应用程序之间，比较容易实现负载均衡的功能，对应用程序透明。
2、pgbouncer连接池介绍
源码地址：
http://git.postgresql.org/gitwcb/2p=pgbouncer.gita=summary
现在的版本是1.6，下载。
拷贝到PostgreSQL主机，或者其他地方。
Configure-help有很多选项，可以使用异步的tps请求等。
pgbouncer是一个比较小巧的PostgreSQL连接池插件，采用线程模式，每个连接仅需2K内存，非常适合短连接的场景。对于客户端，比如连接一万条，仅需2K内存，需要内存很小。
pgbouncer支持三种连接复用模式:
会话模式，当客户端与pgbouncer会话断开时，服务端的连接才可以被复用。当会话还连接在上面时，连接池是不会被其他连接所附有的。
事务模式，当客户端与事务提交后，服务端的连接才可以被复用。比如一个事务提交后，原来占用的连接就可以释放给其它的应用程序，可以用到这个连接，
语句模式，当客户端语句执行完后，服务端的连接才可以被复用。没执行一条语句，就可以共享它的应用程序使用。
使用注意:
对于使用了绑定变量的客户端，请使用会话模式，因为会话中需要保存并复用named prepared statement的信息。如果使用事务模式，事务中断之后，prepared statement连接可能会被其它的程序所占用，下次再提交事务时用到的不是原来的连接，使用绑定变量的情况下，使用连接池会有一些问题，使用绑定变量就必须使用会话模式。
3、安装pgbouncer
需求
GNU Make 3.81+
libevent 2.x 依赖库
http://monkey,org/~provos/libevent/
可选
异步DNS请求库, c-areq
安装libevent 2.x
Wgethttps://github. com/downloads/libevent/libevent/libevent-2.0.21-stable.tar.gz
tar -zxvf libevent-2.0.21-stable.tar.gz
cd libevent-2.0.21-stable
./configure && make && make install
echo "/usr/local/lib" >>/etc/ld.so.conf放入目录里面
配置lib库路径
[root@ db-172-16-3-150 libevent-2.0.21-stable]# idconfig
[root@db-172-16-3-150libevent-2.0.21-stable]#idconfig -plgrep libevent
libevent_ pthreads-2.0.so.5 (libc6,x86-64) => /usr/localib/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_ extra-1.4.so.2 (libc6,x86-64) => /usr/lib64/libevent extra-1 .4.so.2
libevent_core-2.0.so.5 (libc6,x86-64) => /usr/local/ib/libevent_ core-2.0.so.5
libevent_ core-1.4.so.2 (libc6,x86-64) => /usr/lib64/libevent_ core-1.4.so.2
libevent-2.0.so.5 (libc6,x86-64) = > /usr/local/lib/libevent-2.0.so.5
libevent-1.4.so.2 (libc6,x86-64) => /usr/lib64/libevent-1 .4.so.2
安装异步DNS请求LIB库
wget http://c-ares.haxx.se/download/c-ares-1.10.0.tar.gz如果没有安装可以从里面进行下载
tar -zxvf c-ares-1.10.0.tar.gz
cd c-ares-1.10.0
./configure && gmake && gmake install
[root@ db-172-16-3-150 c-ares-1.10.0]# idconfig
[root@ db-172-16-3-150 c-ares-1.10.0]# idconfig -plgrep ares
libcares.so.2 (libc6,x86-64) => /usr/local/lib/libcares.so.2
libcares.so.2 (libc6,x86-64) => /usr/lib64/libcares.so.2
libcares.so (libc6,x86-64) => /usr/local/lib/libcares.so
安装pgbouncer
git clone git://git.postgresql.org/git/pgbouncer.git
cd pgbouncer
git submodule init
git submodule update
./autogen.sh
./configure--prefix=/opt/pgbouncer --with-libevent=/usr/local/lib --with-cares=/usr/localib指定安装目录，指定目录，指定需要请求的localib
make
make install
命令行参数
[root@db-172-16-3-150opt]# /opt/pgbouncer/bin/pgbouncer --help
Usage: pgbouncer [OPTION]... config.ini
-d, --daemon
Run in background (as a daemon)
-R, --restart
Do a online restart
-q, --quiet
Run quietly
-v, --verbose
Increase verbosity
-u, --user <username> Assume identity of <usemame>
-V, --version
Show version
-h. --help
Show this help screen and exit
安装完之后可以看帮助文件，安装在pgbouncer1.6，可以看到使用方式，d是重启pg方式并且作为后台进程，R是在线重启，可以确保客户端和pg方式的连接不会中断，q以quiet模式进行运行，不输出任何东西，v表示增加输出详细的信息，u是pg方式需要用户启动，V是1.6的开发版。
创建配置文件。在pgbouncer1.6安装的，在里面创建目录，etc目录放配置文件，log日志目录，比如用pg93用户启动pgbouncer进程就附给它，写配置文件。
配置pgbouncer
1.主配置文件
mkdir -p /opt/pgbouncer/etc
cd /opt/pgbouncer/etc/
vi config.ini
[databases]
aliandb1 = host=172.16.3.150 port=1921 dbname=digoal client_ encoding=sql_ ascii datestyle=ISO pool_ size =20 pgbouncer 数据库
[pgbouncer]
pool_ mode = transaction
listen_ port = 6543
listen_ addr = 0.0.0.0
auth_ type = md5
auth_ file = /opt/pgbouncer/etc/users.txt
logfile = /opt/pgbouncer/etc/pgbouncer.log
pidfile = /opt/pgbouncer/etc/pgbouncer.pid
unix_ socket_dir = /opt/pgbouncer/etc
admin_ users = pgadmin
stats_ users = pgmon
server_ reset_ query = DISCARD ALL
server_ check_ query = select 1
server_ check_ delay = 30
max_ client_ conn = 50000
default_ pool_ size = 20
reserve_ pool size= 5
dns_ max_ ttl= 15
连接pg_ct1 start数据库
连接池的大小
POOL1 = user1+Aliasdhpame1 配了1个用户，配一个库，算出词的大小
POOLn = user1+Aliasdbhamen
POOLxn = userx+Aliasdbnamen 有好多个用户，并且有好多个Aliasdb，词的大小就是全部都加起来
max_ client conn = 50000配置总的连接数，连接数跟词有关系
Aliasdb1. host-172.16.3.150 port-1921 dbnane-digoal client encoding-sql ascii datestyle=180 pool size=20数据库配置
Aliasdb1是pgbouncer数据库别名，数据库名字叫digoal，监听1921端口，进程是pg93启动的，ip地址是172.16.3.150。
如果pgbouncer连接的是另外一台数据库，连接172.16.3.33，启动的是5432端口，创建数据库，配置可以在配置文件中指定，pgbouncer源码里面能配哪些配置，可以配dlient_encoding，datestyle,时区，stanfard_conforming_string，pool_size不配就是默认的pool_size，数据库连接过来允许最大是20个连接，指的是server端的连接，不是客户端的连接，客户端可以允许很多个连接，从pgbouncer连接到数据库，最多允许20个连接，并且对应到一个用户，如果配了两个用户，就应该对应20个，监听指的是pgbouncer本身的监听，监听0.0.0.0，连接选项是nd5，配置密码文件，
auth file . /opt/pgbouncer1.6/etc/users. txt用户密码文件
logfile/opt pgbouncer1.6/etc/pgbouncer. log日志文件
pidfile . /opt pgbounc r1.6/etc/ pgbouncer. pid位置
adnin users pgadmin 管理用户，随便取名字
users . pgnon查看pgbouncer本身进程，跟输出没有任何关系
server_ reset_ query = DISCARD ALL当pgbouncer连接到query可以被复用之前DISCARD ALL，才能给其它的客户端复用
server_ check_ query = select 1
server_ check_ delay = 30每隔三十秒
max_ client_ conn = 50000最大连接数到客户端
default_ pool_ size = 20
reserve_ pool size= 5
dns_ max_ ttl= 15
2. 用户密码配置文件
配置用户管理密码
"pgadmin" "321 admin"
配置查看状态信息
"pgmon" "hello1888"
创建用户
create role digoal login encrypted password " digoal'
给权限
grant all on database digoal to digoal；
cd /opt/pgbouncer/etc/
vi users.txt
"postgres" "md53175bceld3201d16594cebf9d7eb3f9d"
"pgadmin" "md55bde83786c10fc0f383464f6e56a6d6e"
"pgmon" "123abc'
MD5密码封装规则md5(密码+用户名),与PostgreSQL中存储的md5一致。把密码改成123，md5就变了。加密方法是一样的。
假如pgadmin的密码123abc, 封装成md5为: md55bde83786c 10fc0f383464f6e56a6d6e
digoal=# select md5('123abcpgadmin');
md5
5bde83786c10fc0f383464f6e56a6d6e
(1 row)
给用户使用密码时encrypted password，如果是unencrypted password表里面存的是明文，非常危险，一定要用
encrypted password，否则密码会完全暴露，配完之后启动连接池，用普通用户启动即可，
/opt/pgbouncer1.6/bin/pgbouncer-d/opt/pgbouncer1.6/etc/config. Ini
选择6543监听端口，用6543连接pgbouncer管理后台，密码是321admin。
Pgbouncer命令行管理
pg93@db-172-16-3-150-> psql-h 127.0.0.1 -p 6543 -U pgadmin pgbouncer
Password for user pgadmin:
这里输入pgadmin用户的密码
psql (9.3.1, server 1.6év/bouncer)
Type "help" for help
pgbouncer=# show help;
NOTICE: Console usage
DETAIL:
SHOW HELP CONFIG DATABASES POOLS CLIENTS SERVERS VERSION
SHOW STATS FDS SOCKETSACTIVE_ SOCKETSLISTSMEM
SHOW DNS_ HOSTS DNS_ ZONES
SET key= arg
Alicasdb1别名断开172.16.3.33，pgbouncer管理库连接本地监听，可以看pool有哪些值，当前版本1.6开发版，重新做配置，如果需要关掉，就在pgbouncer管理后台shutdown，查看统计信息，连接数据库，通过连接池连接，通过pgbouncer连接到172.16.3.150。
短连接的测试结果
pg93@db-172-16-3-150-> pgbench -M extended -n-r -f /test.sql -h /tmp -p 6543 -U postgres-c 16-j 4 -C -T 30 aliasdb1
transaction type: Custom query
scaling factor: 1
query mode: extended
number of clients: 16
number of threads: 4
duration: 30s
number of transactions acte.álly processed: 213378
tps = 7110.818977 (including connections establishing)
tps = 181282.71 1671 (excluding connections establishing)
statement latencies in milliseconds:
1.698879 select 1;
密码文件方便在连接时可以不输入密码，方便做检测和自动化的脚本。
6543 : aliasdb1 :digoal:digoa1用户连接都不需要密码
transaction连接事务模式的连接池，使用短连接进行测试因为是线程模式所以看不到连接，在实际的机器上也看不到连接，使用连接池用短连接进行测试，没有理想中非常高的情况，pgbouncer本身处理短连接比postgresql自己处理要好很多。
虽然用了连接池，连接池的server端被复用前也要进行DISCARD ALL的操作。看不到重新建立连接，它不需要连接，因为它是连接池，连接池允许20个连接，实际情况中需要DISCARD ALL，短连接比较少，短连接的开销已经降低到最小，如果使用长连接，prepared statement不允许使用连接池事务模式，要使用连接池会话模式，改成extended不会有问题，或者把pool_mode改成session模式，结果41926，相比在不过连接池的情况下性能差一倍，改成会话模式再进行，比41926要高，因为用了prepared模式又是会话模式没有复用，prepared和会话模式之间差别不是很大。
pgbouncer各参数介绍
查看doc/config.txt
三、数据库高速缓存
本地高速缓存pgfincore
OS Cache
http://git.postgresql.org/ gitweb/?p=pgfincore.git;a=summary
利用posix_ fadvise修改文件的advicei. (参见pgfincore.c
posix_fadvise(fd, o, o, adviceFlag);)
man posix_ fadvise
Shared buffers是有限的，默认是128kb，为了测试外部的高速缓存，设置小一点，改变文件某一个数据块int posixfadvise(int fd, off t offset,off_t len,int advice) ;位移，长度，在文件描述符对应的set里面有多少长度指定物理文件里面数据区域的未来数据不会读会从bireuse剔除掉，如果未来读willneed，验证了数据块在未来可能会读，保持在posix padu，通过调用posix_fadvise改变文件描述符对应的区域标签，让数据持久的保存在操作系统内存，起到高速缓存的作用
异地高速缓存pgmemcache 数据已经超出了本地的内存的大小，加本地内存已经有128g内存，加到256g，只要能够满足数据的小大即可
memcached做数据的中转缓存，在应用程序中用的比较多，在数据库本身用的比较多的是pgfincore
除了рgmеmсасhе以外, рgrеdіѕ也是类似的项目, 只不过是rеdіѕ的一些封装好的АРІ。
参考：htps://github.com/siavashg/pgredis
四、本地高速缓存pgfincore
安装
tar -zxvf pgfincore-b371336.tar.gz
mv pgfincore-b371336 postgresql-9.3. 1/contrib/
cd postgresql-9.3.1/contrib/pgfincore-b371336/
export PATH=/home/pg93/pgsql/bin:$PATH
which Pg_ config
/home/pg93/pgsql/bin/pg_ config
gmake clean
gmake
gmake install
[root@ db-172-16-3-150 pgfincore-b371336]# su - pg93
pg93@db-172-16-3-150-> psql
Type "help" for help.
digoal=# create extension pgfincore;
CREATE EXTENSION
两周以前做了更新，下载拷贝到数据库本身的主机上，解压，再到数据库中创建extension，pg_config不在，要加到路径中，
root root 67K Dec 29 12 :47 pgfincore.so
连接数据库，创建pgfincore，会出现pgfadivise，
pgfadivise_normal,pgfadivise_loader,pgfadivise_random随意访问标签,pgfadivise_sequential连续访问标签,
pgfadivise_willneed下次需要再次访问的标签，相当于把它加载到内存中，
本地高速缓存pgfincore
测试
digoal=# create table user_ info(id int primary key, info text, crt_ time timestamp);创建用户表
CREATE TABLE
digoal=# insert into user_ _info selct generate_ series( I ,000000), md5(randomQ:text), clock timestamp0;插入五百万条信息
改参数,便于观察本地OS缓存,重启数据库
shared_ buffers = 32MB 在shared_ buffers里面放不下
echo 3 > /proc/sys/vm/drop_ caches清缓存
pgbench测试脚本
vi test.sql
\setrandom id 1 5000000
select * from user_info where id=:id;
加载本地缓存前的测试结果
pg93@ db-172-16-3-150-> pgbench -M prepared -n-r -f /test.sql-c 16-j4 -T 10 digoal没有使用高速缓存的情况下，连接16个线程测试10秒钟，测试digoal库，测试完是3万多，因为是ssd的硬盘，比较高
info字段是text字段，如果字段超出2kb，会写到toas表中
select* from pg_class where oid 117665;
查到对应的toas表，
Select* from pg_Class where relname=' user_ info;
对应的toas表的oid是117665,如果索引是变长字段索引，需要加id，因为现在是定长字段的索引，已经完全加好
select *from pgf advise willneed(' pg_toast pg toast 117662' : : regclass
transaction type: Custom query
scaling factor: 1
query mode: prepared
number of clients: 16
number of threads: 4
duration: 10 s
number of transactions actually processed: 27743
tps = 2760.238242 (including connections establishing)
tps = 2764.017578 (excluding connections establishing)
异地高速缓存pgmemcache
htp://blog.163.com/digoal@ 126/blog/static/163877040201210172341257/
pgmemcache是一系列的PostgreSQL函数,用于memcache的读写操作。
通过pgmemcache以及PostgreSQL的触发器可以方便的对数据库中的数据进行缓存。使数据跟缓存有一定的同步，同步之后才能进行生成。pgmemcache是PostgreSQL一系列函数，如果是程序里面控制pgmemcache的使用就不需要安装，方便不想自己调用pgmemcache，可以通过pgmemcache插件。
当然缓存的操作也可以挪至应用程序自己来处理，pgmemcache只是一种选择。
pgmemcache的安装
pgmemcache依赖libmemcache和PostgreSQL
libmemcache依赖libevent和memcached
memcached依赖libevent
依次安装
memcached是外部缓存，安装在3.33上面，libevent安装的是1.4版本
安装libevent
Wget https://github.com/downloads/libevent/libevent/libevent-2.0.20- stable.tar.gz
tar -zxvflibevd-2.0.20-stable.tar.gz
cd libevent-2.0.20-stable
./configure
make
make install
加入lib库路径
vi /etc/id.so.conf
/usr/local/lib目录
idconfig
idconfig -plgrep 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
3.33上放一个缓存，150上面是一个数据库，并且在数据库里面安装pgmemcache的插件，插件里面包含一堆postgresql的函数，用于操作memcache，使用外部缓存要注意数据一致性问题，如果这次失败，但是本地成功，或者本地更新失败，但是刷缓存成功，出现缓存和数据库存的数据不一致的情况，很危险，数据库里面存的数据不可靠，在使用时要当心，用了缓存，要确保缓存和数据库一致，安装完后把库放到路径中，把库加到libevent中。安装memcached下载解压
Wgethttp://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 lopt/memcached-1 .4.1 5/share/man/man1 帮助手册可以看到具体如何使用
man ./memcached.1
启动memcached
memcached -d -u pg93 -m 800启动之后耗费多大，用postgres启动，监听端口是11211，800兆
小提示
64bit对应pointer. size : 64,所以将占用更多的空间，如果没有超过20亿的key,使用32位就够了。
安装libmemcached
wget http://download.tangent.org/libmemcached-0.48.tar.gz
tar -zxvf libmemcached-0.48.tar.gz
cd libmemcached-0.48
Jconfigure --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
idconfig
idconfig -plgrep 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
libmemcachedso(libc6.x86-64)=> /opt/libmemcached-0.48/lib/libmemcached.so
安装pgmemcache，也可以在git. postgresql.org Git中下载
https: //github. com/ohmu/pgmemcache在网页中下载，更新都是在github里面更新的。
wgethttp://pgfoundry.org/frs/download php/301&/pgmemcache_ 2.0.6.tar.bz2 -- 或者
https://github.com/ohmu/pgmemcache/
tar -jxvf pgmemcache_ 2.0.6.tar.bz2
cd pgmemcache
cp -r /root/libnemcached- 0.48 . libmemcached
需要用到pg _config, 所以需要加入到PATH中
. /home/pg9.2.0/.bash_ profile
pgmemcache的头文件中包含了libmnemcached的一些头, 如下，所以需要将这些头文件拷贝到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 /optlibmemeached-0.48/include/libmemcached ./
同时编译时需要用到libmemcached.so,如下Makefile:
less Makefile
SHLIB_ LINK = -imemcached -lsasl2
但是没有指定库目录,所以需要修改一下
vi Makefile
SHLIB_ LINK = -L/opt/libmemcached-0.48/lib-imemcached -isasl2
接下来编译安装就可以了
gmake
gmake install
bin/mkdir -p , /hone pg93/pgsq19.3.1/lib'安装
bin/mkdir -p , hone/pg93/pgsq 19.3.1/s hare/contrib
usr/bin/install-c-m 755 pgnemcache.so‘/home pg93/pgsq19.3.1 lih pgnenc ache . so '
/usr/bin/install-c-m644pgnencache.sql, /hone/pg93/pgsq19.3.1/s hare/contrib/'
pgmencache # which psql
pgnemcache已经编译好，放到路径中，后面再加路径时就没加。
安装好pgmemcache后，需要修改PostgreSQL的配置文件重启数据库,
这里假设172.16.3.150上已经启动了memcached
su- pg93
cd $PGDATA
vi postgresql.conf
shared_ preload_ libraries = 'pgmemcache'修改配置文件
pgmemcache. default_ servers = '172.16.3.150:11211' #多个memcached用逗号隔开配置
pgmemcache. default_ behavior = 'BINARY_ PROTOCOL:1' #多个配置用逗号隔开配置
-ruxr-xp-x 1 root root 96418 dec 29 13:25 pgmemcache.so安装
telnet 172.16.3.33 11211
重启数据库：
root root 6.0K Dec 29 13:25 pgnencache .sql在哪个库下面用它就安装到哪个库里面
psq1-f/home/pg93/pgsql/share/contrib/pgmemcache.sql
问题还是跟版本有关系
pg_ ctl restart -m fast
在加载pgmemcache.sqI前,需要对这个脚本修改一下，否则会报语法错误
cd $PGHOME/share/contrib
vi pgmemcache.sql
LANGUAGE 'C替换掉，写的语法问题，改成LANGUAGE C，不加单引号，因为是root安装的，所以用root更改，再执行就不会报错。
:%s/LANGUAGE\ 'C/LANGUAGE C/g
:х!
可以看到LANGUAGE相关的函数。
在需要的库中执行脚本:
psql -h 127.0.0.1 -U postgres digoal -f ./pgmemcache.sql
测试 :
digoal=> select memcache_ set('key1', '1‘);设置键值以及值
列出服务单，memcache_stats可以看到server端是172.16.3.33，
memcache_ setpid是10326，启动时间1388295006，版本，64位。
Server: 172.16.3.33 <11211> ,
pid: 10326
uptime: 1742
time: 1388295006
version: 1.4.15
Pointer size :54
rusage user: 0.。0
rusage sys tem: 0.0
curr_ items: 0
total_ itens : 0
bytes: 0
Curr_connections : 5
Total_connections : 8
Connections_Puctures : 6
cnd get: 0
cndset:0
get hits: 0
get misses: 0
evict ions: 0
bytes_ read: 26
Bytes_written: 1025
Limit_maxbytes: 8 38860800
threads: 4
t
(1 row)
digoal= > select memcache_ get('key1');取值
memcache_ get
1
(1 row)
digoal=> select ngemcache_ incr('key1',99);
memcache_ incr
100
(1 row)
digoal=> select memcache_ incr('key1‘,99);
memcache_ incr
199
(1 row)
digoal= > select memcache_ stats0;
memcache_ stats
+
Server: 172.16.3.150(11211)+
pid: 1918+
uptime: 13140+
time: 1353222576+
version: 14.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
digoal=> select memcache_ get('key1’);
memcache_ get
(1 row)
cache应用场景举例,将用户密码作为K-V存储到MEMCACHED中,密码校验先从memcached进行匹配,未匹配到再到数据库中检索.
1.测试表
digoal=> create table tbl_user_ jinfo (userid int8 primary key, pwd text);创建用户表
NOTICE: CREATE TABLE/ PRIMARY KEY will create implicit index "tbl_ user. jinfo_ pkey" for table "tbl user _info"
CREATE TABLE
2.测试数据
digoal=> insert into tbl_ user jinfo select generate serics(1,10000000 md5(clock_timnestamp0::text);
INSERT 0 10000000
3.更新触发器(不安全，SQL回滚后memcache的操作不能自动回滚)
CREATE OR REPLACE FUNCTION tbl_user. _info_ upd) RETURNS TRIGGER AS $$
BEGIN
IF OLD.pwd != NEW.pwd THEN先判断新旧密码是否不一样
PERFORM memcache_ set('tb1_ user_info. 'NEW.userid ' _pwd', NEW .pwd);
END IF;
RETURN NULL;如果一样就返回空
END;
$$LANGUAGE 'plpgsql' STRICT;
CREATE TRIGGER tbl_ user_ info_ yupd AFTER UPDATE ON tbl _user_ info FOR EACH ROW EXECUTE PROCEDURE ·tbl_ user info _upd();在更新完之后触发。
如果本地更新失败了，回滚，如果 memcache失败，说明memcache里面存的密码还是老的密码。先把memcache老的密码去掉，再进行set，安全。
4.插入触发器(不安全,同理)
CREATE OR REPLACE FUNCTION tbl_user_ info_ ins（）RETURNS TRIGGER AS $$
BEGIN
PERFORM memcache_ et('tbl_user_info_' NEW.userid '_pwd', NEW.pwd);
RETURN NULL;
END;
$$LANGUAGE 'plpgsql' STRICT;
CREATE TRIGGER tbl_ user_ info_ ins AFTER INSERT ON tbl_ _user_ info FOR EACH ROW EXECUTE PROCEDURE tbl_ user_ info_ ins();
插入时要执行set，再插入after触发器，不安全，因为用户密码改完之后回滚，tbl_user_info已经改掉，memcache没有办法回滚，但是表是可以回滚的。
5.删除触发器(安全,因为无法命中cache是安全的,但是cache数据和table数据不一致是不安全的)密码删除掉，memcache没有密码条目，表回滚，表里面的数据还在，下次查询memcache没法命中而已，并不会造成数据不一致，memcache不会和数据库的数据造成不一致的情况
CREATE OR REPLACE FUNCTION tbl_ user_ info_ del（）RETURNS TRIGGER AS $$
BEGIN
PERFORM memcache_delete('tbl _user_ info _' NEW.userid '_ pwd');
RETURN NULL;
END;
$$ LANGUAGE 'plpgsql' STRICT;
CREATE TRIGGER tbl_user_ info_del AFTER DELETE ON tbl_ user_ info FOR EACH ROW EXECUTE PROCEDURE tbl_ user_ _info_ del();
6. 用户密码校验函数:
校验密码是否跟提供的密码一样
GREATE 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
如果不一样，就会返回错误，检验时先去memcache
amelect pwd into v _user_ pwd_ md5 from tbl_ user _info where userid=i_userid;用户id和用户密码
if found then
raise notice 'hit in table';命中不为空
if(v_ input. _pwd_ md5 =v_ user_ pwd_ md5) then存在md5
return true;
else
return false;
end if;
else
return false;
end if;
end if;
exception
when others then
return false;
end;
$$ language plpgsql strict;
比如有三个触发器delete，insert，update函数，有一百万个表，查看密码是否正确，查出5条密码，检验密码，用户的id是1，密码是06a3827a b2c a973277a6446099ca9faf会告诉现在是在表里面命中的，因为记录还没有写在memcache中，设置key的值tbl_ user _info_1_pwd,06a3827a b2c a973277a6446099ca9faf，再验证就会告诉memcache命中，密码不正确，get<‘tbl_ user _info_1_pwd’>;
得到的密码是06a3827a b2c a973277a6446099ca9faf，密码一样，但是报了flase密码认证失败。
输入的密码做了md5加密，用户传上来的是明文的密码，在md5加密再进行校验，
md5跟06a3827a b2c a973277a6446099ca9faf不匹配，改一下即可，比如用户传上来的就是md5码，存的是用户的md5码，返回的就是t，其实_pwd存的用户的md5码，真正的密码没存，用户传上来是明文，所以加个md5，md5<i pud>;
五、练习
1、pgbouncer连接池搭建,几种模式的使用对比
2、pgbench压力测试，测试短连接
3、本地高速缓存pgfincore的使用,测试它带来的性能提升
4、异地高速缓存pgmemcached的使用
pgbouncer可以在环境里面进行测试。
