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

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

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

3、新增標(biāo)簽修改全量記錄僅插入新標(biāo)簽記錄計(jì)算方式組合條件過濾“人群”集合的交并差運(yùn)算兩種處理方式的對比6如何存儲“人群”集合？Bitmap適合大集合的交并差運(yùn)算roaringbitmap是一種已被業(yè)界廣泛使用的高效的bitmap壓縮算法，使 用者包括Elasticsearch，Durid，Hive，Spack，InfluxDB等，詳見 /注： Elasticsearch在查詢時內(nèi)部用roaringbitmap臨時存儲不同倒排索引的結(jié)果集做交并運(yùn)算。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,每個值對應(yīng)一個特定的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、間更小時實(shí)施轉(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容器對應(yīng)的bit位設(shè)置為1，其它為0）4N4N（可選，N4時沒有）Array容器:short數(shù)組BitSet容器:8KB的bitsetRun容器: 2+ 4*run個數(shù)n_runvalue1length1value2length210參考：/RoaringBitmap/RoaringFormatSpec/目錄海量用戶下精準(zhǔn)營銷的挑戰(zhàn)roaringbitmap在圈人場景中的作用PostgreSQL + roaringbitmap的最佳實(shí)踐如何用分布式PG支撐百億標(biāo)簽實(shí)時查詢11pg_roaringbitmap插件基于gpdb-roaringbitmap并進(jìn)行了功能增強(qiáng)(

7、操作符，函數(shù)，并行聚合)支持PostgreSQL 10，1112pg_roaringbitmap安裝從github下載pg_roaringbitmap編譯并安裝插件登錄到目標(biāo)數(shù)據(jù)庫安裝擴(kuò)展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é))并行度計(jì)算速度(次/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中的隨機(jī)數(shù)，實(shí)際基數(shù)在測試數(shù)據(jù)去重后小于該數(shù)注2）實(shí)際基數(shù)在測試數(shù)據(jù)去重后大概是500w，實(shí)際每整數(shù)占用大小應(yīng)該大約是0.26注3）測試環(huán)境:16C/128G/3000G SSD物理機(jī)+ CentOS 7.3 + PostgreSQL 10.2 + pg_roaringbitmap 0.315超大基數(shù)bitmap的存取如果使用unnest(rb_to_array() 獲取大結(jié)果集，不僅速度慢，而且受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二進(jìn)制格式的存取采用基于Roaringbitmap二進(jìn)制格式的傳輸，不僅降低資源消耗，性能也有幾十倍 以上的提升。示例如下: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í)行計(jì)劃：WK上初次rb_or_agg聚合CN上最終rb_or_agg聚合Citus多CN架構(gòu)(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ù)設(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架構(gòu)在CN節(jié)點(diǎn)的postgresql.conf中添加下面的參數(shù)citus.replication_model=streaming從CN復(fù)制元數(shù)據(jù)到所有worker節(jié)點(diǎn)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. 初次聚合并將結(jié)果寫入到中間表（在所有分片上并發(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ù)