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 • pg_stat_all_indexes 找出候選人 已存在的 index 做空間優化 • index

    bloat • index vacuum • index reindex • 避免在經常更新的 column 設置 index • 錯誤設置 index outline
  2. • 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
  3. 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
  4. 圖 未使用的 index 結果 圖 使用 pg_stat_all_indexes 查 詢未使用的 index

    SET idx_scan & idx_tup_read & idx_tup_fetch = 0 pg_stat_all_indexes
  5. 刪除候選的 index 前 • 確認該 index 是否是真的沒用? • 如:可能是 query

    語法導致 index 沒確實使用到 • 這個 index 是否值得去刪除 • 如:佔據空間不大
  6. 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 因此可以快速的找到需要的資料
  7. 圖 Table full data 圖 透過 extension pageinspect,查看 index 的內部資料,ctid

    為其 heap table的位置 s建立 table 與 index 架構
  8. 圖 Table full data s建立 table 與 index 架構 圖

    透過 extension pageinspect,查看 index 的內部資料,ctid 為其 heap table的位置
  9. s建立 圖 根據 ctid query 的結果 圖 透過 extension pageinspect,查看

    index 的內部資料,ctid 為其 heap table的位置 table 與 index 架構 圖 Table full data
  10. PostgreSQL 對 insert / delete / update 處理 • insert:當原先的

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

    個可用空間中新 tuple,這個過程就叫 "bloat",會導致產生不 必要的空間,因此必須清除 bloat
  12. 如何查出 bloat 情況? pgstattuple extesion 查詢 SQL 語句如以下 圖 查詢

    bloat 結果 table:表 table_x_idx:該 table 的 index name
  13. 如何解決 index bloat? method vacuum vacuum full reindex reindex concurrently

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

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

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

    如何解決 index bloat?- vacuum full
  17. 如何解決 index bloat? method vacuum vacuum full reindex reindex concurrently

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

    (concurrently) 前 圖 執行 reindex (concurrently) 後 reindex reindex concurrently
  19. 避免在經常更新的 column 設置 index - 一般 update • tbl 有1000筆資料

    • UPDATE tbl SET data = 'B' WHERE id = 1000; 圖 update 示意圖, source: postgresql.org
  20. 避免在經常更新的 column 設置 index - HOT update HOT is an

    acronym for "Heap Only Tuple" 定義: • Index 並不會指向原先以外的 block,新舊版本都會存在同一個 block
  21. • UPDATE tbl SET data = 'B' WHERE id =

    1000; 圖 update 示意圖, source: postgresql.org Index 內 TID 沒有改變 避免在經常更新的 column 設置 index - HOT update
  22. HOT is an acronym for "Heap Only Tuple" 定義: •

    Index 並不會指向原先以外的 block,新舊版本都會存在同一個 block 好處: • 可以即使有更新資料,index 可以保持不變,透過 HOT link 找到正確 version • 把中間 dead tuple 重新整理,不需要使用 vacuum 避免在經常更新的 column 設置 index - HOT update
  23. • 清理 dead tuple ,而不需要額外的 vacuum 圖 update 示意圖, source:

    postgresql.org Index 內TID 沒有改變 避免在經常更新的 column 設置 index - HOT update
  24. 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