标签
PostgreSQL , feed , 微博 , 推送 , 分区 , 分片 , UDF , 挖掘 , 文本挖掘
背景
类微博系统,最频繁用到的功能:
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 - 全文检索内置及自定义ranking算法介绍 与案例》
《用PostgreSQL 做实时高效 搜索引擎 - 全文检索、模糊查询、正则查询、相似查询、ADHOC查询》
7、海明,rum,pg_trgm,文本相似搜索
《海量数据,海明(simhash)距离高效检索(smlar) - 阿里云RDS PosgreSQL最佳实践》
《17种文本相似算法与GIN索引 - pg_similarity》
《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 3 rum, smlar应用场景分析》
《PostgreSQL结合余弦、线性相关算法 在文本、图片、数组相似 等领域的应用 - 2 smlar插件详解》
《聊一聊双十一背后的技术 - 毫秒分词算啥, 试试正则和相似度》
《PostgreSQL 文本数据分析实践之 - 相似度分析》
8、plpython, madlib, 文本挖掘
《[转载]易上手的数据挖掘、可视化与机器学习工具: Orange介绍》
9、PPC大赛
《三体高可用PCC大赛 - facebook\微博 like场景 - 数据库设计与性能压测》