PostgreSQL 类微博FEED系统 - 设计与性能指标

3 minute read

背景

类微博系统,最频繁用到的功能:

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');

小结

PG 11后,分区表的写入、查询效率会大幅提升。将来可以直接使用分区表,避免使用UDF动态SQL来访问分区。

PostgreSQL内置UDF(plpgsql, plpython, pljava, plv8 等)功能,可以支持任意排序算法的扩展。

单实例性能指标:

推送 消费
19.5 万行/s 54 万行/s , 2.7 万次/s (平均每次消费20行)

其他辅助技术

1、partial index,只对关心的数据创建索引。

2、流计算,结合流计算实现实时统计,实时数据转换,实时归纳、清洗等。

3、brin 索引,对TS字段,可以使用时序索引。索引小,性能好。类似业务:

《PostgreSQL 时序最佳实践 - 证券交易系统数据库设计 - 阿里云RDS PostgreSQL最佳实践》

4、plproxy,实现分片。阿里云将会提供类似DRDS的PG中间件服务,使得PG的分库分表透明化。

《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 4 水平分库(plproxy) 之 节点扩展》

《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 3 水平分库(plproxy) vs 单机 性能》

《阿里云ApsaraDB RDS for PostgreSQL 最佳实践 - 2 教你RDS PG的水平分库(plproxy)》

5、gpdb mpp,将来如果需要对FEED数据进行挖掘,可以使用HDB PG(Greenplum)。MPP架构,OLAP性能非常棒。类似案例:

《打造云端流计算、在线业务、数据分析的业务数据闭环 - 阿里云RDS、HybridDB for PostgreSQL最佳实践》

6、gin 倒排,文本搜索。实现文本全文检索。

《PostgreSQL 全文检索之 - 位置匹配 过滤语法(例如 ‘速度 <1> 激情’)》

《PostgreSQL UDF实现tsvector(全文检索), array(数组)多值字段与scalar(单值字段)类型的整合索引(类分区索引) - 单值与多值类型复合查询性能提速100倍+ 案例 (含,单值+多值列合成)》

《PostgreSQL - 全文检索内置及自定义ranking算法介绍 与案例》

《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》

7、海明,rum,pg_trgm,文本相似搜索

《海量数据,海明(simhash)距离高效检索(smlar) - 阿里云RDS PosgreSQL最佳实践》

《17种文本相似算法与GIN索引 - pg_similarity》

《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》

《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 2 smlar插件详解》

《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 1 文本(关键词)分析理论基础 - TF(Term Frequency 词频)/IDF(Inverse Document Frequency 逆向文本频率)》

《聊一聊双十一背后的技术 - 毫秒分词算啥, 试试正则和相似度》

《PostgreSQL 文本数据分析实践之 - 相似度分析》

8、plpython, madlib, 文本挖掘

《一张图看懂MADlib能干什么》

《[转载]易上手的数据挖掘、可视化与机器学习工具: Orange介绍》

9、PPC大赛

《三体高可用PCC大赛 - facebook\微博 like场景 - 数据库设计与性能压测》

《阿里云 PostgreSQL 产品生态;案例、开发实践、管理实践、学习资料、学习视频》

《PostgreSQL 传统 hash 分区方法和性能》

Flag Counter

digoal’s 大量PostgreSQL文章入口