Handling a High Performance PostgreSQL Database

Transcript

1. Handling a High Performance PostgreSQL Database
2. Hello! I am Rain! ex- A. SWE @iCHEF ex- SWE @NetDB 2 GitHub: RainrainWu LinkedIn: Rain Wu
3. Outline ⬡ 資料庫基本概念 ⬡ 如何構思解決方案 ⬡ 具體方案與適合情境 3
4. 1. 資料庫基本概念 在進入主題前，先來聊些必備知識
5. “ 資料庫是一種讓我們能穩定儲 存大量資料的軟體，以實現資料 與邏輯的解耦。 5
6. “ 其中又可依據是否對存入的資 料有嚴謹的 Schema 要求分為 RDBMS 和 NoSQL 資料庫，這 次所討論的 PostgreSQL 就是 一種 RDBMS。 6
7. 資料庫有哪些操作 ⬡ CRUD ∙ Create ∙ Read ∙ Update ∙ Delete 7
8. 為什麼 資料庫 會變慢 8
9. 有很多原因 ⬡ 讀取很多資料 ⬡ 搜尋特定資料效率低落 ⬡ 為確保一致性所以有交易隔離機制 ⬡ ... 9
10. 資料庫在道德上必須滿足 ACID ⬡ Atomic ⬡ Consistency ⬡ Isolation ⬡ Durability 10
11. 2. 如何構思解決方案
12. 搜集關鍵 資訊 針對可能的方法搜集資料，逐個 驗證是否適用於當前情境 12
13. 可能有用的資訊 ⬡ 資料表的統計資訊 ⬡ Query plan ⬡ Transaction 統計資訊 ⬡ Monitor 13
14. 可以思考的問題 ⬡ 同樣目標可不可以換一種做法 ⬡ 同樣事情可不可以只做一次 ⬡ 為了高效能有哪些東西可以降低優先級 ⬡ 這資料庫的底層實現是否適合當前情境 14
15. 決策流程飛輪 15 驗證方法適用性 依據資訊提出 候選方案 依據異常行為 搜集資訊
16. 3. 具體方案與適合情境
17. Tune your SQL command ⬡ 找出耗時不合理的指令 ⬡ 看看 query plan 做了哪些事 17
18. Cache ⬡ 同樣的資料不用每次都搜尋 ⬡ 存在快取中能減少資料庫的工作量 18
19. Denormalize ⬡ 基於正規化的原則資料庫不該有冗余 的欄位 ⬡ 但有些常用數值每次計算很耗資源， 同時變動較快也不適用 Cache ⬡ 比如 FB 貼文按讚數、TG 未讀訊息數 19
20. Scale-up ⬡ CPU, RAM 吃滿了？ ⬡ 那就加更多資源或換成更高級別的 代管服務吧 20
21. Scale-out (Clustering) ⬡ 多幾台機器分攤工作量 ⬡ 相關概念包含讀寫分離、HA ⬡ 不過為了強一致性通常只有一個負 責接收寫入的節點 ⬡ 較適用於 Read >> Write 情境 21
22. Write-ahead Log (WAL) PostgreSQL 的可靠性機制，在 執行 transaction 前須先像日誌 一樣寫入 Log 內永久儲存 22
23. 23 Primary WAL PG node Standby WAL PG node Standby WAL PG node pgpool-II Write Read Read Proxy Synchronization
24. 24 Primary WAL PG node Standby WAL PG node Standby WAL PG node pgpool-II Write Read Read Proxy Synchronization 10 W 100 R 10 W 50 R 50 R 10 W 10 W
25. 讀寫分離 主要是分攤讀的工作量，正好目 前多數服務都是 Read >> Write，但仍須考量資料同步的延 遲 25
26. Indexes ⬡ 透過維護額外的資料結構讓查找效 率更高 ⬡ 但變相的會犧牲寫入效率 ⬡ 比如二分搜索樹、倒排索引 26
27. PG 常用 Index GIN 倒排索引，儲存關鍵字與所在文 本之間的映射關係，適合做關鍵 字搜尋 27
28. 28 National Successful University Doc 1 National Failed University Doc 2
29. Forward Index ⬡ Doc1 : National, Successful, University ⬡ Doc2 : National, Failed, University 29
30. Inverted Index ⬡ National : Doc1, Doc2 ⬡ Successful : Doc1 ⬡ University : Doc1, Doc2 ⬡ Failed : Doc2 30
31. PG 常用 Index BRIN 統計資訊邊界分群，比如時間範 圍、地理位置的 Bounding Box， 適用於銷售日誌或區域查詢 31
32. Lower Isolation Level ⬡ 根據對交易隔離的要求，資料庫有 多個不同的隔離等級 ⬡ 隔離越是嚴謹效能損失就越大，可 以思考所需要的等級 32
33. Isolation 層級 ⬡ Read Uncommitted ⬡ Read Committed ⬡ Repeatable Read ⬡ Serilizable 33
35. Isolation 的主流實作策略 ⬡ Shared-exclusive Lock (SX Lock) ∙ MySQL, MS SQL ⬡ Multiversion Concurrency Control (MVCC) ∙ PostgreSQL, Oracle 35
36. SX Lock ⬡ 每筆資料可發行多把 S Lock 和一把 X Lock ⬡ 發行 S Lock 時不可有發行中的 X Lock，而發 行 X Lock 時不可有發行中的 S/X Lock ⬡ 只有 Read-Read 不會互相 block，Read-Write 和 Write-Write 都會 36
37. MVCC ⬡ 每筆紀錄更新或刪除時不直接改動當前資料， 而是新增一個新的版本或刪除標記 ⬡ 每次執行交易時，依據時間點控制該筆交易對 其他紀錄可見的版本 ⬡ 只有 Write-Write 會因為版本先後順序性而 被 block，而 Read-Write 不會 37
38. 常聽到有人批評 PostgreSQL 做 Clustering 成效不如 MySQL ? ⬡ 讀寫分離解決了 MySQL 中 SX Lock 機制的 Read-Write block 問題，而 PostgreSQL 中 MVCC 機制沒這問題 ⬡ 他的 Read 流量沒有大到需要讀寫分離 ⬡ 他配置有問題 38
39. 39 Primary WAL PG node Standby WAL PG node Standby WAL PG node pgpool-II Write Read Read Proxy Synchronization 10 W 100 R 10 W 50 R 50 R 10 W 10 W
40. OLTP v.s. OLAP ⬡ 兩者之間有個很關鍵的行為差異，就 是 range scan ⬡ 計算 real time 指標、產報表都需要 ⬡ 資料庫儲存資料的結構影響很大 40
41. Index-organized Table 41
42. Heap Table 42
43. 有沒有 Range Scan 差很多 ⬡ Heap Table 基本上要 Serial scan ⬡ 當你使用一個 OLTP server 做分析或報表，覺 得他做得很慢時，你可能需要另個 OLAP server 而不是開腦洞優化 OLTP server 43
44. The End