在 GCP 上，對 OLTP 資料庫擴展分析型查詢 Ruian Huang @ Dcard Plaza / Golang Taipei Gathering #42. 2019-06-18 1 

⼤家好 * 畢業於交⼤資⼯ * Dcard 架構團隊的後端⼯程師 * ⽇常⼯作是開發內外部 API
 資料庫查詢優化
 Infrastructure as Code * 愛好是吃甜食 * 右邊是我哥的貓，被我當頭像 * https://github.com/rueian 2 

外部使⽤者 資料庫 ⼀般 Online Transaction Processing 情境 外部使⽤者僅透過公開 API ， 發起預先寫好的查詢存取有限的資料 公開 API 3 

外部使⽤者 資料庫 ⼀般 Online Transaction Processing 情境 外部使⽤者僅透過公開 API ， 發起預先寫好的查詢存取有限的資料 公開 API 資料庫則針對這情境做最佳化，包含：
 
 * 多連線的硬體資源分配
 * 資料庫對硬碟隨機存取的參數
 * 資料 Indexing
 * 資料 Partitioning
 * 資料 Clustering
 * 查詢優化 (Index Scan) 4 

外部使⽤者 資料庫 公開 API 內部員⼯ 來⾃內部員⼯的隨機分析型查詢怎辦呢？ 問題 5 

內部需求包含： * 排程任務 * 報表 * 送批量通知 * 模型訓練 * ETL 內部的分析型查詢 通常需要掃描⼤量資料，
 可能還會有⼤量計算會佔據⼤量硬體資源
 (Seq Scan, Postgres Bitmap Heap Scan) * ⼈⼯ Ad-hoc 查詢 * 成效統計、數據研究 * 開發功能、Debug 6 

外部使⽤者 資料庫 公開 API 我們以前的作法 7 

外部使⽤者 資料庫 公開 API 我們以前的作法 內部查詢的唯讀資料庫 Streaming WAL 為了避免內部查詢影響到外部使⽤者，
 我們建立⼀台內部專⽤的唯讀副本資料庫 8 

外部使⽤者 資料庫 公開 API 我們以前的作法 內部查詢的唯讀資料庫 Streaming WAL 內部⼯具 9 

Master 問題 Slave Streaming WAL 1. Slave 通常被要求硬體規格與 Master 同等，
 甚⾄可能⼀些資料庫參數會被要求跟 Master ⼀樣，
 沒辦法在參數上針對分析型查詢做優化。
 2. Slave 是唯讀，無法在資料上對分析型查詢做優化。 3. Slave 閒置時也不能關機，不然追同步很⿇煩。
 4. Slave 只有⼀台，分析型查詢全都擠在上⾯互相拖慢，
 卻很難快速⽣出第⼆台、第三台，且同步 WAL 對 Master 負擔也不⼩。 10 

Master 問題 Slave Streaming WAL 在查詢 PostgreSQL Slave 上還有⼀個問題： 除了在 Slave 不斷重試查詢之外沒有對 Master 不造成影響的解法
 然⽽不斷重試可能也只是不斷撞到⼀樣的情況
 
 
 
 https://www.postgresql.org/docs/current/hot-standby.html#HOT-STANDBY-CONFLICT 11 

願望 * 讓同事開⼼：
 * 查詢想怎麼下就怎麼下
 * 想查什麼資料庫就查什麼庫
 * 資源隔離
 不⽤怕影響別⼈。 * 讓老闆開⼼：盡量節省成本。 12 

快照 ⼤部分虛擬化平台或是雲端廠商 都有硬碟增量快照功能可以使⽤ 13 

現在的做法 資料庫 定期打快照 14 

現在的做法 資料庫 定期打快照 內部需求 15 

現在的做法 資料庫 db proxy 定期打快照 內部需求 16 

現在的做法 資料庫 db proxy resource management api 定期打快照 內部需求 17 

現在的做法 資料庫 db proxy resource management api 內部⼯具 定期打快照 內部需求 18 

現在的做法 資料庫 db proxy resource management api 內部⼯具 請求資料庫 定期打快照 內部需求 19 

現在的做法 資料庫 db proxy resource management api 找最新的快照 內部⼯具 請求資料庫 定期打快照 內部需求 20 

現在的做法 資料庫 db proxy resource management api 先佔資料庫 ⽤快照創新的先佔資料庫 內部⼯具 請求資料庫 定期打快照 內部需求 21 

現在的做法 資料庫 db proxy resource management api 先佔資料庫 內部⼯具 執⾏查詢 Heartbeat 定期打快照 內部需求 22 

現在的做法 資料庫 db proxy resource management api 內部⼯具 執⾏查詢 Heartbeat 定期打快照 內部需求 先佔資料庫 23 

Resource Management API resource management api 外部需可以接受不同種類資源請求 24 

Resource Management API resource management api 外部需可以接受不同種類資源請求 25 

Resource Management API resource management api 內部則需可以管理不同種類的資源 26 

Resource Management API resource management api 內部則需可以管理不同種類的資源 -> ⽤ plugin ⽅式實作不同種類的 resource controller ⽤ plugin 的⽅式可以： 1. 獨立於主程式
 2. 不限定實作程式語⾔ 3. 各別 resource controller 可以 Hot Reload 或分開部署 27 

Resource Management API resource management api 各別 Resource Controller 需實做的介⾯： * FindResource 負責回應前⾯的 RequestResource 請求，
 從給定的 ResourcePool 之中挑出可⽤的 Resource 回傳，
 或是也可以回傳⼀個全新的 Resource。 * SyncResource 則負責檢查 Resource 狀態並回傳更新的 Resource。 會有額外的 Worker 會不斷對現有的 Resource 
 去呼叫對應 Controller 的 SyncResource 來更新狀態 28 

Resource Management API & Resource Controller Plugin resource management api RequestResource A resource A controller 29 

resource management api RequestResource A resource A controller FindResource A Resource Management API & Resource Controller Plugin 30 

resource management api RequestResource A resource A controller return a Resource Management API & Resource Controller Plugin 31 

resource management api return a resource A controller Resource Management API & Resource Controller Plugin 32 

resource management api resource A controller SyncResource a Resource Management API & Resource Controller Plugin 33 

resource management api resource A controller return a with new state Resource Management API & Resource Controller Plugin 34 

resource management api resource A controller SyncResource a Resource Management API & Resource Controller Plugin 35 

resource management api resource A controller return a with delete mark Resource Management API & Resource Controller Plugin 36 

實作 Resource Controller 37 

實作 Resource Controller 38 

Startup-Script ⼤部分虛擬化平台或雲端廠商都 有提供 startup-script 讓你在 VM 開機之後可以執⾏創建 VM 時注 入的腳本。 startup-script 很適合⽤在根據不 同硬體規格來⾃動調整相關參數 然後再啟動服務。 39 

⼤部分虛擬化平台或雲端廠商都 有提供 startup-script 讓你在 VM 開機之後可以執⾏創建 VM 時注 入的腳本。 startup-script 很適合⽤在根據不 同硬體規格來⾃動調整相關參數 然後再啟動服務。 Startup-Script PostgreSQL 為例： Linux Kernel: /proc/sys/vm/nr_hugepages PostgreSQL config: max_connections shared_buffers effective_cache_size maintenance_work_mem work_mem max_worker_processes max_parallel_workers_per_gather random_page_cost effective_io_concurrency PostgreSQL Data: create index on table cluster table using index analyze database 40 

db proxy db proxy 41 

db proxy db proxy 負責服務來⾃使⽤者的資料庫連線，隱藏背後處理過程。
 負責根據連線內容來跟 resource management api 索取資源。
 
 也負責對 resource management api 進⾏ heartbeat
 讓 resource controller 可以知道 resource 還有⼈在使⽤ 42 

db proxy db proxy StartupMessage username=ooo
 database=xxx resource management api RequestResource
 database=xxx 43 

db proxy db proxy StartupMessage username=ooo
 database=xxx resource management api return Resource
 addr=10.x.x.x:5432 44 

db proxy db proxy StartupMessage username=ooo
 database=xxx 先佔資料庫 StartupMessage username=ooo
 database=xxx addr=10.x.x.x:5432 45 

db proxy db proxy StartupMessage username=ooo
 database=xxx 先佔資料庫 AuthenticationOk addr=10.x.x.x:5432 46 

db proxy db proxy AuthenticationOk 先佔資料庫 AuthenticationOk addr=10.x.x.x:5432 47 

db proxy db proxy Query 先佔資料庫 Query addr=10.x.x.x:5432 48 

Library * ⽤來處理 Postgres Startup Message 的 PGResolver interface * ⽤來處理 Postgres Cancel Request 的 InMemoryConnInfoStore * ⽤來處理各種 Postgres Message 的 Message Handler ⾃⼰寫 proxy 可以拿來做很多事情： * Query logging, auditting, retry transaction if preemptible instance disappeared * Data logging, auditting, masking, obscuring * Separate authentication from database https://github.com/rueian/pgbroker
 ⽤來寫 postgres proxy 的 golang library，提供： 49 

Library * 對內的 Service 層以及對外的 HTTP transport 層實作，以及 golang 的 client library * 使⽤ redis 的 Resource Store 以及 Resource Controller Locker * golang 的 Resource Controller Plugin Skeleton Library * 類似 hashicorp/go-plugin 的 Plugin Launchpad，⽤來管理 subprocess 形式的 Resource Controller * Resource Syncer ⽤來不斷呼叫 Resource Controller 的 SyncResource * OpenCensus 的 metric view，可輸出⾄ Stackdriver 或轉成 Prometheus 格式 https://github.com/rueian/godemand
 ⽤來寫 resource management api 的 golang library，提供： 50 

範例實作 https://github.com/rueian/godemand-example
 節合 godemand 以及 pgbroker，並實作 pg resource controller plugin 的範例： * 可部屬於 GKE * 從 GCP Project 上⾯新的 snapshot 開 pg instance，⽤ startup-script 根據硬體調整參數 * resource controller ⽤ /proc/loadavg 檢查 vm 負載來決定 是否加開 pg 51 

總結 * 透過 db proxy 隱藏背後與 resource mangement api 的互動，讓使⽤者使⽤⼀般的 db client 與 proxy 連線就可以取⽤從最新的 snapshot 開啟的 db instance。 * 透過 db proxy 與 resource management api 的 Heartbeat 機制，resource controller 可以控制 ⼀個 db instance 要服務多久。不僅 db instance 可以使⽤ Preemptible 機器，閒置時可以直接 關機，甚⾄直接刪除以降低成本，當新的連線進來再重新開機或從 snapshot 開新的機器即可。 * resource controller 可以檢查當前 db instance 負載決定是否加開機器，並且可以再開機器時決 定硬體規格與 startup-script 內容，達到按需求動態調整花費。 * 從 snapshot 開的 db instance 不再是原 db 的 hot standby replica，因此可以根據之後的查詢 需求做參數與資料上的調整，例如 postgres 可以降低 max_connection 並提⾼ work_mem，或 是 create index 或是 cluster table。 * 當沒有查詢時，不再需要像原本 hot standby replica ⼀直把 db instance 開著保持同步了。僅 需要開著 db proxy 與 resource management api 兩隻 process，相當省錢。 52 

下⼀步 * ⽀援 mongo db * 更進⼀步⾃動優化 cost & waiting time 53 

https://join.dcard.today/ 54