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

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
云原生数据库 PolarDB 分布式版,标准版 2核8GB
简介:

标签

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在高并发更新下面也是存在性能问题。

《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 秒

其他

可以随时杀掉导入进程

select pg_terminate_backend(pid) from pg_stat_activity where application_name='digoal_loader';    

参考

《PostgreSQL 批量、单步 写入 - row, statement 触发器(中间表)、CTE 几种用法性能对比》

《PostgreSQL 自动创建分区实践 - 写入触发器》

《PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE|DISABLE》

《PostgreSQL rotate table 自动清理调度 - 约束,触发器》

《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》

《数据入库实时转换 - trigger , rule》

《(流式、lambda、触发器)实时处理大比拼 - 物联网(IoT)\金融,时序处理最佳实践》

《快速入门PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器》

《PostgreSQL 10.0 preview 功能增强 - 触发器函数内置中间表》

《PostgreSQL 安全陷阱 - 利用触发器或规则,结合security invoker函数制造反噬陷阱》

《use PostgreSQL trigger manage stock & offer infomation》

《PostgreSQL trigger/rule based replication configure, DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER | RULE》

《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》

《表级复制(base on trigger) -- PostgreSQL general sync and async multi-master replication trigger function》

《PostgreSQL 触发器 用法详解 2》

《PostgreSQL 触发器 用法详解 1》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《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》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
2月前
|
SQL 存储 数据可视化
手机短信SQL分析技巧与方法
在手机短信应用中,SQL分析扮演着至关重要的角色
|
2月前
|
SQL 监控 测试技术
SQL现在到哪个版本及版本更新技巧与方法
SQL(Structured Query Language)作为数据库管理和操作的标准语言,随着技术的不断进步和数据库管理系统(DBMS)的持续发展,其版本也在不断更新和完善
|
2月前
|
SQL Oracle 关系型数据库
SQL数据库当前版本概览与更新趋势
在探讨SQL(Structured Query Language)数据库的当前版本时,我们首先要明确的是,SQL本身是一种查询语言标准,而并非特指某一个具体的数据库产品
|
1月前
|
SQL 关系型数据库 MySQL
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
MySql5.6版本开启慢SQL功能-本次采用永久生效方式
38 0
|
3月前
|
SQL 数据管理 BI
SQL 有哪些版本?
SQL 有哪些版本?
297 4
|
3月前
|
SQL 数据管理 BI
SQL Server 有哪些版本?
SQL Server 有哪些版本?
284 3
|
4月前
|
前端开发 Java JSON
Struts 2携手AngularJS与React:探索企业级后端与现代前端框架的完美融合之道
【8月更文挑战第31天】随着Web应用复杂性的提升,前端技术日新月异。AngularJS和React作为主流前端框架,凭借强大的数据绑定和组件化能力,显著提升了开发动态及交互式Web应用的效率。同时,Struts 2 以其出色的性能和丰富的功能,成为众多Java开发者构建企业级应用的首选后端框架。本文探讨了如何将 Struts 2 与 AngularJS 和 React 整合,以充分发挥前后端各自优势,构建更强大、灵活的 Web 应用。
61 0
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
5月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
120 13

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版