万亿级电商广告 - 毫秒级科技(视觉挖掘姊妹篇) - 阿里云RDS PostgreSQL, HybridDB for PostgreSQL最佳实践

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云原生数据库 PolarDB 分布式版,标准版 2核8GB
云数据库 RDS MySQL Serverless,0.5-2RCU 50GB
简介:

标签

PostgreSQL , BRIN , 数据规整 , 商铺浏览次数 , 商品购买次数 , 条件圈人


背景

单机支持一万亿(100TB级)数据的毫秒级圈人,怎么做到?拥有PostgreSQL即可。

本文的应用场景来自电商的按条件圈人的广告业务。我在另外两篇文档中,分别使用了 空间数据库的视觉挖掘特性、PostgreSQL的GIN倒排索引 两种方法来实现毫秒级别的圈人。

如下:

《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

既然PostgreSQL是全世界最先进的开源数据库,干一件事情,当然是有很多种方法的,对于对技术追求无止境的我,随时都有灵感冒出来,去解决一切业务上的问题。(这主要得益于PostgreSQL的先进,以及一颗热爱PostgreSQL的心。)

相比以上两种方法,BRIN的成本更低廉,效果却更赞。一定是你不可多得的选择。(仅仅增加一步数据规则即可,而广告业务通常数据都是APPEND ONLY的静态数据,数据规整是可以在业务逻辑中加进来的,不会破坏整体的美感。本方案通过业务方验证切实可行。)

废话少说,进入主题。

业务介绍

业务场景和前面两篇文档介绍的一样。

比如一家店铺,如何找到它的目标消费群体?

要回答这个问题,首先我们需要收集一些数据,比如:

1. 这家店铺以及其他的同类店铺的浏览、购买群体。

我们在逛电商时,会产生一些行为的记录,比如在什么时间,逛了哪些店铺,看了哪些商品,最后在哪家店铺购买了什么商品。

然后,对于单个商店来说,有哪些用户逛过他们的商店,购买过哪些商品,可以抽取出一部分人群。

2. 得到这些用户群体后,筛选出有同类消费欲望、或者具备相同属性的群体。

对这部分人群的属性进行分析,可以获得一个更大范围的群体,从而可以对这部分群体进行营销。

业务设计

假设

日用户浏览统计

1、日活用户一亿

2、日活店铺1亿

3、平均每个用户浏览店铺数64家

4、数据量64亿

周用户浏览统计

1、周活用户5亿

2、周活店铺2亿

3、平均每个用户浏览店铺数256家

4、总数据量1280亿

在搞活动时,假设体量*10 最大 1.28万亿。

表结构设计

1、用户浏览统计表,每天从分析系统技术,并生成新的数据,通过阿里云RDS PG的OSS外部表接口,并行导入RDS PG。

create table bi_user_tmall_vis(    
  uid int8, -- 用户ID    
  bid int8, -- 店铺ID,(商品ID使用其他表来表示,结果和查询需求类似,不再赘述)    
  cnt int   -- 浏览次数,(商品浏览次数、购买次数,使用其他表来表示,结果和查询需求类似,不再赘述)    
);    

业务查询需求

1、查询浏览了某个店铺,多少次到多少次,多少次以内,多少次以上的用户ID。

这个方法的目的是找出某一家指定店铺的目标群体。(既然浏览了你的商品,必然对你的店铺感兴趣咯。)

2、同样是以上条件,只不过是由多个OR的条件组成。

BRIN黑科技

BRIN的原理请参考(你只要记住它是几乎0成本的索引即可。):

《PostgreSQL BRIN索引的pages_per_range选项优化与内核代码优化思考》

数据规整

为了得到好的查询效率,必须使用规整,按店铺ID和浏览次数规整。

规整方法如下:

insert into bi_user_tmall_vis1 select * from bi_user_tmall_vis1 order by bid,cnt;    

规整后,再建立bid,cnt联合BRIN索引。

create index idx_bi1 on bi_user_tmall_vis1 using brin (bid,cnt) WITH (pages_per_range='256');    

业务额外需求 - 多属性过滤

用户可能还需要对用户本身的其他属性进行过滤,例如性别、是否老顾客、年龄段等等。

那么也就是说,表结构并没有前面那么简单,只不过为了简化DEMO我做了筛检。

当需要多个查询需求时,有三种优化方法:

1、联合索引

2、多索引,然后利用PostgreSQL的bitmapAnd, bitmapOr合并索引,SKIP扫描

3、使用表分区,将其他查询条件作为分区字段进行分区。

方法的目的都是降低扫描量,提高查询效率。

多级分区表

阿里云HybridDB for PostgreSQL支持多级分区语法。

PostgreSQL则通过多级继承可以实现多级分区。同时,PostgreSQL 10或者pg_pathman插件,都支持多级分区。

(分区键外过滤优化) 利用PG内置多索引 BitmapAnd BitmapOr 扫描功能

例子

create table test(c1 int , c2 int, c3 int);    

索引1

create index idx1 on test (c1);    

索引2

create index idx3 on test (c3);    

当查询索引1和索引2的条件是,PG会自动合并这两个索引。

-- bitmapAnd scan    
    
select * from test where c1 = ? and c3=?;    
    
-- bitmapOr scan    
select * from test where c1 = ? or c3=?;    

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

64亿单表 性能DEMO

1、写入64亿测试数据

vi test.sql    
    
insert into bi_user_tmall_vis select random()*2000000000,random()*100000000,random()*1000 from generate_series(1,10000);    
    
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -t 20000    

表大小:

postgres=# \dt+ bi_user_tmall_vis    
                          List of relations    
 Schema |       Name        | Type  |  Owner   |  Size  | Description     
--------+-------------------+-------+----------+--------+-------------    
 public | bi_user_tmall_vis | table | postgres | 311 GB |     
(1 row)    

2、数据规整

create table bi_user_tmall_vis1 (like bi_user_tmall_vis);    
    
nohup psql -c "set work_mem='128GB';set maintenance_work_mem='128GB';insert into bi_user_tmall_vis1 select * from bi_user_tmall_vis order by bid,cnt;" >/dev/null 2>&1 &    

3、创建brin索引

create index idx_bi on bi_user_tmall_vis1 using brin (bid,cnt) WITH (pages_per_range='512');    

索引大小

3MB左右,夸张吧,311GB的表,索引只有3MB大小。    

4、选出浏览任意店铺,次数在N次到M次之间的用户

 public | idx_bi                 | index | postgres | bi_user_tmall_vis1 | 3336 kB    |       
      
      
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where bid=1 and cnt between 1 and 100;      
                                                               QUERY PLAN                                                                     
----------------------------------------------------------------------------------------------------------------------------------------      
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=521.47..105255.40 rows=7351 width=20) (actual time=16.024..25.791 rows=4 loops=1)      
   Output: uid, bid, cnt      
   Recheck Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
   Rows Removed by Index Recheck: 80380      
   Heap Blocks: lossy=512      
   Buffers: shared hit=529 read=511      
   ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.010..16.010 rows=5120 loops=1)      
         Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
         Buffers: shared hit=528      
 Planning time: 0.238 ms      
 Execution time: 25.822 ms      
(11 rows)      
      
      
      
postgres=# explain (analyze,timing,costs,buffers,verbose) select * from bi_user_tmall_vis1 where (bid=1 and cnt between 1 and 100) or (bid=2000 and cnt <10000) or (bid=12000 and cnt <10000);      
                                  QUERY PLAN       
--------------------------------------------------------------------------------------------------------------------------------------------------------    
 Bitmap Heap Scan on public.bi_user_tmall_vis1  (cost=1674.17..315338.06 rows=153721 width=20) (actual time=47.115..78.014 rows=138 loops=1)      
   Output: uid, bid, cnt      
   Recheck Cond: (((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100)) OR ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000)) OR ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000)))      
   Rows Removed by Index Recheck: 241014      
   Heap Blocks: lossy=1536      
   Buffers: shared hit=2608 read=512      
   ->  BitmapOr  (cost=1674.17..1674.17 rows=241151 width=0) (actual time=47.099..47.099 rows=0 loops=1)      
         Buffers: shared hit=1584      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=16.167..16.167 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 1) AND (bi_user_tmall_vis1.cnt >= 1) AND (bi_user_tmall_vis1.cnt <= 100))      
               Buffers: shared hit=528      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.494..15.494 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 2000) AND (bi_user_tmall_vis1.cnt < 10000))      
               Buffers: shared hit=528      
         ->  Bitmap Index Scan on idx_bi  (cost=0.00..519.63 rows=80384 width=0) (actual time=15.437..15.437 rows=5120 loops=1)      
               Index Cond: ((bi_user_tmall_vis1.bid = 12000) AND (bi_user_tmall_vis1.cnt < 10000))      
               Buffers: shared hit=528      
 Planning time: 0.145 ms      
 Execution time: 78.062 ms      
(19 rows)      
索引精度 单表数据量 单表大小 索引大小 1个条件 2个条件 3个条件
pages_per_range=1 64亿 311GB 1.6GB 8.2秒 - -
pages_per_range=128 64亿 311GB 13MB 62毫秒 - 191毫秒
pages_per_range=256 64亿 311GB 6MB 33毫秒 - 105毫秒
pages_per_range=512 64亿 311GB 3MB 25毫秒 - 78毫秒
pages_per_range=sqrt(pg_class.relpages)=6384 64亿 311GB 300KB 97毫秒 112毫秒 139毫秒

pages_per_range的优化

前面已经讲了,BRIN索引IO方面的成本分为两块,

1、扫描BRIN索引本身的块。

2、扫描HEAP表的块。

两者加起来就是IO方面的成本。其他的就是CPU过滤每一条记录的CPU运算成本。

BRIN索引扫描的IO成本估算

一、单个条件的查询,命中多少个HEAP数据块由两个因素决定:

1、单个条件值占用了多少个数据块,例如bid=1这个条件,有100万条记录,经过前面提到的数据规整,占用到了5000个数据块。

2、pages_per_range的精度,例如精度为512。也就是说至少扫描512个HEAP块。

以上两者取最大值。那么一次查询需要查询5000个HEAP数据块。

二、单个条件的查询,需要扫描多少个BRIN索引数据块则由索引本身的大小决定。

pages_per_range=512时,BRIN索引的大小为3MB左右。

三、单个条件的查询,BRIN索引扫描的IO成本,需要扫描3MB+5000个HEAP BLOCK。

四、多个条件的估算方法类似。

例如3个条件,那么需要扫描3倍的(HEAP BLOCK+BRIN IDX BLOCK)成本。

所以该怎么选择BRIN索引精度参数pages_per_range呢?

pages_per_range的计算方法

给一个衡量标准,10个条件,要求秒级内返回。

如何计算10个条件的BLOCK成本:

1、评估一个等值条件占用多少条记录(A):

1、pg_stats.n_distinct >= 1 时  
  
(pg_class.reltuples/pg_stats.n_distinct)  
  
2、pg_stats.n_distinct < 1 时  
  
(pg_class.reltuples*pg_stats.n_distinct)/pg_class.reltuples  
  
3、pg_stats.n_distinct = -1 时  
  
1  

2、评估相关性(B):

B = abs(pg_stats.correlation)  

3、评估一个等值条件占用多少个HEAP块(C)。

C = A / B  

4、评估pages_per_range=1时,BRIN索引占用多少个数据块(D)。

D = pg_class.relpages/(pg_class.reltuples/pg_class.relpages)  

5、评估pages_per_range=n时,BRIN索引占用多少个数据块(E)

E = D / n   

6、评估pages_per_range=n时,10个查询条件需要扫描多少个BRIN索引块(F)。

F = 10 * E  

7、评估pages_per_range=n时,10个查询条件需要扫描多少个HEAP块(G)。

G = 10 * C  

8、评估pages_per_range=n时,10个查询条件需要扫描多少个HEAP块(H)。

H = F + G  

有了这个公式,你就可以计算到底设置多大的pages_per_range,10个查询条件可以秒级以内返回了。

一万亿体量设计

周统计数据,平时的体量是千亿,搞活动万亿。

前面我们测试的是单表64亿,查询性能完全没有问题(毫秒级返回)。

那么万亿级别怎么搞呢?实际上按店铺、商品ID分区,用分区表即可解决。

例如HASH分区。

按店铺hash分成64个区,每个区1亿。

按店铺hash分成640个区,每个区2 - 20亿。

已测64亿单表性能完全不是问题。你还会担心几亿的小表吗?

PostgreSQL分区使用方法介绍:

https://github.com/postgrespro/pg_pathman

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10.0 preview 功能增强 - 内置分区表》

云端产品

阿里云 RDS PostgreSQL

阿里云 HybridDB for PostgreSQL

小结

本文用到的技术点如下:

1、BRIN,帮助用户0成本,高效过滤数据。

64亿单表,任意店铺条件圈人毫秒级响应。

2、数据规整。提高字段线性相关性,一劳永逸。让BRIN的数据边界近乎完美。

3、分区+数据规整,万亿级别时使用的数据优化方法。

4、HDB 规整 + metascan ,metascan与BRIN类似,是阿里云在HDB for PostgreSQL这个产品上,加的一个内核特性。原始的Greenplum是没有这个特性的。

5、并行APPEND SCAN,拆成多个分区表后,PostgreSQL通过append scan并行,可以并行扫描分区表。提升整体的性能。

6、多字段索引通过bitmapAnd, bitmapOr合并,提高数据过滤精度,降低扫描量,提升查询性能。

参考

《视觉挖掘与PostGIS空间数据库的完美邂逅 - 广告营销\圈人》

《PostgreSQL on ECS多云盘的部署、快照备份和恢复》

《PostgreSQL 10.0 preview sharding增强 - 支持Append节点并行》

《恭迎万亿级营销(圈人)潇洒的迈入毫秒时代 - 万亿user_tags级实时推荐系统数据库设计》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 10.0 preview 功能增强 - 内置分区表》

《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》

相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
存储 容灾 安全
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
在阿里云RDS(Relational Database Service)迁移前准备目标区域选择
23 3
|
2月前
|
监控 NoSQL 关系型数据库
在进行RDS(例如阿里云的RDS)数据迁移后,评估数据一致性
在进行RDS(例如阿里云的RDS)数据迁移后,评估数据一致性
126 3
|
29天前
|
SQL 关系型数据库 MySQL
阿里云MySQL数据库价格、购买、创建账号密码和连接数据库教程
阿里云数据库使用指南:购买MySQL、SQL Server等RDS实例,选择配置和地区,完成支付。创建数据库和账号,设置权限。通过DMS登录数据库,使用账号密码访问。同地域VPC内的ECS需将IP加入白名单以实现内网连接。参考链接提供详细步骤。
368 3
|
1月前
|
弹性计算 关系型数据库 MySQL
阿里云ECS使用docker搭建mysql服务
阿里云ECS使用docker搭建mysql服务
152 1
|
24天前
|
存储 关系型数据库 数据库
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
迎峰而上:汇联易依托阿里云RDS通用云盘,加速业务智能化升级
超1/3中国500强企业都在用的「汇联易」,为什么选用阿里云RDS?
|
29天前
|
弹性计算 关系型数据库 MySQL
阿里云MySQL云数据库优惠价格、购买和使用教程分享!
阿里云数据库使用流程包括购买和管理。首先,选购支持MySQL、SQL Server、PostgreSQL等的RDS实例,如选择2核2GB的MySQL,设定地域和可用区。购买后,等待实例创建。接着,创建数据库和账号,设置DB名称、字符集及账号权限。最后,通过DMS登录数据库,填写账号和密码。若ECS在同一地域和VPC内,可内网连接,记得将ECS IP加入白名单。
431 2
|
30天前
|
SQL 关系型数据库 MySQL
阿里云mysql数据库价格购买和使用教程
阿里云数据库使用指南:购买MySQL、SQL Server等RDS实例,通过选择配置、地域和可用区完成购买。创建数据库和账号,分配权限。使用DMS登录数据库,进行管理操作。确保ECS与RDS在同一地域的VPC内,配置白名单实现内网连接。详细步骤见官方文档。
631 1
|
1月前
|
关系型数据库 MySQL 数据库
使用阿里云的数据传输服务DTS(Data Transmission Service)进行MySQL 5.6到MySQL 8.0的迁移
【2月更文挑战第29天】使用阿里云的数据传输服务DTS(Data Transmission Service)进行MySQL 5.6到MySQL 8.0的迁移
220 2
|
1月前
|
SQL 关系型数据库 MySQL
购买阿里云RDS实例
购买阿里云RDS实例
165 2
|
1月前
|
弹性计算 人工智能 物联网
挖掘阿里云ECS的潜力:创意应用和未来可能性
在云厂商中,我觉得开发者更信赖阿里云的云产品,而且随着阿里云最近宣布云产品降价的消息,会有更多的开发者和企业选择阿里云的云产品。这里拿阿里云的云服务器来做说明,阿里云的云服务器ECS为用户提供了强大的计算资源和灵活的扩展性,使其成为搭建各种有趣和创意应用的理想平台。除了已知的小游戏、小程序和个人网盘等应用案例之外,本文还会进一步探讨ECS在特定场景下的实践经验,并挖掘其在其他领域的潜力,为大家带来更多创意和启发。
556 3
挖掘阿里云ECS的潜力:创意应用和未来可能性

相关产品

  • 云数据库 RDS MySQL 版
  • 云原生数据库 PolarDB