惊天性能!单实例RDS PostgreSQL 支撑 2000亿 实时标签透视案例

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介: 标签 PostgreSQL , varbitx , 标签 , 任意圈选 , 阿里云 , RDS , bitmap , 分段 , 并行计算 , 异步调用 , dblink , 异步dblink , 游标 背景 20亿用户,每个用户1000个标签,基于任意标签组合圈选、透视(业务上的需求是一次最多计算100个标签的组合)。

标签

PostgreSQL , varbitx , 标签 , 任意圈选 , 阿里云 , RDS , bitmap , 分段 , 并行计算 , 异步调用 , dblink , 异步dblink , 游标


背景

20亿用户,每个用户1000个标签,基于任意标签组合圈选、透视(业务上的需求是一次最多计算100个标签的组合)。

相当于要处理2000亿记录。

1、实时求标签组合的记录数。(即满足标签组合的用户有多少)

2、用户ID。(级满足标签组合的用户ID。)

要求实时响应。

通常你肯定会想,这个至少需要上百台机器来支撑。

但是我要给你一个惊喜,这个数据量,一台RDS PG实例即可。怎么做呢?听我道来,用最少的资源解决业务问题,用到RDS PG黑科技。

RDS PG 解决方案

方案如下:

《阿里云RDS PostgreSQL varbitx实践 - 流式标签 (阅后即焚流式批量计算) - 万亿级,任意标签圈人,毫秒响应》

优化方案,提高响应速度

1、bitmap切段

2、计算满足条件的USER COUNT值时,并行计算(使用dblink异步调用)

3、求用户ID时,使用游标,流式返回。

DEMO

1、需要用到的插件

create extension dblink;  
create extension varbitx;  

2、创建标签表,切段,例如20亿个用户,切成400段,每一段5000万个用户BIT。

postgres=# create table t_bitmap (  
  tagid int,   -- 标签ID  
  ofid int,    -- 偏移值, 乘以5000万  
  v varbit     -- userid 比特  
);  
CREATE TABLE  

3、创建索引(约束)

create unique index idx_t_bitmap_1 on t_bitmap (tagid, ofid);  

4、创建1000个标签的BITMAP数据,每一个标签400条,每条的BIT长度为5000万位。

postgres=# do language plpgsql $$  
declare v varbit := repeat('1',5000000)::varbit;  
begin  
  for i in 1..100 loop  
    for x in 0..399 loop  
      insert into t_bitmap values (i, x, v);                            
    end loop;  
  end loop;  
end;  
$$;  
  
  
DO  
Time: 150468.359 ms (02:30.468)  

5、创建生成dblink连接的函数,重复创建不报错。

create or replace function conn(  
  name,   -- dblink名字  
  text    -- 连接串,URL  
) returns void as $$    
declare    
begin    
  perform dblink_connect($1, $2);   
  return;    
exception when others then    
  return;    
end;    
$$ language plpgsql strict;    

6、AND标签组合的并行计算函数(dblink 异步并行),返回USERID透视数。

create or replace function get_bitcount_and(  
  and_tagids int[],   -- 输入标签ID数组  
  v_bit int,          -- 求1或0的比特个数  
  conn text,          -- 连接串
  OUT cnt int8        -- 返回值, 多少个1或0  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   -- 生成400个链接,因为每行5000万,20亿个BIT,刚好400条。并LOOP  
  perform conn('link'||i,  conn);   -- 连接  
  perform dblink_get_result('link'||i);                        -- 消耗掉上一次异步连接的结果,否则会报错。  
    
  -- 发送异步DBLINK调用  
  -- 每次操作一个bit分段,返回BIT为0或1的位数  
  perform dblink_send_query('link'||i, format('select bit_count(bit_and(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s', v_bit, and_tagids, i));    
end loop;  
  
for i in 0..399 loop  
  -- 返回异步调用结果,包括所有分段  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;  

7、OR标签组合的并行计算函数(dblink 异步并行),返回USERID透视数。

create or replace function get_bitcount_or(  
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 连接串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn('link'||i,  conn);   
  perform dblink_get_result('link'||i);  
  perform dblink_send_query('link'||i, format('select bit_count(bit_or(v), %s) from t_bitmap where tagid = any (%L) and ofid=%s', v_bit, or_tagids, i));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;  

8、AND,OR 标签组合的并行计算函数(dblink 异步并行),返回USERID透视数。

create or replace function get_bitcount_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int,   
  conn text,          -- 连接串
  OUT cnt int8  
) returns setof int8 as $$   
declare  
begin  
for i in 0..399 loop   
  perform conn('link'||i,  conn);   
  perform dblink_get_result('link'||i);  
  perform dblink_send_query('link'||i, format('  
    with t1 as (select bit_and(v) b from t_bitmap where tagid = any (%L) and ofid=%s),   
         t2 as (select bit_or(v) b from t_bitmap where tagid = any (%L) and ofid=%s)   
    select bit_count(bitor(t1.b, t2.b), %s) from t1,t2',   
    and_tagids, i, or_tagids, i, v_bit));  
end loop;  
  
for i in 0..399 loop  
  return query SELECT * FROM dblink_get_result('link'||i) as t(cnt int8);  
end loop;  
end;  
$$ language plpgsql strict;  
-- 更复杂的QUERY,可以自行修改函数。实际业务中这种需求较少。  
-- (a and b andc or d) or (a and c) or (d and not b)..........  

9、计数透视的性能如下,50个标签组合,仅1.5秒,100个标签组合,仅2.6秒:

我们统计2000亿个user_tags组合(每个用户一条记录,每条记录1000个标签时的换算),仅仅需要2.6秒。

一个标签:  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,1) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 791.392 ms  
  
10个标签组合:  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 847.427 ms  
  
50个标签组合:  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1478.847 ms (00:01.479)  
  
100个标签组合:  
postgres=# select sum(cnt) from (select get_bitcount_and(array_agg(id),1,'dbname=postgres user=postgres') cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2574.761 ms (00:02.575)  

10、AND 、 OR组合性能如下,性能一样:

postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,1) t(id)) t;  
 sum   
-----  
      
(1 row)  
  
Time: 854.934 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,10) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 889.472 ms  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,50) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 1519.031 ms (00:01.519)  
postgres=# select sum(cnt) from (select get_bitcount_and_or(array_agg(case mod(id,2) when 0 then id end), array_agg(case mod(id,2) when 1 then id end), 1,'dbname=postgres user=postgres') cnt from generate_series(1,100) t(id)) t;  
    sum       
------------  
 2000000000  
(1 row)  
  
Time: 2597.701 ms (00:02.598)  

11、求USERID,AND 函数如下,我们为了达到高速响应,使用游标返回。

create or replace function get_pos_and(  
  and_tagids int[],    -- 标签组合  
  v_bit int            -- 求1或0的BIT位,返回游标,游标包含ofid与位置下标(当然了,这个翻译动作也可以交给程序,那么返回BIT和ofid即可)  
) returns setof refcursor as $$  
declare  
  ref refcursor[];    -- 返回游标数组  
  res refcursor;      -- 返回游标  
  sql text;           -- 游标对应的SQL,即取USERID位置的SQL  
begin  
  for x in 1..400 loop   -- 生成400个游标名  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    -- 使用0到399的偏移值, 乘以5000万系数。  
      
    -- 赋予游标名  
    res := ref[i+1];   
    -- 生成游标对应的动态SQL(ofid, bit位置),注意bit位置可以不翻译,交给程序翻译也没问题。程序翻译的话,翻译好之后,再使用in查询字典  
    -- select uid from uid_mapping where pos in (pos_array);  
    -- 1亿,in 100万, 380毫秒  
    -- [《HTAP数据库 PostgreSQL 场景与性能测试之 25 - (OLTP) IN , EXISTS 查询》](201711/20171107_26.md)    
    sql := format('select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, and_tagids, i);   
    -- 打开游标  
    open res for execute sql ;  
    -- 返回游标  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;  

12、求USERID,OR 函数如下,我们为了达到高速响应,使用游标返回。

create or replace function get_pos_or(  
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format('select %s, bit_posite(bit_or(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, or_tagids, i);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;  

13、求USERID,AND OR 函数如下,我们为了达到高速响应,使用游标返回。

create or replace function get_pos_and_or(  
  and_tagids int[],   
  or_tagids int[],   
  v_bit int   
) returns setof refcursor as $$  
declare  
  ref refcursor[];    
  res refcursor;   
  sql text;  
begin  
  for x in 1..400 loop  
    ref[x] := 'cur'||x;  
  end loop;  
  
  for i in 0..399 loop   
    res := ref[i+1];   
    sql := format('with t1 as  
                   (select bit_and(v) v from t_bitmap where tagid = any (%L) and ofid=%s),  
                   t2 as  
                   (select bit_or(v) v from t_bitmap where tagid = any (%L) and ofid=%s)  
                   select %s, bit_posite(bitor(t1.v, t2.v), %s, true) from t1,t2',   
                  and_tagids, i, or_tagids, i, i, v_bit);  
    open res for execute sql ;  
    return next res;  
  end loop;  
end;  
$$ language plpgsql strict;  

14、求USERID例子,88毫秒响应,极端速度。

postgres=# begin;  
BEGIN  
Time: 0.031 ms  
postgres=# select * from get_pos_and_or(array[1,2,3], array[4,5,6], 1);  
 get_pos_and_or   
----------------  
 cur1  
 cur2  
 cur3  
 cur4  
 cur5  
 cur6  
 cur7  
 ....  
 cur399  
 cur400  
(400 rows)  
  
Time: 88.069 ms  

获取游标值,5000万ID,仅692毫秒:

fetch 1 from cur1;  
Time: 692.408 ms  

15、如果我们把位置翻译放到客户端做,那么只需要获取结果BITMAP,那就更快了,224毫秒就可以获取5000万BIT走。 这块也能做成并发,每个客户端获取不同的ofid。

CREATE OR REPLACE FUNCTION public.get_pos_and(and_tagids integer[])
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
 STRICT
AS $function$
declare
  ref refcursor[];  
  res refcursor; 
  sql text;
begin
  for x in 1..400 loop
    ref[x] := 'cur'||x;
  end loop;

  for i in 0..399 loop 
    res := ref[i+1]; 
    -- sql := format('select %s, bit_posite(bit_and(v), %s, true) from t_bitmap where tagid = any (%L) and ofid=%s', i, v_bit, and_tagids, i);
    sql := format('select %s, bit_and(v) from t_bitmap where tagid = any (%L) and ofid=%s', i, and_tagids, i);
    open res for execute sql ;
    return next res;
  end loop;
end;
$function$;


postgres=# \timing
Timing is on.
postgres=# begin;
BEGIN
Time: 0.045 ms
postgres=# select get_pos_and(array_agg(id)) from generate_series(1,100) t(id);
 get_pos_and 
-------------
 cur1
 cur2
 cur3
...
 cur397
 cur398
 cur399
 cur400
(400 rows)


fetch 1 from cur1;
Time: 224.776 ms

小结

varbitx是阿里云RDS PG提供的一个插件,使用它,单个RDS PG就可以实现万亿级别USER_TAGS的实时圈选。

使用BITMAP分段、DBLINK异步查询、游标等技术,提高性能。

性能指标:

1、求COUNT,2000亿(20亿用户,100个标签组合)USER_IDS,响应速度2.6秒。

2、求USERID明细,返回5000万用户ID位置,仅692毫秒。

3、求USERID明细,如果只返回BITMAP,5000万个BIT仅需224毫秒。

参考

《阿里云RDS PostgreSQL varbitx实践 - 流式标签 (阅后即焚流式批量计算) - 万亿级,任意标签圈人,毫秒响应》

《阿里云RDS for PostgreSQL varbitx插件与实时画像应用场景介绍》

《基于 阿里云 RDS PostgreSQL 打造实时用户画像推荐系统(varbitx)》

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
3月前
|
分布式计算 关系型数据库 MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
大数据-88 Spark 集群 案例学习 Spark Scala 案例 SuperWordCount 计算结果数据写入MySQL
66 3
|
3月前
|
SQL 关系型数据库 MySQL
案例剖析:MySQL唯一索引并发插入导致死锁!
案例剖析:MySQL唯一索引并发插入导致死锁!
325 0
案例剖析:MySQL唯一索引并发插入导致死锁!
|
3月前
|
存储 关系型数据库 MySQL
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB区别,适用场景
一个项目用5款数据库?MySQL、PostgreSQL、ClickHouse、MongoDB——特点、性能、扩展性、安全性、适用场景比较
|
4月前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择:MySQL、PostgreSQL 还是 NoSQL?
在微服务架构中,数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中,我们将深入探讨传统的关系型数据库(如 MySQL 和 PostgreSQL)与现代 NoSQL 数据库的优劣势,并分析在微服务架构下的最佳实践。
|
3月前
|
SQL 关系型数据库 MySQL
案例剖析,MySQL共享锁引发的死锁问题!
案例剖析,MySQL共享锁引发的死锁问题!
|
3月前
|
消息中间件 关系型数据库 MySQL
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
大数据-117 - Flink DataStream Sink 案例:写出到MySQL、写出到Kafka
319 0
|
1月前
|
存储 关系型数据库 MySQL
10个案例告诉你mysql不使用子查询的原因
大家好,我是V哥。上周与朋友讨论数据库子查询问题,深受启发。为此,我整理了10个案例,详细说明如何通过优化子查询提升MySQL性能。主要问题包括性能瓶颈、索引失效、查询优化器复杂度及数据传输开销等。解决方案涵盖使用EXISTS、JOIN、IN操作符、窗口函数、临时表及索引优化等。希望通过这些案例,帮助大家在实际开发中选择更高效的查询方式,提升系统性能。关注V哥,一起探讨技术,欢迎点赞支持!
164 5
|
1月前
|
关系型数据库 MySQL 数据库
市场领先者MySQL的挑战者:PostgreSQL的崛起
PostgreSQL(简称PG)是世界上最先进的开源对象关系型数据库,起源于1986年的加州大学伯克利分校POSTGRES项目。它以其丰富的功能、强大的扩展性和数据完整性著称,支持复杂数据类型、MVCC、全文检索和地理空间数据处理等特性。尽管市场份额略低于MySQL,但PG在全球范围内广泛应用,受到Google、AWS、Microsoft等知名公司支持。常用的客户端工具包括PgAdmin、Navicat和DBeaver。
56 4
|
1月前
|
关系型数据库 MySQL 数据库
数据库数据恢复—MYSQL数据库文件损坏的数据恢复案例
mysql数据库文件ibdata1、MYI、MYD损坏。 故障表现:1、数据库无法进行查询等操作;2、使用mysqlcheck和myisamchk无法修复数据库。
|
2月前
|
存储 关系型数据库 MySQL
MySQL vs. PostgreSQL:选择适合你的开源数据库
在众多开源数据库中,MySQL和PostgreSQL无疑是最受欢迎的两个。它们都有着强大的功能、广泛的社区支持和丰富的生态系统。然而,它们在设计理念、性能特点、功能特性等方面存在着显著的差异。本文将从这三个方面对MySQL和PostgreSQL进行比较,以帮助您选择更适合您需求的开源数据库。
256 4

相关产品

  • 云数据库 RDS
  • 云数据库 RDS PostgreSQL 版
  • 云数据库 RDS MySQL 版