勉強日記

チラ裏

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

OSS-DB PostgreSQL 勉強メモ

gihyo.jp

事象分析

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負荷抑えられる