标簽
PostgreSQL , 觸發器 , row , statement , statement 觸發器中間表 , CTE , 性能對比
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E8%83%8C%E6%99%AF 背景
資料庫寫入方式包括:
1、單條insert, autocommit
insert into xx values ();
2、批量(單QUERY)
insert into xx values (),(),...();
3、分組送出
begin;
insert into xx values ();
...
insert into xx values ();
end;
INSERT協定包括extended, prepared, simple等。
當需要對每行寫入的資料進行邏輯處理時,可以使用以下三種方法:
postgres=# \h create trigger
Command: CREATE TRIGGER
Description: define a new trigger
Syntax:
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }
ON table_name
[ FROM referenced_table_name ]
[ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]
[ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]
[ FOR [ EACH ] { ROW | STATEMENT } ]
[ WHEN ( condition ) ]
EXECUTE PROCEDURE function_name ( arguments )
where event can be one of:
INSERT
UPDATE [ OF column_name [, ... ] ]
DELETE
TRUNCATE
postgres=# \h create rule
Command: CREATE RULE
Description: define a new rewrite rule
Syntax:
CREATE [ OR REPLACE ] RULE name AS ON event
TO table_name [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }
where event can be one of:
SELECT | INSERT | UPDATE | DELETE
1、行級觸發器
2、語句級觸發器
https://www.postgresql.org/docs/devel/static/plpgsql-trigger.html 《PostgreSQL 10.0 preview 功能增強 - 觸發器函數内置中間表》3、CTE寫法
with tmp as (insert into xxx values xxx returning *),
t1 as (邏輯處理1 from tmp where ...),
...,
tn as (邏輯處理n from tmp where ...)
邏輯處理n+1 from tmp where ...;
下面針對以上三種方法,分别介紹單步、批量插入時的性能差異,以便業務上針對性的做出選擇。
項目背景參考
《PostgreSQL 流式處理應用實踐 - 二手商品實時歸類(異步消息notify/listen、閱後即焚)》https://github.com/digoal/blog/blob/master/201807/20180720_01.md#demo DEMO
1、建表
create table test(id int, info text, c1 int , c2 int, crt_time timestamp);
2、邏輯處理的結果寫入下面的表
create table t_result (like test);
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E6%97%A0%E8%A7%A6%E5%8F%91%E5%99%A8%E6%97%B6%E6%89%B9%E9%87%8F%E5%86%99%E5%85%A5%E6%80%A7%E8%83%BD 無觸發器時,批量寫入性能
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 1118.051 ms (00:01.118)
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E8%A1%8C%E7%BA%A7%E8%A7%A6%E5%8F%91%E5%99%A8---%E5%A4%9A%E4%B8%AA%E8%A7%A6%E5%8F%91%E5%99%A8%E7%9A%84%E6%89%B9%E9%87%8F%E5%86%99%E5%85%A5%E6%80%A7%E8%83%BD 行級觸發器 - 多個觸發器的批量寫入性能
1、觸發器函數
create or replace function tg1() returns trigger as $$
declare
begin
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
return null;
end;
$$ language plpgsql strict;
2、2個觸發器的性能
create trigger tg1 after insert on test for each row execute procedure tg1();
create trigger tg2 after insert on test for each row execute procedure tg1();
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 4879.985 ms (00:04.880)
3、4個觸發器的性能
create trigger tg3 after insert on test for each row execute procedure tg1();
create trigger tg4 after insert on test for each row execute procedure tg1();
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 8776.416 ms (00:08.776)
4、6個觸發器的性能
create trigger tg5 after insert on test for each row execute procedure tg1();
create trigger tg6 after insert on test for each row execute procedure tg1();
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 12648.707 ms (00:12.649)
5、将觸發器合并成1個,所有邏輯放到一個觸發器中,批量寫入性能
drop trigger tg1 ON test;
drop trigger tg2 ON test;
drop trigger tg3 ON test;
drop trigger tg4 ON test;
drop trigger tg5 ON test;
drop trigger tg6 ON test;
create or replace function tg1() returns trigger as $$
declare
begin
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
return null;
end;
$$ language plpgsql strict;
create trigger tg1 after insert on test for each row execute procedure tg1();
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 5042.071 ms (00:05.042)
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E8%AF%AD%E5%8F%A5%E7%BA%A7%E8%A7%A6%E5%8F%91%E5%99%A8---1%E4%B8%AA%E8%A7%A6%E5%8F%91%E5%99%A8%E6%89%B9%E9%87%8F%E5%86%99%E5%85%A56%E4%B8%AA%E8%A7%84%E5%88%99%E7%9A%84%E6%89%B9%E9%87%8F%E5%86%99%E5%85%A5%E6%80%A7%E8%83%BD 語句級觸發器 - 1個觸發器,批量寫入,6個規則的批量寫入性能
1、建立觸發器函數,使用中間表,邏輯處理放到中間表中
create or replace function tg1() returns trigger as $$
declare
begin
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
return null;
end;
$$ language plpgsql strict;
drop trigger tg1 ON test;
create trigger tg1 after insert on test REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure tg1();
2、6個規則的性能
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);
INSERT 0 1000000
Time: 1847.532 ms (00:01.848)
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#cte---%E6%89%B9%E9%87%8F%E5%86%99%E5%85%A56%E4%B8%AA%E8%A7%84%E5%88%99%E6%80%A7%E8%83%BD CTE - 批量寫入,6個規則性能
with new_table as (insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000) returning *),
t1 as (insert into t_result select * from new_table where c1<=1 and c2<=1),
t2 as (insert into t_result select * from new_table where c1<=1 and c2<=1),
t3 as (insert into t_result select * from new_table where c1<=1 and c2<=1),
t4 as (insert into t_result select * from new_table where c1<=1 and c2<=1),
t5 as (insert into t_result select * from new_table where c1<=1 and c2<=1)
insert into t_result select * from new_table where c1<=1 and c2<=1;
INSERT 0 222
Time: 2833.217 ms (00:02.833)
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E5%8D%95%E6%9D%A1insert%E8%A1%8C%E7%BA%A7%E8%AF%AD%E5%8F%A5%E7%BA%A7%E8%A7%A6%E5%8F%91%E5%99%A8cte%E7%94%A8%E6%B3%956%E4%B8%AA%E8%A7%84%E5%88%99%E6%80%A7%E8%83%BD%E5%AF%B9%E6%AF%94 單條INSERT,“行級、語句級觸發器、CTE用法”6個規則,性能對比
1、測試腳本
vi test.sql
insert into test values (1,'test',random()*100,random()*100,now());
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 28 -j 28 -T 120
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E6%97%A0%E8%A7%A6%E5%8F%91%E5%99%A8 無觸發器
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 28
number of threads: 28
duration: 120 s
number of transactions actually processed: 29783861
latency average = 0.113 ms
latency stddev = 0.329 ms
tps = 248197.480858 (including connections establishing)
tps = 248213.036221 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.113 insert into test values (1,'test',random()*100,random()*100,now());
2、ROW觸發器
create or replace function tg1() returns trigger as $$
declare
begin
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
if NEW.c1<=1 and NEW.c2<=1 then
insert into t_result values (NEW.*);
end if;
return null;
end;
$$ language plpgsql strict;
create trigger tg1 after insert on test for each row execute procedure tg1();
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 28
number of threads: 28
duration: 120 s
number of transactions actually processed: 18398837
latency average = 0.183 ms
latency stddev = 0.335 ms
tps = 153322.919798 (including connections establishing)
tps = 153332.364931 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.183 insert into test values (1,'test',random()*100,random()*100,now());
3、STATEMENT觸發器
create or replace function tg1() returns trigger as $$
declare
begin
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
insert into t_result select * from new_table where c1<=1 and c2<=1;
return null;
end;
$$ language plpgsql strict;
drop trigger tg1 ON test;
create trigger tg1 after insert on test REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure tg1();
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 28
number of threads: 28
duration: 120 s
number of transactions actually processed: 16868185
latency average = 0.199 ms
latency stddev = 0.214 ms
tps = 140567.379172 (including connections establishing)
tps = 140576.126770 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.199 insert into test values (1,'test',random()*100,random()*100,now());
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E6%80%A7%E8%83%BD%E6%80%BB%E7%BB%93 性能總結
模式 | 規則個數 | 語句觸發器 處理吞吐 | 行觸發器 處理吞吐 | 無規則 處理吞吐 |
---|---|---|---|---|
單條寫入 | 單觸發器6個規則 | 14萬行/s | 15.3萬行/s | 24.8萬行/s |
批量寫入100萬行 | 54.1萬行/s | 19.8萬行/s | 89.4萬行/s | |
2個觸發器2個規則 | - | 20.5萬行/s | ||
4個觸發器4個規則 | 11.4萬行/s | |||
6個觸發器6個規則 | 7.9萬行/s | |||
CTE文法批量寫入100萬行 | 6個規則 | 35.3萬行/s (含6規則) |
https://github.com/digoal/blog/blob/master/201807/20180720_01.md#%E5%B0%8F%E7%BB%93 小結
對于需要邏輯處理的資料表,使用語句級觸發器,同時使用中間表的模式來進行邏輯處理,性能是比較均衡的。相比行級觸發器,性能好很多。
另外,每增加一個觸發器,性能會下降比較厲害。建議把處理邏輯放到一個觸發器裡面,而不要使用多個觸發器。