开发者社区> bqospzg5rfs7g> 正文
阿里云
为了无法计算的价值
打开APP
阿里云APP内打开

6 PostgreSQL 连接池,本地高速缓存,异地高速缓存|学习笔记

简介: 快速学习6 PostgreSQL 连接池,本地高速缓存,异地高速缓存
+关注继续查看

开发者学堂课程【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

image.png

现在的版本是1.6,下载

image.png

拷贝到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方式需要用户启动,V1.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 pgbounce​​r 数据库

[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 digoa​​l​​

c​​d /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

两周以前做了更新下载拷贝到数据库本身的主机上解压再到数据库中创建extensionpg_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的触发器可以方便的对数据库中的数据进行缓存使数据跟缓存有一定的同步同步之后才能进行生成pgmemcachePostgreSQL一系列函数如果是程序里面控制pgmemcache的使用就不需要安装方便不想自己调用pgmemcache可以通过pgmemcache插件

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

pgmemcache的安装

pgmemcache依赖libmemcachePostgreSQL

libmemcache依赖libeventmemcached

memcached依赖libevent

依次安装

memcached是外部缓存安装在3.33上面,libevent安装的是1.4版本

安装libevent

Wget​​ ​​ht​​t​​p​​s​​://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目录

i​​dconfig

i​​dconfig -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下载解压

Wgeth​​t​​tp://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 h​​t​​tp://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

i​​dconfig

i​​dconfig -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中下载

image.png

https: //github. com/ohmu/pgmemcache在网页中下载更新都是在github里面更新的

wgethttp://pgfoundry.org/frs/download php/301&/pgmemcache_ 2.0.6.tar.bz2 -- 或者

ht​​t​​ps://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 = -​​i​​memcached -lsasl2

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

vi Makefile

SHLIB_ LINK = -L/opt/libmemcached-0.48/​​l​​ib-​​i​​memcached -​​i​​sasl2

接下来编译安装就可以了

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/insta​​ll​​-​​c​​-m 755 pgnemcac​​he​​.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

pgnemcac​​he​​已经编译好放到路径中后面再加路径时就没加

安装好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: 1​​0​​25

Limit​​_​​maxbytes: 8 3886​​0800

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('key​​1’​​);

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('tb​​1​​_ 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;

比如有三个触发器deleteinsertupdate函数有一百万个表查看密码是否正确查出5条密码检验密码用户的id是1,密码是06a3827a b2c a973277a6446099ca9faf会告诉现在是在表里面命中的因为记录还没有写在memcache设置key的值tbl_ user _info_1_pwd,06a3827a b2c a973277a6446099ca9faf再验证就会告诉memcache命中密码不正确get<tbl_ user _info_1_pwd>;

image.png

得到的密码是06a3827a b2c a973277a6446099ca9faf密码一样但是报了flase密码认证失败

输入的密码做了md5加密用户传上来的是明文的密码在md5加密再进行校验

md506a3827a b2c a973277a6446099ca9faf不匹配改一下即可比如用户传上来的就是md5存的是用户的md5返回的就是t其实_pwd存的用户的md5真正的密码没存用户传上来是明文所以加个md5,md5<i pud>;

五、练习

1、pgbouncer连接池搭建,几种模式的使用对比

2、pgbench压力测试,测试短连接

3、本地高速缓存pgfincore的使用,测试它带来的性能提升

4、异地高速缓存pgmemcached的使用

pgbouncer可以在环境里面进行测试

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
视频 -RDS 基础概念介绍(四)| 学习笔记
快速学习视频 -RDS 基础概念介绍。
0 0
视频-RDS常见问题排除及 DAS 自动弹性伸缩(四)| 学习笔记
快速学习视频 -RDS 常见问题排除及 DAS 自动弹性伸缩。
0 0
视频- RDS 常见问题排除及 DAS 自动弹性伸缩(三)| 学习笔记
快速学习视频 -RDS 常见问题排除及 DAS 自动弹性伸缩。
0 0
视频 -RDS 常见问题排除及 DAS 自动弹性伸缩(一)| 学习笔记
快速学习视频 -RDS 常见问题排除及 DAS 自动弹性伸缩。
0 0
RDS MySQL 高效设计及性能调优(四)| 学习笔记
快速学习 RDS MySQL 高效设计及性能调优。
0 0
RDS MySQL 高效设计及性能调优(二)| 学习笔记
快速学习 RDS MySQL 高效设计及性能调优。
0 0
本地执行 RDS SQL 语句| 学习笔记
快速学习本地执行 RDS SQL 语句
0 0
PostgreSQL 流复制搭建主从环境,同步和异步的解释,压力测试,主从角色切换|学习笔记
快速学习PostgreSQL 流复制搭建主从环境,同步和异步的解释,压力测试,主从角色切换
0 0
PostgreSQL 监控1统计进程和统计信息的解读|学习笔记(二)
快速学习PostgreSQL 监控1统计进程和统计信息的解读
0 0
《RDS数据库入门一本通》电子版地址
本书体系完整,理论充实,内容由浅入深,循序渐进,更配有详细的实例解说,为初学者提供一个完整、严密的思维框架,零基础的你也可轻松掌握RDS数据库的美妙节奏。
0 0
文章
问答
来源圈子
更多
技术图谱:由专家组参与技术图谱的绘制与编写,知识与实践的结合让开发者们掌握学习路线与逻辑,快速提升技能 电子书:电子书由阿里内外专家打造,供开发者们下载学习,更与课程相结合,使用户更易理解掌握课程内容 训练营:学习训练营 深入浅出,专家授课,带领开发者们快速上云 精品课程:汇集知识碎片,解决技术难题,体系化学习场景,深入浅出,易于理解 技能自测:提供免费测试,摸底自查 体验实验室:学完即练,云资源免费使用
+ 订阅
文章排行榜
最热
最新
相关电子书
更多
云数据库RDS MySQL从入门到高阶
立即下载
PolarDB for PostgreSQL 源码与应用实战
立即下载
PolarDB for PostgreSQL 开源必读手册
立即下载