devbox
天景科技苑
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

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

作者：天景科技苑 | 2024-07-03 01:41:33

赞

踩

postgresql for each row

标签

PostgreSQL , trigger , row , statement , before , after , s_lock

背景

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

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

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

  1. 以insert为例    
  2.     
  3. 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。

  1. 以insert为例    
  2.     
  3. 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、建表

  1. create table a (          
  2.   id int8 primary key,   -- 商品ID          
  3.   att jsonb   -- 商品属性          
  4. );

2、建结果表

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

3、建merge json函数

  1. create or replace function merge_json(jsonb, jsonb) returns jsonb as $$        
  2.   -- select json_object_agg(key,value)::jsonb from (     -- 9.4   
  3.   select jsonb_object_agg(key,value) from (        
  4.   select         
  5.     coalesce(a.key, b.key) as key,         
  6.     case         
  7.     when         
  8.     coalesce(jsonb_array_element(a.value,1)::text::timestamp, '1970-01-01'::timestamp)         
  9.     >         
  10.     coalesce(jsonb_array_element(b.value,1)::text::timestamp, '1970-01-01'::timestamp)         
  11.     then a.value        
  12.     else b.value        
  13.     end        
  14.   from jsonb_each($1) a full outer join jsonb_each($2) b using (key)        
  15.   ) t;          
  16. $$ language sql strict ;

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

1、创建dblink插件

create extension dblink;

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

  1. create or replace function dis_conn(name) returns void as $$    
  2. declare    
  3. begin    
  4.   perform dblink_disconnect($1);    
  5.   return;    
  6. exception when others then    
  7.   return;    
  8. end;    
  9. $$ language plpgsql strict;

3、创建连接函数接口

  1. CREATE OR REPLACE FUNCTION public.conn(name, text)    
  2.  RETURNS void    
  3.  LANGUAGE plpgsql    
  4.  STRICT    
  5. AS $function$                
  6. declare                
  7. begin                
  8.   perform dis_conn($1);      
  9.   perform dblink_connect($1, $2);               
  10.   return;                
  11. exception when others then                
  12.   return;                
  13. end;                
  14. $function$;

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

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

after trigger for each row

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

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

  1. CREATE OR REPLACE FUNCTION notify1() returns trigger          
  2. AS $function$          
  3. declare          
  4. begin          
  5.   if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则1， 价格大于100，写入结果表          
  6.      insert into t_result(class,content) values (        
  7.        'a',    -- 归类        
  8.        format('CLASS:high price, ID:%s, ATT:%s', NEW.id, NEW.att)   -- 消息内容          
  9.      );          
  10.   end if;         
  11.   -- 模拟多轮判断    
  12.   for i in 1..49 loop    
  13.     if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则xx        
  14.       null;         
  15.     end if;        
  16.   end loop;    
  17.   return null;    -- aster 触发器    
  18.   -- return NEW;  -- BEFORE 触发器    
  19. end;          
  20. $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、写入单条，测试

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

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

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

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

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

  1. 以insert为例    
  2.     
  3. insert request to HEAP table -> write tuple to HEAP table -> 所有row一次性generate NEW -> after trigger(s) -> return NULL    
  1. postgres=# \dt+ a|t_result    
  2.                     List of relations    
  3.  Schema | Name | Type  |  Owner   |  Size   | Description     
  4. --------+------+-------+----------+---------+-------------    
  5.  public | a    | table | postgres | 3560 MB |     
  6.  public | t_result | table | postgres | 8192 bytes |     
  7.     
  8. postgres=# \dt+ a    
  9.                     List of relations    
  10.  Schema | Name | Type  |  Owner   |  Size   | Description     
  11. --------+------+-------+----------+---------+-------------    
  12.  public | a    | table | postgres | 3603 MB |     
  13.  public | t_result | table | postgres | 8192 bytes |

6、数据量：1.12亿条

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

1367 秒。

before trigger for each row

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

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

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

2、创建before insert or update触发器

  1. drop trigger tg1 on a;    
  2.     
  3. create trigger tg1 before insert or update on a for each row execute procedure notify1();

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

  1. truncate a;  
  2. truncate t_result;  
  3. select * from get_res() as t(id text);

4、写入过程中查看

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

  1. 以insert为例    
  2.     
  3. insert request to HEAP table -> 每一row立即generate NEW -> before trigger(s) -> return NEW -> write tuple to HEAP table    
  1. postgres=# \dt+ a|t_res*    
  2.                       List of relations    
  3.  Schema |   Name   | Type  |  Owner   |  Size  | Description     
  4. --------+----------+-------+----------+--------+-------------    
  5.  public | a        | table | postgres | 335 MB |     
  6.  public | t_result | table | postgres | 387 MB |     
  7. (2 rows)

6、数据量：1.12亿条

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

1207 秒。

无trigger导入速度：

1、删除触发器

  1. postgres=# drop trigger tg1 on a;    
  2. DROP TRIGGER

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

  1. truncate a;  
  2. truncate t_result;  
  3. select * from get_res() as t(id text);

3、数据量：1.12亿条

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

706 秒。

性能对比

PostgreSQL 10 on CentOS 7.x

PostgreSQL 10 logged table 测试结果

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

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

  1. postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;  
  2.  wait_event_type |     wait_event      | count   
  3. -----------------+---------------------+-------  
  4.  LWLock          | wal_insert          |    40  
  5.                  |                     |    19  
  6.  Activity        | BgWriterMain        |     1  
  7.  Activity        | AutoVacuumMain      |     1  
  8.  IO              | DataFileWrite       |     1  
  9.  Activity        | LogicalApplyMain    |     1  
  10.  Activity        | LogicalLauncherMain |     1  
  11. (7 rows)

PostgreSQL 10 unlogged table 测试结果

  1. truncate a;  
  2. truncate t_result;  
  3. alter table a set unlogged;  
  4. alter table t_result set unlogged;
case并发数写入量耗时
无触发器561.12亿61 秒
before for each row触发器561.12亿1113 秒
after for each row触发器561.12亿1158 秒

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

  1. postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;  
  2.  wait_event_type |     wait_event      | count   
  3. -----------------+---------------------+-------  
  4.  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处理时，很卡很卡，数据库几乎不可用。

卡在哪里？

  1. Samples: 655K of event 'cpu-clock', Event count (approx.): 143038981880  
  2. Overhead  Shared Object          Symbol                                  
  3.   76.93%  postgres               [.] s_lock                              
  4.    3.60%  postgres               [.] LWLockAcquire                       
  5.    3.34%  postgres               [.] LWLockRelease                       
  6.    1.55%  [kernel]               [k] run_timer_softirq                   
  7.    0.84%  postgres               [.] GetSnapshotData                     
  8.    0.73%  postgres               [.] AllocSetAlloc                       
  9.    0.64%  postgres               [.] PushActiveSnapshot                  
  10.    0.59%  [kernel]               [k] __do_softirq                        
  11.    0.54%  [kernel]               [k] _raw_spin_unlock_irqrestore         
  12.    0.40%  [kernel]               [k] finish_task_switch                  
  13.    0.35%  libc-2.17.so           [.] __GI_____strtod_l_internal          
  14.    0.32%  [kernel]               [k] rcu_process_callbacks               
  15.    0.26%  postgres               [.] ExecMakeFunctionResultNoSets        
  16.    0.25%  libc-2.17.so           [.] __memcpy_ssse3_back                 
  17.    0.24%  postgres               [.] palloc                              
  18.    0.21%  plpgsql.so             [.] exec_eval_expr                      
  19.    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并发数写入量耗时
无触发器81400万21 秒 , 147 秒
before for each row触发器81400万210 秒 , 1470 秒
after for each row触发器81400万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》

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/天景科技苑/article/detail/781552
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号