Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

ZHIH
August 12, 2022

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

ZHIH

August 12, 2022
Tweet

Other Decks in Programming

Transcript

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

    View full-size slide

  2. ZHIH

    王重智 ZHIH

    Funliday

    JavaScript / 爬蟲

    剛入行半年的菜鳥

    View full-size slide

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

    View full-size slide

  4. 移除未使用的 index

    View full-size slide

  5. 為何要移除 unused index?

    View full-size slide

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

    View full-size slide

  7. 如何查看 index 的使用情形?

    View full-size slide

  8. pg_stat_all_indexes

    View full-size slide

  9. • 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

    View full-size slide

  10. 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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  13. 刪除候選的 index 前
    • 確認該 index 是否是真的沒用?

    如:可能是 query 語法導致 index 沒確實使用到
    • 這個 index 是否值得去刪除

    如:佔據空間不大

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  17. 已存在的 index 做空間優化

    View full-size slide

  18. 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 因此可以快速的找到需要的資料

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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 負擔導致
    速度變慢

    View full-size slide

  27. 如何解決 index bloat?- vacuum
    圖 執行 vacuum 前
    圖 執行 vacuum 後

    View full-size slide

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

    View full-size slide

  29. 圖 執行 vacuum full 前
    圖 執行 vacuum full 後
    如何解決 index bloat?- vacuum full

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  40. 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

    View full-size slide

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

    View full-size slide

  42. 避免錯誤設置 index

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide