性能要件

性能要件にスループットが入ることはよくあります。
スループットとは、何秒以内に処理できるや同時アクセス数が最大〇〇件までは対応できるなどです。
データベースで対応させるには、インデックスやパラメータのチューニングも考えられます。
限界がありますので、高スペックのサーバを使用したり、複数台のサーバで負荷分散をすることを検討するの考えられることです。

DBMSで検討するにはサーバの性能もそうですが、1箇所に集約して管理する集中型と複数の拠点に設置してデータを同期しながら管理する分散型があります。
集中型のことをシェアードエブリシングといい、分散型をシェアードナッシングといいます。
分散型は集中型に比べて以下メリットがあります。

[メリット]
・読み取りだけであれば、別々のデータベースが応答可能なため負荷分散・アクセス向上
・東京A店データベース、北海道B店データベースなどとしたときに北海道B店で問題が発生しても東京A店は継続可能
・ディスクがクラッシュしたなどでデータが参照できなくても別のデータベースで保持
しかし、分散型は以下デメリットもあります。
[デメリット]
・コスト増加
・書込遅延(2相コミット：データ書込時に各データベースに書き込めるかの確認が必要で書込み可能ならコミットするができない場合はロールバック) ・管理の複雑化(各データベースの障害発生・各データベースへの接続失敗などの処理)
また、分散型ではコストが増加して複雑になり、透過性(位置・移動・分割・重複・障害)も意識する必要があります。
分散型でいう透過性は、分散型データベースが複数のサーバになりその接続先、データ保存・重複格納・分割格納、障害発生を意識することなく利用者が利用できることです。

性能チューニング

性能をチューニングするには、ボトルネックを発見してそれに対して改善することです。
データベースでは、再編成と再構成があります。

[再編成]
レコードの追加・削除を繰り返すと、ファイルに断片的で使用できないデータ格納領域を再配置することです。

[再構成]
スキーマの変更、インデックス・カラム追加などを行うことです。

PostgreSQLはクエリ実行時、以下のフローで動作します。

• (1) クエリ処理：ユーザが作成したクエリを実行
• (2) パーサー：文法の誤りなどのチェック、構文解析
• (3) リライタ：定義に従って(解析結果)クエリ書換
• (4) プランナ：最適と思われるプランでクエリ作成
• (5) エグゼキュータ：クエリ実行

(4)の段階でインデックスを利用した方がよいと判断したら、インデックスを利用しますが、そうでない場合はシーケンシャルスキャンします。
また、インデックスが利用されないなどの場合は、以下が考えられます。
・統計情報が正しく更新されていない：統計情報更新(vaccum,analyz)
→大量のデータのインサートや削除などをしている場合は可能性大
・インデックス破損：インデックスを作成しなおす(reindex)

プランナーが利用するものを強制的に変更(pg_hint_plan)することも可能です。
(random_page_costの値が小さく設定またはeffective_cache_sizeを大きく設定)

これを確認するには、クエリの前に「EXPLAIN」をつけます。EXPLAINだけでは確認までしかせず、ANALYZEをつけると実際に実行までします。

これでも性能が改善しない場合、以下で改善しないかも確認します。

・ファイル断片化がされているため再編成
→アップデートが多いと一つのレコードが一つのファイルに収まらずポインターで別のファイルを参照している可能性があり、それの改善(VACCUM FULL)
・OS並びにPostgreSQLのメモリ設定チューニング
→JOIN,SORTなどはpostgresql.confメモリ設定を確認
設定で改善しない場合はコストも垣間見て以下を検討してみてください。

• (1) サーバのスペック向上：1台のサーバ処理改善
• (2) クラスタ化：参照速度改善
• (3) キーレンジ分割方式：参照処理改善
• (4) ストアドプロシージャ：参照先を増やすことで性能改善

データと取り込みであれば、insertでデータを挿入せずにCOPYを使用することで高速に取り込むことも可能です。
以下ならコミットするまでログに書きません。
begin;
create table test ...;
copy test from...;
commit;

また、postgres.confで以下を修正することで改善します。

• ・maintenance_work_memを増加
• ・chheckpointsegmentsを増加
• ・wal_levelをminimal
• ・archive_modeを無効
• ・fsync = off
• ・同期書き込みの無効化
• ・非同期コミットをOFFにするsynchronous_commit = off
• ・WALにファイルが書かれなくてもユーザに結果を返す

性能計測

データベースで性能を計測するにあたり、実行クエリが期待通りの秒数で完了していないことを確認(ベンチマーク)したり、サーバのリソースが十分であるかを確認します。

pgAdminのダッシュボードでPosstgreSQLのセッション数やトランザクション数を確認できます。

サーバのリソースが不足していれば性能はでません。
運用していてCPUの使用率が高くなってなかったり、メモリが不足してしまいディスクを利用する問題(スラッシング)が発生していなかったりするなど運用していてサーバのスペックが十分であるか確認します。
何がボトルネックになっているのかが重要になります。(CPU使用率が高いがメモリが不足しているのが原因でメモリがボトルネックなど)
全ての性能を向上すれば性能はあげられるかもしれませんが、不要なものをあげてもただのコストアップにしかならないです。
また、サーバのリソースが不足していてもPostgreSQLの設定で改善できる場合もあります。
それは、チューニングで記載します。
OSがLinuxですが、以下が確認コマンドです。

[確認コマンド]

• ・top　プロセスごとの状況
• ・ps　プロセスごとの状況
• ・sar　システム統計情報
• ・uptime　ロードアベレージ
• ・iostat　ディスク不可
• ・mpstat　マルチCPUが正常に稼働しているか
• ・lsof　ファイルを開きすぎていないか
• ・netstat　ネットワークの接続状況

一般的に書き込みバッファを持つディスク装置と大容量の仮想メモリを割り当てたOSを使用すれば性能は向上します。

チューニングに記載しますが、クエリのベンチマークをPosstgreSQLで計測するには「EXPLAIN」を利用します。
EXPLAINを利用するとどれぐらいの件数がどれぐらいの速度で表示できるか確認できます。
また、チェックポイント発生状況、キャッシュヒット率、デッドロックなど

インデックス(索引)

インデックスとは辞書でいうところの索引にあたり目的のデータを高速に探せるようにするためのものです。
必ず使わなくてはいけないものではありませんが、検索の性能改善では利用するのが好ましいです。
データを特定するためのキー（インデックス）として登録することでデータを効率よく利用できます。

インデックスはカーディナリティが高い場合に有効で、カーディナリティ高いとは会員番号・名前などたくさんの種類があるデータでカーディナリティ低いとは性別・真偽値など少ない種類のデータです。

• ・主キー(プライマリキー)インデックス

  プライマリキーに対してインデックスを設定します。PostgreSQLではプライマリキーを設定すると自動でインデックスが設定されます。

• ・一意(ユニーク)インデックス

  ユニークになる値(同じ値が許されない)に対してインデックスを設定します。PostgreSQLでは一意性制約を設定すると自動でインデックスが設定されます。

• ・非ユニークインデックス：

  非ユニークになる値(同じ値が許される)に対してインデックスを設定します。複数のレコードを抽出する性能の改善が見込めます。外部キーなどで結合する場合に参照する値として利用されます。

• ・部分インデックス

  条件に応じてインデックスを設定します。1から10はインデックスを張らないが、11から20は索引探索で表示したいなどの場合で利用します。

• ・クラスタインデックス

  指定した列をソートしながら保存していきます。範囲をまとめて検索したい場合に使用するものです。
  例えば、問題番号の10～20を高速に取得したいなどです。一つのテーブルでは1回のクラスタ化しかできません。
  PostgreSQLでは、「CLUSTER テーブル名 USING インデックス名」でクラスタ化します。

  クラスタインデックスでは、データは索引に基づいて、列の値ごとに異なるページに格納されます。
  同じ列の値を持つ行は、同じページに連続して配置されます。
  データを取得時には、対象となる行数を1ページに含まれる平均行数で割り、その数のページを読み込みます。

• ・非クラスタインデックス

  クラスタインデックスを指定していない場合、そのインデックスは非クラスタインデックスといいます。
  また、テーブルのデータを参照せず、インデックスだけで処理が完了するインデックスをカバリングインデックスといいます。
  PostgreSQLの標準インデックスとしてはB⁺木インデックスが使われます。このB⁺木インデックスは、カーディナリティ（データの多様性）が高い場合に有効です。
  カーディナリティが高いとは、テーブル内のデータが多くの異なる値を持っている状態を指します。

  非クラスタインデックスでは、データ行は各ページにランダムに均等に分布しているため、特定のページに偏りがありません。
  このため、ページのバッファヒット率は0%と仮定されます。
  対象行を読み込む際には、対象行が存在するすべてのページを順次読み込む必要があります。

インデックスが利用される場合は、全てのページを読み込む必要はなく必要なページだけを読み込みますので探索が早く読み込むページ数が少なくなりメモリの削減につながります。
インデックスが利用されない検索の場合、全行を一から総なめするシーケンシャルスキャンが利用されます。
シーケンシャルスキャンとは、全データを上から順番に検索します。
そのため、最悪の場合は検索しているテーブルの前ページを読み込む必要が発生してしまい、無駄なメモリを利用することが考えられます。

但し、インデックスを追加すればデータとは別にインデックスのデータが必要になり、データ量が増加してInsert・Updateなどの性能が低下することも注意が必要です。

PostgreSQLにはHOT(Heap Only Tuple)があり、UPDATE時にインデックスの更新は行わずにポイント参照をすることで更新性能を向上させます。
HOTが有効になるのは、ページ内に空き領域(FILLFACTER)が必要で、削除してからの追加や大量の更新などでは動作しませんので注意が必要です。

データベーススペシャリストでは、主索引と副次索引と複合索引と呼ばれる索引があります。
主索引は主キーに対して付けるインデックスでキー順にページが読まれて、副次索引は主キー以外に対して付けるインデックスで行をランダムなページで読みます。
複合索引は複数の列に付けるインデックスです。

インデックスといっても種類があり、B⁺木インデックスが一般的ですが、次にインデックスの種類を記載します。

• ・B⁺木インデックス

  log x →木構造でデータを持ち、最下層のリーフと呼ばれるノードが実データのポインタを保持します。最上位であるルートからノードを順に辿ってデータを検索します。(カーディナリティが高い場合に有効でBETWEENを用いた範囲指定検索などで効果的)

• ・ビットマップインデックス

  カラムに対して、発生する値ごとにビットマップを作成してデータを検索します。(カーディナリティが低い場合に有効でAND操作やOR操作だけで行える検索やNOTを用いた否定検索など効果的)

• ・ハッシュインデックス：キーとデータを関連付けしてキーを指定しデータを検索します。(ユニークな値を検索するのに効果的)