187
音視圖(泛內容)網站透視分析 DB設計 - 阿裏雲(RDS、HybridDB) for PostgreSQL最佳實踐
標簽
PostgreSQL , 用戶透視 , 設備透視 , 圈人 , 標簽 , 視頻網站 , 優酷 , 土豆 , 喜馬拉雅
背景
日常生活中，人們使用最多的除了社交類網站、購物網站，估計就是音頻、視頻、圖文信息類內容網站了。
視頻網站，已經滲透到各種終端，除了喜聞樂見的手機，還包括移動終端、電腦、盒子、電視、投影儀等。有設備屬性、會員屬性、渠道屬性等。
內容運營是非常重要的環節，而透視則是運營的重要武器。
業務需求
1、生成設備、會員畫像
ID、各個維度的標簽、其中包括一些多值列標簽（例如最近7天、一個月看過的導演、演員主演的片子，。。。。。。）
通常多值列的值可能是幾十萬的量（幾十萬演員，幾十萬片源，。。。）。通常一個設備、人有幾十上百個屬性列。通常一個設備單個多值列內可能有幾十個VALUE。
2、畫像透視
2.1、根據任意標簽條件組合查詢滿足條件的數目（圈中設備、會員的數目，count）
2.2、根據任意標簽條件組合圈選出人群，統計該人群某些列中各種的分別數目比例（count, group by, 分位數, 多維透視）
並發要求較低。
多維透視和分位數統計詳見：
《PostgreSQL 9.5 new feature - Support GROUPING SETS, CUBE and ROLLUP.》
《PostgreSQL aggregate function 2 : Aggregate Functions for Statistics》
3、圈選目標設備、會員
根據任意標簽條件組合分頁查詢滿足條件的id列表（圈出滿足條件的設備或會員）
並發要求較低。
4、點查（鍵值查詢）需求（根據任意標簽條件組合和用戶id查詢這個用戶是否滿足該圈人規則）。
點查的並發需求較高，可能達到幾萬次請求每秒。
小結，量級估算：
作為一個音頻視頻網站，內容數目通常不會超過百萬（當然，引入了小視頻、自媒體後，也許有可能達到億甚至百億級別）。
用戶數+設備數頂到天也就百億（地球人口才多少呀）。而且設備是會老化的，活躍設備數，估計頂天也就十億級別。
標簽列數，這個看抽象能力，就以百來計吧。多值列（例如喜歡的演員、影片、導演等）的占比可能較多，預計一半一半吧。
多值列的VALUE範圍（例如演員、影片、導演等），預計在百萬級別。（A用戶喜愛綜藝明星標簽：汪涵,張宇,劉維）
多值列標簽，預計在幾十個的級別，通常對運營有效的是最近看過的影片。除了鑒黃師，一般也不會一天到晚的看電影對吧。
G點分析
1、多值列的存儲和透視。
1.1 多值列的存儲：
PostgreSQL支持數組類型，可以作為多值標簽列的數據類型。同時由於它是一個數據類型，所以內置了數組常見的包含、相交、不相交、疊加、UNNEST、類型轉換的操作符和函數。使用起來非常方便。
https://www.postgresql.org/docs/9.6/static/functions-array.html
1.2 多值列的透視：
例如用戶喜愛的演員是個多值列，求浙江省的用戶最喜愛的演員TOP 10。
表級統計，可以直接使用統計信息進行估計：
《PostgreSQL pg_stats used to estimate top N freps values and explain rows》
如果是帶條件的TOP N，需要實際的運算，unnest(array)即可，例如
select unnest(c2) as c2, count(*) from -- 使用unnest將數組元素解開進行統計
tbl
group by 1
order by 2 desc
limit 10; -- 求c2多值列，在某個條件下的TOP 10元素
2、時間、空間（地理位置）維度分析
會員、設備實際上是有地理位置屬性的，我們可以加入GIS透視。PostgreSQL支持geometry類型、索引、操作符、聚合函數、窗口函數、聚類分析函數等。在GIS方麵的處理能力特別強悍。
有許多案例可以參考：
《Greenplum 空間(GIS)數據檢索 b-tree & GiST 索引實踐 - 阿裏雲HybridDB for PostgreSQL最佳實踐》
《數據尋龍點穴（空間聚集分析） - 阿裏雲RDS PostgreSQL最佳實踐》
《PostGIS空間索引(GiST、BRIN、R-Tree)選擇、優化 - 阿裏雲RDS PostgreSQL最佳實踐》
《(新零售)商戶網格化(基於位置GIS)運營 - 阿裏雲RDS PostgreSQL、HybridDB for PostgreSQL最佳實踐》
《時間、空間、對象多維屬性 海量數據任意多維 高效檢索 - 阿裏雲RDS PostgreSQL最佳實踐》
《PostgreSQL\GPDB 毫秒級海量時空數據透視 典型案例分享》
《PostgreSQL\GPDB 毫秒級海量多維數據透視 案例分享》
《自動選擇正確索引訪問接口(btree,hash,gin,gist,sp-gist,brin,bitmap...)的方法》
《無人駕駛背後的技術 - PostGIS點雲(pointcloud)應用 - 2》
《無人駕駛背後的技術 - PostGIS點雲(pointcloud)應用 - 1》
《視覺挖掘與PostGIS空間數據庫的完美邂逅 - 廣告營銷\圈人》
《開放地圖OpenStreetMap與PostGIS的三生三世十裏桃花》
《基於PostgreSQL和地理位置信息打造的洞察平台 - CARTO》
《PostGIS 地理信息、柵格數據 多核並行處理(st_memunion, st_union)》
《蜂巢的藝術與技術價值 - PostgreSQL PostGIS's hex-grid》
《(AR虛擬現實)紅包 技術思考 - GIS與圖像識別的完美結合》
《如何建立GIS測試環境 - 將openstreetmap的樣本數據導入PostgreSQL PostGIS庫》
《PostgreSQL 最佳實踐 - 塊級增量備份(ZFS篇)單個數據庫采用多個zfs卷(如表空間)時如何一致性備份》
《GIS附近查找性能優化 - PostGIS long lat geometry distance search tuning using gist knn function》
《PostgreSQL 9.1 nearest-neighbor search use gist index》
3、運算量
透視需求，需要較大運算能力。PostgreSQL 9.6開始支持多核並行，以目前的10萬左右的硬件，單機每秒可以處理的吞吐約10GB/s，已經達到了HTAP的水準。
如果要尋求更好的分析計算能力，可以采用阿裏雲HybridDB for PostgreSQL，屬於PostgreSQL的多機並行(OLAP)版本。支持列存儲、行存儲、壓縮、多機並行等特性。
4、數據導入
通常視頻係統的用戶畫像實時性要求並不高，每天都會生成新的用戶畫像，每天將生成的畫像寫入阿裏雲RDS PostgreSQL或HybridDB for PostgreSQL，提供透視即可。
架構設計
為了同時滿足透視、高並發點查的需求。有兩套方案可以實現。
方案1：阿裏雲 RDS PG 9.4 + HDB PG
通過ETL調度係統，畫像數據經過OSS導入RDS PG和HDB PG。
點查需求交給RDS PG 9.4。
透視需求交給HDB PG。
方案2：阿裏雲 RDS PG 10
RDS PG 10，支持多核並行，64核的機器每秒處理的數據約10GB（硬盤的帶寬視情況，通常單塊NVME卡可以達到約1.6GB/s）。很容易估算透視的時間。
如果業務的透視需求時間允許，可以考慮隻使用RDS PG 10來實現業務的目標。
另一方麵，RDS PG 10支持多個索引BITMAP SCAN合並掃描，實際上不需要掃描全表。效率非常高。
《PostgreSQL bitmapAnd, bitmapOr, bitmap index scan, bitmap heap scan》
《多字段，任意組合條件查詢(無需建模) - 毫秒級實時圈人 最佳實踐》
DEMO 1（方案1 驗證與性能）
產生隨機多值列的函數
創建一個函數，方便產生我們需要的多值列。
create or replace function gen_rand_int(
int, -- 類別數量：影片、演員、分類(喜劇、恐怖...)、年份、種類(電影、電視劇...).... 。
int, -- 熱門類別數量，類似大V數。必須小於$1
int -- 標簽數
) returns int[] as $$
select array(
select (ceil(random()*$1))::int+$2 from generate_series(1,$3/2) -- 一半普通
union all
select (ceil(random()*$2))::int from generate_series(1,$3/2) -- 一半熱門
) ;
$$ language sql strict;
postgres=# select gen_rand_int(10000,100,30);
gen_rand_int
-------------------------------------------------------------------------------------------------------------------------
{5946,6877,7287,9091,1761,4870,2100,573,8388,2079,1400,9744,703,4834,6843,18,13,32,97,53,68,43,72,32,62,85,47,15,79,70}
(1 row)
postgres=# select gen_rand_int(10000,100,30);
gen_rand_int
--------------------------------------------------------------------------------------------------------------------------
{4558,3818,3836,1744,2808,5496,6513,4808,5969,4801,6786,7268,4444,7388,2002,11,91,32,43,88,85,11,30,56,43,92,40,90,7,19}
(1 row)
postgres=# select gen_rand_int(10000,100,30) from generate_series(1,10);
gen_rand_int
---------------------------------------------------------------------------------------------------------------------------
{5950,6793,6047,7559,4635,1463,8022,1337,9059,8822,1641,6099,116,6626,5311,77,89,62,30,72,48,56,29,72,72,61,29,64,60,38}
{9932,1943,579,5878,9401,5113,7241,7322,6349,6200,6043,7889,2199,6059,4415,74,37,32,35,66,3,83,22,31,54,93,91,83,56,51}
{2109,5528,7033,2587,1306,6333,7600,8446,3555,3848,4546,9498,1636,6644,5456,59,39,90,90,74,56,93,56,77,24,10,70,15,92,26}
{6594,1261,8079,3526,3748,9284,9759,1247,7630,3213,4995,2075,2610,6531,8619,79,23,24,69,13,97,24,5,53,1,28,62,70,42,54}
{9646,769,6562,7625,4195,210,6808,3853,1356,4337,6966,6251,6312,9476,2681,48,73,49,72,41,62,68,65,66,21,65,94,82,35,36}
{3558,3132,4296,10019,657,8391,128,7364,2143,1384,1601,9008,7534,7813,8383,1,25,56,49,96,97,10,64,61,76,84,26,70,65,61}
{543,10050,9165,4739,9969,9721,3029,9997,6985,5071,1280,8486,3979,8714,6198,22,87,86,77,36,81,73,45,45,34,21,28,59,90,93}
{2024,9511,9292,1089,4149,9160,710,7078,9056,7595,2048,236,5980,5927,8850,20,80,74,6,57,9,87,30,54,31,64,75,58,22,64}
{5106,4223,5900,4297,5211,9949,3357,5821,6926,2313,3315,8874,2449,9195,4701,11,11,26,85,16,83,94,2,13,48,33,76,22,90,98}
{8637,4072,3953,4436,8268,9064,4285,1525,4784,1110,3737,7999,9884,6086,7093,44,71,81,70,56,97,53,50,99,65,97,31,40,18,21}
(10 rows)
畫像表
1、阿裏雲 RDS PostgreSQL
-- 70個字段
-- 40個INT字段，10000,1000,100取值範圍。
-- 30個數組字段，使用上述隨機數組生成函數（總標簽數10萬，熱門標簽150，關注標簽數20）
-- PostgreSQL 10
do language plpgsql $$
declare
sql text := '';
begin
for i in 1..10 loop
sql := sql||'c'||i||' int default random()*10000,';
end loop;
for i in 11..20 loop
sql := sql||'c'||i||' int default random()*1000,';
end loop;
for i in 21..40 loop
sql := sql||'c'||i||' int default random()*100,';
end loop;
for i in 41..70 loop
sql := sql||'c'||i||' int[] default gen_rand_int(100000,150,20),';
end loop;
sql := rtrim(sql, ',');
sql := 'create table test ('||sql||') with (autovacuum_enabled=off, toast.autovacuum_enabled=off, parallel_workers=32)';
execute sql;
end;
$$;
/*
-- 建立分區表
-- PostgreSQL 10
do language plpgsql $$
declare
begin
for i in 1..64 loop
execute 'create unlogged table test'||i||'(like test including all) inherits(test) with (autovacuum_enabled=off, toast.autovacuum_enabled=off, parallel_workers=32)';
end loop;
end;
$$;
*/
2、阿裏雲HybridDB for PostgreSQL
-- Greenplum
create or replace function cb() returns void as
$$
declare
sql text := '';
begin
for i in 1..10 loop
sql := sql||'c'||i||' int default random()*10000,';
end loop;
for i in 11..20 loop
sql := sql||'c'||i||' int default random()*1000,';
end loop;
for i in 21..40 loop
sql := sql||'c'||i||' int default random()*100,';
end loop;
for i in 41..70 loop
sql := sql||'c'||i||' int[] default gen_rand_int(100000,150,20),';
end loop;
sql := rtrim(sql, ',');
sql := 'create table test ('||sql||') with (APPENDONLY=true, ORIENTATION=column, COMPRESSTYPE=zlib, CHECKSUM=false, COMPRESSLEVEL=5) distributed randomly';
execute sql;
end;
$$ language plpgsql strict;
select cb();
數據導入
1、寫入示例
insert into test values (1);
postgres=# select * from test;
-[ RECORD 1 ]-------------------------------------------------------------------------------------------
c1 | 1
c2 | 4880
c3 | 6058
c4 | 1080
c5 | 9862
c6 | 7605
c7 | 9139
c8 | 2541
c9 | 5599
c10 | 9818
c11 | 658
c12 | 882
c13 | 249
c14 | 514
c15 | 504
c16 | 390
c17 | 97
c18 | 422
c19 | 906
c20 | 374
c21 | 25
c22 | 4
c23 | 74
c24 | 87
c25 | 52
c26 | 56
c27 | 83
c28 | 47
c29 | 36
c30 | 18
c31 | 40
c32 | 29
c33 | 67
c34 | 1
c35 | 40
c36 | 66
c37 | 77
c38 | 31
c39 | 91
c40 | 33
c41 | {29495,57121,21227,54417,8477,71587,93375,18150,13788,84006,84,58,133,45,38,62,128,12,133,32}
c42 | {26442,28622,50446,93545,29479,90221,59274,6384,21385,50413,59,76,11,91,8,24,48,148,51,68}
c43 | {82075,89069,83949,70379,18540,9073,11113,3676,17058,99304,38,65,42,113,55,86,98,144,95,130}
c44 | {46129,2464,37175,53362,62667,42021,68922,94306,40090,2482,60,33,137,35,139,15,49,5,20,74}
c45 | {2359,38160,92410,29984,13302,29003,86937,78513,24719,50124,98,106,79,3,36,23,66,139,14,126}
c46 | {95012,48541,5658,86261,71773,97751,95901,3926,806,9065,80,5,71,68,50,91,111,30,58,148}
c47 | {69608,3397,69214,21894,5231,92972,36696,48935,85503,45654,49,121,141,57,100,99,54,94,104,55}
c48 | {71140,22280,39205,18064,67376,71903,78140,41324,91387,16578,60,92,30,14,124,38,3,29,111,131}
c49 | {64638,6116,67292,58532,44051,33617,24049,79587,95692,93341,24,100,23,83,127,124,40,94,36,27}
c50 | {79012,63559,78516,98686,72313,60953,23440,73888,79936,96978,91,67,5,42,4,71,92,40,40,86}
c51 | {19894,41908,23496,35213,96590,7941,17758,23024,70375,41477,61,74,8,29,72,116,120,107,76,90}
c52 | {67889,11450,3921,70683,39257,6576,17377,530,33128,43508,86,80,128,121,132,123,133,9,7,88}
c53 | {46869,45123,7791,51604,64032,55412,28502,43744,26323,79136,5,141,136,11,97,45,20,123,45,70}
c54 | {25178,87116,99137,10293,67656,86921,91847,55986,92314,96275,22,59,62,34,136,8,116,29,73,6}
c55 | {97823,51814,97527,88109,58677,61970,17501,71964,43640,47272,28,103,52,26,118,3,6,106,87,145}
c56 | {66630,71970,35032,7726,94002,25368,12705,71295,44055,61277,112,63,20,108,45,107,51,71,65,116}
c57 | {94158,61360,45962,28245,78426,24621,29838,82264,94976,87266,118,92,89,20,104,80,58,123,36,124}
c58 | {42712,98691,23844,55502,70678,53379,26818,4484,265,69948,123,142,47,42,34,14,78,78,138,71}
c59 | {39169,69661,8193,98104,82656,77075,50890,20869,58510,74821,5,2,110,40,85,66,120,125,73,120}
c60 | {52889,29852,74145,83896,57293,96361,93179,9204,48264,84576,84,131,81,96,128,55,62,54,86,149}
c61 | {10646,60146,190,83857,86676,56463,27596,66435,39404,75669,70,138,8,31,114,94,25,104,108,97}
c62 | {53342,27357,51760,7277,91190,36845,43718,31948,72670,878,47,125,92,47,101,71,131,142,21,40}
c63 | {69836,59842,18662,75056,79995,94400,37418,96359,63166,8834,92,25,54,19,36,41,74,101,89,33}
c64 | {67779,89791,4675,28502,20745,71397,75751,8011,65565,89284,52,53,74,80,16,44,71,71,38,16}
c65 | {56107,85714,26515,91997,98009,49849,18926,46998,16751,77652,103,127,101,110,19,132,67,133,144,15}
c66 | {77465,30127,44793,26290,83019,54798,54960,30160,2072,79839,61,87,98,100,75,95,25,103,15,50}
c67 | {46151,78465,17210,13293,51346,29791,1029,95475,17972,96626,8,143,40,75,32,14,7,115,59,10}
c68 | {55925,79647,64106,21177,46179,13831,84287,62410,82330,94244,143,43,109,19,62,36,63,64,29,90}
c69 | {38828,23945,54898,65279,73454,76174,74600,77610,52260,13930,126,12,140,72,44,59,92,20,3,66}
c70 | {7291,96804,71685,79699,8954,13008,3303,50744,55210,22232,16,141,69,98,89,29,62,50,145,140}
2、動態寫入對應分區的方法
PostgreSQL 10
create or replace function ff(
int, -- 分區號
int -- 寫入記錄數
) returns void as $$
declare
begin
execute 'insert into test'||$1||' select random()*100 from generate_series(1,'||$2||')';
end;
$$ language plpgsql strict;
3、直接寫入單表的方法
PostgreSQL 10
vi test.sql
insert into test select random()*100 from generate_series(1,100);
nohup pgbench -M prepared -n -r -P 5 -f ./test.sql -c 64 -j 64 -t 50000 >/tmp/log 2>&1 &
約6.2萬行每秒，性能取決於單行的大小。
我這邊的測試3.2億數據占用977GB，如果單行變小，性能會隨之提升。
transaction type: ./test.sql
scaling factor: 1
query mode: prepared
number of clients: 64
number of threads: 64
number of transactions per client: 50000
number of transactions actually processed: 3200000/3200000
latency average = 102.605 ms
latency stddev = 29.016 ms
tps = 622.235371 (including connections establishing)
tps = 622.236656 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
102.611 insert into test select random()*100 from generate_series(1,100);
postgres=# \dt+ test
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------+-------+----------+--------+-------------
public | test | table | postgres | 977 GB |
(1 row)
4、寫入阿裏雲HybridDB for PostgreSQL
-- Greenplum
vi test.sql
\timing
insert into test select random()*100 from generate_series(1,320000000);
nohup psql -f ./test.sql >/tmp/log_gpdb 2>&1 &
GPDB 列存，壓縮後458GB。
postgres=# select pg_size_pretty(pg_total_relation_size('test'));
pg_size_pretty
----------------
458 GB
(1 row)
6、也可以從PG導出到HDB，例子
date
psql -c "copy test to stdout"|psql -U dege.zzz -p 15432 -d postgres -c "copy test from stdin"
date
7、大多數時候，生產環境使用的是 從OSS導入HDB或RDS PG的方法。
手冊如下
https://help.aliyun.com/document_detail/44461.html
https://help.aliyun.com/document_detail/35457.html
透視測試(阿裏雲HybridDB for PostgreSQL)
1、符合條件的設備、會員計數
1.1 HybridDB for PostgreSQL (列存儲) 硬算
等值查詢：
postgres=# select count(*) from test where c2=1;
count
-------
32233
(1 row)
Time: 581.304 ms
數組（多值類型）相交（包含任意）查詢：
postgres=# select count(*) from test where c41 && array [1,2,1000];
count
----------
40227896
(1 row)
Time: 10227.078 ms
等值 以及 數組（多值類型）相交（包含任意）查詢：
postgres=# select count(*) from test where c40=1 and c41 && array [1,2,1000];
count
--------
401802
(1 row)
Time: 6011.031 ms
等值 或 數組（多值類型）相交（包含任意）查詢：
postgres=# select count(*) from test where c40=1 or c41 && array [1,2,1000];
count
----------
43025528
(1 row)
Time: 9945.750 ms
數組（多值類型）包含（包含所有）查詢：
postgres=# select count(*) from test where c41 @> array [1,2,1000];
count
-------
123
(1 row)
Time: 10934.176 ms
1.2 采用統計信息進行估算
《論count與offset使用不當的罪名 和 分頁的優化》
1.3 采用估值類型hll進行估算
《Greenplum 最佳實踐 - 估值插件hll的使用(以及hll分式聚合函數優化)》
2、符合條件的數據多值列、普通列透視（TOP N）
2.1 透視多值列：
postgres=# select unnest(c41) c41, count(*) from test where c2=1 group by 1 order by 2 desc limit 100;
c41 | count
-----+-------
72 | 2276
132 | 2255
65 | 2250
130 | 2244
84 | 2241
......
41 | 2137
31 | 2137
137 | 2135
(100 rows)
Time: 8058.408 ms
2.2 透視標量列：
postgres=# select c40, count(*) from test where c2=1 group by 1 order by 2 desc limit 100;
c40 | count
-----+-------
40 | 363
5 | 358
2 | 356
93 | 355
67 | 353
18 | 351
99 | 350
......
86 | 288
71 | 287
84 | 277
0 | 160
(100 rows)
Time: 1328.181 ms
圈選測試(阿裏雲HybridDB for PostgreSQL)
3、圈選符合條件的設備ID、用戶ID。
采用遊標圈選目標人群，每一頁都絲般柔滑。
postgres=# begin;
BEGIN
Time: 0.764 ms
postgres=# declare cur1 cursor for select c1 from test where c2=1;
DECLARE CURSOR
Time: 215.695 ms
postgres=# fetch 10 from cur1;
c1
----
44
50
86
48
27
71
10
83
24
96
(10 rows)
Time: 0.288 ms
postgres=# fetch 10 from cur1;
c1
----
39
16
24
90
25
1
41
33
76
0
(10 rows)
Time: 0.087 ms
圈選3萬人群：
postgres=# explain analyze select c1 from test where c2=1 ;
QUERY PLAN
-----------------------------------------------------------------------------------------
Gather Motion 48:1 (slice1; segments: 48) (cost=0.00..35186640.00 rows=31953 width=4)
Rows out: 32233 rows at destination with 877 ms to end, start offset by 1.299 ms.
-> Append-only Columnar Scan on test (cost=0.00..35186640.00 rows=666 width=4)
Filter: c2 = 1
Rows out: 0 rows (seg0) with 10 ms to end, start offset by 62 ms.
Slice statistics:
(slice0) Executor memory: 347K bytes.
(slice1) Executor memory: 478K bytes avg x 48 workers, 494K bytes max (seg2).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 878.970 ms
(13 rows)
Time: 880.017 ms
圈選5800萬人群：
postgres=# explain analyze select c1 from test where c41 && array[1,2,100];
QUERY PLAN
-------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice1; segments: 48) (cost=0.00..35186640.00 rows=1600000 width=4)
Rows out: 58538502 rows at destination with 23842 ms to end, start offset by 1.282 ms.
-> Append-only Columnar Scan on test (cost=0.00..35186640.00 rows=33334 width=4)
Filter: c41 && '{1,2,100}'::integer[]
Rows out: 0 rows (seg0) with 7.488 ms to end, start offset by 35 ms.
Slice statistics:
(slice0) Executor memory: 347K bytes.
(slice1) Executor memory: 494K bytes avg x 48 workers, 494K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 23843.827 ms
(13 rows)
Time: 23845.061 ms
圈選6萬人群：
postgres=# explain analyze select c1 from test where c41 @> array[1,2,100];
QUERY PLAN
------------------------------------------------------------------------------------------
Gather Motion 48:1 (slice1; segments: 48) (cost=0.00..35186640.00 rows=320000 width=4)
Rows out: 63866 rows at destination with 8920 ms to end, start offset by 1.375 ms.
-> Append-only Columnar Scan on test (cost=0.00..35186640.00 rows=6667 width=4)
Filter: c41 @> '{1,2,100}'::integer[]
Rows out: 0 rows (seg0) with 28 ms to end, start offset by 13 ms.
Slice statistics:
(slice0) Executor memory: 347K bytes.
(slice1) Executor memory: 490K bytes avg x 48 workers, 494K bytes max (seg0).
Statement statistics:
Memory used: 128000K bytes
Settings: optimizer=off
Optimizer status: legacy query optimizer
Total runtime: 8921.803 ms
(13 rows)
Time: 8922.994 ms
鍵值查詢測試(阿裏雲RDS for PostgreSQL)
4、根據設備ID、用戶ID 點查
(建立ID索引，本例使用BLOCK NUM進行測試，實際效果一樣)
postgres=# select c1 from test where ctid='(1,1)';
c1
----
49
(1 row)
Time: 0.408 ms
壓測準備
postgres=# show block_size;
block_size
------------
32768
(1 row)
postgres=# analyze test;
ANALYZE
postgres=# select relpages from pg_class where relname='test';
relpages
----------
32000002
(1 row)
postgres=# select c1 from test where ctid='(1,10)';
c1
----
28
(1 row)
postgres=# select c1 from test where ctid='(1,11)';
c1
----
(0 rows)
壓測
vi test.sql
\set x random(1,32000002)
\set y random(1,10)
select * from test where ctid=('('||:x||','||:y||')')::tid;
單次響應1.1毫秒
transaction type: ./test.sql
scaling factor: 1
query mode: extended
number of clients: 64
number of threads: 64
duration: 120 s
number of transactions actually processed: 6762499
latency average = 1.136 ms
latency stddev = 6.042 ms
tps = 56349.372585 (including connections establishing)
tps = 56353.497075 (excluding connections establishing)
script statistics:
- statement latencies in milliseconds:
0.001 \set x random(1,32000002)
0.000 \set y random(1,10)
1.135 select * from test where ctid=('('||:x||','||:y||')')::tid;
由於數據977GB，超過了內存大小，如果全網數據都是熱數據，磁盤讀取速率達到11GB/s。
1 1 98 0 0 0| 90M 33M| 0 0 | 0 0 |9775 18k
36 21 8 34 0 1| 11G 0 | 239B 446B| 0 0 | 426k 369k
37 21 8 33 0 1| 11G 4096B| 120B 334B| 0 0 | 430k 374k
37 22 7 32 0 2| 11G 660k| 239B 727B| 0 0 | 433k 383k
26 44 5 23 0 1|8313M 0 | 120B 753B| 0 0 | 307k 260k
35 28 7 29 0 1| 11G 172k| 477B 1183B| 0 0 | 390k 328k
36 17 9 37 0 2| 11G 0 | 344B 2385B| 0 0 | 441k 381k
33 26 8 32 0 1| 10G 0 |1449B 1093B| 0 0 | 396k 333k
31 34 7 26 0 1|9585M 0 | 120B 588B| 0 0 | 347k 303k
性能指標
環境規格
HybridDB for PostgreSQL規格：48C SSD。
RDS PostgreSQL規格：60核。
性能數據
1、符合條件的設備、會員計數
1.1 等值查詢：
0.5 秒
1.2 數組（多值類型）相交（包含任意）查詢：
10 秒
1.3 等值 以及 數組（多值類型）相交（包含任意）查詢：
6 秒
1.4 等值 或 數組（多值類型）相交（包含任意）查詢：
10 秒
1.5 數組（多值類型）包含（包含所有）查詢：
10 秒
2、符合條件的數據多值列、普通列透視（TOP N）
2.1 透視多值列：
8 秒
2.2 透視標量列：
1.3 秒
3、圈選符合條件的設備ID、用戶ID。
采用遊標，每一頁效率一致
3.1 標量條件，圈選3萬人群：
總耗時 0.9 秒
3.2 多值條件，圈選5850萬人群：
總耗時 24 秒
3.3 多值條件，圈選6萬人群：
總耗時 9 秒
4、根據設備ID、用戶ID 點查
1.1 毫秒
雲端產品
小結
視頻網站，已經滲透到各種終端，除了喜聞樂見的手機，還包括移動終端、電腦、盒子、電視、投影儀等。有設備屬性、會員屬性、渠道屬性等。
內容運營是非常重要的環節，而透視則是運營的重要武器。
通過阿裏雲RDS PostgreSQL, HybridDB for PostgreSQL，可以高效的滿足（時間、空間(GIS)、多值列、單值列）的多維數據透視、人群圈選、點查等需求。
最後更新：2017-08-28 10:32:40