D.Horiyama's output

内部構造から学ぶPostgreSQL 設計・運用計画の鉄則 ch16 パフォーマンス・チューニング (1/2)

OSS-DBPostgreSQL勉強メモ

出典: 

事象分析

PostgreSQLログの取得

  • パフォーマンス低下の前兆のログは必ずしもERRORやWARNINGではない
  • でも何でもかんでもログ出力すると負荷が大きい
  • サービス環境以外でログレベルを変える等の対策を検討する

テーブル統計情報の取得

  • システム、初めは元気だったが稼働しているうちに遅くなりがち

  • pg_statsに保存される統計情報を定期的に保存しておくとよい

    • 大したサイズではないので月次などで

クエリ統計情報の取得

  • pg_stat_user_functions
postgres=# SELECT * FROM pg_stat_user_functions;
 funcid | schemaname | funcname | calls | total_time | self_time 
--------+------------+----------+-------+------------+-----------
(0 rows)
  • pg_stat_statements
  • こちらは拡張モジュールなのでCREATE EXTENSIONで有効化する必要がある
CREATE EXTENSION pg_stat_statements;
postgres=# SELECT * FROM pg_stat_statements;
 userid | dbid  |       queryid        |                query                | calls |     total_time     |      min_time      |      max_time      |     mean_time      | stddev_time | rows | shared_blks_hit | shared_blks_read | shared_blks_dirtied | shared_blks_written | local_blks_hit | local_blks_read | local_blks_dirtied | local_blks_written | temp_blks_read | temp_blks_written | blk_read_time | blk_write_time 
--------+-------+----------------------+-------------------------------------+-------+--------------------+--------------------+--------------------+--------------------+-------------+------+-----------------+------------------+---------------------+---------------------+----------------+-----------------+--------------------+--------------------+----------------+-------------------+---------------+----------------
     10 | 13408 | -1739183385080879393 | create extension pg_stat_statements |     1 |             7.3282 |             7.3282 |             7.3282 |             7.3282 |           0 |    0 |             852 |              129 |                  53 |                   2 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
     10 | 13408 | -3725811799635485246 | SHOW shared_preload_libraries       |     1 |              0.011 |              0.011 |              0.011 |              0.011 |           0 |    0 |               0 |                0 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
     10 | 13408 | -7520291481456088499 | select count(*) from tbl            |     1 | 194.11110000000002 | 194.11110000000002 | 194.11110000000002 | 194.11110000000002 |           0 |    1 |               0 |            66372 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
     10 | 13408 |  1930089032433548407 | SELECT * FROM pg_stat_statements    |     1 |             0.0752 |             0.0752 |             0.0752 |             0.0752 |           0 |    2 |               0 |                0 |                   0 |                   0 |              0 |               0 |                  0 |                  0 |              0 |                 0 |             0 |              0
(4 rows)

システムリソース情報の取得

  • vmstat

    • virtual memory ,cpu

  • sar

    • いろいろ取れるやつ

事象分析の流れ

  1. 情報を取得する

    • 問題の再現方法

      • 発生時の状況

        • いつ誰がどのように
      • 設定ファイル
      • データベースのサイズ・運用期間

  2. 事象を分析する

    • 多角的に

      • ログなどのメッセージ
      • 統計情報
      • リソース情報

  3. 原因を絞り込む

    • S/W
    • H/W

  4. チューニングを実施して効果を測定する

    • 可能な限り1パラメータずつ変えようね

  5. 影響を評価する

    • 何かしら副作用がある

      • インデックスを作成したらメンテナンスコストが上がる
      • 統計情報の精度を上げたために取得に時間がかかる
      • etc.
    • システム全体として妥当であるか評価する

スケールアップ

  • 丁寧にチューニングするのではなく、札束で殴るという選択肢

【事例1】SSDに置き換えが有効なケース

  • PostgreSQLはHDDへのランダムアクセスが最もコストが高いと想定している

    • デフォルトの基準値は seq:random = 1:4
#seq_page_cost = 1.0			# measured on an arbitrary scale
#random_page_cost = 4.0			# same scale as above
  • SSDではseqとrandomで性能差がほとんどないので1:1に近づける

  • ランダムアクセスはいつ発生するか

    • インデックススキャン
    • パラレルスキャン
    • 宣言的パーティショニング
  • コスト基準値の設定をサボると、インデックススキャンのほうが実際には速いのに、ランダムアクセスのコストが過大に見積もられ、シーケンシャルスキャンが選択されてしまったりする

【事例2】メモリ容量の拡張が有効なケース

  • PostgreSQLではディスクI/Oが支配的

  • 共有バッファを大きくし、リレーションのデータを多く展開することで…

    • ディスクI/Oを抑えられる
    • メモリ管理のCPU負荷は上がる

  • LinuxならばHugePage設定もあわせてチューニングしよう

    • 1ページあたりの容量を増やす = ページテーブルは小さくなる = メモリ管理のCPU負荷抑えられる