基本的に怠Diary

主に日常と作ったものを書いていく。

前半は雑メモ

後半はタイトル通りの中身でハンズオンしたものを記事にした。

VACUUM

何する

デッドタプル削除

フリースペースマップの更新

統計情報の更新

VACUUMはテーブルロックなしで並行アクセスが可能なのに対して、

VACUUM Fullはディスク容量を完全に解放する。テーブル全体をロックして、かなり時間がかかる。

いつ実行される

更新削除された行数 > vacuum_threshhold + vacuum_scale_factor * テーブルの行数

デフォルト値

vacuum_threshhold: 50
vacuum_scale_factor: 0.2

デフォルト設定で、1000行のテーブルの場合、250行が更新削除されたら、自動VACUUM

Insert時のインデックス生成

基本的にはインデックス定義時にバックグラウンドで生成される。

空テーブルに大量Insertするとリアルタイムでインデックスが生成されるため、

後からインデックスを定義するか、すでにある場合は一度インデックスを削除・無効化してからInsertする。

Free Space Map

59.3. 空き領域マップ

公式？ドキュメントはいまいち容量が掴めなかった。

各ページにどれだけ空き容量があるかを記録

INSERTやUPDATEで新しいデータを書き込む際、空きのあるページを素早く見つけるために使用

テーブルファイルとは別に <テーブルのOID>_fsm というファイルとして保存される

役割

PostgreSQLはMVCCのため、UPDATEやDELETEで古いタプルが残る

VACUUMで不要タプルを削除した後、その空き領域を再利用するためにFSMが必要

つまりFSMのようなものがないと、空きページを探すために全ページをスキャンする必要がある

ちなみにFSMの状態についてはpg_freespace拡張で確認できる。

work_mem

ディスクアクセスが多い場合、メモリを増設したら早くなるかもしれない。

ディスクアクセスのチェック

キャッシュヒット率

SELECTsum(heap_blks_read)as heap_read,sum(heap_blks_hit)as heap_hit,sum(heap_blks_hit) / (sum(heap_blks_hit) +sum(heap_blks_read))as cache_hit_ratioFROM pg_statio_user_tables;

インデックスキャッシュヒット率

SELECTsum(idx_blks_read)as idx_read,sum(idx_blks_hit)as idx_hit,sum(idx_blks_hit) / (sum(idx_blks_hit) +sum(idx_blks_read))as idx_cache_hit_ratioFROM pg_statio_user_indexes;

ワーキングメモリ不足

SELECT name, setting, unitFROM pg_settingsWHERE nameIN ('work_mem','temp_files');

実際に動かしてみる

GitHub - wasuken/learn_vacuum_cache_hit

リポジトリを作ってみた。

docker-compose exec -T postgres psql -U testuser -d testdb < check_cache.sql=========================================テーブルのキャッシュヒット率========================================= heap_read | heap_hit | cache_hit_ratio_percent-----------+----------+-------------------------      9900 |  2634490 |                   99.63(1 row)=========================================インデックスのキャッシュヒット率========================================= idx_read | idx_hit | idx_cache_hit_ratio_percent----------+---------+-----------------------------     1311 | 4645239 |                       99.97(1 row)=========================================ワーキングメモリ設定=========================================         name         | setting | unit----------------------+---------+------ effective_cache_size | 131072  | 8kB maintenance_work_mem | 65536   | kB shared_buffers       | 32768   | 8kB work_mem             | 4096    | kB(4 rows)=========================================テンポラリファイル使用状況========================================= datname | temp_files | temp_size---------+------------+----------- testdb  |          5 | 18 MB(1 row)

テーブル・インデックスのキャッシュヒット率

問題なし

テンポラリファイル使用状況

ソートやハッシュ結合がwork_memに収まらずディスクスピル（メモリに収まりきらないデータを一時的にディスクに書き出す処理）が発生している。

メモリサイズを増やしてみる。

=========================================ワーキングメモリ設定=========================================         name         | setting | unit----------------------+---------+------ effective_cache_size | 131072  | 8kB maintenance_work_mem | 65536   | kB shared_buffers       | 32768   | 8kB work_mem             | 16384   | kB(4 rows)=========================================テンポラリファイル使用状況========================================= datname | temp_files | temp_size---------+------------+----------- testdb  |          6 | 25 MB(1 row)

悪化してる？

と思ったけど、どうやらこれリセットしないといけないみたい。

make down && make upしただけだとリセットされないみたいなので

docker-compose exec postgres psql -U testuser -d testdb -c"SELECT pg_stat_reset();"

を実行して再度

=========================================ワーキングメモリ設定=========================================         name         | setting | unit----------------------+---------+------ effective_cache_size | 131072  | 8kB maintenance_work_mem | 65536   | kB shared_buffers       | 32768   | 8kB work_mem             | 16384   | kB(4 rows)=========================================テンポラリファイル使用状況========================================= datname | temp_files | temp_size---------+------------+----------- testdb  |          1 | 7008 kB(1 row)

よくなってる。

ちなみに、max_connectionsが多い場合、単純にwork_memと掛け算でメモリを食い散らかすので注意が必要だ。