http://blog.163.com/digoal@126/blog/static/163877040201331252945440/
是以, 可以使用多條記錄來緩解行鎖沖突的問題, 如下 : # 建立測試表, a, 假設要經常count(*) from a. [email protected]> psql psql (9.2.4) Type"help"for help. postgres=# drop table a; DROP TABLE postgres=# create table a(id serial4 primary key, info text, crt_time timestamp(0) default now()); NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial column "a.id" NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for table "a" CREATE TABLE # 建立記錄a表記錄數的表 postgres=# create table cnt_a(id int primary key, cnt int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "cnt_a_pkey" for table "cnt_a" CREATE TABLE # 為了緩解行鎖沖突, 這裡使用了1001條記錄來存儲count(*) from a的值. # 在計算count(*) a時, 使用sum(cnt) from cnt_a就可以了. 是以隻需要掃描1001行. # 後面會看到當a表的記錄數越多, 性能提升約明顯. postgres=# insert into cnt_a select generate_series(0,1000),0; INSERT 0 1001 # 建立插入觸發器函數 CREATE OR REPLACE FUNCTION public.tg_insert_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; rm numeric; begin select max(id),random()into m_id,rm from cnt_a; update cnt_a set cnt=cnt+1where id=(rm*m_id)::int; returnnull; end; $function$; # 建立删除觸發器函數 CREATE OR REPLACE FUNCTION public.tg_delete_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; rm numeric; begin select max(id),random()into m_id,rm from cnt_a; update cnt_a set cnt=cnt-1where id=(rm*m_id)::int; returnnull; end; $function$; # 建立truncate觸發器函數 CREATE OR REPLACE FUNCTION public.tg_truncate_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare begin update cnt_a set cnt=0wherenot cnt=0; returnnull; end; $function$; # 建立觸發器 create trigger tg1 after insert on a for each row execute procedure tg_insert_a(); create trigger tg2 after delete on a for each row execute procedure tg_delete_a(); create trigger tg3 after truncate on a for each statement execute procedure tg_truncate_a(); # 建立pgbench 使用的插入腳本 [email protected]> cat insert.sql insert into a (info) values ('test'); # pgbench做插入測試 [email protected]> pgbench -M prepared -r -n -f ./insert.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:1831418 tps=30514.831839(including connections establishing) tps =30522.057886(excluding connections establishing) statement latencies in milliseconds: 0.522411 insert into a (info) values ('test'); # 測試完後通過count(*) 和sum(cnt)比對資料是否一緻 postgres=# select count(*) from a; count --------- 1755964 (1 row) Time: 285.491 ms postgres=# select sum(cnt) from cnt_a ; sum --------- 1755964 (1 row) Time: 0.689 ms # 性能提升非常明顯. # 建立pgbench用于删除a表記錄的測試腳本 vi delete.sql \setrandom id 11000000 deletefrom a where id=:id; # 進行測試 [email protected]> pgbench -M prepared -r -n -f ./delete.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:3353233 tps=55865.635772(including connections establishing) tps =55878.855793(excluding connections establishing) statement latencies in milliseconds: 0.002594 \setrandom id 11000000 0.282123 deletefrom a where id=:id; # 測試完删除操作後, 比對count(*)和sum(cnt)是否一緻 postgres=# select count(*) from a; count --------- 9687739 (1 row) Time: 1550.239 ms postgres=# select sum(cnt) from cnt_a ; sum --------- 9687739 (1 row) Time: 0.817 ms 當記錄數到達千萬級别後, 性能以及提升幾千倍了. # 建立同時進行删除和插入操作的測試腳本 vi id.sql \setrandom id 120000000 deletefrom a where id=:id; insert into a (info) values ('test'); # 測試 [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:1061090 tps=17680.045577(including connections establishing) tps =17684.251890(excluding connections establishing) statement latencies in milliseconds: 0.003181 \setrandom id 120000000 0.381986 deletefrom a where id=:id; 0.516256 insert into a (info) values ('test'); # 測試完後比對count(*)和sum(cnt)的結果是否一緻 postgres=# select count(*) from a; count ---------- 10219555 (1 row) Time: 1648.371 ms postgres=# select sum(cnt) from cnt_a ; sum ---------- 10219555 (1 row) Time: 1.339 ms # 最後要測試的是truncate表. postgres=# truncate a; TRUNCATE TABLE Time: 434.581 ms postgres=# select count(*) from a; count ------- 0 (1 row) Time: 0.831 ms postgres=# select sum(cnt) from cnt_a ; sum ----- 0 (1 row) Time: 1.354 ms # 當并行的超過1001時, 或者以及明顯感覺到行鎖沖突時, 可以通過實時增加cnt_a表的記錄來達到緩解行鎖沖突的目的. # 不需要中斷業務, 但是必須注意cnt_a表的id必須連續, 并且cnt的初始值必須為0. 不要出現空檔. 否則使用以上觸發器函數會出現資料不準确的現象. 例如 :
pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres
在測試的同時添加記錄 postgres=# insert into cnt_a (id,cnt) select generate_series(1001,2000),0; INSERT 0 1000 # 測試完後檢查是否準确, 測試新增的cnt_a.id是否有計數. postgres=# select count(*) from a; count --------- 1283144 (1 row) postgres=# select sum(cnt) from cnt_a ; sum --------- 1283144 (1 row) postgres=# select sum(cnt) from cnt_a where id>1000; sum -------- 623957 (1 row) # 如果要避免不準确的現象, 除了cnt_a.id連續, 還可以在觸發器函數中添加一個異常捕獲. CREATE OR REPLACE FUNCTION public.tg_insert_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; rm numeric; new_cnt int; begin select max(id),random()into m_id,rm from cnt_a; update cnt_a set cnt=cnt+1where id=(rm*m_id)::int returning cnt into new_cnt; ifnot found or new_cnt isnullthen raise exception ''; endif; returnnull; end; $function$; CREATE OR REPLACE FUNCTION public.tg_delete_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; rm numeric; new_cnt int; begin select max(id),random()into m_id,rm from cnt_a; update cnt_a set cnt=cnt-1where id=(rm*m_id)::int returning cnt into new_cnt; ifnot found or new_cnt isnullthen raise exception ''; endif; returnnull; end; $function$; # 測試 : # 插入cnt=null的非法值, 看看會不會捕獲異常, 看看結果是否正确. postgres=# insert into cnt_a (id,cnt) select 2001,null; INSERT 0 1 # 測試pgbench [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres Client 13 aborted in state 2: ERROR: Client6 aborted in state 2: ERROR: Client8 aborted in state 2: ERROR: Client1 aborted in state 2: ERROR: Client0 aborted in state 2: ERROR: Client2 aborted in state 2: ERROR: Client7 aborted in state 2: ERROR: Client11 aborted in state 2: ERROR: Client4 aborted in state 2: ERROR: Client3 aborted in state 2: ERROR: Client9 aborted in state 2: ERROR: Client12 aborted in state 2: ERROR: Client10 aborted in state 2: ERROR: Client14 aborted in state 2: ERROR: Client15 aborted in state 2: ERROR: Client5 aborted in state 2: ERROR: transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:54704 tps=7617.195278(including connections establishing) tps =7632.604983(excluding connections establishing) statement latencies in milliseconds: 0.003084 \setrandom id 120000000 0.184270 deletefrom a where id=:id; 0.366083 insert into a (info) values ('test'); # 結果校驗, 加了異常捕獲, 是以結果正确. postgres=# select sum(cnt) from cnt_a; sum --------- 1334221 (1 row) postgres=# select count(*) from a; count --------- 1334221 (1 row) # 插入不連續的id, 看看是否可以捕獲異常, 比對結果是否準确 直接跳過1000條, 導緻id不連續. random()*max_id将有可能取到無記錄的情況. 是以會出現not found, 捕獲這個異常 postgres=# insert into cnt_a (id,cnt) select 3001,null; INSERT 0 1 # 如下pgbench實際每個連接配接平均隻處理了28條, 看看結果是否正确 [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres Client 0 aborted in state 1: ERROR: Client3 aborted in state 2: ERROR: Client13 aborted in state 2: ERROR: Client14 aborted in state 2: ERROR: Client7 aborted in state 2: ERROR: Client2 aborted in state 2: ERROR: Client8 aborted in state 2: ERROR: Client4 aborted in state 2: ERROR: Client5 aborted in state 2: ERROR: Client10 aborted in state 2: ERROR: Client6 aborted in state 1: ERROR: Client1 aborted in state 1: ERROR: Client9 aborted in state 2: ERROR: Client11 aborted in state 2: ERROR: Client15 aborted in state 2: ERROR: Client12 aborted in state 2: ERROR: transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:28 tps=801.167415(including connections establishing) tps =1372.515380(excluding connections establishing) statement latencies in milliseconds: 0.004773 \setrandom id 120000000 1.731136 deletefrom a where id=:id; 2.530098 insert into a (info) values ('test'); # 結果正确 postgres=# select sum(cnt) from cnt_a; sum --------- 1334246 (1 row) postgres=# select count(*) from a; count --------- 1334246 (1 row) 【小結】 1. 使用這種方法帶來來優化count(*), 如果insert和delete本來就不是系統瓶頸的話, 是值得提倡的. 2. random()函數為volatile屬性, 是以同一個事務中多次調用時需要多次運算. rm*max_id勢必得到不同的id. postgres=# select provolatile from pg_proc where proname='random'; provolatile ------------- v (1 row) 是以可以想象一下. 2.1. random()多次運算比一次運算的開銷大. 2.2. 由于每次得到的id不一樣, 如果是批量插入的話, 一個事務中将會鎖cnt_a表的多行, 這種場景容易産生死鎖. 要解決這個問題, 可以嘗試使用stable或者immutable随機函數. 那麼一個事務中多次調用的話都将得到同一個值, 減少了運算量同時也避免了以上場景中死鎖的産生. 實作方法是 使用advisory lock, 如下 : # 新增pid和lock_time用來記錄會話pid和事務啟動時間. postgres=# alter table cnt_a add column pid int; ALTER TABLE Time: 18.649 ms postgres=# alter table cnt_a add column lock_time timestamp; ALTER TABLE Time: 1.018 ms postgres=# \d cnt_a Table "public.cnt_a" Column | Type | Modifiers -----------+-----------------------------+--------------- id | integer | not null cnt | integer | pid | integer | lock_time | timestamp without time zone | Indexes: "cnt_a_pkey" PRIMARY KEY, btree (id) # 建立插入觸發器函數 CREATE OR REPLACE FUNCTION public.tg_insert_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; a_lock boolean; rm numeric; max_id int; new_cnt int; begin -- now()為stable,同一事務結果一緻. select id into m_id from cnt_a where pid=pg_backend_pid()and lock_time=now() limit 1; if found then update cnt_a set cnt=cnt+1where id=m_id returning cnt into new_cnt; if new_cnt isnullthen raise exception 'cnt_a.cnt is null, please init with zero.'; endif; returnnull; else --1由于read committed,并發時可能同時搶鎖1條記錄.造成不必要的等待. --1select id into m_id from cnt_a where locked=false limit 1for update; --2使用這種方法可以減輕鎖同一記錄的壓力,但是增加了查詢開銷. --2select id into m_id from cnt_a where locked=false order by random() limit 1for update; --3通過55P03捕獲異常.并發明顯時,這種異常會很多. --3select id into m_id from cnt_a where locked=false limit 1for update nowait; --4以下需要關注高并發的情況下,得到鎖需要周遊的記錄條數,還有優化的空間.(結合mvcc與檢索機制) for a_lock,m_id inselect pg_try_advisory_xact_lock(id),id from cnt_a loop if a_lock then --加鎖成功 update cnt_a set cnt=cnt+1,pid=pg_backend_pid(),lock_time=now()where id=m_id returning cnt into new_cnt; if new_cnt isnullthen raise exception 'cnt_a.cnt is null, please init with zero.'; endif; returnnull; endif; end loop; --到這裡說明周遊所有的cnt_a都沒有加鎖成功,原因是都被鎖了. --那麼随機取一條更新進行等待即可 select max(id),random()into max_id,rm from cnt_a; update cnt_a set cnt=cnt+1,pid=pg_backend_pid(),lock_time=now()where id=(rm*m_id)::int returning cnt into new_cnt; ifnot found or new_cnt isnullthen raise exception 'cnt_a.id:%, cnt_a.cnt:%.',(rm*m_id)::int, new_cnt; endif; returnnull; endif; returnnull; end; $function$; # 建立删除觸發器函數 CREATE OR REPLACE FUNCTION public.tg_delete_a() RETURNS trigger LANGUAGE plpgsql AS $function$ declare m_id int; a_lock boolean; rm numeric; max_id int; new_cnt int; begin -- now()為stable,同一事務結果一緻. select id into m_id from cnt_a where pid=pg_backend_pid()and lock_time=now() limit 1; if found then update cnt_a set cnt=cnt-1where id=m_id returning cnt into new_cnt; if new_cnt isnullthen raise exception 'cnt_a.cnt is null, please init with zero.'; endif; returnnull; else --1由于read committed,并發時可能同時搶鎖1條記錄.造成不必要的等待. --1select id into m_id from cnt_a where locked=false limit 1for update; --2使用這種方法可以減輕鎖同一記錄的壓力,但是增加了查詢開銷. --2select id into m_id from cnt_a where locked=false order by random() limit 1for update; --3通過55P03捕獲異常.并發明顯時,這種異常會很多. --3select id into m_id from cnt_a where locked=false limit 1for update nowait; --4以下需要關注高并發的情況下,得到鎖需要周遊的記錄條數,還有優化的空間.(結合mvcc與檢索機制) for a_lock,m_id inselect pg_try_advisory_xact_lock(id),id from cnt_a loop if a_lock then --加鎖成功 update cnt_a set cnt=cnt-1,pid=pg_backend_pid(),lock_time=now()where id=m_id returning cnt into new_cnt; if new_cnt isnullthen raise exception 'cnt_a.cnt is null, please init with zero.'; endif; returnnull; endif; end loop; --到這裡說明周遊所有的cnt_a都沒有加鎖成功,原因是都被鎖了. --那麼随機取一條更新進行等待即可 select max(id),random()into max_id,rm from cnt_a; update cnt_a set cnt=cnt-1,pid=pg_backend_pid(),lock_time=now()where id=(rm*m_id)::int returning cnt into new_cnt; ifnot found or new_cnt isnullthen raise exception 'cnt_a.id:%, cnt_a.cnt:%.',(rm*m_id)::int, new_cnt; endif; returnnull; endif; returnnull; end; $function$; # 以下測試原始場景16個并發以及16條cnt_a記錄的單事務多sql的場景, 發生了可以預料到的死鎖. postgres=# truncate a; TRUNCATE TABLE postgres=# delete from cnt_a ; DELETE 2002 postgres=# insert into cnt_a(id,cnt) select generate_series(0,15),0; INSERT 0 16 [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres Client 2 aborted in state 8: ERROR: deadlock detected DETAIL: Process10738 waits forShareLock on transaction 433211275; blocked by process 10737. Process10737 waits forShareLock on transaction 433211280; blocked by process 10738. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 3 aborted in state 8: ERROR: deadlock detected DETAIL: Process10742 waits forShareLock on transaction 433211275; blocked by process 10737. Process10737 waits forExclusiveLock on tuple (0,11) of relation 25592 of database 12044; blocked by process 10740. Process10740 waits forShareLock on transaction 433211281; blocked by process 10742. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 12 aborted in state 4: ERROR: deadlock detected DETAIL: Process10732 waits forShareLock on transaction 433211286; blocked by process 10740. Process10740 waits forShareLock on transaction 433211276; blocked by process 10736. Process10736 waits forExclusiveLock on tuple (0,12) of relation 25592 of database 12044; blocked by process 10734. Process10734 waits forShareLock on transaction 433211275; blocked by process 10737. Process10737 waits forExclusiveLock on tuple (0,11) of relation 25592 of database 12044; blocked by process 10732. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 13 aborted in state 11: ERROR: deadlock detected DETAIL: Process10736 waits forExclusiveLock on tuple (0,12) of relation 25592 of database 12044; blocked by process 10734. Process10734 waits forShareLock on transaction 433211275; blocked by process 10737. Process10737 waits forShareLock on transaction 433211286; blocked by process 10740. Process10740 waits forShareLock on transaction 433211276; blocked by process 10736. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 5 aborted in state 12: ERROR: deadlock detected DETAIL: Process10737 waits forShareLock on transaction 433211286; blocked by process 10740. Process10740 waits forShareLock on transaction 433211272; blocked by process 10731. Process10731 waits forShareLock on transaction 433211279; blocked by process 10734. Process10734 waits forShareLock on transaction 433211275; blocked by process 10737. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 1 aborted in state 10: ERROR: deadlock detected DETAIL: Process10734 waits forShareLock on transaction 433211287; blocked by process 10730. Process10730 waits forShareLock on transaction 433211286; blocked by process 10740. Process10740 waits forShareLock on transaction 433211272; blocked by process 10731. Process10731 waits forShareLock on transaction 433211279; blocked by process 10734. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 7 aborted in state 8: ERROR: deadlock detected DETAIL: Process10743 waits forShareLock on transaction 433211288; blocked by process 10744. Process10744 waits forShareLock on transaction 433211282; blocked by process 10733. Process10733 waits forExclusiveLock on tuple (0,22) of relation 25592 of database 12044; blocked by process 10730. Process10730 waits forShareLock on transaction 433211286; blocked by process 10740. Process10740 waits forShareLock on transaction 433211284; blocked by process 10743. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 14 aborted in state 12: ERROR: deadlock detected DETAIL: Process10740 waits forExclusiveLock on tuple (0,16) of relation 25592 of database 12044; blocked by process 10735. Process10735 waits forShareLock on transaction 433211274; blocked by process 10739. Process10739 waits forShareLock on transaction 433211282; blocked by process 10733. Process10733 waits forExclusiveLock on tuple (0,22) of relation 25592 of database 12044; blocked by process 10730. Process10730 waits forShareLock on transaction 433211286; blocked by process 10740. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 11 aborted in state 4: ERROR: deadlock detected DETAIL: Process10745 waits forExclusiveLock on tuple (0,3) of relation 25592 of database 12044; blocked by process 10741. Process10741 waits forShareLock on transaction 433211278; blocked by process 10735. Process10735 waits forShareLock on transaction 433211274; blocked by process 10739. Process10739 waits forShareLock on transaction 433211291; blocked by process 10733. Process10733 waits forShareLock on transaction 433211290; blocked by process 10730. Process10730 waits forExclusiveLock on tuple (0,3) of relation 25592 of database 12044; blocked by process 10745. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 0 aborted in state 10: ERROR: deadlock detected DETAIL: Process10730 waits forExclusiveLock on tuple (0,3) of relation 25592 of database 12044; blocked by process 10741. Process10741 waits forShareLock on transaction 433211278; blocked by process 10735. Process10735 waits forShareLock on transaction 433211274; blocked by process 10739. Process10739 waits forShareLock on transaction 433211291; blocked by process 10733. Process10733 waits forShareLock on transaction 433211290; blocked by process 10730. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 8 aborted in state 10: ERROR: deadlock detected DETAIL: Process10731 waits forShareLock on transaction 433211294; blocked by process 10733. Process10733 waits forExclusiveLock on tuple (0,76) of relation 25592 of database 12044; blocked by process 10744. Process10744 waits forShareLock on transaction 433211289; blocked by process 10731. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 4 aborted in state 8: ERROR: deadlock detected DETAIL: Process10733 waits forShareLock on transaction 433211293; blocked by process 10744. Process10744 waits forExclusiveLock on tuple (0,89) of relation 25592 of database 12044; blocked by process 10735. Process10735 waits forShareLock on transaction 433211294; blocked by process 10733. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 15 aborted in state 4: ERROR: deadlock detected DETAIL: Process10744 waits forShareLock on transaction 433211296; blocked by process 10735. Process10735 waits forShareLock on transaction 433211298; blocked by process 10739. Process10739 waits forExclusiveLock on tuple (0,90) of relation 25592 of database 12044; blocked by process 10744. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 9 aborted in state 10: ERROR: deadlock detected DETAIL: Process10735 waits forShareLock on transaction 433211298; blocked by process 10739. Process10739 waits forShareLock on transaction 433211296; blocked by process 10735. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement Client 6 aborted in state 11: ERROR: deadlock detected DETAIL: Process10741 waits forShareLock on transaction 433211317; blocked by process 10739. Process10739 waits forShareLock on transaction 433211316; blocked by process 10741. HINT: See server log for query details. CONTEXT: SQL statement "update cnt_a set cnt=cnt+1 where id=(rm*m_id)::int returning cnt" PL/pgSQL function tg_insert_a() line 8 at SQL statement transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:23826 tps=397.094633(including connections establishing) tps =397.187975(excluding connections establishing) statement latencies in milliseconds: 0.002638 \setrandom id 120000000 0.063353 begin; 0.098917 deletefrom a where id=:id; 0.090903 deletefrom a where id=:id; 1.541656 insert into a (info) values ('test'); 0.096450 deletefrom a where id=:id; 1.784244 insert into a (info) values ('test'); 0.095878 deletefrom a where id=:id; 0.899185 insert into a (info) values ('test'); 0.096219 deletefrom a where id=:id; 0.942108 insert into a (info) values ('test'); 0.441609 insert into a (info) values ('test'); 0.482926 insert into a (info) values ('test'); 0.079380 end; # 以下測試改進函數後的場景16個并發以及16條cnt_a記錄的單事務多sql的場景, 避免了死鎖, 同上提高了tps. [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:42402 tps=706.377762(including connections establishing) tps =706.544148(excluding connections establishing) statement latencies in milliseconds: 0.004023 \setrandom id 120000000 0.128100 begin; 0.376305 deletefrom a where id=:id; 0.149250 deletefrom a where id=:id; 14.473279 insert into a (info) values ('test'); 0.206936 deletefrom a where id=:id; 1.340881 insert into a (info) values ('test'); 0.207271 deletefrom a where id=:id; 1.301736 insert into a (info) values ('test'); 0.209022 deletefrom a where id=:id; 1.294269 insert into a (info) values ('test'); 1.342260 insert into a (info) values ('test'); 1.337499 insert into a (info) values ('test'); 0.250370 end; postgres=# select count(*) from a; count -------- 396719 (1 row) postgres=# select sum(cnt) from cnt_a ; sum -------- 396719 (1 row) # 測試原始場景單事務單sql, 16并發16條cnt_a記錄的結果. 比對于改進後的函數tps [email protected]> pgbench -M prepared -r -n -f ./insert.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:1480488 tps=24668.474181(including connections establishing) tps =24674.365320(excluding connections establishing) statement latencies in milliseconds: 0.646597 insert into a (info) values ('test'); # 測試改進函數後的場景單事務單sql, 16并發16條cnt_a記錄的結果. 比對于改進後的函數tps [email protected]> pgbench -M prepared -r -n -f ./insert.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:736812 tps=12278.457482(including connections establishing) tps =12281.288634(excluding connections establishing) statement latencies in milliseconds: 1.300583 insert into a (info) values ('test'); # 測試cnt_a記錄足夠多的情況下(例如2000條), 測試原始場景單事務單sql, 16并發 : postgres=# insert into cnt_a(id,cnt) select generate_series(16,1999),0; INSERT 0 1984 [email protected]> pgbench -M prepared -r -n -f ./insert.sql -h $PGDATA -p 1919 -U postgres -T 60 -c 16 -j 4 postgres transaction type: Custom query scaling factor: 1 query mode: prepared number of clients: 16 number of threads: 4 duration: 60 s number of transactions actually processed: 1722562 tps = 28705.262293 (including connections establishing) tps = 28712.163471 (excluding connections establishing) statement latencies in milliseconds: 0.555513 insert into a (info) values ('test'); # 測試cnt_a記錄足夠多的情況下(例如2000條), 測試改進函數後的場景單事務單sql, 16并發 : [email protected]> pgbench -M prepared -r -n -f ./insert.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:482195 tps=8034.913678(including connections establishing) tps =8036.928653(excluding connections establishing) statement latencies in milliseconds: 1.988503 insert into a (info) values ('test'); # 測試cnt_a記錄足夠多的情況下(例如2000條), 測試原始場景單事務多sql, 16并發 : # 與上面的測試一樣出現了大量的死鎖 [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres Client 0 aborted in state 12: ERROR: deadlock detected transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:123264 tps=2054.315191(including connections establishing) tps =2054.804565(excluding connections establishing) statement latencies in milliseconds: 0.002890 \setrandom id 120000000 0.055029 begin; 0.154473 deletefrom a where id=:id; 0.092312 deletefrom a where id=:id; 0.398831 insert into a (info) values ('test'); 0.099380 deletefrom a where id=:id; 0.374859 insert into a (info) values ('test'); 0.099221 deletefrom a where id=:id; 0.400103 insert into a (info) values ('test'); 0.099028 deletefrom a where id=:id; 0.397862 insert into a (info) values ('test'); 0.444252 insert into a (info) values ('test'); 0.460034 insert into a (info) values ('test'); 0.082733 end; # 測試cnt_a記錄足夠多的情況下(例如2000條), 測試改進函數後的場景單事務多sql, 16并發 : [email protected]> pgbench -M prepared -r -n -f ./id.sql -h $PGDATA -p 1919-U postgres -T 60-c 16-j 4 postgres transaction type:Custom query scaling factor:1 query mode: prepared number of clients:16 number of threads:4 duration:60 s number of transactions actually processed:178495 tps=2974.062219(including connections establishing) tps =2974.751878(excluding connections establishing) statement latencies in milliseconds: 0.003536 \setrandom id 120000000 0.145519 begin; 0.432378 deletefrom a where id=:id; 0.190400 deletefrom a where id=:id; 1.394283 insert into a (info) values ('test'); 0.250328 deletefrom a where id=:id; 0.443856 insert into a (info) values ('test'); 0.234544 deletefrom a where id=:id; 0.420465 insert into a (info) values ('test'); 0.225787 deletefrom a where id=:id; 0.412413 insert into a (info) values ('test'); 0.436313 insert into a (info) values ('test'); 0.437742 insert into a (info) values ('test'); 0.333693 end; # 綜合以上測試, 改進後的函數在單事務中隻有單條a表dml操作的場景中沒有優勢, 在事務中處理需要處理多條a記錄的情況下有優勢. # 對于改進函數的二次改進, 見下一篇blog http://blog.163.com/[email protected]126/blog/static/16387704020133151402415/ 【參考】 以前寫的幾篇優化group by和count(distinct column)的文章, 有興趣的朋友也可以參考一下 1. http://blog.163.com/[email protected]/blog/static/16387704020129851138327/ 2. http://blog.163.com/[email protected]/blog/static/16387704020128142829610/ 關于函數的穩定性 : 1. http://blog.163.com/[email protected]/blog/static/163877040201211241434248/ 2. http://blog.163.com/[email protected]/blog/static/163877040201151011105494/ 随機查詢優化 : 1. http://blog.163.com/[email protected]/blog/static/163877040201111292628555/ advisory locks, 應用程式鎖 : 1. http://blog.163.com/[email protected]/blog/static/163877040201172492217830/
[參考] 為友善大家查詢, 彙總PostgreSQL實時和非實時資料統計的案例分析文章系列 - 如下 : 1. http://blog.163.com/[email protected]/blog/static/163877040201331252945440/ 2. http://blog.163.com/[email protected]/blog/static/16387704020133151402415/ 3. http://blog.163.com/[email protected]/blog/static/16387704020133155179877/ 4. http://blog.163.com/[email protected]/blog/static/16387704020133156636579/ 5. http://blog.163.com/[email protected]/blog/static/16387704020133218305242/ 6. http://blog.163.com/[email protected]/blog/static/16387704020133224161563/ 7. http://blog.163.com/[email protected]/blog/static/16387704020133271134563/
8. http://blog.163.com/[email protected]/blog/static/16387704020134311144755/