PREPARATION
受験対策

受験対策
OSS-DB道場
第9回　パフォーマンス・チューニング(前編)

OSS-DB入門

第9回　パフォーマンス・チューニング(前編)

今回と次回はパフォーマンスのチューニングにフォーカスを当てていきます。
前編では

・「postgresql.conf」で変更を検討すべき項目
・テーブルへのインデックス作成

を紹介します。
特にインデックスについてはこれまでの回で作成したテーブルを用いて、変更前と変更後でどのくらいの効果があるのかを見ていきたいと思います。

postgresql.confの変更を検討すべき項目

PostgreSQLの設定ファイルである「postgresql.conf」は、前回の「OSS-DB入門 #08 バックアップ・リストア」でも出てきましたが、パフォーマンスの観点から見ると初期設定値のままではハードウェアの性能を活かしきれない可能性があります。代表的なパラメータを以下に紹介しますので、利用用途に応じて見直してみてください。

●メモリー関連

・shared_buffers

共有バッファサイズを指定します。（デフォルト値は32MB）
データベースからデータを取得する際には一旦ハードディスクから共有バッファに読み込まれ、再度同じデータにアクセスがあった場合には共有バッファからデータを取得することができます。
メモリーよりも速度の遅いハードディスクにアクセスする回数を減らすことができる為、なるべく多くのデータが共有バッファに載っている状態がパフォーマンスの観点から望ましいと言えます。
共有バッファサイズの設定値は物理メモリーサイズの4分の1程度が目安とされています。例えば4GBのメモリーを搭載したマシンの場合は1GBとなります。
あまりに大きな値を設定してしまうと、他のプロセスが使用するメモリーを圧迫してしまい、データベース全体として速度劣化を引き起こす可能性があります。

・work_mem

内部並び替えとハッシュテーブル操作が使用するメモリーサイズを指定します。（デフォルト値は1MB）ORDER BYによるソートやテーブルの結合、集約といった複雑な問い合わせを行う場合に使用されます。設定した値は同時に複数実行される問い合わせ毎に使用される為、実際に消費されるメモリーサイズは設定値の数倍になりますので、大きな値を設定するとメモリーが足りずにスワップが発生してしまう可能性があります。設定可能な最大値は物理メモリーサイズからshared_buffersのサイズを引いた値をmax_connectionsで割った値となります。

●WAL関連

・wal_buffers

WALバッファサイズを設定します。（デフォルト値はshared_buffersの1/32のサイズ）WALバッファとはWALログをディスクに書き出すまで保持しておくバッファです。このバッファのサイズを大きく設定すれば、WALバッファが一杯になったのでディスクへ書き出す、という状態になることを減らすことができます。

・checkpoint_segments

チェックポイントの処理を実行するタイミングをWALセグメントの数で設定します。（デフォルト値は3）チェックポイントとはデータの挿入や変更の内容を保持しているWALログからディスクへ書き出す処理で、大量のディスクI/Oが発生する為、コストの高いものです。1セグメントのWALログファイルのサイズは16MBです。デフォルト値の3の場合は48MB分のWALログが溜まったタイミングでチェックポイントを実行することになります。この設定値を大きくしてディスクへ書き込む頻度を減らすことで性能を向上させることができます。その代わりにWALログファイルの数が増えることになりますので、ハードディスクには十分な空き容量が必要です。また、リカバリーを行う際にも時間がかかるようになります。※WALについては本連載の「第8回バックアップ・リストア」のPITRの項、OSS-DB道場の「第14回トランザクションログ」に説明がありますので参照してください。

・checkpoint_timeout

チェックポイントの処理を実行するタイミングを設定しますが、こちらは時間間隔で設定します。（デフォルト値は5m）
デフォルト値の5分よりも長い間隔とすることで、上記のcheckpoint_segmentの値を大きくした場合と同様にディスクへの書き込み頻度を減らします。

インデックス

インデックス（索引）はデータベースへの問い合わせ性能を向上させるための方法です。
データベースのテーブルを本に見立てて例に取ると、探したい単語が本の何ページにあるかを調べたいとき、インデックスがなければ1ページ目から最後のページまで調べなければなりませんが、インデックスがあれば何ページ目にあるかがわかるので順番に調べる必要はなく、目的のページに辿り着くまでの労力は随分と軽減されるでしょう。

テーブルにも検索によく使われるであろう列にあらかじめインデックスを作成しておくことで検索性能を向上させることができます。

これまでに作成したテーブルの中で最もレコード数の多いordersテーブルでも362件しかありませんので、インデックス有無の差をわかりやすく体感する為にここで1つテーブル"sales"を追加します。

CREATE文は以下の通りです。

CREATE TABLE sales (
id serial,
product_name character varying(255) NOT NULL,
sales_datetime timestamp without time zone NOT NULL,
sales_num integer NOT NULL
);

100000件のサンプルデータを投入するSQLファイルを用意しましたので、テーブルを作ったら以下のファイルをダウンロードしてデータをロードしてください。

※少々大きいのでzip圧縮してあります。解凍したsqlファイルからサンプルデータをロードする方法は「第5回 SQL基礎I 3.データの準備」を参照してください。

ossdb-sales.zip

きちんとデータが入っているかを確認してみましょう。
以下のSQL文を実行して件数を見てみます。

SELECT COUNT(*) FROM sales;

100000件のレコードが登録されていることがわかります。
データの中身も少し見てみましょう。以下のSQL文を実行してください。

SELECT * FROM sales LIMIT 10;

このように表示されれば正常にデータが入っています。
salesテーブルにserial型の列idを作成してありますので、データのロードによって連番を振られていることも確認できます。

データが入ったので次に以下のSQL文を実行しておいてください。

ANALYZE sales;

※ANALYZEについては次回で取り上げます。ここではおまじないだと思って実行しておいてください。

ではsalesテーブルにインデックスを作る前の状態でWHERE句をつけた以下のSQLがどのように実行されるかをEXPLAINを使って見ておきましょう。
EXPLAINをSQLの前に付与することで実行計画を見ることができます。
※実行計画については次回詳しく見ていきますので、今回は赤枠で囲った部分、赤線を引いた部分に注目してください。

idが1のレコードを抽出してみます。
以下のSQLを実行してください。

EXPLAIN SELECT * FROM sales WHERE id = 1;

赤枠の部分に「Seq Scan on sales」とあります。
これはsalesテーブルを順読みして検索しますよ、という意味です。また、この問い合わせのコストは1918と見積もられています。
※コストの単位は秒ではありませんので注意してください。

次にインデックスを追加して同じように見てみます。
インデックス作成の基本的な構文は以下の通りです。

CREATE INDEX インデックス名 ON テーブル名 (列名);

他にも様々なパラメータやオプション、アクセスメソッドがあるので、用途に応じてより細かく指定することもできます。
公式ドキュメントに詳細な記載がありますので興味のある方は参照してください。
http://www.postgresql.jp/document/9.1/html/indexes.html

salesテーブルの列idに対して検索を行うので以下のSQL文でインデックスを作成します。

CREATE INDEX sales_idx ON sales (id);

インデックスを作成しましたので先ほどと同じSQLを実行します。
EXPLAIN SELECT * FROM sales WHERE id = 1;

今度は赤枠部分の表示が変わりました。「Index Scan using sales_idx on sales」とあり先ほど作成したインデックスが使われます。
またコストも大幅に削減されています。インデックス作成前では1918だったものが8.28にまで小さくなりました。

今回は100000件から1件だけを抽出するという比較的インデックスの優位性が際立つケースでした。
あらゆる検索においてこのような性能向上が得られるとは限りませんが、その効果は感じていただけたと思います。
ただし効果があるからといって無駄に多くのインデックスを作ってしまうと今度は登録・更新性能が劣化していき、ディスク容量も無駄に消費してしまいますので注意してください。

最後に

今回はPostgreSQLのインストール後にまず変更を検討すべき設定項目、テーブル設計と同時に検討しておきたいインデックスを紹介しました。
いずれも運用後の早い段階から効いてくるものなので是非積極的に活用してください。

次回は、今回最後に少し触れた実行計画についてもう少し詳しく解説します。
また、運用を開始するとデータは増えていくものですが、それによってSQLの問い合わせが遅くなってしまった、といった場合にどのように手がかりを得るかというところにも触れていきたいと思います。

執筆：
市脇康二氏（OSS-DB Gold認定者）株式会社オークニージオソリューション事業部　マネージャー
PostGISを含めたFOSS4G(Free Open Source Software for Geospatial)ソフトウェアを活用し、地図やGPS等の位置情報を組み合わせたアプリケーションやサービスの設計・開発に従事。
サービスプラットフォームがクラウドへ、利用シーンがモバイルへと広がりを見せる中、位置情報の更なる活用シーンを模索中。

アドバイザー：
森亮氏　株式会社オークニー代表取締役
2002年にオークニーを設立し、代表取締役に就任。マッピング・ GIS・LBS・ITS関連プロジェクトのビジネスコンサルティングを経て、現在は会社経営の傍ら、『入門Webマッピング』（Tyler Mitchell原著；オライリー・ジャパン発行）の翻訳、Open Source Geospatial財団日本支部の代表者を務めるなど、FOSS4G（オープンソース地理空間ソフトウェア）の普及活動に取り組んでいる。

監修：
松田　神一