（陳華軍）蘇寧大規模標簽場景應用實踐（28頁）.pdf

1、MongoDB+PostgreSQL中文社區南京大會蘇寧大規模標簽場景應用實踐陳華軍微信：chenhj_07蘇寧易購 數據庫研發中心1目錄海量用戶下精準營銷的挑戰roaringbitmap在圈人場景中的作用PostgreSQL+roaringbitmap最佳實踐如何用分布式PG支撐百億標簽實時查詢2如何快速找到目標營銷人群(圈人)？精準基于準確的用戶畫像實時實時查詢滿足條件的目標人群靈活多變的查詢條件組合用戶畫像的實時更新可擴展支撐數億甚至數十億的用戶規模支撐百萬，億，甚至百億/千億規模的標簽3常規技術方案的短板方案問題Hive由于每次查詢都需要對億級的用戶表做全量掃描，資源消耗極大，響應時間

2、很長Spark+ElasticSearch利用ES的索引技術結合并行處理，查詢性能比Hive有幾十倍的提升；業務上經常需要新增標簽(字段)，導致必須重新灌全量ES數據，非常耗時。曾經用過的方案4參考：https:/ 搜索“人”到搜索“標簽(人群)”用戶ID性別城市1男南京2女北京標簽類型標簽值用戶ID集合性別男1,3,5,6,.性別女2,4,7,8城市南京1,4,17,城市北京2,17,98搜”人”搜”標簽”記錄數十億級百萬級索引數幾十，上百一個新增標簽修改全量記錄僅插入新標簽記錄計算方式組合條件過濾“人群”集合的交并差運算兩種處理方式的對比6如何存儲“人群”集合？-Bitmap適合大集合的交

3、并差運算-roaringbitmap是一種已被業界廣泛使用的高效的bitmap壓縮算法，使用者包括Elasticsearch，Durid，Hive，Spack，InfluxDB等，詳見http:/roaringbitmap.org/注：Elasticsearch在查詢時內部用roaringbitmap臨時存儲不同倒排索引的結果集做交并運算。7Roaringbitmap的存儲格式Roaringbitmap 將32位的整形拆分成高16位和低16位，高16位作為容器的key，低16位作為value存儲在3種不同的容器中。每個容器最多存儲65536個值，最多有65536個容器。每種容器存儲方法不同適用

4、于不同場景容器類型存儲形式容器大小容量容器轉換Array有序的short數組基數*2Byte4096基數超過4096時自動轉換為Bitset容器Bitset8KB大小的bitset,每個值對應一個特定的bit位8KB65535基數低于4096時自動轉換為Array容器runRLE(Run Length Encoding行程長度編碼)格式，由成對的short型value+length組成。比如10,11,12,13壓縮為10,34B128KB65535調用run優化且run格式存儲占用空間更小時實施轉換8不包含RUN容器時的序列化格式cookie(固定為12346)sizekey1card1-1

5、offset1container1container2444N4NArray容器:short數組BitSet容器:8KB的bitset每個值平均占用存儲空間估算:基數/范圍=1/13：使用Bitset容器，每個數值占用范圍/基數bit最糟糕的情況下，每個值都分布在不同的容器中，平均1個值占用10字節。9包含RUN容器時的序列化格式cookie(固定為12347)size-1bitmapOfRunkey1card1-1offset1container1container24(N+7)/8（所有run容器對應的bit位設置為1，其它為0）4N4N（可選，N4時沒有）Array容器:short數組R

6、un容器:2+4*run個數BitSet容器:8KB的bitsetn_runvalue1length1value2length210參考：https:/ 10，1112pg_roaringbitmap安裝從github下載pg_roaringbitmap編譯并安裝插件登錄到目標數據庫安裝擴展su postgresmakesudo make install13create extension roaringbitmappg_roaringbitmap使用示例(部分功能)Bitmap Calculation(OR,AND,XOR,ANDNOT)Bitmap Aggregate(OR,AND,XOR

7、,BUILD)CardinalitySELECT roaringbitmap(1,2,3)|roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)&roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)#roaringbitmap(3,4,5);SELECT roaringbitmap(1,2,3)-roaringbitmap(3,4,5);SELECT rb_or_agg(bitmap)FROM t1;SELECT rb_and_agg(bitmap)FROM t1;SELECT rb_xor_agg(bit

8、map)FROM t1;SELECT rb_build_agg(e)FROM generate_series(1,100)e;SELECT rb_cardinality(rb_build(1,2,3);14pg_roaringbitmap性能參考測試SQL：select rb_cardinality(rb_or_agg(bitmap)from bitmaptb數據量bitmap基數（注1）整形范圍表大小(字節)查詢時間(ms)平均記錄大小(字節)平均每整形占用空間(字節)并行度計算速度(次/core/秒)1000000011w5217894401450.932525223446061 1000

9、0000110w5217894401442.005525223467394 1000000011000w5217894401439.639525223473093 10000000110億5217894401463.23525223417098 3417098 10000000101w6826721281407.813686.823551608 100000001010w7656079361868.86777.722675428 10000000101000w13429555204377.3413413.421142246 100000001010億14124154884696.1781411

10、4.121064696 1064696 1000000010001w2048000000010334.64120482.02483810 10000000100010w2827771084821462.61128282.82232963 1000000010001000w41931038720106924.88441934.2246762 10000000100010億1.04045E+11531798.6951040410.429402 9402 100010000001000w1302405120734.80813024051.311361 1000100000010億2200985600

11、6086.90722009862.21164 164 1000100000001000w(注2)1302405120714.31613024050.1311400 10001000000010億2076681011241784.536207668102.112424注1）該基數值只是插入到bitmap中的隨機數，實際基數在測試數據去重后小于該數注2）實際基數在測試數據去重后大概是500w，實際每整數占用大小應該大約是0.26注3）測試環境:16C/128G/3000G SSD物理機+CentOS 7.3+PostgreSQL10.2+pg_roaringbitmap0.315超大基數bitma

12、p的存取如果使用unnest(rb_to_array()獲取大結果集，不僅速度慢，而且受array類型最大1GB的限制，在bit位超過7000萬時發生錯誤。postgres=#select count(*)from unnest(rb_to_array(rb_fill(1,10,100,1,70000000);ERROR:invalid memory alloc request size 107374182416基于Roaringbitmap二進制格式的存取采用基于Roaringbitmap二進制格式的傳輸，不僅降低資源消耗，性能也有幾十倍以上的提升。示例如下:create table tes

13、ttb(id int,bitmap roaringbitmap);String sql=select bitmap:bytea from testtb where id=?;PreparedStatement stmt=conn.prepareStatement(sql);stmt.setInt(1,1);ResultSet rs=stmt.executeQuery();while(rs.next()RoaringBitmap rb=new RoaringBitmap();DataInputStream is=new DataInputStream(rs.getBinaryStream(1);

14、rb.deserialize(is);is.close();rs.close();stmt.close();表定義:讀取bitmap數據：17String sql=INSERT INTO testtb(id,bitmap)VALUES(?,?:bytea:roaringbitmap);PreparedStatementstmt=conn.prepareStatement(sql);RoaringBitmaprb=RoaringBitmap.bitmapOf();for(int i=0;i Distributed Subplan 3_1-HashAggregate(cost=0.00.0.00

15、rows=0 width=0)Group Key:remote_scan.brand_cd-Custom Scan(Citus Real-Time)(cost=0.00.0.00 rows=0 width=0)Task Count:32Tasks Shown:One of 32-TaskNode:host=10.47.147.130 port=6432 dbname=app_db-GroupAggregate(cost=17.65.17.72 rows=3 width=96)Group Key:brand_cd-Sort (cost=17.65.17.66 rows=3 width=96)So

16、rt Key:brand_cd-Seq Scan on member_order_102033 member_order(cost=0.00.17.62 rows=3 width=96)Filter:(statis_date=2019-05-20:date)Task Count:1Tasks Shown:All-TaskNode:host=10.47.147.130 port=6432 dbname=app_db-Limit (cost=53.22.53.47 rows=100 width=48)-Sort (cost=53.22.55.72 rows=1000 width=48)Sort K

17、ey:(rb_andnot_cardinality(intermediate_result.bitmap_cur,intermediate_result.bitmap_sum)DESC-Function Scan on read_intermediate_resultintermediate_result(cost=0.00.15.00 rows=1000 width=48)SQL：執行計劃：WK上初次rb_or_agg聚合CN上最終rb_or_agg聚合Citus多CN架構(Citus MX)24PostgreSQLAppWorkerPostgreSQLCoordinatormetadata

18、tb1tb1_1PostgreSQLWorkertb1_2PostgreSQLWorker(MX node)metadatatb1tb1_1PostgreSQLWorker(MX node)metadatatb1tb1_2僅支持DMLPostgreSQLCoordinatormetadatatb1App普通Citus集群Citus MX集群支持DDL&DML優化：把最終聚合分散到所有WK上2.創建中間表25set citus.shard_count=8;-建議中間表分片數設置為等于worker數create unlogged table tb_dispatch(brand_cd text,-其

19、他維度(略)bitmap_cur roaringbitmap,bitmap_sum roaringbitmap);elect create_distributed_table(tb_dispatch,brand_cd,colocate_with=none);在CN節點的postgresql.conf中添加下面的參數1.配置Citus多CN架構citus.replication_model=streaming從CN復制元數據到所有worker節點SELECT start_metadata_sync_to_node($cituswk1_ip,$cituswk1_port);SELECT start

20、_metadata_sync_to_node($cituswk2_ip,$cituswk2_port);SELECT start_metadata_sync_to_node($cituswk3_ip,$cituswk3_port);選擇其中一個分組維度作為分片字段3.初次聚合并將結果寫入到中間表（在所有分片上并發執行）with tmp as(SELECT brand_cd,rb_and_cardinality(rb_or_agg(bitmap_cur),rb_or_agg(bitmap_sum)AS oldusercount,-老買家數rb_andnot_cardinality(rb_or_a

21、gg(bitmap_cur),rb_or_agg(bitmap_sum)AS newusercount-新買家數FROM tb_dispatchGROUP BY brand_cd)select*from tmp order by newusercount desc limit 100;26truncate tb_dispatch;select run_command_on_shards(member_order:regclass,$insert into tb_dispatchSELECT brand_cd,rb_or_agg(bitmap_cur)AS bitmap_cur,rb_or_ag

22、g(bitmap_sum)AS bitmap_sumFROM%sWHERE statis_date=20190520GROUP BY brand_cd$);4.從中間表收集數據進行最終聚合 該SQL將并行在每個分片上產生一個SELECT和N(中間表分片數)個INSERT。如果原始表有32個分片,中間表有8個分片，將在WK上同時產生32+32*8=288個連接。因此該SQL不適宜過多并發執行。本例中中間結果6000w1.在WK上對中間表進行初次聚合（本例中初次聚合結果集100w）2.在CN上收集WK上初次聚合的結果進行最終聚合注:中間表的分片字段包含在最終查詢的分組字段中，不同分片上初次聚合的結果不存在重復。優化后，性能提升10倍，執行時間壓縮到10秒以下參考https:/