利用FDW的水平分片
一、 说明
架构采用一主多从,主节点和从节点之间采用FDW外部表的方式关联。利用PG的继承和分区表特性做到数据的水平分片。
二、 实施验证
找五个服务器安装数据库,1一个master库存放全局数据和数据分片的定义,数据路由算法。4个下层节点数据库,用于存放分片数据。
207.121.127.106 admin_123 64位 CentOS6.2 TMS9500硬件 master
207.101.67.102 admin_123 64位 CentOS6.5
207.101.67.128 admin_123 64位 CentOS6.5
207.101.67.130 admin_123 64位 CentOS6.5
207.101.67.19 admin_123 64位 CentOS6.5
初始化,都创建数据库create database lpj;
连接到master库,创建外部server:
postgres=# \c lpj
You are now connected to database "lpj" as user "postgres".
lpj=# create extension postgres_fdw;
CREATE EXTENSION
lpj=# create server bm102 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.102', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm128 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.128', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm130 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.130', port '5432', dbname 'lpj');
CREATE SERVER
lpj=# create server bm19 foreign data wrapper postgres_fdw options (hostaddr '207.101.67.19', port '5432', dbname 'lpj');
CREATE SERVER
lpj=#
创建user mapping
lpj=# create user mapping for postgres server bm102 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm128 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm130 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=# create user mapping for postgres server bm19 options (user 'postgres', password 'passwd');
CREATE USER MAPPING
lpj=#
在四个外部server节点创建表tbl_test_bm*
--207.101.67.128
create table tbl_test_bm128(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm128 add constraint check_tbl_test_bm128 check (abs(mod(id,4))=0);
--207.101.67.130
create table tbl_test_bm130(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm130 add constraint check_tbl_test_bm130 check (abs(mod(id,4))=1);
--207.101.67.102
create table tbl_test_bm102(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm102 add constraint check_tbl_test_bm102 check (abs(mod(id,4))=2);
--207.101.67.19
create table tbl_test_bm19(id int primary key, info text, crt_time timestamp);
alter table tbl_test_bm19 add constraint check_tbl_test_bm19 check (abs(mod(id,4))=3);
连接到主节点,创建外部表,这里使用了import foreign schema语法,一键创建
lpj=# import FOREIGN SCHEMA public from server bm128 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm130 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm102 into public;
IMPORT FOREIGN SCHEMA
lpj=# import FOREIGN SCHEMA public from server bm19 into public;
IMPORT FOREIGN SCHEMA
lpj=# \det
List of foreign tables
Schema | Table | Server
--------+----------------+--------
public | tbl_test_bm102 | bm102
public | tbl_test_bm128 | bm128
public | tbl_test_bm130 | bm130
public | tbl_test_bm19 | bm19
(4 rows)
lpj=#
创建主表,用户操作主表即可。当然用户也可以直接操作子表,这个特点结合BM业务可以将访问数据库直接移到自己本地数据库的查询修改上。
lpj=# create table tbl_test(id int, info text, crt_time timestamp);
CREATE TABLE
lpj=#
设置外部表的继承关系,继承到主表
alter foreign table tbl_test_bm128 inherit tbl_test;
alter foreign table tbl_test_bm130 inherit tbl_test;
alter foreign table tbl_test_bm102 inherit tbl_test;
alter foreign table tbl_test_bm19 inherit tbl_test;
创建外部表的约束,约束即路由算法的一部分。
注意,带约束条件的SQL,数据库会自动选择对应的外部表进行操作。
不带约束条件的SQL,数据库会选择所有节点操作。
所以建议每条SQL都带上约束条件。这一点和原先的分区表一样。
使用外部表的方式接入master节点的时候是不带约束的。
lpj=# \d tbl_test_bm19
Foreign table "public.tbl_test_bm19"
Column | Type | Collation | Nullable | Default | FDW options
----------+-----------------------------+-----------+----------+---------+--------------------------
id | integer | | not null | | (column_name 'id')
info | text | | | | (column_name 'info')
crt_time | timestamp without time zone | | | | (column_name 'crt_time')
Server: bm19
FDW options: (schema_name 'public', table_name 'tbl_test_bm19')
Inherits: tbl_test
lpj=#
alter foreign table tbl_test_bm128 add constraint check_tbl_test_bm128 check (abs(mod(id,4))=0);
alter foreign table tbl_test_bm130 add constraint check_tbl_test_bm130 check (abs(mod(id,4))=1);
alter foreign table tbl_test_bm102 add constraint check_tbl_test_bm102 check (abs(mod(id,4))=2);
alter foreign table tbl_test_bm19 add constraint check_tbl_test_bm19 check (abs(mod(id,4))=3);
加完之后外部表在master节点上就有约束了
lpj=# \d tbl_test_bm19
Foreign table "public.tbl_test_bm19"
Column | Type | Collation | Nullable | Default | FDW options
----------+-----------------------------+-----------+----------+---------+--------------------------
id | integer | | not null | | (column_name 'id')
info | text | | | | (column_name 'info')
crt_time | timestamp without time zone | | | | (column_name 'crt_time')
Check constraints:
"check_tbl_test_bm19" CHECK (abs(mod(id, 4)) = 3)
Server: bm19
FDW options: (schema_name 'public', table_name 'tbl_test_bm19')
Inherits: tbl_test
lpj=#
由于约束是表达式需要带上表达式才能使约束直接起作用。(类似于表达式索引)
lpj=# explain select * from tbl_test where id=1;
QUERY PLAN
-----------------------------------------------------------------------------
Append (cost=0.00..500.68 rows=25 width=44)
-> Seq Scan on tbl_test (cost=0.00..0.00 rows=1 width=44)
Filter: (id = 1)
-> Foreign Scan on tbl_test_bm128 (cost=100.00..125.17 rows=6 width=44)
-> Foreign Scan on tbl_test_bm130 (cost=100.00..125.17 rows=6 width=44)
-> Foreign Scan on tbl_test_bm102 (cost=100.00..125.17 rows=6 width=44)
-> Foreign Scan on tbl_test_bm19 (cost=100.00..125.17 rows=6 width=44)
(7 rows)
lpj=# explain select * from tbl_test where id=1 and abs(mod(id,4)) = (abs(mod(1,4)));
QUERY PLAN
-----------------------------------------------------------------------------
Append (cost=0.00..134.10 rows=2 width=44)
-> Seq Scan on tbl_test (cost=0.00..0.00 rows=1 width=44)
Filter: ((id = 1) AND (abs(mod(id, 4)) = 1))
-> Foreign Scan on tbl_test_bm130 (cost=100.00..134.10 rows=1 width=44)
(4 rows)
lpj=#
创建插入路由触发器函数
create or replace function f_tbl_ins() returns trigger as
$$
declare
begin
case abs(mod(NEW.id, 4))
when 0 then
insert into tbl_test_bm128 (id, info, crt_time) values (NEW.*);
when 1 then
insert into tbl_test_bm130 (id, info, crt_time) values (NEW.*);
when 2 then
insert into tbl_test_bm102 (id, info, crt_time) values (NEW.*);
when 3 then
insert into tbl_test_bm19 (id, info, crt_time) values (NEW.*);
else
return null;
end case;
return null;
end;
$$
language plpgsql;
create trigger tg1 before insert on tbl_test for each row execute procedure f_tbl_ins();
三、 特性验证
1. 支持分布式事务:
出错全部回滚,可以回退外部表
insert into tbl_test values (1,'test1',now());
insert into tbl_test values (2,'test2',now());
update tbl_test set id=6;
ERROR: new row for relation "tbl_test_bm130" violates check constraint "check_tbl_test_bm130" DETAIL: Failing row contains (6, test_change, 2018-07-10 11:46:30.897977). CONTEXT: Remote SQL command: UPDATE public.tbl_test_bm130 SET id = 6 SQL 状态:23514
此时两条记录都不会被改动。
支持外部表事务退滚。
begin;
insert into tbl_test values (3,'test3',now());
select * from tbl_test;
rollback;
支持跨库事务和本地事务结合时的全局一致性
验证略
2. 支持绑定变量
lpj=# prepare p1 (int,text,timestamp) as insert into tbl_test values ($1,$2,$3);
PREPARE
lpj=# execute p1(4,'test4',now());
INSERT 0 0
lpj=# select * from tbl_test;
id | info | crt_time
----+-------+----------------------------
4 | test4 | 2018-07-10 14:02:23.025278
1 | test2 | 2018-07-10 11:46:30.897977
2 | test2 | 2018-07-10 11:56:37.573761
(3 rows)
lpj=#
3. 支持外部表和外部表的跨库join,外部表、全局表的JOIN
验证略
4. 大查询性能消耗
待验证
四、 扩展能力和性能
1. master和数据分片都可以水平扩展。
2. 性能可以随着节点数的增加线性提升。
3. 更适合OLTP。OLAP目前还是MPP做得比较好。
补充:
联机事务处理OLTP系统强调数据库内存效率,强调内存各种指标的命令率,强调绑定变量,强调并发操作;
联机分析处理OLAP系统则强调数据分析,强调SQL执行市场,强调磁盘I/O,强调分区等。
五、 存在的缺点
1. truncate 目前不支持外部表
直接报错,不支持。
2. JOIN目前不能下推到数据节点执行
create table tbl_aa(info text,des text);
insert into tbl_aa values ('test2','aa');
insert into tbl_aa values ('test2','bb');
insert into tbl_aa values ('test4','cccc');
insert into tbl_aa values ('test4','dddd');
select * from tbl_test join tbl_aa using (info);
所以最好是所有的join查询能分解到下层节点单个库里面去。
3. 条件可以下推到数据节点
select * from tbl_test where substring(info,1,4)='test';
看执行计划是在每一个外部服务器执行带条件的查询再把数据发回master节点处理。
4. 我们之前的缓存机制基于listen和notify,无法直接适配,需要一定的修改。
六、 疑问
1. (A union B)join C 和(A join C)union (B join C)是否等价?
怎么证明? 反正法可证明,过程略。
如果等价则有:
(A union B)join (C union D)
= (A join (C union D))union (B join (C union D))
= (A join C) union (A join D) union (B join C) union (B join D)
如果关联字段满足,A和C绑定,B和D绑定。即可简化为
= (A join C) union (B join D)
AC同一台服务器,BD同一台服务器,即可实现压力分摊。
2. 如果下层节点坏了影响有多大?
验证,当下层节点服务宕机后,上层节点直接从维度表里面查询直接报错。手动停掉128的服务。
lpj=# select * from tbl_test;
ERROR: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
CONTEXT: Remote SQL command: START TRANSACTION ISOLATION LEVEL REPEATABLE READ
直接查询其他外部表是没有问题的。可以考虑当BM异常的时候将相关继承关系disable掉,待服务正常后再启用。
alter table tbl_test_bm128 NO INHERIT tbl_test;
上层节点的维度表可以继续使用,功能OK。
可以再加一层异常处理,如果出现此问题,可以判断所有的下层节点,将有问题的剔除,但是什么时间进行恢复将会是一个难题。
3. 是否支持外部表是分区表?
lpj=# insert into tbl_test values (9,'test_2',now());
ERROR: function func_create_inherit_table(character varying, character varying, unknown, timestamp without time zone, interval) does not exist
HINT: No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT: Remote SQL command: INSERT INTO public.tbl_test_bm130(id, info, crt_time) VALUES ($1, $2, $3)
PL/pgSQL function public.func_insert_tbl_test_bm130() line 19 at PERFORM
SQL statement "insert into tbl_test_bm130 (id, info, crt_time) values (NEW.*)"
PL/pgSQL function f_tbl_ins() line 8 at SQL statement
不支持,关键在于FDW不支持使用触发器?
经过验证读取是没有问题的,也就是说只要可以业务上保证入库在各个从节点上入库,且不需要做消息通知就不会有问题。
但实际这样的应用场景下,主节点不能insert(可以update)。
考虑先做按时间分表在做按bm分表是否可行?bm上再创建一个维度表(全局表)。通过维度表的冗余,应该可以实现。实际维度表只存储路由规则,不影响使用。
后面和守星确认我们业务不需要master节点的insert的操作,可以直接使用第一种方案,操作简单一些。
4. 是否支持上下层节点互相共享数据,部分配置类的表需要BM从原先IMOS数据库获取。
验证,上层节点里面有一张配置表tbl_aa,看能否在上层节点以下层节点作为外部服务器的情况下,将上层节点作为下层节点的外部服务器使用。确认混合使用是否有问题。
在下层节点130上,添加fdw配置。
create extension postgres_fdw;
create server master foreign data wrapper postgres_fdw options (hostaddr '207.101.127.106', port '5432', dbname 'lpj');
create user mapping for postgres server master options (user 'postgres', password 'passwd');
import FOREIGN SCHEMA public from server master into public;
卡死在这一步,应该是不支持。也可能是import 全部无法支持,这个待确认。实在不行可以使用dblink来实现,应该不会成为风险点。(还要分析这种情况下的一致性问题,是否会需要修改上层节点配置表?是否存在跨库join,bm目前确认存在跨库join,这样就需要考虑同步的方案。)
import foreign schema public limit to (tbl_aa) from server master into public; -- 也还是不行
IMPORT FOREIGN SCHEMA remote_schema
[ { LIMIT TO | EXCEPT } ( table_name [, ...] ) ]
FROM SERVER server_name
INTO local_schema
[ OPTIONS ( option 'value' [, ... ] ) ]
5. postgres_fdw和file_fdw的区别
file_fdw模块提供了外部数据封装器file_fdw,可以用来在服务器的文件系统中访问数据文件。数据文件必须是COPY FROM 可读的格式
postgres_fdw模块提供外部数据封装器的功能,PostgreSQL通过 它可以访问存储在外部的 PostgreSQL服务器上的数据。
本模块提供的功能不但涵盖老版本中dblink模块实现的功能, 而且postgres_fdw提供更加透明和符合标准的语法来访问远程表,并在许多情况下 提供更好的性能。