标签
PostgreSQL , trigger , row , statement , before , after , s_lock
背景
数据库触发器的触发时机，性能，高并发批量导入时，触发器的性能如何？
批量导入时，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
postgres=# \dt+ a|t_result
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | a | table | postgres | 3560 MB |
public | t_result | table | postgres | 8192 bytes |
postgres=# \dt+ a
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+---------+-------------
public | a | table | postgres | 3603 MB |
public | t_result | table | postgres | 8192 bytes |
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
postgres=# \dt+ a|t_res*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------+-------+----------+--------+-------------
public | a | table | postgres | 335 MB |
public | t_result | table | postgres | 387 MB |
(2 rows)
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所有能力了。
postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;
wait_event_type | wait_event | count
-----------------+---------------------+-------
LWLock | wal_insert | 40
| | 19
Activity | BgWriterMain | 1
Activity | AutoVacuumMain | 1
IO | DataFileWrite | 1
Activity | LogicalApplyMain | 1
Activity | LogicalLauncherMain | 1
(7 rows)
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性能差异不大）。
postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;
wait_event_type | wait_event | count
-----------------+---------------------+-------
Lock | extend | 41
《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在高并发更新下面也是存在性能问题。
建议本文提到的场景，不要使用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 秒 |
其他
可以随时杀掉导入进程
select pg_terminate_backend(pid) from pg_stat_activity where application_name='digoal_loader';
参考
《PostgreSQL 批量、单步 写入 - row, statement 触发器(中间表)、CTE 几种用法性能对比》
《PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE|DISABLE》
《PostgreSQL rotate table 自动清理调度 - 约束，触发器》
《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》
《(流式、lambda、触发器)实时处理大比拼 - 物联网(IoT)\金融,时序处理最佳实践》
《快速入门PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器》
《PostgreSQL 10.0 preview 功能增强 - 触发器函数内置中间表》
《PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱》
《use PostgreSQL trigger manage stock & offer infomation》
《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》
《PostgreSQL 触发器应用 - use trigger audit record which column modified, insert, delete.》
《use event trigger function record user who alter table's SQL》
《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》
《PostgreSQL 触发器应用 - (触发器WHEN)前置条件过滤跟踪目标记录》
《PostgreSQL 闪回 - flash back query emulate by trigger》
《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》
《PostgreSQL general public partition table trigger》
《表级复制(base on trigger) -- multi master replication & performance tuning》
《表级复制(base on trigger) -- one(rw) to many(ro|rw)》
《PostgreSQL 跟踪DDL时间 - cann't use pg_class's trigger trace user_table's create,modify,delete Time》
