スライド 1

Size: px

Start display at page:

Download "スライド 1"

やすもりしまむね
5 years ago
Views:

OSS-DB Exam Gold 技術解説無料セミナー 2018/12/2 SRA OSS, Inc.

2 目次 OSS-DB 技術者認定資格 PostgreSQL 内部構造編メモリ / プロセス / ストレージストレージ上の物理配置データの読み取り / 書き込み PostgreSQL 性能分析編稼働状況の確認実行時統計情報実行計画 PostgreSQLパラメータチューニング編共有バッファの設定ワークメモリメンテナンスワークメモリの設定その他の設定 SRA OSS, Inc. Japan All rights reserved. 2

3 OSS-DB 技術者認定資格オープンソースデータベース (OSS-DB) に関する技術と知識を認定する IT 技術者認定データベースシステムの設計開発導入運用ができる技術者大規模データベースシステムの改善運用管理コンサルティングができる技術者 OSS-DB 技術者認定資格の必要性商用 /OSS を問わず様々な RDBMS の知識を持ちデータベースの構築運用ができるまたは顧客に最適なデータベースを提案できる技術者が求められている SRA OSS, Inc. Japan All rights reserved. 3

4 OSS-DB Gold 認定エンジニア大規模データベースシステムの改善運用管理コンサルティングができる技術者 RDBMS と SQL に関する知識を有するオープンソースデータベースに関する深い知識を有するオープンソースを利用して大規模なデータベースの運用管理ができるオープンソースを利用して大規模なデータベースの開発を行う事ができる PostgreSQL などの OSS-DB の内部構造を熟知している PostgreSQL などの OSS-DB の利用方法やデータベースの状態を検証してパフォーマンスチューニングをすることができる PostgreSQL などの OSS-DB の利用方法やデータベースの状態を検証してトラブルシューティングをすることができる SRA OSS, Inc. Japan All rights reserved. 4

5 OSS-DB Exam Gold 出題範囲運用管理 (30%) データベースサーバ構築運用管理コマンド全般データベースの構造ホットスタンバイ運用性能監視 (30%) アクセス統計情報テーブル / カラム統計情報クエリ実行計画その他の性能監視パフォーマンスチューニング (20%) 性能に関するパラメータチューニングの実施障害対応 (20%) 起こりうる障害のパターン破損クラスタ復旧ホットスタンバイ復旧 OSS-DB Exam は PostgreSQL 9.0 以上を基準の RDBMS として採用 2016 年現在 9.4 まで対応 SRA OSS, Inc. Japan All rights reserved. 5

7 メモリ / プロセス / ストレージ共有プロセス毎に閾値として利用メモリ共有バッファメンテナンスワークメモリワークメモリワークメモリワークメモリ WAL バッファワークメモリワークメモリ postmaster postgres postgres postgres プロセス postgres stats collector autovacuum worker checkpointer logger autovacuum launcher writer データベースクラスタ wal writer archiver wal sender wal receiver バックアップアーカイブログストレージデータベース WAL その他一時ファイル SRA OSS, Inc. Japan All rights reserved. 7

8 メモリ / プロセス / ストレージ (1) 常駐プロセス必ず起動するプロセス postmaster stats collector wal writer writer checkpointer PostgreSQL の親プロセス接続を待ち受けるプロセス統計情報を収集するプロセス WAL(Write-Ahead Log) の書き込みを行うプロセス共有バッファ変更内容をディスクに書き出すプロセスチェックポイント処理を行うプロセス $ ps xf PID TTY STAT TIME COMMAND ( 省略 ) pts/1 S 0:00 /usr/pgsql-9.4/bin/postgres 64723? Ss 0:00 _ postgres: logger process 64725? Ss 0:00 _ postgres: checkpointer process 64726? Ss 0:00 _ postgres: writer process 64727? Ss 0:00 _ postgres: wal writer process 64728? Ss 0:00 _ postgres: autovacuum launcher process 64729? Ss 0:00 _ postgres: stats collector process SRA OSS, Inc. Japan All rights reserved. 8

9 メモリ / プロセス / ストレージ (2) プロセスと設定設定によって起動するプロセス archiver logger autovacuum launcher wal sender wal receiver WAL をアーカイブするプロセス (archive_mode = on) PostgreSQL のログをファイルに書き出すプロセス (logging_collector = on) データの更新を監視し autovacuum worker プロセスを起動するプロセス (autovacuum = on) WAL をスタンバイサーバへ転送するプロセス WAL をマスターサーバから受信するプロセス常駐せず都度起動するプロセス postgres autovacuum worker 個々のクライアントの要求を処理するプロセス (max_connections = 100) 自動バキュームを実行するプロセス (autovacuum_max_workers = 3) SRA OSS, Inc. Japan All rights reserved. 9

10 メモリ / プロセス / ストレージ (3) メモリの種類共有メモリとして確保共有バッファ (shared_buffers = 128MB) WAL バッファ (wal_buffers = -1) データベースの読み書きに使われる共有メモリ実メモリが 1GB 以上の場合は 1/4 程度 Windows 上では 512MB 以内に設定 WAL 書き込みに使われる共有メモリデフォルト -1 は shared_buffers の 1/32 が自動設定 WAL ファイルのサイズと同じ 16MB が最大値 [postgres]$ ipcs -m 共有メモリセグメントキー shmid 所有者権限バイト nattch 状態 ( 省略 ) 0x0052e2c postgres 子プロセス毎に確保するメモリ work memory (work_mem = 4MB) maintenance work memory (maintenance_work_mem = 64MB) ソート処理やハッシュ作成処理に使われるメモリ不足すると一時ファイルを作成して処理する max_connenctions との関係を考慮バキュームやインデックス作成に使われるメモリ autovacuum_max_workers との関係を考慮 SRA OSS, Inc. Japan All rights reserved. 10

12 ストレージ上の物理配置 (1) データベースクラスタデータベースクラスタ ($PGDATA ディレクトリ ) PG_VERSION base $ ls $PGDATA global pg_clog pg_dynshmem pg_hba.conf pg_ident.conf pg_log pg_logical ( 省略 ) pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_xlog postgresql.auto.conf postgresql.conf postmaster.opts postmaster.pid PG_VERSION pg_logical pg_subtrans base pg_multixact pg_tblspc global pg_notify pg_twophase pg_clog pg_replslot pg_xlog pg_dynshmem pg_serial postgresql.auto.conf pg_hba.conf pg_snapshots postgresql.conf pg_ident.conf pg_stat postmaster.opts pg_log pg_stat_tmp postmaster.pid 設定ファイル postgresql.conf - PostgreSQL の設定ファイル pg_hba.conf - クライアント認証 SRA OSS, Inc. Japan All rights reserved. 12

13 ストレージ上の物理配置 (2) データベースの物理配置データベースクラスタ PG_VERSION base global pg_clog pg_dynshmem pg_hba.conf 1/ pg_ident.conf 13051/ pg_log 13056/ pg_logical 16384/ ( 省略 ) pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_xlog postgresql.auto.conf postgresql.conf postmaster.opts postmaster.pid base ディレクトリ各データベースに対応したディレクトリ配置 oid2name でデータベース名を特定 $ oid2name All databases: Oid Database Name Tablespace postgres pg_default template0 pg_default 1 template1 pg_default test pg_default SRA OSS, Inc. Japan All rights reserved. 13

14 ストレージ上の物理配置 (3) テーブルファイルの物理配置データベースクラスタ PG_VERSION base global pg_clog pg_dynshmem pg_hba.conf pg_ident.conf pg_log pg_logical ( 省略 ) pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_xlog postgresql.auto.conf postgresql.conf postmaster.opts postmaster.pid データベースディレクトリテーブルインデックス等のデータファイルが格納される (8kB 単位 ) oid2name でテーブルファイル名を特定 1/ 13051/ 13056/ 16384/ : _fsm 16387_vm : pgsql_tmp/ $ oid2name -d test -i From database "test": Filenode Table Name t t1_id_seq t1_pkey pgsql_tmp ディレクトリには work_mem が不足した場合に一時ファイルが作成される SRA OSS, Inc. Japan All rights reserved. 14

15 ストレージ上の物理配置 (4) タプルの物理配置タプル ( 行データ ) は 8kB のブロック単位でファイルに格納 8kB 8kB 0 ページ 1 ページ PageHeaderData pd_linp[1] pd_linp[2] pd_linp[3] ctid = (1, 1) pd_linp[0] ctid = (1, 2) ctid = (1, 3) pd_linp[n] ctid = (1, 4) ctid = (1, n + 1) 8kB 2 ページフリースペース 8kB n ページタプル 3 タプル 2 タプル 1 タプル n 特殊データ =# SELECT *, ctid FROM t1 WHERE id = 10; id val ctid d3d a d38e6d163e820 (0,10) (1 row) SRA OSS, Inc. Japan All rights reserved. 15

16 ストレージ上の物理配置 (5) WAL ファイルの物理配置データベースクラスタ PG_VERSION base global pg_clog pg_dynshmem pg_hba.conf pg_ident.conf pg_log pg_logical ( 省略 ) pg_stat_tmp pg_subtrans pg_tblspc pg_twophase pg_xlog postgresql.auto.conf postgresql.conf postmaster.opts postmaster.pid WAL( ログ先行書き込み ) ファイルトランザクションログとも呼ばれる 1 ファイル 16MB $ ls $PGDATA/pg_xlog A B archive_status SRA OSS, Inc. Japan All rights reserved. 16

19 データ読み取り / 書き込み (2) トランザクションログデータ変更は WAL バッファと共有バッファに行うコミット時に WAL ファイルに書き込み変更 01 変更 02 変更 03 (3) コミットデータ変更 (1) WAL バッファ WAL ファイル (pg_xlog) (2) ページ対応するブロックとページの内容が一致しなくなるクラッシュしたら WAL ファイルから復旧ブロック共有バッファテーブルファイル SRA OSS, Inc. Japan All rights reserved. 19

20 データ読み取り / 書き込み (3) 同期処理共有バッファの変更内容をディスクに反映チェックポイント - checkpoint_timeout = 5min checkpoint_segments = 3 キャッシュ追い出し ( すべてのページが変更された場合 ) writer プロセスがバックグラウンドで同期処理を行うディスク反映済みの WAL ファイルは不要になるページ共有バッファ同期処理を行い対応するブロックをページと一致させるブロックテーブルファイル WAL ファイル (pg_xlog) アーカイブログへ SRA OSS, Inc. Japan All rights reserved. 20

21 データ読み取り / 書き込み (4) 同時実行制御トランザクション ID を用いてバージョン管理追記型の書き込みテーブルt1の内容 xmin=12302 xmax=0 value='s' xmin=12302 xmax=12306 value='s' xmin=12306 xmax=0 value='g' xmin=12302 xmax=12306 value='s' xmin=12306 xmax=12308 value='g' トランザクション ID SELECT * FROM t1 トランザクション ID UPDATE t1 SET value = 'G' トランザクション ID DELETE FROM t1 各トランザクション内で次にテーブル参照したとき整合性が保たれる SELECT xmin, xmax, * FROM t1; SRA OSS, Inc. Japan All rights reserved. 21

25 実行時統計情報統計情報コレクタ (stats collector プロセス ) によって収集されるデータベースの動作状況に関する情報収集された統計情報はビューや関数等で参照できる統計情報の収集はデフォルトで有効 #track_counts = on #track_activities = on pg_stat_activity 用統計情報はデータベース単位でリセットできる =# SELECT pg_stat_reset(); SRA OSS, Inc. Japan All rights reserved. 25

26 実行時統計情報 (1) pg_stat_activity =# SELECT * FROM pg_stat_activity; -[ RECORD 1 ] datid データベースのOID datname postgres データベース名 pid プロセスID usesysid 10 ユーザのOID usename postgres ユーザ名 application_name アプリケーション名 client_addr クライアントのIPアドレス client_hostname クライアントのホスト名 client_port クライアントのTCPポート番号 backend_start :13: プロセス開始時間 xact_start :14: 現在のトランザクション開始時間 query_start :14: 現在のクエリの開始時間 state_change :14: state が最後に更新された時間 waiting f 現在ロック待ちであるか state active active, idle など状態を表示 backend_xid トランザクションID backend_xmin 1952 次トランザクションID(xmin) query UPDATE pgbench_accounts 最後に実行 SET abalan or 実行中のクエリ SRA OSS, Inc. Japan All rights reserved. 26

28 実行時統計情報 (3) pg_stat_database =# SELECT * FROM pg_stat_database; -[ RECORD 3 ] datid データベースのOID datname postgres データベース名 numbackends 0 データベースへの接続数 xact_commit 2680 コミットされたトランザクション数 xact_rollback 0 ロールバックされたトランザクション数 blks_read ディスクから読み取られたブロック数 blks_hit バッファキャッシュに存在したブロック数 tup_returned 取得された行数 tup_fetched 抽出された行数 tup_inserted INSERTされた行数 tup_updated 25 UPDATEされた行数 tup_deleted 283 DELETEされた行数 conflicts 0 スタンバイサーバでリカバリ処理と競合した回数 temp_files 2 問合せにより作成された一時ファイル数 temp_bytes 問合せにより作成された一時ファイルの累積サイズ deadlocks 0 検知されたデッドロック回数 blk_read_time 0 ブロックの読み取りに費やされた累積時間 ( ミリ秒 ) blk_write_time 0 ブロックの書き込みに費やされた累積時間 ( ミリ秒 ) stats_reset :41: 統計情報が最後にリセットされた時間 SRA OSS, Inc. Japan All rights reserved. 28

29 実行時統計情報 (4) pg_stat_database データベースに関する統計情報ビュー numbackends 以外の項目は最後にリセットされてからの累積値を表示 - 設定変更時はカウンタをリセットする blks_read blks_hit にてデータベースごとのキャッシュヒット率を確認 - blks_read / (blks_hit + blks_read) - ただし blks_hit には OS 側のキャッシュヒットは含まれていない temp_files temp_bytes にて work_mem の不足状況を確認 SRA OSS, Inc. Japan All rights reserved. 29

30 実行時統計情報 (5) pg_stat_user_tables =# SELECT * FROM pg_stat_user_tables; -[ RECORD 1 ] relid schemaname public relname t1 seq_scan 1 seq_tup_read 0 idx_scan 1 idx_tup_fetch 1 n_tup_ins n_tup_upd 0 n_tup_del 0 n_tup_hot_upd 0 n_live_tup n_dead_tup 0 n_mod_since_analyze 0 last_vacuum :50: last_autovacuum last_analyze last_autoanalyze :49: vacuum_count 1 autovacuum_count 0 analyze_count 0 autoanalyze_count 1 デブルのOID 属しているスキーマ名テーブル名実行されたシーケンシャルスキャンの回数シーケンシャルスキャンで取得された有効行数実行されたインデックススキャンの回数インデックススキャンで取得された有効行数 INSERTされた行数 UPDATEされた行数 DELETEされた行数 HOTによる (INDEXの更新を伴わない) 更新行数有効行数無効行数最後にANALYZEが実行されてからの変更行数最後に実行された手動 VACUUMの時間最後に実行された自動 VACUUMの時間最後に実行された手動 ANALYZEの時間最後に実行された自動 ANALYZEの時間実施された手動 VACUUM 数実施された自動 VACUUM 数実施された手動 ANALYZE 数実施された自動 ANALYZE 数 SRA OSS, Inc. Japan All rights reserved. 30

31 実行時統計情報 (6) pg_stat_user_tables ユーザが作成したテーブルに関する行単位の統計情報ビュー n_live_tup n_dead_tup にて有効行無効行を確認 last_autovacuum last_autoanalyze にて自動 VACUUM の実行状況を確認 pg_stat_sys_tables: システムテーブルについて pg_stat_all_tables: すべてのテーブルについて pg_statio_user_tables ではブロック単位の統計情報が確認できる SRA OSS, Inc. Japan All rights reserved. 31

32 実行時統計情報 (7) pg_stat_user_indexes =# SELECT * FROM pg_stat_user_indexes; -[ RECORD 1 ] relid indexrelid schemaname public relname t1 indexrelname idx_scan 1 idx_tup_read 1 idx_tup_fetch 1 t1_pkey インデックスが張られているテーブルの OID インデックスの OID 属しているスキーマ名インデックスが張られているテーブル名インデックス名インデックススキャンが実行された回数インデックススキャンによって取得されたノード数インデックススキャンによって抽出された行数ユーザが作成したインデックスに関する行単位の統計情報ビュー idx_scan にてインデックスの利用状況を確認 pg_stat_sys_indexes: システムインデックスについて pg_stat_all_indexes: すべてのインデックスについて SRA OSS, Inc. Japan All rights reserved. 32

34 実行計画 (1) EXPLAIN SQL 実行にあたり内部的にどのような処理方式を組み合わせて実行するかを事前に見積もる選択できるすべての実行計画を検証実行計画の確認 EXPLAIN: 実行計画を表示 EXPLAIN ANALYZE: 実行計画と実行結果に基づく情報 =# EXPLAIN SELECT * FROM pgbench_accounts JOIN pgbench_branches USING (bid) WHERE aid < 1000; QUERY PLAN Hash Join (cost= rows=1106 width=457) Hash Cond: (pgbench_accounts.bid = pgbench_branches.bid) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost= rows=1106 width=97) Index Cond: (aid < 1000) -> Hash (cost= rows=8 width=364) -> Seq Scan on pgbench_branches (cost= rows=8 width=364) (6 rows) SRA OSS, Inc. Japan All rights reserved. 34

35 実行計画 (2) 計画タイプ計画タイプ ( 例 :Hash Join) 内部的な処理の方式同じ処理であっても異なる方式が存在する Hash Join (cost= rows=1106 width=457) Hash Cond: (pgbench_accounts.bid = pgbench_branches.bid) スキャン方式結合方式テーブルインデックステーブルテーブルテーブルテーブルハッシュ Seq Scan Index Scan Nested Loop Hash Join テーブルインデックスビットマップテーブル Bitmap Index/Heap Scan ソート 9 8 ソート 5 テーブル Merge Join テーブル SRA OSS, Inc. Japan All rights reserved. 35

36 実行計画 (3) EXPLAIN 表示情報コスト ( 例 :cost= ) シーケンシャルアクセスでディスクから 1 ページ読み取るコストを 1 とした相対的な数値を表示最初の行を取得するまでの指定コストとすべての行を取得するまでの推定コスト ( 実行時間 ) 下位の計画ノードにおける推定コストが含まれる行数 ( 例 :rows=1106) 取得される推定行数行の平均サイズ ( 例 :width=457) 取得される行の平均サイズ ( バイト ) Hash Join (cost= rows=1106 width=457) Hash Cond: (pgbench_accounts.bid = pgbench_branches.bid) SRA OSS, Inc. Japan All rights reserved. 36

37 実行計画 (4) EXPLAIN ANALYZE postgres=# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts JOIN pgbench_branches USING (bid) WHERE aid < 1000; QUERY PLAN Hash Join (cost= rows=1106 width=457) (actual time= rows=999 loops=1) Hash Cond: (pgbench_accounts.bid = pgbench_branches.bid) -> Index Scan using pgbench_accounts_pkey on pgbench_accounts (cost= rows=1106 width=97) (actual time= rows=999 loops=1) Index Cond: (aid < 1000) -> Hash (cost= rows=8 width=364) (actual time= rows=8 loops=1) Buckets: 1024 Batches: 1 Memory Usage: 1kB -> Seq Scan on pgbench_branches (cost= rows=8 width=364) (actual time= rows=8 loops=1) Planning time: ms Execution time: ms (9 rows) SRA OSS, Inc. Japan All rights reserved. 37

38 実行計画 (5) EXPLAIN ANALYZE 表示情報実行時間 ( 例 : actual time= ) 最初の行を取得するまでの実行時間とすべての行を取得するまでの実行時間 ( ミリ秒 ) 下位の計画ノードにおける実行時間を含む行数 ( 例 :rows=999) 実際に取得された行数ループ回数 ( 例 :loops=1) 繰り返して実行された回数実行時間と行数にはループ回数を乗じる計画作成時間 ( 例 :Planning time: ms) 実行時間 ( 例 :Execution time: ms) SRA OSS, Inc. Japan All rights reserved. 38

40 共有バッファの設定共有バッファ ( データの読み取り時に使用されるバッファ ) を増やして読み取り性能を向上させるデフォルト値 (128MB 以下 ) は少なすぎる物理メモリが 1GB 以上の場合は共有バッファに 4 分の 1 を割り当てる shared_buffers = 256MB 共有バッファのメモリ容量 ( 要再起動 ) 共有バッファを大きくしすぎるとメモリ領域を圧迫して逆に性能が低下 Linux では余ったメモリ領域をディスク I/O のバッファキャッシュとして使用してくれる postgres プロセス共有バッファ OS のディスクキャッシュテーブルファイル / インデックスファイル SRA OSS, Inc. Japan All rights reserved. 40

41 共有バッファのページ割り当て pg_stat_bgwriter ビューバックグランドライタと共有メモリに関する情報すべてのデータベースに共通 buffers_backend が buffers_alloc よりも大きいケースでは =# SELECT * FROM pg_stat_bgwriter; shared_buffers 不足の傾向 -[ RECORD 1 ] checkpoints_timed 384 checkpoints_req 14 checkpoint_write_time checkpoint_sync_time 3067 buffers_checkpoint buffers_clean 0 maxwritten_clean 0 buffers_backend buffers_backend_fsync 0 buffers_alloc stats_reset :25: =# SELECT pg_stat_reset_shared('bgwriter'); -- リセット SRA OSS, Inc. Japan All rights reserved. 41

42 データベース単位のキャッシュヒット pg_stat_database 統計情報ビュー =# SELECT * FROM pg_stat_database; -[ RECORD 4 ] datid datname test numbackends 1 xact_commit 4001 xact_rollback 0 blks_read blks_hit データベースの OID データベース名データベースに接続中のプロセス数コミットされたトランザクション数ロールバックされたトランザクション数ディスクから読み取られたブロック数バッファキャッシュ内に存在したブロック数キャッシュヒット率 = blks_hit blks_read + blks_hit SRA OSS, Inc. Japan All rights reserved. 42

43 テーブル単位のキャッシュヒット pg_stat_user_tables 統計情報ビュー =# SELECT * FROM pg_statio_user_tables; -[ RECORD 4 ] relid schemaname public relname t1 heap_blks_read 838 heap_blks_hit idx_blks_read 276 idx_blks_hit toast_blks_read 0 toast_blks_hit 0 tidx_blks_read 0 tidx_blks_hit 0 テーブルの OID スキーマ名テーブル名テーブルに関するディスク読み取り数キャッシュヒット数インデックスに関するディスク読み取り数キャッシュヒット数 TOAST テーブルに関するディスク読み取り数キャッシュヒット数 TOAST テーブルのインデックスに関するディスク読み取り数キャッシュヒット数 SRA OSS, Inc. Japan All rights reserved. 43

44 SQL 単位のキャッシュヒット EXPLAIN (ANALYZE, BUFFERS) SQL =# EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM pgbench_accounts AS a JOIN pgbench_accounts AS b USING (aid); QUERY PLAN Merge Join (cost= rows= width=190) (actual time= rows= loops=1) Merge Cond: (a.aid = b.aid) Buffers: shared hit=61210 read=61202 written=2 -> Index Scan using pgbench_accounts_pkey on pgbench_accounts a (cost= rows= width=97) (actual time= rows= loops=1) Buffers: shared hit= > Index Scan using pgbench_accounts_pkey on pgbench_accounts b (cost= rows= width=97) (actual time= rows= loops=1) Buffers: shared hit=4 read=61202 written=2 Planning time: ms Execution time: ms (9 rows) shared hit = ヒットしたページ数 read= 読み込んだブロック数 written= 書き込んだブロック数 SRA OSS, Inc. Japan All rights reserved. 44

46 ワークメモリの設定ワークメモリ ( ソートやハッシュ作成時などに使用されるメモリ ) を増やして SQL の実行時間を短くするワークメモリが少ないとソート時に一時ファイルの作成が必要になったりハッシュ作成に十分なメモリを確保できない共有メモリとは別にバックエンドごとに確保される - work_mem max_connections( 最大接続数 ) #work_mem = 4MB ワークメモリのメモリ容量 log_temp_files を利用して調査大きなメモリを設定する場合にはセッショントランザクションごとに設定 SET log_temp_files to 0; SET work_mem TO '10MB'; SELECT ; 大きな値を設定すると物理メモリ不足になる可能性がある SRA OSS, Inc. Japan All rights reserved. 46

47 一時ファイルの利用状況の確認 work_mem が十分でないとソート処理やハッシュ作成処理などで一時ファイルが利用される大量の一時ファイルを使う SQL を調査指定サイズ以上の一時ファイルを利用する SQL を特定 log_temp_files = 1MB 0 ですべて EXPLAIN ANALYZE でも確認できる Sort Method: external merge Disk: 11912kB Sort Method: quicksort Memory: 25kB 一時ファイルが多用されている場合には work_mem のチューニングの余地あり SRA OSS, Inc. Japan All rights reserved. 47

48 メンテナンスワークメモリの設定メンテナンスワークメモリを増やしバキュームを効率的に実行して短時間に完了させるメンテナンスワークメモリ VACUUM や REINDEX CREATE INDEX などの実行時に使用されるメモリの閾値共有メモリとは別に確保 #maintenance_work_mem = 64MB メンテナンスワークメモリの最大メモリ容量 VACUUM などは一般的に同時に実行しないため基本的に大きな値を設定しても問題ない自動バキュームでは複数のプロセスが VACUUM を同時に実行するため ( デフォルトでは 3 プロセス ) 値を大きくしすぎないように注意 SRA OSS, Inc. Japan All rights reserved. 48

50 自動バキュームの活動調査自動バキュームの活動状況をログに記述テーブル単位で自動バキュームの実行時間の特定 log_autovacuum_min_duration = 1s 0 ですべての活動状況 LOG: automatic vacuum of table "test.public.t1": index scans: 1 pages: 0 removed, 8334 remain tuples: removed, remain, 0 are dead but not yet removable buffer usage: hits, 4 misses, dirtied avg read rate: MB/s, avg write rate: MB/s system usage: CPU 0.08s/1.60u sec elapsed sec 確認実行回数 /1 日各テーブルがどれほどの頻度で実行されるか実行時間帯アクセスが多い時間帯に実行される頻度実行時間 SRA OSS, Inc. Japan All rights reserved. 50

51 WAL バッファの設定 WAL バッファ (WAL ファイルの書き込み時に使用されるバッファ ) を増やして書き込み性能を向上させるデフォルトでは -1=shared_buffers 32 - shared_buffers = 512MB で 16MB 更新が多い場合には増やす ( 最大 16MB) サーバ起動時に共有メモリバッファ (shared_buffers) とは別に指定された容量のメモリが確保される #wal_buffers = -1 WAL バッファのメモリ容量 ( 要再起動 ) WAL バッファが少なすぎるとコミット時以外にも書き込みが発生してしまう SRA OSS, Inc. Japan All rights reserved. 51

52 チェックポイントの設定チェックポイント処理の頻度を減らしてディスク I/O の総量を減少させる更新が少ない場合には checkpoint_timeout を長く更新が多い場合には checkpoint_segments を多く設定チェックポイント処理時の書き込み量が増えるため一時的なディスク I/O への負荷は増加 #checkpoint_segments = 3 #checkpoint_timeout = 5min #checkpoint_completion_target = 0.5 完了済み WAL ファイルが 3 つを超えたチェックポイント処理を実行するまでの完了済み WAL ファイル数チェックポイント処理を実行するまでの間隔チェックポイント処理を完了するまでの時間 ( 間隔に対する割合 ) 5 分 0.5 = 2 分 30 秒以内にチェックポイント処理が完了するように負荷を分散チェックポイント処理チェックポイント処理最後にチェックポイント処理を実行してから 5 分過ぎた SRA OSS, Inc. Japan All rights reserved. 52

53 チェックポイントの実行頻度と負荷状況チェックポイントの実行をログに記録し vmstat などで取った負荷状況と見比べる log_checkpoints = on チェックポイントが頻発していないかチェックポイント時の負荷が高すぎないか LOG: checkpoint starting: xlog LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=0.100 s, sync=0.007 s, total=0.135 s; sync files=1, longest=0.007 s, average=0.007 s LOG: checkpoint starting: time LOG: checkpoint complete: wrote 2107 buffers (12.9%); 0 transaction log file(s) added, 0 removed, 1 recycled; write= s, sync=0.116 s, total= s; sync files=35, longest=0.041 s, average=0.003 s SRA OSS, Inc. Japan All rights reserved. 53

54 プランナコスト定数の設定プラットフォームの特性に合わせてプランナコスト定数を調整しより適切な実行計画が作成されるようにするプランナはプランナコスト定数をもとにコストを推定 #seq_page_cost = 1.0 #random_page_cost = 4.0 #effective_cache_size = 4GB シーケンシャルアクセスでディスクから 1 ブロック読み取るコストランダムアクセスでディスクから 1 ブロック読み取るコストプランナが想定するキャッシュサイズ ( 実際にメモリは確保されない ) インデックススキャンが選択されやすくするには random_page_cost を減らして effective_cache_size を増やす effective_cache_size は共有メモリバッファの 2 倍 ( 物理メモリの 50%) くらいが適切 SRA OSS, Inc. Japan All rights reserved. 54

pg_monz 監視アイテム一覧 :Template App PostgreSQL Template App PostgreSQL アプリケーション LLD アイテムトリガー監視タイプ更新間隔ヒストリトレンドデフォルト説明ステータス pg.get pgsql.get.pg.bgwriter Zabb

pg_monz 監視アイテム一覧 :Template App PostgreSQL Template App PostgreSQL アプリケーション LLD アイテムトリガー監視タイプ更新間隔ヒストリトレンドデフォルト説明 pg.get pgsql.get.pg.bgwriter 60 90 365 無効 pg.bgwriterアプリケーションの監視アイテムの取得を行う pg.get pgsql.get.pg.transactions