HOTの上手な使い方

NTT オープンソースソフトウェアセンタ 笠原 辰仁

 

さて、HOTシリーズの第3回目となりました。前回・前々回を通じて、HOTがどんな効果を持ち、どのように振舞うのかを見てきました。今回は、HOTを上手く使うためのコツを解説していきたいと思います。HOTの機能を上手く生かすには何が必要になるのでしょうか？次の4つのキーワードに沿って進んでいきましょう。

  • インデックスキーの更新は避けよう
  • 使われないインデックスは削除しよう
  • ページの空き領域を確保しておこう
  • ロングトランザクションに要注意

インデックスキーの更新は避けよう！

1つ目のコツは、「インデックスキーの更新を避ける」です。

HOTが機能するための前提として、「インデックスキーの更新が起こらない」ことが挙げられます。もちろん、必要不可欠なインデックスキーの更新処理は避けることができませんが、HOTを機能させるための条件ですので、覚えておいて下さい。

キーが更新されたかは、実際に値が変化したかで判定されます。もし値の変更が無ければ、UPDATE文のSETで列を指定しても構いません。

使われないインデックスは削除しよう！

2つ目のコツは、「いらないインデックスは削除する」です。

先述の様に、インデックスキーの更新時にはHOTの機能が生かせません。つまり、なるべく更新対象となるインデックスは減らすよう努める必要があります。とはいえ、性能を考えるとインデックスを闇雲に消すというのも抵抗があります。そこで「使っていない」インデックスの判別方法を解説します。使われないインデックスの削除は、HOTの活用に繋がるだけでなく、更新負荷の軽減、DB領域の節約やメンテナンス稼動の削減にも有効です。

PostgreSQLでは、各インデックスについてどれだけのアクセスがされたかを記録しています。これを調べるには、pg_stat_user_indexesテーブルを使います。以下の様なSQLで確認ができます。

=# SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch
     FROM pg_stat_user_indexes WHERE relname = 'test';
　
     indexrelname     | idx_scan | idx_tup_read | idx_tup_fetch
----------------------+----------+--------------+---------------
 test_pkey            |        1 |            1 |             1
 idx_test_name        |        1 |           54 |             0
 idx_test_last_access |        1 |            0 |             0

上記のカラムは以下の様な情報となっています。

  • indexrelname : インデックス名
  • idx_scan : インデックスに対してスキャンされた回数
  • idx_tup_read : スキャンされたインデックスキーの数
  • idx_tup_fetch : 実際にインデックスキーからテーブルのレコードが読み取られた数

 

 

idx_scanが 0 のインデックスについては、過去にそのインデックスが使われたことがない、ということを示しています。つまり、SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0; の結果出力されたインデックスは、少なくとも現時点では使われていないものとなります。削除対象の候補としての絞込みに、上記の方法が活用できますね。

ページに空き領域を確保しておこう！

3つ目のコツは「更新の多いテーブルはFILLFACTORで空き領域を作っておこう」です。

前回の「HOTの仕組み」の話で、ラインポインタのリダイレクトによりインデックス更新のスキップを実現していることを解説しました。ただし、これは同じページに新規のレコードが配置された場合に限ります(図1)。同じページに新規レコードを配置する空きがない場合は、HOTの「不要なインデックスをスキップする」機能が働きません(図2)。

Hot Page Space available

図1. ページに空き領域がある場合

 
Hot Page Full

図2. ページに空き領域がない場合

つまり、更新したデータを配置するための空き領域を、ページ内に持っておくことが重要になります。この「予め空き領域を確保する」ための機能が FILLFACTOR です。Oracle Database にも PCTFREE という設定がありますが、ちょうど FILLFACTOR = 100 - PCTFREE の関係があります。FILLFACTORはテーブル毎に設定するパラメータで、以下の用にテーブルの作成時、もしくはALTERコマンドでのテーブル定義の変更時に指定が可能です。

=# CREATE TABLE fillfactor_test (id serial PRIMARY KEY, name text)
     WITH (FILLFACTOR=90);
=# ALTER TABLE fillfactor_test SET (FILLFACTOR=85);

上記のCREATE TABLEでは、FILLFACTOR=90を指定しています。この場合、このテーブルは10%の空き領域を更新用に確保することになります。20%の空き領域を確保したい場合はFILLFACTOR=80とします。一般的には、FILLFACTOR=90でHOTが十分に機能するでしょう。

なお、FILLFACTORを設定すると空き領域をテーブルデータ内に作ることになるため、テーブルデータの密度が下がります。密度が下がると、読み込むデータ量が増えます(キャッシュヒット率の低下)。そのため、INSERT、SELECT処理がメインとなるテーブルについては、キャッシュヒット率を重視する意味で、FILLFACTORは指定せず、デフォルトである 100% の設定を使うほうが良いでしょう。

各テーブルのFILLFACTOR設定値は、pg_classシステムテーブルの、reloptionsというカラムで確認ができます。以下はSQLでの確認例です。

=# SELECT relname, reloptions FROM pg_class 
    WHERE relname = 'fillfactor_test';
　
     relname     |   reloptions
-----------------+-----------------
 fillfactor_test | {fillfactor=85}

FILLFACTORはインデックスについてもテーブルと同様に指定が可能です。インデックスはデフォルトでFILLFACTORが設定されており、その値は90となっています。こちらは、特に変更する必要はないでしょう。

ロングトランザクションに要注意！

4つ目のコツは「ガベージ掃除を阻害するロングトランザクションを排除する」です。

HOTの機能により、INSERT、SELECT、UPDATE時に、必要があれば自動でガベージの掃除をしてくれることを前回に解説しました。しかし、このガベージの掃除を妨害されることがあるのです。PostgreSQLでは、削除されたデータであっても、実行中のトランザクションがそのデータを見る可能性がある場合、そのデータの掃除はしません。具体的には、「実行中の最も古いトランザクションの開始以降に発生した不要領域は掃除できない」ことになります(図3)。

Hot with Long Transaction

図3. ロングトランザクションによるガベージ回収の阻害

図にもあるとおり、ロングトランザクションはHOTだけでなくVACUUMも阻害します。長期間実施されているロングトランザクションはpg_stat_activityビューで確認が可能です。もし、HOTが機能している or VACUUMを実施しているのに、どんどんDBが肥大化しているような場合には、メンテナンス前にロングトランザクションがいないかをチェックしましょう。以下はPostgreSQL8.3系での確認方法の一例です。

 

=# SELECT datname, usename, current_query, waiting, 
          (current_timestamp - xact_start) AS duration
     FROM pg_stat_activity WHERE procpid <> pg_backend_pid();　
　

 datname | usename  |     current_query     | waiting |    duration
---------+----------+-----------------------+---------+-----------------
 test    | postgres | <IDLE> in transaction | f       | 00:33:08.424853
(1 row)

上記のカラムは以下の様な情報となっています。

  • datname : トランザクション処理が行われているDB名
  • usename : トランザクション処理を実施しているDBユーザ名
  • current_query : トランザクション処理の実施内容
  • waiting : トランザクション処理がロック待ちかどうか(t ならロック待ち状態)
  • duration : トランザクション処理が開始されてからの経過時間(現在の時間とトランザクション開始時間の差分)

上記のSQLの結果を見ると、「postgresユーザ」が、「testデータベース」にて、「BEGIN;+αの処理」を行ってから、「33分間放置」されている状態だと分かります。もしdurationが大きいトランザクションがいた場合は要注意です。以下の様な処理が実施されていないか確認しましょう。

  • BEGIN;を発行して適当なSELECT(監視など)をしたまま、COMMITしていないトランザクション
  • ロックを持ったまま長期間なにもしていないトランザクション
  • 巨大なテーブルへのANALYZE処理

まとめ

さて、全3回に渡って、HOTの効果・仕組み・活用のコツ、を解説してきました。いかがだったでしょうか？仕組みについてはやや難しい話になってしまいましたが、活用するためのコツはそれほど難しくはなく、それでいて大きな効果を期待できることが伝えられたかな、と思います。本シリーズの記事が、PostgreSQLのHOT機能の凄さを体感する足がかりになれば幸いです。