(Microsoft PowerPoint _JPUG\216d\221g\202\335\225\252\211\310\211\357_v1.1.ppt)

Size: px

Start display at page:

Download "(Microsoft PowerPoint _JPUG\216d\221g\202\335\225\252\211\310\211\357_v1.1.ppt)"

ときひろなが
5 years ago
Views:

1 PostgreSQL のパラメータチューニングについて JPUG 仕組み分科会勉強会 NTT OSS センタ笠原辰仁

2 アジェンダ本日のアジェンダ DBMS のチューニングについてパラメータチューニングに際してチューニングを始める前に変更しておきたいパラメータ一覧その他チューニングと対になるもの 2

3 はじめに本日の趣旨 PostgreSQL を安心して使うためにどのパラメータを触ればよいのか? どういう考え方で変更値を決めればよいのか? 上記を ( 簡単に ) ざっくりとお伝えします凝ったパラメータの話は別途に性能問題で困ったというケースのいわゆるトラブルシューティングでのチューニングの話も別途にただし今回の話はトラブルを避ける意味もあるのでプレシューティングという位置づけになると思います 3

4 DBMS のチューニングチューニングには様々なアプローチがありますハードウェアレベルミドルウェアレベル論理 / 物理的なスキーマ設計レベル AP/SQL レベルパラメータレベル CPU メモリストレージをスケールアップ RAID10 vs RAID5 コネクションプーリングで接続コスト軽減ロードバランサ / レプリケーションで負荷分散テーブルスペースで IO 負荷分散巨大なテーブルはパーティショニングテーブルの垂直分割 (TOAST 回避 ) 関数インデックス部分インデックスの利用 LIKE 句ではなく全文検索の活用 work_mem 調整でソート / ハッシュ処理の高速化 *_page_cost や enable_* で実行計画の改善本日はパラメータに関するチューニングのお話 4

5 パラメータチューニングに際して (1/2) 最近の PostgreSQL はチューニング対象となるパラメータが減少パラメータ数的には機能追加に伴い増加中 103(ver7.4) 119(ver8.0) 146(ver8.1) 154(ver8.2) 167(ver8.3) 169(ver8.4) ただし昔に比べると自動で最適化してくれるものが増加特に bgwriter や free_space_map などの裏方の常駐プロセスで扱うパラメータ pgtune といった支援ツールも利用可能簡単な入力要素 ( メモリサイズ使い方コネクション数 ) でチューニングした設定ファイルを出力変更すべき / しておくと良いパラメータは実はそれほど多くありません 100 以上ある内のせいぜい 20 未満くらい 5

6 ( 参考 )pgtune による変更メモリ 8GB 接続数 80 での各用途別の値 ( 空白は変更なし ) パラメータ dw mixed oltp web desktop default_statistics_target maintenance_work_mem 960MB 480MB 480MB 480MB 480MB constraint_exclusion on on checkpoint_completion_target effective_cache_size 5632MB 5632MB 5632MB 5632MB 1920MB work_mem 48MB 48MB 96MB 96MB 16MB wal_buffers 32MB 8MB 8MB 4MB 1536kB checkpoint_segments shared_buffers 1920MB 1920MB 1920MB 1920MB 480MB max_connections 変更点は 10 項目 # work_mem について oltp web 多すぎ dw は少なすぎ? oltp と web ではスワップが心配 6

7 パラメータチューニングに際して (2/2) パラメータは以下の様に変更すると良いです必ず変えておく必要のあるものを変更問題の発生を確実に防ぐため問題があった場合に有用な情報を取得しておくためメンテナンスや監視に関わる箇所変更するのが面倒なもの再起動を要するなど機械的に変更できるもの HW リソースや DB サイズを考慮して決められるものここまでこなしておけば大抵のケースでは OK( のはず ) 以下はより最適化を目指すために用途によってはより効果的になるものを変更 7

8 チューニングを始める前に HW や DBMS の状況を確認しておきましょうパラメータ変更の際の基準になるためです HW CPU コア数は? メモリサイズは?(DBMS で占有できるサイズは?) ストレージの能力容量スピンドル数キャッシュサイズ DBMS DB の合計サイズは? その内アクティブな ( アクセス頻度の高い ) 領域の割合は? DB 内にあるテーブル / インデックスの個数やそれぞれのサイズは? 特に大きなものは把握しておきましょうどのようなワークロードが主ですか? 更新が多い? ほとんど参照? ボトルネックになりそうなのは? CPU バウンドになりそう?I/O バウンドになりそう? 監視しておくべき情報は? スロークエリは把握する必要はある? ユーザ別に知りたい情報が異なる? 8

9 変更しておきたいパラメータ一覧 ( 対象 ) パラメータ名重要度 ( ) shared_buffers max_fsm_pages / relations log_destination / redirect_stderr(logging_collector) log_min_duration_statement checkpoint_segments 重要度 ( ) superuser_reserved_connections effective_cache_size stats_block_level / row_level autovacuum_max_workers autovacuum_vacuum_cost_limit 重要度 ( ) work_mem bgwriter_all_percent / pages random_page_cost default_statistics_target 対象 ver (all) (~8.3) (8.1~) (8.1~) (all) (all) (all) (~8.2) (8.3~) (8.3~) (all) (~8.2) (~8.2) (8.1~) お勧め設定値 ( 方針 ) 搭載メモリの 10~20% pages:db サイズ (byte)*0.2/8192 relations:db クラスタ内のテーブル / インデックス総数 + DB 数 * 160 (destination, redirect_stderr) = ( syslog, off) or ( stderr, on) or ( syslog, stderr, on ) 必ずどこかにログを残す設定に! 許容できる SQL レスポンス時間の上限 ( 更新処理が多いほど多めに ) 管理者権限で同時に実施する処理数 + 1 搭載メモリの 50% - 60% on DB サイズの 20% 以上のサイズのテーブル数程度で様子見 trace_sort EXPLAIN ANALYZE 等で調整 percent : 5, pages : 50~ ( 更新処理が多いほど多めに ) 3.0~2.0 大きなテーブルへの LIKE がある場合は 100 程度に ( ロングトランザクションに注意 ) 対象 ver はそれぞれのパラメータについて変更しておくと良いバージョンを示していますそのパラメータを扱えるバージョンという意味ではないです 9

10 shared_buffers (ALL) 何をする? PostgreSQL の共有メモリのサイズを調節値はどうする? 物理メモリの 10-20% 程度に設定設定次第で問題が起こる? ( 小さすぎると ) キャッシュヒット率の低下 ( 大きすぎると ) チェックポイントスパイク発生 PostgreSQL がディスクから読み込んだデータは shared_buffers に置かれる他カーネルのキャッシュ領域にも置かれる shared_ buffers カーネルページキャッシュメモリ領域 shared_buffers がある程度小さくてもカーネルのキャッシュで救える shared_buffers カーネルページキャッシュ shared_ buffers カーネルページキャッシュ DB checkpoint 処理で大量のデータを書き出す必要がある 10

11 max_fsm_pages / relations (~8.3) 何をする? VACUUM で掃除をした不要領域の再利用マップのサイズ調節値はどうする? pages : DB サイズの 20%(byte) / 8192 以上 (VACUUM されるまで 20% 程度更新される場合 ) relations : DB クラスタ内のテーブル + インデックス + DB*160( システムテーブル分 ) 設定次第で問題が起こる? ( 小さすぎると )VACUUM をしてても DB サイズが増加しつづける VACUUM は不要領域を見つけるとそこを掃除して再利用可能な領域として FSM(FreeSpaceMap) にその箇所を記録する VACUUM テーブルゴミ FSM ゴミ max_fsm_relations 分のテーブル / インデックス max_fsm_pages 分のページ領域を記録できる (8.4 では必要な分だけ自動で確保する ) FSM が足りている FSM が不足している再利用 OK 空再利用 OK 以降の更新では再利用可能な箇所にデータが格納される空単に空の領域がテーブル内に残存する無駄な領域なのでキャッシュヒット率の低下や DB の肥大化の原因に管理漏れがないようテーブル / インデックス数の max_fsm_relations と更新されそうなページ数の max_fsm_pages を設定しましょうもし空の領域が多すぎる場合は CLUSTER や VACUUM FULL でサイズ削減を! 11

12 log_destination / redirect_stderr / log_min_duration_statement(all) 何をする? log_destination:postgresql のログの出力先調節 log_stderr: 標準出力エラーのリダイレクト調節 log_min_duration_statement : 規定時間以上かかった SQL の出力調節値はどうする? syslog に出力する場合 :log_destination = syslog ユーザファイルに出力する場合 :log_destination = stderr redirect_stderr = on syslog とユーザファイルに出力する場合 : :log_destination = syslog, stderr redirect_stderr = on log_min_duration_statement : 許容できる SQL レスポンス時間 ( ただし syslog には出力しないほうが良い ) 設定次第で問題が起こる? 有用な情報が取得できないログ情報は必ず取りましょう syslog の他ユーザの指定したファイルにも書き出せますログに出力する情報を色々と制御できますが有用なのは以下のものですお勧め! log_min_duration_statement : スロークエリ ( 遅いSQL) を把握できますあると便利 log_lock_waits : deadlock_timeout 以上の時間ロック待ちしたSQLを把握できます log_error_verbosity : ERRORの発生したソースコード位置 SQLエラーコードが把握できますなお大量のログを syslog に出すとそこがボトルネックになります特に検証時などの SQL を大量にロギングしたい場合などはユーザファイルに出すと良いでしょう 12

13 checkpoint_segments(all) 何をする? WAL セグメントの数を調節値はどうする? 16 ~ 64 以上に設定 ( 更新処理が多いほど多めに ) 設定次第で問題が起こる? ( 小さすぎると ) チェックポイントスパイクが発生トランザクションログ PostgreSQL pg_xlog/ WAL WAL WAL checkpoint_setments の値が小さいとすぐに checkpoint が頻発するデフォルトの 3 では小さすぎることが多いので増やしておくと吉特に更新処理が多い場合は必須 checkpoints are occurring too frequently というログが出たら大抵はこれが原因ですトランザクションログを WAL セグメントにどんどん書き込みセグメントが一杯になると次のセグメントへ書き出す checkpoint_segments 分のセグメントが一杯になったら checkpoint 処理が走るなお WAL セグメント (1 個 16MB) は最大で checkpoint_segments * のサイズになるのでその分のストレージは確保しましょう 13

14 effective_cache_size(all) 何をする? PostgreSQL 内部でのキャッシュサイズの調節値はどうする? 物理メモリの 1/2 ~ 2/3 に設定設定次第で問題が起こる? ( 小さすぎると ) インデックススキャンを実施してくれない PostgreSQL がインデックススキャンにかかるコストを内部で算出する際のパラメータとして使用していますこのパラメータ自体は増やしても別にリソースを消費するわけではありません古い HW を基準に初期値が定められているため非常に低い値です ( 最近は多めに取ってくれるようになりましたがそれでもまだ低いです ) とりあえずメモリ量の 50~60% までは引き上げてきましょう 14

15 superuser_reserved_connections(all) 何をする? 管理者 ( スーパーユーザ ) 用に確保する接続数の調節値はどうする? 管理者権限で同時に実施する処理数 + 1( 予備 ) 設定次第で問題が起こる? ( 足りないと ) 管理者権限で接続できず重要な処理が実施できない max_connections = 管理者権限での接続 (superuser_reserved_connections) + その他接続数です十分な数の superuser_reserved_connections を確保しておかないといざという時に大事な処理ができませんなお 8.2 以前の autovacuum は暗黙的に管理者権限の接続を使用してますので注意 15

16 stat_command_string / stat_block_level / row_level (~8.2) 何をする? DB のアクティビティ ( 実施中の SQL やスキャン数など ) を捕捉するかどうか上記を稼動統計情報と呼びます値はどうする? on 設定次第で問題が起こる? (off だと ) 有用な稼動統計情報が取得できない PostgreSQL では stats collector というプロセスが DB 内の様々なアクティビティ情報を収集し記録していますこれらの情報は pg_stat_* というシステムビューで提供され非常に有用です 8.2 以前では各テーブルやインデックスへのアクセス回数や読み取られたブロック数を記録するかどうかのオプションが off になっていますぜひ on にしておきましょう性能的にも数 % のオーバーヘッドがかかる程度ですなお 8.2 で autovacuum を使う場合は stats_low_level が on になっている必要があります 8.3 からは track_counts というパラメータになっておりデフォルトで on です 16

17 autovacuum_max_workers(8.3~) 何をする? 同時に動く autovacuum プロセス数の調節値はどうする? 大きな (DB の 2 割近いサイズ ) テーブルの数 + 1 設定次第で問題が起こる? ( 小さすぎると )autovacuum を必要とするテーブルが待たされる worker が 1 の場合 autovacuum worker が 2~ の場合 autovacuum autovacuum テーブル A 大きなテーブルの VACUUM の間後続の VACUUM を必要としているテーブルが待たせる ( ゴミが溜まってしまう ) テーブル A テーブル C テーブル B VACUUM の必要があるテーブルに並列で処理が実施できる待たされるテーブルが出ないように大きいテーブル +1( 小さいテーブル用 ) の worker を用意しておくと良いです 17

18 autovacuum_vacuum_cost_limit(8.3~) 何をする? autovacuum の遅延の有無と遅延時間の調節値はどうする? 200 程度に設定 ( ディスク性能が高ければ多めに ) 設定次第で問題が起こる? (-1 のままだと )autovacuum がリソースを喰ってしまう ( 大きすぎると )autovacuum の遅延がされない PostgreSQL 内部ではもし autovacuum( または VACUUM) の遅延が有効になっていた場合バッファを読んだり書き換えたりする度にそれらの処理に予め相当するコストをカウントアップしていく具体的にはバッファ上のページ読み込みの際キャッシュヒットした = vacuum_cost_page_hit (def=1) バッファ上のページ読み込みの際キャッシュヒットしなかった = vacuum_cost_page_miss (def=10) バッファ上のページを書き換えた = vacuum_cost_page_ditry (def=20) これが cost_limit を超えると autovacuum( または VACUUM) は cost_delay 時間分休止する autovacuum が実施される契機は不定サービスの繁忙期に autovacuum が実施されるとマシンリソースの一部が取られてしまうそのため処理の忙しい時は autovacuum をゆっくり行う設定をしてシステムの本業にリソースを与えられるようにすると良い 18

19 変えておくと良いパラメータ一覧 ( 対象 ) パラメータ名 maintenance_work_mem work_mem random_page_cost bgwriter_all_percent / pages default_statistics_target constraint_exclusion log_autovacuum_min_duration 対象 ver (all) (all) (~8.2) (~8.2) (8.1~) (8.1~) (8.3~) 役割 / お勧め設定値 ( 方針 ) VACUUM や REINDEX 処理用の作業メモリ領域の調節 - 128MB 程度 ~( 大きなテーブルがあるほど多めに ) ソートやハッシュ処理用の作業メモリ領域の - trace_sort や EXPLAIN ANALYZE(8.3~) を見ながら調節 PostgreSQL 内部でのランダムアクセスのコスト調節程度まで下げる ( インデックススキャンを使ってくれない場合 ) バックグラウンドライターによるダーティバッファの書き出し量調節 - percent:5~ pages:50~ 程度で様子見 ( 更新処理が多いほど多めに ) ANALYZE 時のサンプリング数の調節 - 行数の多いテーブルへの LIKE がある場合は 100 程度に CHECK 制約を考慮して検索対象とするテーブルを絞りこむ - パーティション機能を使用している場合は on (8.4 では partition) に規定時間以上かかった autovacuum のロギング調節 - 10min 程度に設定し時間のかかった autovacuum の処理内容を把握 19

20 ( 参考 )trace_sort trace_sort は隠し? 設定パラメータ postgresql.conf に trace_sort = on と記述 sort の処理内容をログに出力できます LOG: begin tuple sort: nkeys = 1, workmem = 1024, randomaccess = t LOG: switching to external sort: CPU 0.00s/0.00u sec elapsed 0.00 sec LOG: performsort starting: CPU 0.01s/0.01u sec elapsed 0.05 sec LOG: finished writing run 1: CPU 0.01s/0.01u sec elapsed 0.06 sec LOG: finished writing final run 2: CPU 0.02s/0.02u sec elapsed 0.09 sec LOG: finished merge step: CPU 0.02s/0.02u sec elapsed 0.10 sec LOG: performsort done: CPU 0.02s/0.02u sec elapsed 0.10 sec LOG: external sort ended, 103 disk blocks used: CPU 0.05s/0.03u sec elapsed 0.16 sec 8.4 は EXPLAIN ANALYZE でソート処理内容を確認可能 =# EXPLAIN ANALYZE SELECT * FROM test2 ORDER BY 1; QUERY PLAN ディスクの 103 ブロックを使用 : ソート処理のために work_mem を増やす必要がある Sort (cost= rows=10 width=4) (actual time= rows=10 loops=1) Sort Key: id Sort Method: quicksort Memory: 25kB ( メモリ不足の時は external sort Disk: nnnnnnkb) -> Seq Scan on test2 (cost= rows=10 width=4) (actual time= rows =10 loops=1) Total runtime: ms (5 rows) 20

21 その他以下の点もチェックしましょうそもそも動かない or 問題発生の可能性がカーネルパラメータ shmmax shmall shared_buffers より大きくしないと FATAL エラーで起動できません Linux であれば /etc/sysctl.conf/ に設定を記述します max_connections * work_mem + shared_buffers 上記が物理メモリ量を大きく超えるとスワップの危険カーネルの消費するメモリ (1GB 程度 ) くらいは余るように! pg_hba.conf アクセス制御用のファイルリモートからのアクセスを許可していますか? 21

22 その他 TIPS パラメータの一部はユーザや DB 毎に設定値を与えることが可能です例 1: あるユーザのみ実施した SQL を全て記録したい ALTER USER some_user SET log_statement TO 'all ; 例 2: ある DB のみ 1 分以上かかった SQL を強制的に終了させたい ALTER DATABASE some_db SET statement_timeout TO '60s'; 変更の確認例 =# SELECT usename, useconfig FROM pg_user; usename useconfig some_user {log_statement=all} =# SELECT datname, datconfig FROM pg_database ; datname datconfig some_db {statement_timeout=60s} パラメータの一部は SET 文で動的に変更することもできます例 1: ある処理の時だけ work_mem を大きくしたい psql some_db =# SET work_mem TO 128MB ; 22

23 チューニングと対になるものチューニングの効果を観測できる手段を持っておきましょう OS で取得できるリソース情報 vmstat iostat sar ps top. 手軽に取得できます PostgreSQL で取得できるリソース情報 pg_stat_*. PostgreSQL の活動状況が動的に把握できます EXPLAIN / EXPLAIN ANALYZE SQL の実行計画が把握できますログ情報必須ですその他ベンチマーク pgbench... プロファイラ Oprofile Dtrace systemtap. 23

24 おわり本日に紹介しきれなかったパラメータについては lets postgres などで解説 or 次回以降の勉強会で! オタワで使用された資料が良い参考になりますご清聴ありがとうございました 24

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗

PostgreSQL SQL チューニング入門 ~ Explaining Explain より ~ 2012 年 11 月 30 日株式会社アシスト田中健一朗アジェンダ 1.EXPLAIN とは 2. 表アクセスの基本 3. 結合の基本 4. 統計情報とは 5.EXPLAIN コマンド 6. 問題解決例 7. まとめ 2 1.EXPLAIN とは実行計画とは - 目的地は 1 つでもアクセス方法は複数