FILLFACTORによる性能改善
めっちゃ寒くてマジ冬かってかんじ。
今日はポスグレアンカンファレンスというイベント当日、行けなくて悲しい。行けてたら発表しようかなと思っていた内容をブログに書く。微妙に前回分の発展版となっているので、ご興味がある方は、合わせてどうぞ。
TL;DR
- FILLFACTORの値を設定するとブロック内に空き領域を持つ
- HOTの効果により更新系の性能が改善する
- クラスタの維持により参照系の性能が改善する
- テーブルサイズとのトレードオフを考慮して設定するべき
FILLFACTORとは
以前にブログにも書いたように、Postgresにおいてテーブルのデータはブロックという単位に区切られて格納される。ブロックはデフォルトで8KBであり、例えば一行が1KBのレコードなら8行が入る。
FILLFACTORの値を設定すると、このブロックに空きスペースを設ける。デフォルトでは「100」になっていて、前述のようにレコードを詰め詰めに入れる。例えば、「80」に設定すると、8KBのうち、80%だけを使うようになり、残りの20%を空けておく。実質使えるスペースは8KB * 0.8 = 6.4KBとなり、1KBのレコードは6行しか入らなくなる。
なぜわざわざ無駄な空き容量を設けるのかというと、HOTを活用するため。HOTについてはLet's Postgresにわかりやすい解説があるので、そちらを参照されたいが、簡単に説明する。
Postgresは、これもまた過去のブログで書いたように、追記型のアーキテクチャを採用している。あるブロックAにあるタプルT1をUPDATEするとき、以下の動きが発生する。
- タプルT1をコピーし、値を書き換えたタプルT2をブロックBに作成する
- タプルT1の削除フラグを立てる
- ブロックAを向いていたタプルT1のインデックスをブロックBに向けなおす
ここで、HOTとは、1.においてブロックB=ブロックAとすること、つまり、同一ブロック内でタプルのコピーを行うことで、3.のインデックス向けなおしのコストを削減する、という機能のこと。HOTが機能するためには、ブロックAの中に、タプルT2を置くための空き容量が残っている必要があり、そのためにFILLFACTORが効く。
FILLFACTORはHOTによる更新性能向上のための設定値として紹介されることが多いが、実は参照性能の向上にも寄与する。それは、前回ブログで書いたクラスタの持続性を高めることによる、間接的な効果である。HOTによって更新されるということは、レコードがブロック間を移動しないということなので、クラスタが維持されやすくなる。
これらを踏まえて、以下、3つの実験をやってみた。
- 実験1 - HOTとFILLFACTOR
- 空きスペースの確保によりHOTが機能しやすくなり、更新性能が改善することを確認する
- 実験2 - CLUSTERとFILLFACTOR
- 空きスペースの確保によりHOTが機能しやすくなり、クラスタが崩れにくくなることを確認する
- 実験3 - テーブルデータ密度とFILLFACTOR
- 上記メリットとのトレードオフとなる、テーブルデータ密度の減少を確認する
実験1 - HOTとFILLFACTOR
HOTを機能させるようにFILLFACTORを設定することで、更新系の性能が改善することを実験してみる。
準備
FILLFACTORが100（デフォルト）のテーブルと、80のテーブルを作成する。
CREATE TABLE test_full(test_id int primary key, test_id_1 int, test_id_2 int, test_id_3 int, test_id_4 int, test_id_5 int, test_id_6 int,test_id_7 int,test_id_8 int,test_id_9 int, test_text text); CREATE TABLE test_space(test_id int primary key, test_id_1 int, test_id_2 int, test_id_3 int, test_id_4 int, test_id_5 int, test_id_6 int,test_id_7 int,test_id_8 int,test_id_9 int, test_text text) WITH (FILLFACTOR=80);
データを1万行INSERTする。1万行という数字に意味はない。なんとなくいっぱい。test_textには一律"a"1000文字を格納する。
a = "a" * 1000 for i in range(0, 10000): cur.execute("INSERT INTO test_full (test_id, test_id_1,test_id_2,test_id_3,test_id_4,test_id_5,test_id_6,test_id_7,test_id_8,test_id_9, test_text) \ values (" + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + ",'" + a + "');") cur.execute("INSERT INTO test_space (test_id, test_id_1,test_id_2,test_id_3,test_id_4,test_id_5,test_id_6,test_id_7,test_id_8,test_id_9, test_text) \ values (" + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + "," + str(i) + ",'" + a + "');")
HOTの効果はインデックスの更新を省略できることなので、その差をわかりやすくするために、primary keyを含め10個のインデックスを持たせておく（test_id_1〜9はこのためのカラム）。
for i in range(1, 9): cur.execute("CREATE INDEX full_index_" + str(i) + " ON test_full (test_id_" + str(i) + ");") cur.execute("CREATE INDEX space_index_" + str(i) + " ON test_space (test_id_" + str(i) + ");")
レコードサイズは、 (int) * 10 + ("a") + 1000 = 4B * 10 + 1B * 1000 = 1040B ≒ 1KB。対して、FILLFACTORが80とすると、ブロック内の空きスペースは、 (ブロックサイズ) * (1 - 0.8) = 8KB * 0.2 = 1.6KB。そのため、1レコードが十分収まる空きスペースが用意されており、HOTが機能できる状態になっている。
計測
この状態で、それぞれのテーブルに対するUPDATEの実行時間を計測する。test_textを一律"b"1000文字に更新する。
b = "b" * 1000 # 2000回ずつUPDATEする for i in range(0, 2000): # UPDATEする行はランダムに決める target_test_id = random.randint(0, 10000) cur.execute("EXPLAIN ANALYZE UPDATE test_full SET test_text = '" + b + "' WHERE test_id = " + str(target_test_id)) for row in cur: # EXPLAIN ANALYZEの結果の文字列から、Execution timeを抜き出す for i in row[0].split("\n"): if(i.find("Execution") != -1): execition_time_full.append(i.split(" ")[2]) cur.execute("EXPLAIN ANALYZE UPDATE test_space SET test_text = '" + b + "' WHERE test_id = " + str(target_test_id)) for row in cur: for i in row[0].split("\n"): if(i.find("Execution") != -1): execution_time_space.append(i.split(" ")[2])
実行時間は、EXPLAIN ANALYZEした結果の文字列から抜き出している。書き慣れていない感がすごい。もっと簡単なやり方があれば教えてください。
結果
計測した execution_time_full と execution_time_space をプロットしてみる。
明らかに、FILLFACTORが80のほうが、実行時間は短く、安定していることがわかる*1。
考察
UPDATEの性能が向上しているのは、先述のとおりHOTが機能しているためと考えられる。実際に、どれくらいのタプルがHOTによって更新されたかを確認する。
SELECT relname, n_tup_upd, n_tup_hot_upd, round(n_tup_hot_upd*100/n_tup_upd, 2) AS hot_upd_ratio FROM pg_stat_user_tables WHERE relname in ('test_full', 'test_space');
| relname | n_tup_upd | n_tup_hot_upd | hot_upd_ratio |
|---|---|---|---|
| test_full | 2000 | 836 | 41.00 |
| test_space | 2000 | 2000 | 100.00 |
FILLFACTORが80のテーブル（test_space）は100%の確率でHOTが機能し、同一ブロック内で更新されている。それに対し、FILLFACTORが100のテーブル（test_full）は、41%となっている。FILLFACTORが100のときでもHOTが機能する理屈はわからん。だれか教えてください。もしくはそのうち検証します。
実験2 - CLUSTERとFILLFACTOR
FILLFACTORの設定がクラスタの維持に効果をもたらすことを確認する。確認方法としては、FILLFACTORが未設定（デフォルトの100）のテーブルと、それ以下に設定したテーブルのそれぞれに同一のUPDATEを繰り返し、correlationの変化を計測する。
準備
FILLFACTORが未設定のテーブル test_full と、FILLFACTORを設定したテーブル test_spaceを用意する。FILLFACTORの値は、ここでは89に設定する例を挙げている。
CREATE TABLE test_full (test_id int primary key, test_text text); CREATE TABLE test_space (test_id int primary key, test_text text) WITH (FILLFACTOR=89);
これらのテーブルに、実験用のデータをINSERTする。test_textには、実験1と同様に一律"a"1000文字を格納し、後ほど一律"b"1000文字に更新する。
a = "a" * 1000 for i in range(0, 1000): cur.execute("INSERT INTO test_full (test_id, test_text) VALUES (" + str(i) + ",'" + a + "')") cur.execute("INSERT INTO test_space (test_id, test_text) VALUES (" + str(i) + ",'" + a + "')")
これらのテーブルに対し、ランダムな行をUPDATEしながら、correlationの変化を計測する。
b = "b" * 1000 for i in range(0, 2000): # UPDATEする行はランダムに決める target_test_id = random.randint(0, 1000) cur.execute("UPDATE test_full SET test_text = '" + b + "' WHERE test_id = " + str(target_test_id)) cur.execute("UPDATE test_space set test_text = '" + b + "' WHERE test_id = " + str(target_test_id)) # 統計情報を更新しておく cur.execute("ANALYZE test_full") cur.execute("ANALYZE test_space") # pg_statsからcorrelationを取得する（配列に格納するところは省略） cur.execute("SELECT tablename, correlation FROM pg_stats WHERE attname = 'test_id' and (tablename = 'test_space' or tablename = 'test_full')")
結果
FILLFACTORが100の場合と89の場合で、correlationの変化は以下のようになった。
FILLFACTORが100でも90でも、UPDATEにともなってクラスタが同様に崩れていく。崩れ方が同じで、グラフが重なっている。
FILLFACTORが89になるとクラスタが崩れにくくなっている。
FILLFACTORが88になると完全にクラスタが維持されている。
考察
FILLFACTORを88以下に設定すると、今回実験に使ったUPDATEはすべてHOTが機能して同一ブロック内で更新され、クラスタが維持されることがわかった。クラスタが維持されるということは、前回ブログで示した参照系の性能が持続するということなので、クラスタを利用する場合かつなるべくメンテナンスのコスト＝CLUSTERを実行するコストをかけたくない場合は、FILLFACTORを88以下に設定すればよい、ということになる。
FILLFACTORが89がしきい値となり、その前後でHOTが機能するかしないかがわかれている。FILLFACTORが89ということは、空きスペースは(ブロックサイズ) * (1- 0.89) = 8KB * 0.11 = 0.88KB ≒ 901B。一方、レコードのサイズは、 (int) + ("a") + 1000 = 4B + 1B * 1000 = 1004B。カラム内圧縮によってtest_textカラムがTOAST圧縮され、1行のレコードがギリギリ収まるしきい値になっていると考えられる。TOAST圧縮については別の機会に書くかもしれない。
実験3 - テーブルデータ密度とFILLFACTOR
準備
FILLFACTORが100と88のテーブルを作成する。
CREATE TABLE test_full (test_id int primary key, test_text text); CREATE TABLE test_space (test_id int primary key, test_text text) WITH (FILLFACTOR=88);
テーブルサイズの差をみるため、データを投入する。
a = "a" * 1000 for i in range(0, 1000): cur.execute("INSERT INTO test_full (test_id, test_text) VALUES (" + str(i) + ",'" + a + "')") cur.execute("INSERT INTO test_space (test_id, test_text) VALUES (" + str(i) + ",'" + a + "')")
計測
pg_classを参照してそれぞれのテーブルサイズををみてみる。
SELECT relname,reltuples,relpages, (relpages * 8)::text || 'KB' AS tablesize FROM pg_class WHERE relname IN ('test_full','test_space');
結果
| relname | reltuples | relpages | tablesize |
|---|---|---|---|
| test_full | 1000 | 143 | 1144KB |
| test_space | 1000 | 167 | 1336KB |
どちらも同じ1000行のレコードを保持しているにも関わらず、空きスペースがあるぶん、test_spaceのほうがテーブルサイズが大きくなっており、テーブルデータの密度は下がっている。
考察
テーブルデータの密度が下がるということは、同じSELECT文を実行しても読み込むデータ量が増えるため、キャッシュヒット率の低下など、参照性能の劣化につながる。札束で殴る*2ことである程度解決するが、限界はあるし無駄はないに越したことはない。
さいごに
FILLFACTORを設定すると、以下のメリットが得られることがわかった。
- HOTの頻度向上により更新系の性能が改善する
- クラスタの維持向上により参照系の性能が改善する
また、テーブルデータ密度とのトレードオフが生じることもわかった。これらの要因を総合的に考慮し、各テーブルにFILLACTORを設定するか、するなら値はどれくらいが適切か、そこらへんはなんかいいかんじに検討されたい。
Postgresユーザの良き友であるドキュメントサイト『Let's Postgres』をみても以下の記述があり、
INSERT、SELECT処理がメインとなるテーブルについては、キャッシュヒット率を重視する意味で、FILLFACTORは指定せず、デフォルトである 100% の設定を使うほうが良いでしょう。
CLUSTERによる間接的な参照性能改善にあまり目を向けられていないようだったので、書いてみた。その意味で、実験2のことさえ書かれていればよかったのだが、前回と今回合わせてここまで冗長なブログにしてしまって猛省している。もっと端的にいきたい。あと、こんなもん書いてる暇があったらアンカンファレンスに行けよという苦情は一切受け付けない。ご盛会をお祈りしています。
