PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异分析、建议 - 含9.4 , 10版本

digoal 2018-07-21

216

作者

digoal

日期

2018-07-21

背景

数据库触发器的触发时机，性能，高并发批量导入时，触发器的性能如何？

批量导入时，before, after触发器在for each row模式下，触发机制如何，什么时候开始条到触发器指定的function中进行运算？

1、before for each row，在数据落目标表前，被触发，同时返回的内容（TUPLE）被REPLACE到对应的数据文件存储。触发器必须明确返回NEW。

```
以insert为例

insert request to HEAP table -> 每一row立即generate NEW -> before trigger(s) -> return NEW -> write tuple to HEAP table
```

2、after for each row，在数据落到目标表之后，再被触发(如果是批量写入，那么会等批量写入结束后，才开始触发after trigger procedure)。after tirgger procedure返回什么值都无所谓，因为用不上。after for each row建议触发器返回null。

```
以insert为例

insert request to HEAP table -> write tuple to HEAP table -> 所有row一次性generate NEW -> after trigger(s) -> return NULL
```

到底哪个性能好？

测试

测试场景参考

《PostgreSQL 流式处理应用实践 - 二手商品实时归类(异步消息notify/listen、阅后即焚)》

《PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异》

1、建表

create table a ( id int8 primary key, -- 商品ID att jsonb -- 商品属性 );

2、建结果表

create table t_result(id serial8 primary key, class text, content text);

3、建merge json函数

create or replace function merge_json(jsonb, jsonb) returns jsonb as $$ -- select json_object_agg(key,value)::jsonb from ( -- 9.4 select jsonb_object_agg(key,value) from ( select coalesce(a.key, b.key) as key, case when coalesce(jsonb_array_element(a.value,1)::text::timestamp, '1970-01-01'::timestamp) > coalesce(jsonb_array_element(b.value,1)::text::timestamp, '1970-01-01'::timestamp) then a.value else b.value end from jsonb_each($1) a full outer join jsonb_each($2) b using (key) ) t; $$ language sql strict ;

批量，并发数据写入性能对比(before, after, no trigger)

1、创建dblink插件

create extension dblink;

2、建立断开连接的函数，目的是不抛异常。

create or replace function dis_conn(name) returns void as $$ declare begin perform dblink_disconnect($1); return; exception when others then return; end; $$ language plpgsql strict;

3、创建连接函数接口

CREATE OR REPLACE FUNCTION public.conn(name, text) RETURNS void LANGUAGE plpgsql STRICT AS $function$ declare begin perform dis_conn($1); perform dblink_connect($1, $2); return; exception when others then return; end; $function$;

4、创建并行，批量加载函数。 56个并行，每一批写入200万条数据。总共写入1.12亿行。

CREATE OR REPLACE FUNCTION public.get_res() RETURNS SETOF record LANGUAGE plpgsql STRICT AS $function$ declare start_time timestamptz := clock_timestamp(); loops int := 55; batchs int := 2000000; -- 总数据量1.12亿 begin for i in 0..loops loop perform conn('link'||i, 'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres application_name=digoal_loader'); perform '1' from dblink_get_result('link'||i) as t(res text); perform dblink_send_query('link'||i, format($_$ insert into a select id, '{"price":[10000, "2018-01-01 10:10:11"]}' from generate_series(%s,%s) t(id) on conflict (id) -- 9.4 注释掉这行 do update set -- 9.4 注释掉这行 att = merge_json(a.att, excluded.att) -- 9.4 注释掉这行 $_$, i*batchs, (i+1)*batchs-1)); end loop; for i in 0..loops loop return query select extract(epoch from clock_timestamp()-start_time)::text from dblink_get_result('link'||i) as t(res text); end loop; end; $function$;

after trigger for each row

当一条SQL写入a完成后，触发after触发器，开始处理每行。

1、建触发器函数，用于处理每一行原始数据，包括50个处理逻辑.

CREATE OR REPLACE FUNCTION notify1() returns trigger AS $function$ declare begin if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then -- 规则1，价格大于100，写入结果表 insert into t_result(class,content) values ( 'a', -- 归类 format('CLASS:high price, ID:%s, ATT:%s', NEW.id, NEW.att) -- 消息内容 ); end if; -- 模拟多轮判断 for i in 1..49 loop if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then -- 规则xx null; end if; end loop; return null; -- aster 触发器 -- return NEW; -- BEFORE 触发器 end; $function$ language plpgsql strict;

2、创建after insert or update触发器

create trigger tg1 after insert or update on a for each row execute procedure notify1();

3、写入单条，测试

insert into a values (1, '{"price":[10000, "2018-01-01 10:10:11"]}') on conflict (id) do update set att = merge_json(a.att, excluded.att) -- 合并新属性，保留老属性，需要使用一个UDF来合并 ;

4、调用并行接口，批量并发写入

select * from get_res() as t(id text);

5、你会发现，数据是在写入完成后，才开始逐行处理触发器内部逻辑。

目标表在写入，但是trigger并没有处理，因此结果表还没有看到任何记录

```
以insert为例

insert request to HEAP table -> write tuple to HEAP table -> 所有row一次性generate NEW -> after trigger(s) -> return NULL
```

6、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

1367 秒。

before trigger for each row

before触发器，在数据落盘前，触发before trigger function

1、建触发器函数，用于处理每一行原始数据，包括50个处理逻辑.

CREATE OR REPLACE FUNCTION notify1() returns trigger AS $function$ declare begin if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then -- 规则1，价格大于100，写入结果表 insert into t_result(class,content) values ( 'a', -- 归类 format('CLASS:high price, ID:%s, ATT:%s', NEW.id, NEW.att) -- 消息内容 ); end if; -- 模拟多轮判断 for i in 1..49 loop if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then -- 规则xx null; end if; end loop; -- return null; -- aster 触发器 return NEW; -- BEFORE 触发器 end; $function$ language plpgsql strict;

2、创建before insert or update触发器

```
drop trigger tg1 on a;

create trigger tg1 before insert or update on a for each row execute procedure notify1();
```

3、调用并行接口，批量并发写入

truncate a; truncate t_result; select * from get_res() as t(id text);

4、写入过程中查看

你会发现，目标表和结果表同时在增长，因为

```
以insert为例

insert request to HEAP table -> 每一row立即generate NEW -> before trigger(s) -> return NEW -> write tuple to HEAP table
```

6、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

1207 秒。

无trigger导入速度：

1、删除触发器

postgres=# drop trigger tg1 on a; DROP TRIGGER

2、调用并行接口，批量并发写入

truncate a; truncate t_result; select * from get_res() as t(id text);

3、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

706 秒。

性能对比

PostgreSQL 10 on CentOS 7.x

PostgreSQL 10 logged table 测试结果

case | 并发数 | 写入量 | 耗时
---|---|---|---
无触发器 | 56 | 1.12亿 | 103 秒
before for each row触发器 | 56 | 1.12亿 | 1165 秒
after for each row触发器 | 56 | 1.12亿 | 1247 秒

性能瓶颈，在写wal日志上面，如果使用unlogged table，就可以发挥出CPU所有能力了。

PostgreSQL 10 unlogged table 测试结果

truncate a; truncate t_result; alter table a set unlogged; alter table t_result set unlogged;

case | 并发数 | 写入量 | 耗时
---|---|---|---
无触发器 | 56 | 1.12亿 | 61 秒
before for each row触发器 | 56 | 1.12亿 | 1113 秒
after for each row触发器 | 56 | 1.12亿 | 1158 秒

现在“无触发器”模式的瓶颈变成了EXTEND BLOCK，也就是扩展数据文件。触发器的情况下，CPU计算为瓶颈，没有其他瓶颈，所以unlogged与logged table性能差异不大）。

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

PostgreSQL 9.4 on CentOS 7.x

PostgreSQL 9.4 logged table 测试结果

PostgreSQL 9.4，当批量导入的TABLE加了trigger，并且trigger function里面有query处理时，很卡很卡，数据库几乎不可用。

卡在哪里？

Samples: 655K of event 'cpu-clock', Event count (approx.): 143038981880 Overhead Shared Object Symbol 76.93% postgres [.] s_lock 3.60% postgres [.] LWLockAcquire 3.34% postgres [.] LWLockRelease 1.55% [kernel] [k] run_timer_softirq 0.84% postgres [.] GetSnapshotData 0.73% postgres [.] AllocSetAlloc 0.64% postgres [.] PushActiveSnapshot 0.59% [kernel] [k] __do_softirq 0.54% [kernel] [k] _raw_spin_unlock_irqrestore 0.40% [kernel] [k] finish_task_switch 0.35% libc-2.17.so [.] __GI_____strtod_l_internal 0.32% [kernel] [k] rcu_process_callbacks 0.26% postgres [.] ExecMakeFunctionResultNoSets 0.25% libc-2.17.so [.] __memcpy_ssse3_back 0.24% postgres [.] palloc 0.21% plpgsql.so [.] exec_eval_expr 0.21% [kernel] [k] tick_nohz_idle_exit

lwlockacquire到release的过程可能过长。

PostgreSQL 10在各方面都有优化，比如典型的GIN索引场景，9.4在高并发更新下面也是存在性能问题。

《PostgreSQL 10 GIN索引锁优化》

建议本文提到的场景，不要使用9.4的版本。(并发控制到8以下，s_lock问题才不是那么明显)，以下是并发8的测试结果

下面测试只写入1400万，耗时乘以7，可以对比postgresql 10

case | 并发数 | 写入量 | 耗时
---|---|---|---
无触发器 | 8 | 1400万 | 21 秒 , 147 秒
before for each row触发器 | 8 | 1400万 | 210 秒 , 1470 秒
after for each row触发器 | 8 | 1400万 | 206 秒 , 1442 秒