驚天效能！單例項RDSPostgreSQL支撐2000億實時標籤透視案例
標籤
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=# iming
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)》
相關文章
- PgSQL·應用案例·驚天效能！單RDSPostgreSQL例項支撐2000億SQL
- canvas標籤clock(時鐘)案例Canvas
- HTML 使用表單標籤實現註冊頁面的例項程式碼HTML
- vuejs實現新增tag標籤程式碼例項VueJS
- jQuery自定義標籤程式碼例項jQuery
- javascript球形標籤雲程式碼例項JavaScript
- 高效資料通道支撐生產情況實時分析與視覺化視覺化
- jQuery實現的刪除指定標籤程式碼例項jQuery
- 使用<link>標籤引入css檔案簡單程式碼例項CSS
- 透過Span實現高效能陣列，例項解析陣列
- JSTL的標籤及使用，包含例項JS
- jQuery刪除html標籤程式碼例項jQueryHTML
- javascript使用標籤包裹字串程式碼例項JavaScript字串
- dd dt dl標籤應用例項程式碼
- Git檢視tag標籤建立時間Git
- 三項執行層的支援，支撐起敏捷實施的天空敏捷
- jquery獲取元素的標籤tagName程式碼例項jQuery
- 刪除字串中的html標籤程式碼例項字串HTML
- html中常用的標籤-表單標籤HTML
- 架構視覺化支撐系統演進探索架構視覺化
- iOS簡單高效能標籤TagView（巧用YYLabel）iOSView
- 舒明：穩定支撐日高峰億級保單交易，國泰產險的運維創新實踐運維
- 支撐微博億級社交平臺，小白也能玩轉Redis叢集(實戰篇)Redis
- a標籤裡面巢狀一個a標籤，點選子連結標籤時，同時觸發了父標籤a巢狀
- JSP簡單標籤標籤庫開發JS
- 獲取iframe標籤的src屬性值程式碼例項
- 天氣例項ApiAPI
- duplicate搭建DG最大效能（rac-單例項）單例
- 使用HAProxy、PHP、Redis和MySQL支撐每週10億請求PHPRedisMySql
- 愛奇藝短視訊智慧標籤生成實踐
- 透過列舉enum實現單例單例
- 【quickhybrid】API多平臺支撐的實現UIAPI
- storm實時計算例項（socket實時接入)ORM
- 創新支撐網路安全西電捷通8項技術被立為國際標準
- 支撐2715億元海量訂單 揭祕京東大促背後的資料庫基石資料庫
- 綠盟科技蟬聯“CNNVD優秀技術支撐單位”CNN
- HTML5 audio標籤使用js進行播放控制例項HTMLJS
- 達夢資料庫單例項轉實時同步叢集資料庫單例