Digoal.zhou

Digoal.zhou

公益是一辈子的事, I'm digoal, Just do it.

A Smart PostgreSQL extension plproxy 2.2 practices

7 minute read

背景

PostgreSQL分布式设计

三层结构 :

1. 路由层(几乎无限扩展)

主角: plproxy,postgresql

2. 连接池层(几乎无限扩展)

主角: pgbouncer

3. 数据层(几乎无限扩展)

主角: postgresql

扩展方式:

1. 路由层扩展:

路由层包含了数据层的连接配置(FDW或函数),plproxy语言写的函数壳(内置路由算法),这些基本上是静态数据,所以扩展非常方便.

添加服务器就行了.

2. 连接池层扩展:

连接池层扩展加服务器.

3. 数据层扩展:

数据层扩展,添加服务器,通过流复制增加数据节点,结合路由算法重分布数据(建议路由算法2^n取模),

物理分布:

1. 路由层和连接池层尽量靠近部署.可以考虑部署在同一台物理机.

2. 数据层尽量每个节点一台物理机.

环境需求:

CentOS 5.7 x64  
flex-2.5.35  
PostgreSQL-9.1.1  
plproxy-2.2  
pgfincore-v1.1  
libevent-1.4.14b-stable  
pgbouncer 1.4.2

测试环境描述:

1. pgbench : 172.16.3.176

2. pgbouncer on pgbench HOST :

172.16.3.176:1998(  
proxy0 = host=172.16.3.150 dbname=proxy port=1921 pool_size=16  
proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16  
proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16  
proxy3 = 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/digoal

4. 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/proxy

5. 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=8  
digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8  
digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8  
digoal3 = 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=64  
gmake world  
gmake install-world

3. 编译安装plproxy-2.2

mv plproxy-2.2 postgresql-9.1.1/contrib/plproxy-2.2  
make PG_CONFIG=/path/to/pg_config  
make install PG_CONFIG=/path/to/pg_config

4. 编译安装pgfincore-v1.1

mv pgfincore-v1.1 postgresql-9.1.1/contrib/pgfincore-v1.1  
cp pgfincore.control /  
make clean  
make  
su  
make 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

配置:

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 postgres  
  update 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=8  
digoal1 = host=172.16.3.39 dbname=digoal port=1921 pool_size=8  
digoal2 = host=172.16.3.40 dbname=digoal port=1921 pool_size=8  
digoal3 = host=172.16.3.33 dbname=digoal port=1921 pool_size=8  
[pgbouncer]  
pool_mode = transaction  
listen_port = 1999  
unix_socket_dir = /opt/pgbouncer/etc  
listen_addr = *  
auth_type = md5  
auth_file = /opt/pgbouncer/etc/users1999.txt  
logfile = /dev/null  
pidfile = /opt/pgbouncer/etc/pgbouncer1999.pid  
max_client_conn = 10000  
reserve_pool_timeout = 0  
server_reset_query =  
admin_users = pgbouncer_admin  
stats_users = pgbouncer_guest  
ignore_startup_parameters = extra_float_digits  
  
postgres@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=16  
proxy1 = host=172.16.3.39 dbname=proxy port=1921 pool_size=16  
proxy2 = host=172.16.3.40 dbname=proxy port=1921 pool_size=16  
proxy3 = host=172.16.3.33 dbname=proxy port=1921 pool_size=16  
[pgbouncer]  
pool_mode = transaction  
listen_port = 1998  
unix_socket_dir = /opt/pgbouncer/config  
listen_addr = *  
auth_type = md5  
auth_file = /opt/pgbouncer/config/users.txt  
logfile = /dev/null  
pidfile = /opt/pgbouncer/config/pgbouncer1998.pid  
max_client_conn = 1500  
reserve_pool_timeout = 0  
server_reset_query =   
admin_users = pgbouncer_admin  
stats_users = pgbouncer_guest  
ignore_startup_parameters = extra_float_digits

测试

数据节点, 创建测试表, 插入测试数据:

proxy=# \c digoal digoal  
create 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$  
declare  
begin  
select userid,engname,cnname,occupation,birthday,signname,email,qq  
into o_userid,o_engname,o_cnname,o_occupation,o_birthday,o_signname,o_email,o_qq  
from 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$  
declare  
begin  
insert into user_logout_rec (userid,logout_time,ip) values (i_userid,now(),inet_client_addr());  
o_result := 0;  
return;  
exception   
when others then  
o_result := 1;  
return;  
end;  
$BODY$  
language plpgsql;

代理节点 :

创建server

CREATE SERVER digoal FOREIGN DATA WRAPPER plproxy  
OPTIONS (  
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 digoal  
OPTIONS (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/bash  
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 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 50000000  
SELECT f_user_login(:userid);  
  
postgres@db-digoal-> cat logout.sql  
\setrandom userid 0 50000000  
SELECT f_user_logout(:userid);  
  
cat .pgpass 略

测试结果 :

postgres@db-digoal-> cat login_0.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 665468  
tps = 3695.624216 (including connections establishing)  
tps = 3695.675102 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002366        \setrandom userid 0 50000000  
        2.158355        SELECT f_user_login(:userid);  
  
postgres@db-digoal-> cat login_1.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 665288  
tps = 3694.720318 (including connections establishing)  
tps = 3694.777428 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002289        \setrandom userid 0 50000000  
        2.159063        SELECT f_user_login(:userid);  
  
postgres@db-digoal-> cat login_2.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 645371  
tps = 3585.275832 (including connections establishing)  
tps = 3585.340161 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002341        \setrandom userid 0 50000000  
        2.225684        SELECT f_user_login(:userid);  
  
postgres@db-digoal-> cat login_3.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 732428  
tps = 4068.985625 (including connections establishing)  
tps = 4069.059175 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002358        \setrandom userid 0 50000000  
        1.960421        SELECT f_user_login(:userid);  
  
postgres@db-digoal-> cat logout_0.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 774532  
tps = 4302.899259 (including connections establishing)  
tps = 4302.942647 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002279        \setrandom userid 0 50000000  
        1.853726        SELECT f_user_logout(:userid);  
  
postgres@db-digoal-> cat logout_1.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 773650  
tps = 4298.002332 (including connections establishing)  
tps = 4298.047243 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002308        \setrandom userid 0 50000000  
        1.855774        SELECT f_user_logout(:userid);  
  
postgres@db-digoal-> cat logout_2.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 752476  
tps = 4180.389824 (including connections establishing)  
tps = 4180.437536 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002331        \setrandom userid 0 50000000  
        1.908120        SELECT f_user_logout(:userid);  
  
postgres@db-digoal-> cat logout_3.log   
transaction type: Custom query  
scaling factor: 1  
query mode: extended  
number of clients: 8  
number of threads: 8  
duration: 180 s  
number of transactions actually processed: 855429  
tps = 4752.346080 (including connections establishing)  
tps = 4752.383363 (excluding connections establishing)  
statement latencies in milliseconds:  
        0.002288        \setrandom userid 0 50000000  
        1.677890        SELECT f_user_logout(:userid);

小结 :

每秒处理事务数 : 32581

平均耗时 : 1.974879125 毫秒.

数据库节点平均负载 : 6

数据库节点平均空闲 : 78%

另一组测试的测试数据 :

8000W数据分布到4个节点,根据PK进行更新。

更新SQL请求频率 : 33027 次每秒

平均SQL处理耗时 : 1.9352235 毫秒

从测试结果来看，PLPROXY部署的环境得到的性能提升是超线性的。4台服务器得到的性能大于等于4倍单节点数据库的性能。

Flag Counter

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

17 minute read

背景 云数据库PPAS版，是阿里云与EnterpriseDB公司(简称EDB)合作基于PostgreSQL高度兼容Oracle语法的数据库服务，为用户提供易于操作的迁移工具，兼容范围涵盖：PL/SQL、数据类型、高级函数、表分区等。 用户可以直接在阿里云购买PPAS进行使用。 如果在购买PPAS前，想试用一下...

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

1 minute read

背景 IoT场景，车联网场景，共享单车场景，人的行为位点等，终端实时上报的是孤立的位点，我们需要将其补齐成轨迹。 例如共享单车，下单，开锁，生成订单，骑行，关闭订单，关锁。这个过程有一个唯一的订单号，每次上报的位点会包含时间，订单号，位置。 根据订单号，将点聚合为轨迹。 使用pipelinedb插件，可以实...

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法

2 minute read

背景 使用datediff，对时间或日期相减，得到的间隔，转换为目标单位(日、月、季度、年、小时、秒。。。等)的数值。 DATEDIFF ( datepart, {date|timestamp}, {date|timestamp} ) 周 select datediff(week,'2009-01-0...