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

Transcript

1. 釋放你的儲存空間！移除那些已經沒 使用的 index ZHIH, Funliday
2. ZHIH • 王重智 ZHIH • Funliday • JavaScript / 爬蟲 • 剛入行半年的菜鳥
4. 移除未使用的 index • pg_stat_all_indexes 找出候選人 已存在的 index 做空間優化 • index bloat • index vacuum • index reindex • 避免在經常更新的 column 設置 index • 錯誤設置 index outline
5. 移除未使用的 index
6. 為何要移除 unused index？
7. • 預算有限下能使用的儲存空間有限，增加備份時間 • delete / insert / update 造成空間碎片化，進而造成效率降低 • 隨著 feature 演進原先的特地設置的 index 不再有效 為何要移除 unused index？
8. 如何查看 index 的使用情形？
9. pg_stat_all_indexes
10. • 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
11. 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
12. 圖 未使用的 index 結果 圖 使用 pg_stat_all_indexes 查 詢未使用的 index SET idx_scan & idx_tup_read & idx_tup_fetch = 0 pg_stat_all_indexes
13. 你看到的零並不是零 • optimizer 會讀取 index 作統計資料以利後續的優化，讀 取 index 的動作並不會計到 pg_stats_all_indexes 裡
14. 刪除候選的 index 前 • 確認該 index 是否是真的沒用？ • 如：可能是 query 語法導致 index 沒確實使用到 • 這個 index 是否值得去刪除 • 如：佔據空間不大
15. 刪除 index 之後 •確認刪除的 index 是否對效能有不好的影響 •定期 reset pg_stat 監看使用 index 狀態
16. 刪除 index 之後 圖 重置統計 table 的 SQL 語句
17. 圖 重置統計前 圖 重置統計後 刪除 index 之後
18. 已存在的 index 做空間優化
19. 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 因此可以快速的找到需要的資料
20. 圖 Table full data 圖 透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置 s建立 table 與 index 架構
21. 圖 Table full data s建立 table 與 index 架構 圖 透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置
22. s建立 圖 根據 ctid query 的結果 圖 透過 extension pageinspect，查看 index 的內部資料，ctid 為其 heap table的位置 table 與 index 架構 圖 Table full data
23. PostgreSQL 對 insert / delete / update 處理 • insert：當原先的 page 儲存空間已滿的時候，勢必要新增 一個新的 page 來容納，此舉會讓 B-tree 高度增加，後續 在 query 時花更多時間 • delete：將原先位置的資料刪除而產生空間，造成了空間上 的不連續 • update：並不會找到指定的資料直接進行修改，實際上接 近於先 delete 再 insert，會先標註原本 data 為 dead 再進行 insert 導致的碎片化，產生許多沒用的空間
24. bloat 是什麼 • update table 中的內容時， 將 tuple 標記為 dead，然後在下一 個可用空間中新 tuple，這個過程就叫 "bloat"，會導致產生不 必要的空間，因此必須清除 bloat
25. 如何查出 bloat 情況？ pgstattuple extesion 查詢 SQL 語句如以下 圖 查詢 bloat 結果 table：表 table_x_idx：該 table 的 index name
26. 如何解決 index bloat？ method vacuum vacuum full reindex reindex concurrently read lock 會發生 write lock 會發生 會發生 extra 回收不再使用的儲 存空間，提供重複使 用並不會把儲存空 間還給 OS 清除 bloat 並 reclaim 整個 disk space，把 儲存空間還給 OS 重建索引，替換舊的 版本 允許重建 index 時繼 續正常操作，對於 production 很有幫助 ，缺點就是會重建的 比較久，增加 CPU、 記憶體、IO 負擔導致 速度變慢
27. 如何解決 index bloat？ method vacuum vacuum full reindex reindex concurrently read lock 會發生 write lock 會發生 會發生 extra 回收不再使用的儲 存空間，提供重複使 用並不會把儲存空 間還給 OS 清除 bloat 並 reclaim 整個 disk space，把 儲存空間還給 OS 重建索引，替換舊的 版本 允許重建 index 時繼 續正常操作，對於 production 很有幫助 ，缺點就是會重建的 比較久，增加 CPU、 記憶體、IO 負擔導致 速度變慢
28. 如何解決 index bloat？- vacuum 圖 執行 vacuum 前 圖 執行 vacuum 後
29. 如何解決 index bloat？ method vacuum vacuum full reindex reindex concurrently read lock 會發生 write lock 會發生 會發生 extra 回收不再使用的儲 存空間，提供重複使 用並不會把儲存空 間還給 OS 清除 bloat 並 reclaim 整個 disk space，把 儲存空間還給 OS 重建索引，替換舊的 版本 允許重建 index 時繼 續正常操作，對於 production 很有幫助 ，缺點就是會重建的 比較久，增加 CPU、 記憶體、IO 負擔導致 速度變慢
30. 圖 執行 vacuum full 前 圖 執行 vacuum full 後 如何解決 index bloat？- vacuum full
31. 如何解決 index bloat？ method vacuum vacuum full reindex reindex concurrently read lock 會發生 write lock 會發生 會發生 extra 回收不再使用的儲 存空間，提供重複使 用並不會把儲存空 間還給 OS 清除 bloat 並 reclaim 整個 disk space，把 儲存空間還給 OS 重建索引，替換舊的 版本 允許重建 index 時繼 續正常操作，對於 production 很有幫助 ，缺點就是會重建的 比較久，增加 CPU、 記憶體、IO 負擔導致 速度變慢
32. 如何解決 index bloat？- reindex & reindex concurrently 圖 執行 reindex (concurrently) 前 圖 執行 reindex (concurrently) 後 reindex reindex concurrently
33. 依上述的解決方式 production：reindex concurrently 會比較好 development : 皆可
34. 避免在經常更新的 column 設置 index
35. 避免在經常更新的 column 設置 index - 一般 update • tbl 有1000筆資料 • UPDATE tbl SET data = 'B' WHERE id = 1000; 圖 update 示意圖, source: postgresql.org
36. 避免在經常更新的 column 設置 index - 一般 update 導致在 index 和 table 都要新增 tuple，增加 I/O loading
37. 避免在經常更新的 column 設置 index - HOT update HOT is an acronym for "Heap Only Tuple" 定義： • Index 並不會指向原先以外的 block，新舊版本都會存在同一個 block
38. • UPDATE tbl SET data = 'B' WHERE id = 1000; 圖 update 示意圖, source: postgresql.org Index 內 TID 沒有改變 避免在經常更新的 column 設置 index - HOT update
39. HOT is an acronym for "Heap Only Tuple" 定義： • Index 並不會指向原先以外的 block，新舊版本都會存在同一個 block 好處： • 可以即使有更新資料，index 可以保持不變，透過 HOT link 找到正確 version • 把中間 dead tuple 重新整理，不需要使用 vacuum 避免在經常更新的 column 設置 index - HOT update
40. • 清理 dead tuple ，而不需要額外的 vacuum 圖 update 示意圖, source: postgresql.org Index 內TID 沒有改變 避免在經常更新的 column 設置 index - HOT update
41. 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
42. 避免在經常更新的 column 設置 index • 如果更新 index value，index 會對應產生新的 tuple，同時在 table 也 會對應新增 data，如此勢必會浪費很多空間
43. 避免錯誤設置 index
44. 對於某一個 column 其值只有少數幾個，或是實務上只需要用到特定 幾個值，卻做了 full index，佔據空間 避免錯誤設置 index Partial index 針對特定的值做 index 即可
45. 避免錯誤設置 index 圖 Full index & partial index 結果
46. conclusion • 透過定期監看 pg_stat_all_indexes，確認沒用到的 index ，並移除 • 定時去處理 index bloat 問題，節省空間同時也能維護效率
47. thanks