参考文档:
1)德哥博客:http://blog.163.com/digoal@126/blog/static/163877040201192535630895/
2)/wiki介绍:http://wiki.postgresql.org/wiki/PL/Proxy#Documentation( 包含PLProxy下载地址,用法介绍,用法举例)
3)德哥视频:http://pan.baidu.com/share/link?shareid=5060&uk=201377085
遗留问题:
1)plproxy 建立Cluster 后可以运行在RUN ALL下,这个时候如果链接pgbouncer期中一个节点失效
那么,运行在整个节点上的函数全部都会报错,对于分布式数据库需要从分库查询数据时就会影响
到可以正常工作的库。
2)从各个文章中发现有介绍怎么在代理库上建立代理函数,查询当前库的版本和节点配置信息以及
他们的链接参数有什么意义。
PostgreSQL分布式设计:
1)德哥博客:http://blog.163.com/digoal@126/blog/static/163877040201192535630895/
2)/wiki介绍:http://wiki.postgresql.org/wiki/PL/Proxy#Documentation( 包含PLProxy下载地址,用法介绍,用法举例)
3)德哥视频:http://pan.baidu.com/share/link?shareid=5060&uk=201377085
遗留问题:
1)plproxy 建立Cluster 后可以运行在RUN ALL下,这个时候如果链接pgbouncer期中一个节点失效
那么,运行在整个节点上的函数全部都会报错,对于分布式数据库需要从分库查询数据时就会影响
到可以正常工作的库。
2)从各个文章中发现有介绍怎么在代理库上建立代理函数,查询当前库的版本和节点配置信息以及
他们的链接参数有什么意义。
PostgreSQL分布式设计:
三层结构 :
1. 路由层(几乎无限扩展)
主角: plproxy,postgresql
2. 连接池层(几乎无限扩展)
主角: pgbouncer
3. 数据层(几乎无限扩展)
主角: postgresql
扩展方式:
1. 路由层扩展:
路由层包含了数据层的连接配置(FDW或函数),plproxy语言写的函数壳(内置路由算法),这些基本上是静态数据,所以扩展非常方便.
添加服务器就行了.
2. 连接池层扩展:
连接池层扩展加服务器.
3. 数据层扩展:
数据层扩展,添加服务器,通过流复制增加数据节点,结合路由算法重分布数据(建议路由算法2^n取模),
物理分布:
1. 路由层和连接池层尽量靠近部署.可以考虑部署在同一台物理机.
2. 数据层尽量每个节点一台物理机.
环境需求:
CentOS 5.7 x64flex-2.5.35PostgreSQL-9.1.1plproxy-2.2pgfincore-v1.1libevent-1.4.14b-stablepgbouncer 1.4.2
测试环境描述:
1. pgbench : 172.16.3.1762. pgbouncer on pgbench HOST :172.16.3.176:1998(proxy0 = host=172.16.3.150 dbname=proxy port=1921 pool_size=16proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16proxy3 = host=172.16.3.33 dbname=proxy port=1921 pool_size=16)3. PostgreSQL 数据节点 : 172.16.3.150:1921/digoal, 172.16.3.39:1921/digoal, 172.16.3.40:1921/digoal, 172.16.3.33:1921/digoal4. PostgreSQL plproxy节点 : 172.16.3.150:1921/proxy, 172.16.3.39:1921/proxy, 172.16.3.40:1921/proxy, 172.16.3.33:1921/proxy5. pgbouncers on plproxy HOST :172.16.3.150:1999, 172.16.3.39:1999, 172.16.3.40:1999, 172.16.3.33:1999(digoal0 = host=172.16.3.150 dbname=digoal port=1921 pool_size=8digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8digoal3 = host=172.16.3.33 dbname=digoal port=1921 pool_size=8)
环境搭建:
1. 编译安装flex-2.5.35
./configure && make && make install
2. 编译安装PostgreSQL-9.1.1
./configure --prefix=/opt/pgsql --with-pgport=1921 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt --enable-thread-safety --with-wal-segsize=64gmake worldgmake install-world
3. 编译安装plproxy-2.2
mv plproxy-2.2 postgresql-9.1.1/contrib/plproxy-2.2make PG_CONFIG=/path/to/pg_configmake install PG_CONFIG=/path/to/pg_config
4. 编译安装pgfincore-v1.1
(这个扩展可以把表放到操作系统的Cache中,提高性能)
mv pgfincore-v1.1 postgresql-9.1.1/contrib/pgfincore-v1.1cp pgfincore.control /make cleanmakesumake install PG_CONFIG=/path/to/pg_config
5. 编译安装libevent-1.4.14b-stable
./configure && make && make install
6. 编译安装pgbouncer 1.4.2
./configure --prefix=/opt/pgbouncer && make && make install
注意:如果找不到相关的 xxx.so文件,请把该文件的目录放入文件,最后执行 /sbin/ldconfig –v命令
vim /etc/ld.so.conf
include /etc/ld.so.conf.d/*.conf
/usr/local/lib/
配置:
1. 配置数据节点信息
新建用户 : digoal(nosuperuser)
新建表空间 : digoal, digoal_idx
新建数据库 : digoal
digoal库新建schema : digoal
digoal库新建过程语言 : plpgsql
允许代理函数连的连接池所在的服务器连接上面新建的用户和库 : 配置pg_hba.conf
配置postgresql.conf : 略
2. 配置plproxy节点信息(本例与数据节点共用PostgreSQL数据库实例集群)
新建用户 : proxy(nosuperuser)
新建表空间 : 共用digoal
新建数据库 : proxy
proxy库新建schema : proxy
plproxy初始化 : 用超级用户执行/opt/pgsql/share/contrib/plproxy.sql 创建handler function,language,validator function,foreign data wrapper
更改language可信度(否则普通用户不可以使用plproxy语言) :
proxy=> \c proxy postgresupdate pg_language set lanpltrusted='t' where lanname='plproxy';
这个操作是为了途方便, 生产中请使用超级用户创建plproxy函数, 把execute权限赋予给普通用户.
3. 配置pgfincore
连接到数据节点\c digoal postgres
CREATE EXTENSION pgfincore;
4. 配置pgbouncer(代理函数连的连接池)
4台主机都需要配置,
postgres@db-digoal-> cat config1999.ini[databases]digoal0 = host=172.16.3.150 dbname=digoal port=1921 pool_size=8digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8digoal3 = host=172.16.3.33 dbname=digoal port=1921 pool_size=8[pgbouncer]pool_mode = transactionlisten_port = 1999unix_socket_dir = /opt/pgbouncer/etclisten_addr = *auth_type = md5auth_file = /opt/pgbouncer/etc/users1999.txtlogfile = /dev/nullpidfile = /opt/pgbouncer/etc/pgbouncer1999.pidmax_client_conn = 10000reserve_pool_timeout = 0server_reset_query =admin_users = pgbouncer_adminstats_users = pgbouncer_guestignore_startup_parameters = extra_float_digitspostgres@db-digoal-> cat users1999.txt"digoal" "md5462f71c79368ccf422f8a773ef40074d"
5. 配置pgbouncer(pgbench连的连接池)
postgres@db-digoal-> cat config1998.ini[databases]proxy0 = host=172.16.3.150 dbname=proxy port=1921 pool_size=16proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16proxy3 = host=172.16.3.33 dbname=proxy port=1921 pool_size=16[pgbouncer]pool_mode = transactionlisten_port = 1998unix_socket_dir = /opt/pgbouncer/configlisten_addr = *auth_type = md5auth_file = /opt/pgbouncer/config/users.txtlogfile = /dev/nullpidfile = /opt/pgbouncer/config/pgbouncer1998.pidmax_client_conn = 1500reserve_pool_timeout = 0server_reset_query =admin_users = pgbouncer_adminstats_users = pgbouncer_guestignore_startup_parameters = extra_float_digits
数据节点, 创建测试表, 插入测试数据:
proxy=# \c digoal digoalcreate table user_info(userid int,engname text,cnname text,occupation text,birthday date,signname text,email text,qq numeric,crt_time timestamp without time zone,mod_time timestamp without time zone);create table user_login_rec(userid int,login_time timestamp without time zone,ip inet);create table user_logout_rec(userid int,logout_time timestamp without time zone,ip inet);
测试数据 :
0号节点
insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(0,50000000,4),'digoal.zhou','德哥','DBA','1970-01-01',E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!','digoal@126.com',276732431,clock_timestamp(),NULL;
1号节点
insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(1,50000000,4),'digoal.zhou','德哥','DBA','1970-01-01',E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!','digoal@126.com',276732431,clock_timestamp(),NULL;
2号节点
insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(2,50000000,4),'digoal.zhou','德哥','DBA','1970-01-01',E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!','digoal@126.com',276732431,clock_timestamp(),NULL;
3号节点
insert into user_info (userid,engname,cnname,occupation,birthday,signname,email,qq,crt_time,mod_time)select generate_series(3,50000000,4),'digoal.zhou','德哥','DBA','1970-01-01',E'公益是一辈子的事, I\'m Digoal.Zhou, Just do it!','digoal@126.com',276732431,clock_timestamp(),NULL;
所有节点 :
set work_mem='2048MB';set maintenance_work_mem='2048MB';alter table user_info add constraint pk_user_info primary key (userid) using index tablespace digoal_idx;
开发:
数据节点 :
实体函数:
登录函数 :
create or replace function f_user_login(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$declarebeginselect userid,engname,cnname,occupation,birthday,signname,email,qqinto o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qqfrom user_info where userid=i_userid;insert into user_login_rec (userid,login_time,ip) values (i_userid,now(),inet_client_addr());return;end;$BODY$language plpgsql;
退出函数 :
create or replace function f_user_logout(i_userid int,OUT o_result int)as $BODY$declarebegininsert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());o_result := 0;return;exceptionwhen others theno_result := 1;return;end;$BODY$language plpgsql;
代理节点 :
创建server
CREATE SERVER digoal FOREIGN DATA WRAPPER plproxyOPTIONS (connection_lifetime '1800',disable_binary '1',p0 'dbname=digoal0 host=127.0.0.1 port=1999 client_encoding=UTF8',p1 'dbname=digoal1 host=127.0.0.1 port=1999 client_encoding=UTF8',p2 'dbname=digoal2 host=127.0.0.1 port=1999 client_encoding=UTF8',p3 'dbname=digoal3 host=127.0.0.1 port=1999 client_encoding=UTF8');
创建user mapping
CREATE USER MAPPING FOR proxy SERVER digoalOPTIONS (user 'digoal', password 'digoal');
赋权server
grant usage on foreign server digoal to proxy;
创建代理函数:
\c proxy proxy登录函数:CREATE OR REPLACE FUNCTION f_user_login(i_userid int,OUT o_userid int,OUT o_engname text,OUT o_cnname text,OUT o_occupation text,OUT o_birthday date,OUT o_signname text,OUT o_email text,OUT o_qq numeric)as $BODY$CLUSTER 'digoal';RUN ON i_userid;target digoal.f_user_login;$BODY$LANGUAGE plproxy;退出函数:create or replace function f_user_logout(i_userid int,OUT o_result int)as $BODY$CLUSTER 'digoal';RUN ON i_userid;target digoal.f_user_logout;$BODY$language plproxy;
pgbench压力测试 :
postgres@db-digoal-> cat begin.sh#!/bin/bashnohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy0 >>./login_0.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy1 >>./login_1.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy2 >>./login_2.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/login.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy3 >>./login_3.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy0 >>./logout_0.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy1 >>./logout_1.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy2 >>./logout_2.log 2>&1 &nohup pgbench -M extended -r -c 8 -f /home/postgres/digoal_bench/logout.sql -j 8 -n -T 180 -h 127.0.0.1 -p 1998 -U proxy proxy3 >>./logout_3.log 2>&1 &postgres@db-digoal-> cat login.sql\setrandom userid 0 50000000SELECT f_user_login(:userid);postgres@db-digoal-> cat logout.sql\setrandom userid 0 50000000SELECT f_user_logout(:userid);
cat .pgpass 略
测试结果 :
postgres@db-digoal-> cat login_0.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 665468tps = 3695.624216 (including connections establishing)tps = 3695.675102 (excluding connections establishing)statement latencies in milliseconds:0.002366 \setrandom userid 0 500000002.158355 SELECT f_user_login(:userid);postgres@db-digoal-> cat login_1.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 665288tps = 3694.720318 (including connections establishing)tps = 3694.777428 (excluding connections establishing)statement latencies in milliseconds:0.002289 \setrandom userid 0 500000002.159063 SELECT f_user_login(:userid);postgres@db-digoal-> cat login_2.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 645371tps = 3585.275832 (including connections establishing)tps = 3585.340161 (excluding connections establishing)statement latencies in milliseconds:0.002341 \setrandom userid 0 500000002.225684 SELECT f_user_login(:userid);postgres@db-digoal-> cat login_3.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 732428tps = 4068.985625 (including connections establishing)tps = 4069.059175 (excluding connections establishing)statement latencies in milliseconds:0.002358 \setrandom userid 0 500000001.960421 SELECT f_user_login(:userid);postgres@db-digoal-> cat logout_0.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 774532tps = 4302.899259 (including connections establishing)tps = 4302.942647 (excluding connections establishing)statement latencies in milliseconds:0.002279 \setrandom userid 0 500000001.853726 SELECT f_user_logout(:userid);postgres@db-digoal-> cat logout_1.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 773650tps = 4298.002332 (including connections establishing)tps = 4298.047243 (excluding connections establishing)statement latencies in milliseconds:0.002308 \setrandom userid 0 500000001.855774 SELECT f_user_logout(:userid);postgres@db-digoal-> cat logout_2.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 752476tps = 4180.389824 (including connections establishing)tps = 4180.437536 (excluding connections establishing)statement latencies in milliseconds:0.002331 \setrandom userid 0 500000001.908120 SELECT f_user_logout(:userid);postgres@db-digoal-> cat logout_3.logtransaction type: Custom queryscaling factor: 1query mode: extendednumber of clients: 8number of threads: 8duration: 180 snumber of transactions actually processed: 855429tps = 4752.346080 (including connections establishing)tps = 4752.383363 (excluding connections establishing)statement latencies in milliseconds:0.002288 \setrandom userid 0 500000001.677890 SELECT f_user_logout(:userid);
小结 :
每秒处理事务数 : 32581
平均耗时 : 1.974879125 毫秒.
数据库节点平均负载 : 6
数据库节点平均空闲 : 78%
另一个测试的测试数据 :
8000W数据分布到4个节点,根据PK进行更新。
更新SQL请求频率 : 33027 次每秒
平均SQL处理耗时 : 1.9352235 毫秒
从测试结果来看,PLPROXY部署的环境得到的性能提升是超线性的。4台服务器得到的性能大于等于4倍单节点数据库的性能。