釋放你的儲存空間！移除那些已經沒使用的 index

by ZHIH

Slide 1

Slide 1 text

釋放你的儲存空間！移除那些已經沒使用的 index ZHIH, Funliday

Slide 2

Slide 2 text

ZHIH • 王重智 ZHIH • Funliday • JavaScript / 爬蟲 • 剛入行半年的菜鳥

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

移除未使用的 index • pg_stat_all_indexes 找出候選人已存在的 index 做空間優化 • index bloat • index vacuum • index reindex • 避免在經常更新的 column 設置 index • 錯誤設置 index outline

Slide 5

Slide 5 text

移除未使用的 index

Slide 6

Slide 6 text

為何要移除 unused index？

Slide 7

Slide 7 text

• 預算有限下能使用的儲存空間有限，增加備份時間 • delete / insert / update 造成空間碎片化，進而造成效率降低 • 隨著 feature 演進原先的特地設置的 index 不再有效為何要移除 unused index？

Slide 8

Slide 8 text

如何查看 index 的使用情形？

Slide 9

Slide 9 text

pg_stat_all_indexes

Slide 10

Slide 10 text

• The pg_stat_all_indexes view will contain one row for each index in the current database, showing statistics about accesses to that specific index. The pg_stat_user_indexes and pg_stat_sys_indexes views contain the same information, but filtered to only show user and system indexes respectively. pg_stat_all_indexes

Slide 11

Slide 11 text

pg_stat_all_indexes ● relid：使用這個 index 的 table uid ● indexrelid：index uid ● schemaname：schema name ● relname：table name ● indexrelname：index name ● idx_scan：index scan 的次數 ● idx_tup_read：通過掃描 index 讀取 index 的數目 ● idx_tup_fetch：通過掃描 index 讀取 index 的 fetch 整個 row (live) 的數目圖 pg_stat_all_indexes columns

Slide 12

Slide 12 text

圖未使用的 index 結果圖使用 pg_stat_all_indexes 查詢未使用的 index SET idx_scan & idx_tup_read & idx_tup_fetch = 0 pg_stat_all_indexes

Slide 13

Slide 13 text

你看到的零並不是零 • optimizer 會讀取 index 作統計資料以利後續的優化，讀取 index 的動作並不會計到 pg_stats_all_indexes 裡

Slide 14

Slide 14 text

刪除候選的 index 前 • 確認該 index 是否是真的沒用？ ● 如：可能是 query 語法導致 index 沒確實使用到 • 這個 index 是否值得去刪除 ● 如：佔據空間不大

Slide 15

Slide 15 text

刪除 index 之後 •確認刪除的 index 是否對效能有不好的影響 •定期 reset pg_stat 監看使用 index 狀態

Slide 16

Slide 16 text

刪除 index 之後圖重置統計 table 的 SQL 語句

Slide 17

Slide 17 text

圖重置統計前圖重置統計後刪除 index 之後

Slide 18

Slide 18 text

已存在的 index 做空間優化

Slide 19

Slide 19 text

table 與 index 架構 •table 與 index 分開存放 •table data 存放在 page or block，每一個 page 為 8kb，裡面存放很多 tuple，每一個 tuple 就代表一個 row 的資料，並給予一個 id 為 TID，其 format 為 (block number, tuple index within the block)，代表真正的所在位置 − (22,8) 資料位於 block 22 的第 8 個 element •index 內存放著 TID 因此可以快速的找到需要的資料

Slide 20

Slide 20 text

圖 Table full data 圖透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置 s建立 table 與 index 架構

Slide 21

Slide 21 text

圖 Table full data s建立 table 與 index 架構圖透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置

Slide 22

Slide 22 text

s建立圖根據 ctid query 的結果圖透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置 table 與 index 架構圖 Table full data

Slide 23

Slide 23 text

PostgreSQL 對 insert / delete / update 處理 ● insert：當原先的 page 儲存空間已滿的時候，勢必要新增一個新的 page 來容納，此舉會讓 B-tree 高度增加，後續在 query 時花更多時間 ● delete：將原先位置的資料刪除而產生空間，造成了空間上的不連續 ● update：並不會找到指定的資料直接進行修改，實際上接近於先 delete 再 insert，會先標註原本 data 為 dead 再進行 insert 導致的碎片化，產生許多沒用的空間

Slide 24

Slide 24 text

bloat 是什麼 • update table 中的內容時，將 tuple 標記為 dead，然後在下一個可用空間中新 tuple，這個過程就叫 "bloat"，會導致產生不必要的空間，因此必須清除 bloat

Slide 25

Slide 25 text

如何查出 bloat 情況？ pgstattuple extesion 查詢 SQL 語句如以下圖查詢 bloat 結果 table：表 table_x_idx：該 table 的 index name

Slide 26

Slide 26 text

如何解決 index bloat？ method vacuum vacuum full reindex reindex concurrently read lock 會發生 write lock 會發生會發生 extra 回收不再使用的儲存空間，提供重複使用並不會把儲存空間還給 OS 清除 bloat 並 reclaim 整個 disk space，把儲存空間還給 OS 重建索引，替換舊的版本允許重建 index 時繼續正常操作，對於 production 很有幫助，缺點就是會重建的比較久，增加 CPU、記憶體、IO 負擔導致速度變慢

Slide 27

Slide 27 text

Slide 28

Slide 28 text

如何解決 index bloat？- vacuum 圖執行 vacuum 前圖執行 vacuum 後

Slide 29

Slide 29 text

Slide 30

Slide 30 text

圖執行 vacuum full 前圖執行 vacuum full 後如何解決 index bloat？- vacuum full

Slide 31

Slide 31 text

Slide 32

Slide 32 text

如何解決 index bloat？- reindex & reindex concurrently 圖執行 reindex (concurrently) 前圖執行 reindex (concurrently) 後 reindex reindex concurrently

Slide 33

Slide 33 text

依上述的解決方式 production：reindex concurrently 會比較好 development : 皆可

Slide 34

Slide 34 text

避免在經常更新的 column 設置 index

Slide 35

Slide 35 text

避免在經常更新的 column 設置 index - 一般 update ● tbl 有1000筆資料 ● UPDATE tbl SET data = 'B' WHERE id = 1000; 圖 update 示意圖, source: postgresql.org

Slide 36

Slide 36 text

避免在經常更新的 column 設置 index - 一般 update 導致在 index 和 table 都要新增 tuple，增加 I/O loading

Slide 37

Slide 37 text

避免在經常更新的 column 設置 index - HOT update HOT is an acronym for "Heap Only Tuple" 定義： • Index 並不會指向原先以外的 block，新舊版本都會存在同一個 block

Slide 38

Slide 38 text

● UPDATE tbl SET data = 'B' WHERE id = 1000; 圖 update 示意圖, source: postgresql.org Index 內 TID 沒有改變避免在經常更新的 column 設置 index - HOT update

Slide 39

Slide 39 text

HOT is an acronym for "Heap Only Tuple" 定義： • Index 並不會指向原先以外的 block，新舊版本都會存在同一個 block 好處： • 可以即使有更新資料，index 可以保持不變，透過 HOT link 找到正確 version • 把中間 dead tuple 重新整理，不需要使用 vacuum 避免在經常更新的 column 設置 index - HOT update

Slide 40

Slide 40 text

● 清理 dead tuple ，而不需要額外的 vacuum 圖 update 示意圖, source: postgresql.org Index 內TID 沒有改變避免在經常更新的 column 設置 index - HOT update

Slide 41

Slide 41 text

HOT is an acronym for "Heap Only Tuple" 定義： • Index 並不會指向原先以外的 block，新舊版本都會存在同一個 block 好處： • 可以即使有更新資料，index 可以保持不變，透過 HOT link 找到正確 version • 把中間 dead tuple 重新整理，不需要使用 vacuum 滿足條件: • page 或 block 足夠的空間容納新版本 data • 更新的 column value 並不屬於任何一個 index 避免在經常更新的 column 設置 index - HOT update

Slide 42

Slide 42 text

避免在經常更新的 column 設置 index • 如果更新 index value，index 會對應產生新的 tuple，同時在 table 也會對應新增 data，如此勢必會浪費很多空間

Slide 43

Slide 43 text

避免錯誤設置 index

Slide 44

Slide 44 text

對於某一個 column 其值只有少數幾個，或是實務上只需要用到特定幾個值，卻做了 full index，佔據空間避免錯誤設置 index Partial index 針對特定的值做 index 即可

Slide 45

Slide 45 text

避免錯誤設置 index 圖 Full index & partial index 結果

Slide 46

Slide 46 text

conclusion • 透過定期監看 pg_stat_all_indexes，確認沒用到的 index ，並移除 • 定時去處理 index bloat 問題，節省空間同時也能維護效率

Slide 47

Slide 47 text

thanks