パフォーマンス・チューニング(前編)今回と次回はパフォーマンスのチューニングにフォーカスを当てていきます。テーブルへのインデックス作成を紹介します。インデックスについてはこれまでの回で作成したテーブルを用いて、変更前と変更後でどのくらいの効果があるのかを見ていきたいと思います。バックアップ・リストア」でも出てきましたが、パフォーマンスの観点から見ると初期設定値のままではハードウェアの性能を活かしきれない可能性があります。代表的なパラメータを以下に紹介しますので、利用用途に応じて見直してみてください。・shared_buffers共有バッファサイズを指定します。(デフォルト値は32MB)データベースからデータを取得する際には一旦ハードディスクから共有バッファに読み込まれ、再度同じデータにアクセスがあった場合には共有バッファからデータを取得することができます。メモリーよりも速度の遅いハードディスクにアクセスする回数を減らすことができる為、なるべく多くのデータが共有バッファに載っている状態がパフォーマンスの観点から望ましいと言えます。共有バッファサイズの設定値は物理メモリーサイズの4分の1程度が目安とされています。4GBのメモリーを搭載したマシンの場合は1GBとなります。あまりに大きな値を設定してしまうと、他のプロセスが使用するメモリーを圧迫してしまい、データベース全体として速度劣化を引き起こす可能性があります。・work_mem内部並び替えとハッシュテーブル操作が使用するメモリーサイズを指定します。BYによるソートやテーブルの結合、集約といった複雑な問い合わせを行う場合に使用されます。設定した値は同時に複数実行される問い合わせ毎に使用される為、実際に消費されるメモリーサイズは設定値の数倍になりますので、大きな値を設定するとメモリーが足りずにスワップが発生してしまう可能性があります。設定可能な最大値は物理メモリーサイズからshared_buffersのサイズを引いた値をmax_connectionsで割った値となります。・wal_buffersWALバッファサイズを設定します。(デフォルト値はshared_buffersの1/32のサイズ)WALバッファとはWALログをディスクに書き出すまで保持しておくバッファです。このバッファのサイズを大きく設定すれば、WALバッファが一杯になったのでディスクへ書き出す、という状態になることを減らすことができます。・checkpoint_segmentsチェックポイントの処理を実行するタイミングをWALセグメントの数で設定します。(デフォルト値は3)チェックポイントとはデータの挿入や変更の内容を保持しているWALログからディスクへ書き出す処理で、大量のディスクI/Oが発生する為、コストの高いものです。1セグメントのWALログファイルのサイズは16MBです。デフォルト値の3の場合は48MB分のWALログが溜まったタイミングでチェックポイントを実行することになります。この設定値を大きくしてディスクへ書き込む頻度を減らすことで性能を向上させることができます。その代わりにWALログファイルの数が増えることになりますので、ハードディスクには十分な空き容量が必要です。リカバリーを行う際にも時間がかかるようになります。トランザクションログ」に説明がありますので参照してください。・checkpoint_timeoutチェックポイントの処理を実行するタイミングを設定しますが、こちらは時間間隔で設定します。(デフォルト値は5m)デフォルト値の5分よりも長い間隔とすることで、上記のcheckpoint_segmentの値を大きくした場合と同様にディスクへの書き込み頻度を減らします。インデックスインデックス(索引)はデータベースへの問い合わせ性能を向上させるための方法です。データベースのテーブルを本に見立てて例に取ると、探したい単語が本の何ページにあるかを調べたいとき、インデックスがなければ1ページ目から最後のページまで調べなければなりませんが、インデックスがあれば何ページ目にあるかがわかるので順番に調べる必要はなく、目的のページに辿り着くまでの労力は随分と軽減されるでしょう。テーブルにも検索によく使われるであろう列にあらかじめインデックスを作成しておくことで検索性能を向上させることができます。これまでに作成したテーブルの中で最もレコード数の多いordersテーブルでも362件しかありませんので、インデックス有無の差をわかりやすく体感する為にここで1つテーブル"sales"を追加します。CREATE文は以下の通りです。NULL);100000件のサンプルデータを投入するSQLファイルを用意しましたので、テーブルを作ったら以下のファイルをダウンロードしてデータをロードしてください。※少々大きいのでzip圧縮してあります。3.データの準備」を参照してください。ossdb-sales.zipきちんとデータが入っているかを確認してみましょう。以下のSQL文を実行して件数を見てみます。sales;100000件のレコードが登録されていることがわかります。データの中身も少し見てみましょう。以下のSQL文を実行してください。10;このように表示されれば正常にデータが入っています。salesテーブルにserial型の列idを作成してありますので、データのロードによって連番を振られていることも確認できます。データが入ったので次に以下のSQL文を実行しておいてください。sales;※ANALYZEについては次回で取り上げます。ここではおまじないだと思って実行しておいてください。salesテーブルにインデックスを作る前の状態でWHERE句をつけた以下のSQLがどのように実行されるかをEXPLAINを使って見ておきましょう。EXPLAINをSQLの前に付与することで実行計画を見ることができます。※実行計画については次回詳しく見ていきますので、今回は赤枠で囲った部分、赤線を引いた部分に注目してください。idが1のレコードを抽出してみます。以下のSQLを実行してください。sales」とあります。これはsalesテーブルを順読みして検索しますよ、という意味です。この問い合わせのコストは1918と見積もられています。※コストの単位は秒ではありませんので注意してください。次にインデックスを追加して同じように見てみます。インデックス作成の基本的な構文は以下の通りです。(列名);他にも様々なパラメータやオプション、アクセスメソッドがあるので、用途に応じてより細かく指定することもできます。公式ドキュメントに詳細な記載がありますので興味のある方は参照してください。http://www.postgresql.jp/document/9.1/html/indexes.htmlsalesテーブルの列idに対して検索を行うので以下のSQL文でインデックスを作成します。(id);インデックスを作成しましたので先ほどと同じSQLを実行します。1;今度は赤枠部分の表示が変わりました。sales」とあり先ほど作成したインデックスが使われます。コストも大幅に削減されています。インデックス作成前では1918だったものが8.28にまで小さくなりました。今回は100000件から1件だけを抽出するという比較的インデックスの優位性が際立つケースでした。あらゆる検索においてこのような性能向上が得られるとは限りませんが、その効果は感じていただけたと思います。効果があるからといって無駄に多くのインデックスを作ってしまうと今度は登録・更新性能が劣化していき、ディスク容量も無駄に消費してしまいますので注意してください。最後に今回はPostgreSQLのインストール後にまず変更を検討すべき設定項目、テーブル設計と同時に検討しておきたいインデックスを紹介しました。いずれも運用後の早い段階から効いてくるものなので是非積極的に活用してください。次回は、今回最後に少し触れた実行計画についてもう少し詳しく解説します。運用を開始するとデータは増えていくものですが、それによってSQLの問い合わせが遅くなってしまった、といった場合にどのように手がかりを得るかというところにも触れていきたいと思います。Geospatial)ソフトウェアを活用し、地図やGPS等の位置情報を組み合わせたアプリケーションやサービスの設計・開発に従事。サービスプラットフォームがクラウドへ、利用シーンがモバイルへと広がりを見せる中、位置情報の更なる活用シーンを模索中。株式会社オークニー代表取締役2002年にオークニーを設立し、代表取締役に就任。Geospatial財団日本支部の代表者を務めるなど、FOSS4G(オープンソース地理空間ソフトウェア)の普及活動に取り組んでいる。メリット受験対策インタビューFAQ資料請求・お問い合わせプライバシーポリシーこの内容で送信します。よろしいですか?キャンセル。