488 阿裏雲技術社區[雲棲]

ALICloudDB for PostgreSQL 試用報告 - 2 教你RDS PG的水平分庫

使用pl/proxy 做分布式處理的性能。

大家可供參考，注意目前plproxy不支持跨庫關聯，僅僅是函數代理。

如果要做跨庫事務需要結合PostgreSQL的prepared transaction(分布式事務/2PC)來實現，

如果要做跨庫關聯，可以用PostgreSQL的外部表，例如在每個節點上都建立其他節點需要關聯的表的外部表，這樣也可以做關聯。

plproxy支持run on all,any,NR,HASH四種方式。

接下來我會一一測試。

09ac11e3d54a464d240f2a06ee4ca37713400336

8aab3a18af08e8089b255c37e9179c37ce55723e

部署ECS：

安裝PostgreSQL 9.4.3，略。

安裝plproxy，可參考

https://blog.163.com/digoal@126/blog/static/1638770402013102242543765/

http://git.postgresql.org/gitweb/?p=plproxy.git;a=summary

cd plproxy

export PATH=/opt/pgsql/bin:$PATH

gmake

gmake install

psql

create extension plproxy;

在plproxy代理節點部署數據庫密碼文件：

編輯密碼文件，免輸入密碼。(主機名和密碼以模煳化，一共有16台RDS)

# vi ~/.pgpass

xxxx.pg.rds.aliyuncs.com:3433:*:digoal:xxxx

xxxx.pg.rds.aliyuncs.com:3433:*:renny:xxxx

xxxx.pg.rds.aliyuncs.com:3433:*:postgres:xxxx

xxxx.pg.rds.aliyuncs.com:3433:*:dbnosql:xxxx

xxxx.pg.rds.aliyuncs.com:3433:*:dbuser:xxxx

chmod 400 ~/.pgpass

創建server，用於部署plproxy前期管理遠程數據庫。

create extension dblink;

CREATE SERVER p0 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx1.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p1 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx2.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p2 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx3.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p3 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx4.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p4 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx5.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p5 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx6.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p6 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx7.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p7 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx8.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p8 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx9.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p9 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx10.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p10 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx11.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p11 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx12.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p12 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx13.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p13 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx14.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p14 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx15.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

CREATE SERVER p15 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx16.pg.rds.aliyuncs.com', dbname 'postgres', port '3433');

創建user mapping

CREATE USER MAPPING FOR public SERVER p0 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p1 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p2 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p3 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p4 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p5 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p6 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p7 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p8 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p9 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p10 OPTIONS (user 'renny');

CREATE USER MAPPING FOR public SERVER p11 OPTIONS (user 'postgres');

CREATE USER MAPPING FOR public SERVER p12 OPTIONS (user 'dbnosql');

CREATE USER MAPPING FOR public SERVER p13 OPTIONS (user 'dbuser');

CREATE USER MAPPING FOR public SERVER p14 OPTIONS (user 'dbuser');

CREATE USER MAPPING FOR public SERVER p15 OPTIONS (user 'dbuser');

創建一個不報錯的dblink建立函數，方便管理用：

create or replace function new_dblink_connect(text,text) returns void as $$

declare

begin

perform dblink_connect($1,$2);

exception

when SQLSTATE '42710' then

return;

when others then

raise;

end;

$$ language plpgsql;

在16台數據節點分別創建2個數據庫，一共32個數據庫(db0,db16; db1,db17;, ..... db15,db31;)，將用於演示數據節點擴容。

do language plpgsql $$

declare

begin

for i in 0..15 loop

perform new_dblink_connect('p'||i||'_conn', 'p'||i);

perform dblink_exec('p'||i||'_conn', 'create database db'||i, false);

perform dblink_exec('p'||i||'_conn', 'create database db'||(i+16), false);

perform dblink_disconnect('p'||i||'_conn');

end loop;

end;

$$;

修改server到對應的32個DB。

alter server p0 options (set dbname 'db0');

alter server p1 options (set dbname 'db1');

alter server p2 options (set dbname 'db2');

alter server p3 options (set dbname 'db3');

alter server p4 options (set dbname 'db4');

alter server p5 options (set dbname 'db5');

alter server p6 options (set dbname 'db6');

alter server p7 options (set dbname 'db7');

alter server p8 options (set dbname 'db8');

alter server p9 options (set dbname 'db9');

alter server p10 options (set dbname 'db10');

alter server p11 options (set dbname 'db11');

alter server p12 options (set dbname 'db12');

alter server p13 options (set dbname 'db13');

alter server p14 options (set dbname 'db14');

alter server p15 options (set dbname 'db15');

新建剩餘的DB。每個+16得到新的DB號。

CREATE SERVER p16 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx1.pg.rds.aliyuncs.com', dbname 'db16', port '3433');

CREATE SERVER p17 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx2.pg.rds.aliyuncs.com', dbname 'db17', port '3433');

CREATE SERVER p18 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx3.pg.rds.aliyuncs.com', dbname 'db18', port '3433');

CREATE SERVER p19 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx4.pg.rds.aliyuncs.com', dbname 'db19', port '3433');

CREATE SERVER p20 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx5.pg.rds.aliyuncs.com', dbname 'db20', port '3433');

CREATE SERVER p21 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx6.pg.rds.aliyuncs.com', dbname 'db21', port '3433');

CREATE SERVER p22 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx7.pg.rds.aliyuncs.com', dbname 'db22', port '3433');

CREATE SERVER p23 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx8.pg.rds.aliyuncs.com', dbname 'db23', port '3433');

CREATE SERVER p24 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx9.pg.rds.aliyuncs.com', dbname 'db24', port '3433');

CREATE SERVER p25 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx10.pg.rds.aliyuncs.com', dbname 'db25', port '3433');

CREATE SERVER p26 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx11.pg.rds.aliyuncs.com', dbname 'db26', port '3433');

CREATE SERVER p27 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx12.pg.rds.aliyuncs.com', dbname 'db27', port '3433');

CREATE SERVER p28 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx13.pg.rds.aliyuncs.com', dbname 'db28', port '3433');

CREATE SERVER p29 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx14.pg.rds.aliyuncs.com', dbname 'db29', port '3433');

CREATE SERVER p30 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx15.pg.rds.aliyuncs.com', dbname 'db30', port '3433');

CREATE SERVER p31 FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'xxxx16.pg.rds.aliyuncs.com', dbname 'db31', port '3433');

創建user mapping

CREATE USER MAPPING FOR public SERVER p16 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p17 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p18 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p19 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p20 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p21 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p22 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p23 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p24 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p25 OPTIONS (user 'digoal');

CREATE USER MAPPING FOR public SERVER p26 OPTIONS (user 'renny');

CREATE USER MAPPING FOR public SERVER p27 OPTIONS (user 'postgres');

CREATE USER MAPPING FOR public SERVER p28 OPTIONS (user 'dbnosql');

CREATE USER MAPPING FOR public SERVER p29 OPTIONS (user 'dbuser');

CREATE USER MAPPING FOR public SERVER p30 OPTIONS (user 'dbuser');

CREATE USER MAPPING FOR public SERVER p31 OPTIONS (user 'dbuser');

增加一個application_name的參數，用於分辨節點：

alter server p0 options (add application_name '0');

alter server p1 options (add application_name '1');

alter server p2 options (add application_name '2');

alter server p3 options (add application_name '3');

alter server p4 options (add application_name '4');

alter server p5 options (add application_name '5');

alter server p6 options (add application_name '6');

alter server p7 options (add application_name '7');

alter server p8 options (add application_name '8');

alter server p9 options (add application_name '9');

alter server p10 options (add application_name '10');

alter server p11 options (add application_name '11');

alter server p12 options (add application_name '12');

alter server p13 options (add application_name '13');

alter server p14 options (add application_name '14');

alter server p15 options (add application_name '15');

alter server p16 options (add application_name '16');

alter server p17 options (add application_name '17');

alter server p18 options (add application_name '18');

alter server p19 options (add application_name '19');

alter server p20 options (add application_name '20');

alter server p21 options (add application_name '21');

alter server p22 options (add application_name '22');

alter server p23 options (add application_name '23');

alter server p24 options (add application_name '24');

alter server p25 options (add application_name '25');

alter server p26 options (add application_name '26');

alter server p27 options (add application_name '27');

alter server p28 options (add application_name '28');

alter server p29 options (add application_name '29');

alter server p30 options (add application_name '30');

alter server p31 options (add application_name '31');

在16台數據節點的32個數據庫，每個RDS跑兩個數據庫。(db0,db16; db1,db17;, ..... db15,db31;)：

1. 創建schema: digoal；

2. 創建動態函數，用於plproxy測試動態查詢。

do language plpgsql $$

declare

v_sql text;

begin

v_sql := 'CREATE OR REPLACE FUNCTION digoal.dy(sql text)

RETURNS SETOF record

LANGUAGE plpgsql

STRICT

AS $function$

declare

rec record;

begin

for rec in execute sql loop

return next rec;

end loop;

return;

end;

$function$;';

for i in 0..31 loop

perform new_dblink_connect('p'||i||'_conn', 'p'||i);

perform dblink_exec('p'||i||'_conn', 'create schema digoal', false);

perform dblink_exec('p'||i||'_conn', v_sql, false);

perform dblink_disconnect('p'||i||'_conn');

end loop;

end;

$$;

創建plproxy使用的集群，注意順序：

CREATE SERVER rds_pg_cluster FOREIGN DATA WRAPPER plproxy options(

connection_lifetime '1800',

disable_binary '0',

p0 'host=xxxx1.pg.rds.aliyuncs.com dbname=db0 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=0',

p1 'host=xxxx2.pg.rds.aliyuncs.com dbname=db1 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=1',

p2 'host=xxxx3.pg.rds.aliyuncs.com dbname=db2 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=2',

p3 'host=xxxx4.pg.rds.aliyuncs.com dbname=db3 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=3',

p4 'host=xxxx5.pg.rds.aliyuncs.com dbname=db4 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=4',

p5 'host=xxxx6.pg.rds.aliyuncs.com dbname=db5 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=5',

p6 'host=xxxx7.pg.rds.aliyuncs.com dbname=db6 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=6',

p7 'host=xxxx8.pg.rds.aliyuncs.com dbname=db7 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=7',

p8 'host=xxxx9.pg.rds.aliyuncs.com dbname=db8 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=8',

p9 'host=xxxx10.pg.rds.aliyuncs.com dbname=db9 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=9',

p10 'host=xxxx11.pg.rds.aliyuncs.com dbname=db10 port=3433 user=renny keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=10',

p11 'host=xxxx12.pg.rds.aliyuncs.com dbname=db11 port=3433 user=postgres keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=11',

p12 'host=xxxx13.pg.rds.aliyuncs.com dbname=db12 port=3433 user=dbnosql keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=12',

p13 'host=xxxx14.pg.rds.aliyuncs.com dbname=db13 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=13',

p14 'host=xxxx15.pg.rds.aliyuncs.com dbname=db14 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=14',

p15 'host=xxxx16.pg.rds.aliyuncs.com dbname=db15 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=15',

p16 'host=xxxx1.pg.rds.aliyuncs.com dbname=db16 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=16',

p17 'host=xxxx2.pg.rds.aliyuncs.com dbname=db17 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=17',

p18 'host=xxxx3.pg.rds.aliyuncs.com dbname=db18 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=18',

p19 'host=xxxx4.pg.rds.aliyuncs.com dbname=db19 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=19',

p20 'host=xxxx5.pg.rds.aliyuncs.com dbname=db20 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=20',

p21 'host=xxxx6.pg.rds.aliyuncs.com dbname=db21 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=21',

p22 'host=xxxx7.pg.rds.aliyuncs.com dbname=db22 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=22',

p23 'host=xxxx8.pg.rds.aliyuncs.com dbname=db23 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=23',

p24 'host=xxxx9.pg.rds.aliyuncs.com dbname=db24 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=24',

p25 'host=xxxx10.pg.rds.aliyuncs.com dbname=db25 port=3433 user=digoal keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=25',

p26 'host=xxxx11.pg.rds.aliyuncs.com dbname=db26 port=3433 user=renny keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=26',

p27 'host=xxxx12.pg.rds.aliyuncs.com dbname=db27 port=3433 user=postgres keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=27',

p28 'host=xxxx13.pg.rds.aliyuncs.com dbname=db28 port=3433 user=dbnosql keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=28',

p29 'host=xxxx14.pg.rds.aliyuncs.com dbname=db29 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=29',

p30 'host=xxxx15.pg.rds.aliyuncs.com dbname=db30 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=30',

p31 'host=xxxx16.pg.rds.aliyuncs.com dbname=db31 port=3433 user=dbuser keepalives_idle=30 keepalives_interval=10 keepalives_count=10 application_name=31'

);

CREATE USER MAPPING FOR public SERVER rds_pg_cluster;

執行動態SQL的代理函數：

CREATE OR REPLACE FUNCTION dy(sql text)

RETURNS SETOF record

LANGUAGE plproxy

STRICT

AS $function$

cluster 'rds_pg_cluster';

run on all;

target digoal.dy;

$function$;

例子(IP已隱去部分)：

postgres=# select * from dy('select inet_server_addr(),inet_server_port(),inet_client_addr(),inet_client_port(),count(*) from pg_stat_activity group by 1,2,3,4') as t(c1 inet,c2 int,c3 inet,c4 int,cnt int8) order by 1,2;

c1 | c2 | c3 | c4 | cnt

---------------+------+----------------+-------+-----

10.151. | 3012 | 10.172. | 48477 | 2
10.151. | 3012 | 10.172. | 48493 | 2
10.151. | 3013 | 10.172. | 27255 | 3
10.151. | 3013 | 10.172. | 27239 | 3
10.151. | 3014 | 10.172. | 64573 | 2
10.151. | 3014 | 10.172. | 64557 | 2
10.151. | 3004 | 10.172. | 63958 | 2
10.151. | 3004 | 10.172. | 63974 | 2
10.151. | 3009 | 10.172. | 34966 | 3
10.151. | 3009 | 10.172. | 34982 | 3
10.151. | 3010 | 10.172. | 24074 | 2
10.151. | 3010 | 10.172. | 24058 | 2
10.151. | 3009 | 10.172. | 56821 | 2
10.151. | 3009 | 10.172. | 56837 | 2
10.151. | 3011 | 10.172. | 29265 | 3
10.151. | 3011 | 10.172. | 29249 | 3
10.151. | 3012 | 10.172. | 14945 | 2
10.151. | 3012 | 10.172. | 14961 | 2
10.151. | 3008 | 10.172. | 24139 | 2
10.151. | 3008 | 10.172. | 24155 | 2
10.151. | 3003 | 10.172. | 9419 | 2
10.151. | 3003 | 10.172. | 9435 | 2
10.151. | 3004 | 10.172. | 35252 | 2
10.151. | 3004 | 10.172. | 35236 | 2
10.151. | 3004 | 10.172. | 47530 | 2
10.151. | 3004 | 10.172. | 47546 | 2
10.151. | 3006 | 10.172. | 33434 | 2
10.151. | 3006 | 10.172. | 33418 | 2
10.151. | 3006 | 10.172. | 56858 | 2
10.151. | 3006 | 10.172. | 56842 | 2
10.151. | 3010 | 10.172. | 46645 | 2
10.151. | 3010 | 10.172. | 46629 | 2
(32 rows)

16個RDS，server IP有8個。

使用dblink在所有節點創建以下dy_ddl實體函數，用於執行DDL：

do language plpgsql $$

declare

v_sql text;

begin

v_sql := 'CREATE OR REPLACE FUNCTION digoal.dy_ddl(sql text)

RETURNS VOID

LANGUAGE plpgsql

STRICT

AS $function$

declare

begin

execute sql;

return;

exception when others then return;

end;

$function$;';

for i in 0..31 loop

perform new_dblink_connect('p'||i||'_conn', 'p'||i);

perform dblink_exec('p'||i||'_conn', v_sql, false);

perform dblink_disconnect('p'||i||'_conn');

end loop;

end;

$$;

創建plproxy函數，代理DDL語句：

CREATE OR REPLACE FUNCTION dy_ddl(sql text)

RETURNS setof void

LANGUAGE plproxy

STRICT

AS $function$

cluster 'rds_pg_cluster';

run on all;

target digoal.dy_ddl;

$function$;

利用這個plproxy代理DDL函數在所有節點創建test表:

postgres=# select dy_ddl('create table test(id int)');

dy_ddl

--------

......

(32 rows)

Time: 35.683 ms

查詢剛剛創建的test表:

postgres=# select * from dy('select id from test') as t(id int);

----

(0 rows)

Time: 2.958 ms

刪除test表：

select dy_ddl('drop table test');

接下來部署測試用例：

使用dblink，連接到不同的db創建測試表：

do language plpgsql $$

declare

v_sql text;

begin

for i in 0..31 loop

perform new_dblink_connect('p'||i||'_conn', 'p'||i);

v_sql := 'create table digoal.userinfo(dbid int default '||i||',userid int,info text)';

perform dblink_exec('p'||i||'_conn', v_sql, false);

v_sql := 'create table digoal.session (dbid int default '||i||',userid int,last_login timestamp)';

perform dblink_exec('p'||i||'_conn', v_sql, false);

v_sql := 'create table digoal.login_log (dbid int default '||i||',userid int,db_user name,client_addr inet,

client_port int,server_addr inet,server_port int,login_time timestamp)';

perform dblink_exec('p'||i||'_conn', v_sql, false);

v_sql := 'create table digoal.tbl_small (userid int primary key,info text)';

perform dblink_exec('p'||i||'_conn', v_sql, false);

perform dblink_disconnect('p'||i||'_conn');

end loop;

end;

$$;

生成測試數據，每個庫200萬數據(每個RDS 400萬)，一共6400萬用戶數據：<

最後更新：2017-04-01 13:44:33

ALICloudDB for PostgreSQL 試用報告 - 2 教你RDS PG的水平分庫

上一篇：［翻譯］JDK 8 兼容性指南

下一篇： MySQL · 引擎特性 · InnoDB Fulltext簡介

相關內容

熱門內容

最新內容

ALICloudDB for PostgreSQL 試用報告 - 2 教你RDS PG的水平分庫

上一篇： ［翻譯］JDK 8 兼容性指南

下一篇： MySQL · 引擎特性 · InnoDB Fulltext簡介

相關內容

熱門內容

最新內容

上一篇：［翻譯］JDK 8 兼容性指南