PostgreSQL類微博FEED系統-設計與效能指標

德哥發表於2018-04-18

背景

類微博系統，最頻繁用到的功能：

A,D,E使用者關注B使用者。  
  
B使用者推送訊息。  
  
A,D,E使用者接收訊息。  
  
A,D,E使用者消費訊息。涉及消費排序演算法。

之前寫過一篇《三體高可用PCC大賽 – facebook微博 like場景 – 資料庫設計與效能壓測》

LIKE相關場景，用PostgreSQL來設計，效能槓槓的。

本文則是與訊息推送、訊息消費相關的場景。

以內容2048字為例。

設計

為了滿足高效率的推送與消費，設計時，需要考慮到分割槽。分割槽後，也便於將來做較為透明的分庫。

例如可以按使用者的UID進行雜湊分割槽。

1 hash 分割槽表

建立訊息推送表

create table tbl_feed(  
  uid int8,   -- 使用者ID  
  from_uid int8,   -- 被關注使用者ID  
  ts timestamp,    -- 被關注使用者傳送該訊息的時間  
  content text,    -- 被關注使用者傳送該訊息的內容  
  status int       -- 訊息被當前使用者閱讀的狀態, 0 初始狀態， 1 已消費  
);

建立partial index，因為消費時，只關心沒有被消費的記錄。

create index idx_tbl_feed_1 on tbl_feed(uid,ts) where status=0;

建立1024個分割槽

do language plpgsql $$    
declare    
begin    
  for i in 0..1023 loop    
    execute format(`create table tbl_feed_%s (like tbl_feed including all , constraint ck_tbl_feed_%s check(abs(mod(uid,1024))=%s)) inherits(tbl_feed)`, i, i, i);    
  end loop;    
end;    
$$;

2 寫入 UDF

目前RDS PG 10的分割槽表寫入效率和查詢效率不是特別理想，為了達到較好的寫入效率，建議可以先使用UDF，動態拼接SQL。

create or replace function ins_feed(int8, int8, timestamp, text, int) returns void as $$  
declare  
  i int := abs(mod($1,1024));  -- 動態拼接表名  
begin  
  execute format(`insert into tbl_feed_%s(uid,from_uid,ts,content,status) values(%s,%s,%L,%L,%s)`, i, $1,$2,$3,$4,$5);  
end;  
$$ language plpgsql strict;

寫入效能

假設有20億使用者，隨機輸入1個使用者，並推送一條2048個英文字的訊息。

PG 10，單例項，寫入 19.5 萬行/s，瓶頸主要在寫WAL日誌的LOCK上。

set uid random(1,2000000000)  
select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5(`a`),64),0);  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 56  
number of threads: 56  
duration: 120 s  
number of transactions actually processed: 23464891  
latency average = 0.286 ms  
latency stddev = 0.486 ms  
tps = 195379.681306 (including connections establishing)  
tps = 195404.169885 (excluding connections establishing)  
statement latencies in milliseconds:  
         0.001  set uid random(1,2000000000)  
         0.285  select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5(`a`),64),0);

消費 UDF

目前RDS PG 10的分割槽表寫入效率和查詢效率不是特別理想，為了達到較好的寫入效率，建議可以先使用UDF，動態拼接SQL。

create or replace function get_feed(int8, int, text) returns setof tbl_feed as $$  
declare  
  i int := abs(mod($1,1024));   -- 動態拼接表名  
begin  
return query execute format(`with tmp as   
(  
update tbl_feed_%s set status=1 where ctid = any (array(  
  select ctid from tbl_feed_%s where status=0 and uid=%s order by ts limit %s  -- 每次消費N條，按時間先或後消費都可以，都會走索引  
))   
returning *  
)  
select * from tmp order by %s`,  -- 排序演算法可以寫成UDF，或引數傳入, 本例使用ts排序    
i, i, $1, $2, $3  
);    
end;  
$$ language plpgsql strict;

消費例子

postgres=# select * from get_feed(642960384,10,`from_uid`);  
-[ RECORD 1 ]------------------------------------------------------------------------------------------------  
uid      | 642960384  
from_uid | 642960385  
ts       | 2018-03-05 19:41:40.574568  
content  | 0cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc17  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b  
9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e2697726610cc175b9c0f1b6a831c399e  
2697726610cc175b9c0f1b6a831c399e269772661  
status   | 1

消費效能

為了觀察到實際的消費，即每次消費都有至少20條被真實消費掉，這裡先生成一批密集的資料再測。

set uid random(1,4096)  
select ins_feed(:uid,:uid+1,now()::timestamp,repeat(md5(`a`),64),0);  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 120

隨機輸入一個隨機使用者，每次消費20行。平均每秒消費 2.7 萬次。

# set uid random(1,2000000000)  
測試時使用 set uid random(1,4096) 
select * from get_feed(:uid,20,`ts`);  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 56 -j 56 -T 45

transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 56
number of threads: 56
duration: 45 s
number of transactions actually processed: 1195840
latency average = 2.106 ms
latency stddev = 2.707 ms
tps = 26560.345111 (including connections establishing)
tps = 26572.467067 (excluding connections establishing)
statement latencies in milliseconds:
         0.001  set uid random(1,4096) 
         2.105  select * from get_feed(:uid,20,`ts`);