大規(guī)模分布式數(shù)據(jù)庫標簽場景應用

1、 揚帆起航蘇寧大規(guī)模分布式數(shù)據(jù)庫標簽場景應用目錄海量用戶下精準營銷的挑戰(zhàn)roaringbitmap在圈人場景中的作用PostgreSQL + roaringbitmap最佳實踐如何用分布式PG支撐百億標簽實時查詢2如何快速找到目標營銷人群(圈人)？精準基于準確的用戶畫像實時實時查詢滿足條件的目標人群靈活多變的查詢條件組合用戶畫像的實時更新可擴展支撐數(shù)億甚至數(shù)十億的用戶規(guī)模支撐百萬，億，甚至百億/千億規(guī)模的標簽3常規(guī)技術方案的短板方案問題Hive由于每次查詢都需要對億級的用戶表做全量掃描，資源 消耗極大，響應時間很長Spark+ElasticSearch利用ES的索引技術結合并行處理，查詢性能比

2、Hive有幾 十倍的提升；業(yè)務上經(jīng)常需要新增標簽(字段)，導致必須重新灌全量ES 數(shù)據(jù)，非常耗時。曾經(jīng)用過的方案4參考：https:/article/9*dSWwPT6UqSH1LSeNzq目錄海量用戶下精準營銷的挑戰(zhàn)roaringbitmap在圈人場景中的作用PostgreSQL + roaringbitmap的最佳實踐如何用分布式PG支撐百億標簽實時查詢5從搜索“人”到搜索“標簽(人群)”用戶ID性別城市1男南京2女北京標簽類型標簽值用戶ID集合性別男1,3,5,6,.性別女2,4,7,8城市南京1,4,17,城市北京2, 17,98搜”人”搜”標簽”記錄數(shù)十億級百萬級索引數(shù)幾十，上百一個

3、新增標簽修改全量記錄僅插入新標簽記錄計算方式組合條件過濾“人群”集合的交并差運算兩種處理方式的對比6如何存儲“人群”集合？Bitmap適合大集合的交并差運算roaringbitmap是一種已被業(yè)界廣泛使用的高效的bitmap壓縮算法，使 用者包括Elasticsearch，Durid，Hive，Spack，InfluxDB等，詳見 /注： Elasticsearch在查詢時內(nèi)部用roaringbitmap臨時存儲不同倒排索引的結果集做交并運算。7Roaringbitmap的存儲格式Roaringbitmap 將32位的整形拆分成高16位和低16位，高16位作為容器的key，低16位作為valu

4、e存儲在3種不同的 容器中。每個容器最多存儲65536個值，最多有65536個容器。每種容器存儲方法不同適用于不同場景容器類型存儲形式容器大小容量容器轉(zhuǎn)換Array有序的short數(shù)組基數(shù)*2Byte4096基數(shù)超過4096時自動轉(zhuǎn)換為Bitset容器Bitset8KB大小的bitset,每個值對應一個特定的bit位8KB65535基數(shù)低于4096時自動轉(zhuǎn)換為Array容器runRLE(Run Length Encoding行程長度編碼)格式，由成 對的short型value+length組成。比如10,11,12,13壓縮為10,34B128KB65535調(diào)用run優(yōu)化且run格式存儲占用空

5、間更小時實施轉(zhuǎn)換8不包含RUN容器時的序列化格式cookie(固定為12346)sizekey1card1-1offset1container1container2444N4NArray容器:short數(shù)組BitSet容器:8KB的bitset每個值平均占用存儲空間估算:基數(shù)/范圍= 1/13 ：使用Bitset容器，每個數(shù)值占用范圍/基數(shù) bit最糟糕的情況下，每個值都分布在不同的容器中，平均 1個值占用10字節(jié)。9包含RUN容器時的序列化格式cookie(固定為 12347)size - 1bitmapOfRunkey1card1-1offset1container1container24

6、(N+7)/8（所有run容器對應的bit位設置為1，其它為0）4N4N（可選，N4時沒有）Array容器:short數(shù)組BitSet容器:8KB的bitsetRun容器: 2+ 4*run個數(shù)n_runvalue1length1value2length210參考：/RoaringBitmap/RoaringFormatSpec/目錄海量用戶下精準營銷的挑戰(zhàn)roaringbitmap在圈人場景中的作用PostgreSQL + roaringbitmap的最佳實踐如何用分布式PG支撐百億標簽實時查詢11pg_roaringbitmap插件基于gpdb-roaringbitmap并進行了功能增強(

7、操作符，函數(shù)，并行聚合)支持PostgreSQL 10，1112pg_roaringbitmap安裝從github下載pg_roaringbitmap編譯并安裝插件登錄到目標數(shù)據(jù)庫安裝擴展su postgres makesudo make install13create extension roaringbitmappg_roaringbitmap使用示例(部分功能)Bitmap Calculation (OR, AND, XOR, ANDNOT)Bitmap Aggregate (OR, AND, XOR, BUILD)CardinalitySELECT roaringbitmap(1,2,

8、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(bitmap) FROM t1;SELECT rb_build_ag

9、g(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數(shù)據(jù)量bitmap基數(shù)（注1）整形范圍表大小(字節(jié))查詢時間(ms)平均記錄大小(字節(jié))平均每整形占 用空間(字節(jié))并行度計算速度(次/core/秒)1000000011w5217894401450.932525223446061 10000000110w5217894401442.0055

10、25223467394 1000000011000w5217894401439.639525223473093 10000000110億5217894401463.23525223417098 10000000101w6826721281407.813686.823551608 100000001010w7656079361868.86777.722675428 10000000101000w13429555204377.3413413.421142246 100000001010億14124154884696.17814114.121064696 1000000010001w20480000

11、00010334.64120482.02483810 10000000100010w2827771084821462.61128282.82232963 1000000010001000w41931038720106924.88441934.2246762 10000000100010億1.04045E+11531798.6951040410.429402 100010000001000w1302405120734.80813024051.311361 1000100000010億22009856006086.90722009862.21164 1000100000001000w(注2)130

12、2405120714.31613024050.1311400 10001000000010億2076681011241784.536207668102.1124注1）該基數(shù)值只是插入到bitmap中的隨機數(shù)，實際基數(shù)在測試數(shù)據(jù)去重后小于該數(shù)注2）實際基數(shù)在測試數(shù)據(jù)去重后大概是500w，實際每整數(shù)占用大小應該大約是0.26注3）測試環(huán)境:16C/128G/3000G SSD物理機+ CentOS 7.3 + PostgreSQL 10.2 + pg_roaringbitmap 0.315超大基數(shù)bitmap的存取如果使用unnest(rb_to_array() 獲取大結果集，不僅速度慢，而且受a

13、rray類型最大1GB的限制，在bit位超過7000萬時發(fā)生錯誤。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 testtb(id int, bitmap roaringbitmap);String

14、 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); rb.deserialize(is);is.

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

16、=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=30 port=6432 dbname=app_db- GroupAggregate (cost=2 rows=3 width=96) Group Key: brand_cd- Sort (cost=6 rows=3 width=96) S

17、ort 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: 1 Tasks Shown: All- TaskNode: host=30 port=6432 dbname=app_db- Limit (cost=7 rows=100 width=48)- Sort (cost=2 rows=1000 width=48)So

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

19、tb1PostgreSQLtb1_1WorkerPostgreSQLtb1_2PostgreSQLWorker(MX node)metadatatb1 tb1_1Worker(MX node)PostgreSQLmetadatatb1tb1_2僅支持DMLCoordinatorPostgreSQLmetadatatb1App普通Citus集群Citus MX集群支持DDL&DML優(yōu)化：把最終聚合分散到所有WK上252. 創(chuàng)建中間表set citus.shard_count =8; -建議中間表分片數(shù)設置為等于worker數(shù)create unlogged table tb_dispatch( b

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

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