jaws-asa-postgres-parameter

jaws-asa-postgres-parameter

Transcript

1. Aurora Postgresの
 Parameter Group
 (監査・ログ系)を
 調べてみた

2. 自己紹介
 
 名前：富松 広太（とみまつ　こうた）
 所属：justInCase Technologies
 Blog：https://cloud-aws-gcp.hateblo.jp/ 
 twitter：@kotamemento
 （前職）2020~22 APN Ambassador、Top Engineers

3. はじめに
 私の親はOracleです。
 今日はPostgresの話です。
 極力調べて来ましたが用語を
 Oracleと間違えていたら指定頂けますと幸いです
 今日はDatabaseの話をします

4. 会社紹介 & 背景
 ・取引先に大手保険会社様が多い
 ・セキュリティ基準がいっぱい

5. 要件
 ・DBアクセスログ取得したい
 ・SQL実行ログを取得したい（パラメタ含む）

6. 要件
 ・DBアクセスログ取得したい
 ・SQL実行ログを取得したい（パラメタ含む）
 
 Parameter Groupがdefault値だった（現在開発中）

7. 要件
 ・DBアクセスログ取得したい
 ・SQL実行ログを取得したい（パラメタ含む）
 
 Parameter Groupがdefault値だった（現在開発中）
 設定値を全部調べてしまおう！

8. 要件
 ・DBアクセスログ取得したい
 ・SQL実行ログを取得したい（パラメタ含む）
 ・ログ設定全般を見直したい
 ・その他必要な設定があれば追加したい

9. アクセスログ
 設定項目
 意味
 変更後の値
 log_connections
 接続ログを取得するか 
 1（取得する）
 log_disconnections
 接続ログ（切断時）を取得するか 
 1（取得する）
 設定値
 出力例
 接続時
 接続断時
 接続のソース（IP）、ユーザー名、時間が出力される 

10. SQL実行履歴を取得（パラメタ含む）
 設定項目
 意味
 変更後の値
 pgaudit.role
 pgauditで利用するrole名 
 rds_pgaudit(固定)
 pgaudit.log
 pgauditの記録対象
 all -misc
 (checkpoint,vacuum,set等のその他扱 いの構文以外全て )
 pgaudit.log_statement_once 
 同じSQLを複数回出力される際に省略して出力する 
 1(有効化)
 pgaudit.log_relation
 関連するtable名等の情報を付与する 
 1(有効化)
 pgaudit.log_parameter 
 SQL中のパラメータ情報を付与する 
 1(有効化)
 shared_preload_libraries 
 pgAudit(ログに操作種別等の情報を付与し、追いやすくする設定) 
 pgAudit(追加)
 設定値
 出力例
 xx xx xx 実行sqlがパラメタ内容含めてログ出力される 

11. SQL実行履歴で変更しなかった値
 設定項目
 意味
 値(defaultのまま)
 log_statement
 sqlログを出力するか 
 none
 log_min_duration_statement 
 SQLの実行に指定したミリ秒以上の時間がかかった場合に、 
 そのSQLと所要時間をログファイルに記録します 
 -
 出力例（log_statement利用時）
 設定値
 pgaudit利用時は適度にマスキングしてくれる
 log_statementがnoneでもpgauditを設定しておけば出力される
 パスワードが
 出力されてしまう
 パスワードは
 マスキングされる

12. その他変更しなかった値
 設定項目
 意味
 値(defaultのまま)
 log_temp_files 割り当てられたメモリよりも多くの一時領域を 必要とする処理があった場合に logするか default値
 log_lock_waits ロックにより待ちが発生した場合に logするか （default1秒以上） default値
 log_autovacuum_min_duration 指定時間よりも長い vacumeをログ ※documentより初期値が推奨値でない default値
 log_temp_files 割り当てられたメモリよりも多くの一時領域を 必要とする処理があった場合に logするか default値
 結論
 設定値
 Perfomance Insightで良さそうなため出力しない

13. ログ設定全般
 設定項目 意味 変更後の値 log_destination サーバーログの出力形式 csvlog(csv形式) force_admin_logging_level rdsadminでの操作ログ AWSからの操作（パスワード変更等） log(取得する) 設定値
 出力例（log_destination）
 出力例（force_admin_logging_level）
 xx xx xx CSVで出力される
 rdsadminのログが出力される 

14. 設定項目
 意味
 設定値
 rds.enable_plan_management query pran mangermentを有効化 db側でquery実行の必要あり 1(有効化)
 apg_plan_mgmt.capture_plan_baselines 実行計画を自動で収集する automatic
 apg_plan_mgmt.use_plan_baselines approvedまたはpreffedとして 登録した実行計画のみを利用対象とするか FALSE(default値)
 その他ログ以外の設定(QPM：Query Plan Management)
 設定値

15. Query Plan Management（背景）
 通常時
 SQL 実行計画作成
 （最適なアクセス方法を導く） 
 DB ある時
 SQL 実行計画作成
 経年によりデータが変化し、 
 実行計画も変化する 
 DB 定期的に統計情報収集 
 （データ特性を分析） 
 実行計画がデータに適さない時がある 
 （性能問題となり表面化） 
 実行計画の適応タイミングを管理したい → Query Plan Management

16. Query Plan Management（利用後）
 通常時
 SQL 実行計画作成
 DB 事前に利用可能な実行計画を選択 
 選択したもののみ利用可能にするか 
 apg_plan_mgmt.use_plan_baselines 
 実行計画の 候補 実行計画を自動で保持 apg_plan_mgmt.capture_plan_baselines 
 設定項目
 意味
 設定値
 rds.enable_plan_management query pran mangermentを有効化 db側でquery実行の必要あり 1(有効化)
 apg_plan_mgmt.capture_plan_baselines 実行計画を自動で収集する automatic
 apg_plan_mgmt.use_plan_baselines approvedまたはpreffedとして 登録した実行計画のみを利用対象とするか FALSE(default値)
 性能問題の発生想定と運用増のバランスから実行計画の収集のみ有効化

17. defaultから変更した値まとめ
 設定項目
 意味
 変更後の値
 log_destination
 サーバーログの出力形式 csvlog log_connections 接続ログを取得するか 1（取得する） log_disconnections 接続ログ（切断時）を取得するか 1（取得する） force_admin_logging_level rdsadminでの操作ログ log pgaudit.role pgauditで利用するrole名 rds_pgaudit pgaudit.log pgauditの記録対象 all -misc pgaudit.log_statement_once
 同じSQLを複数回出力される際に省略して出力する 
 1(有効化)
 pgaudit.log_relation
 関連するtable名等の情報を付与する 
 1(有効化)
 pgaudit.log_parameter
 SQL中のパラメータ情報を付与する 
 1(有効化)
 rds.enable_plan_management
 query pran mangermentを有効化 db側でquery実行の必要あり
 1(有効化)
 apg_plan_mgmt.capture_plan_baselines
 実行計画を自動で収集する 
 automatic
 apg_plan_mgmt.use_plan_baselines
 approvedまたはpreffedとして 登録した実行計画のみを利用対象とするか 
 FALSE
 shared_preload_libraries
 pgAudit(ログに操作種別等の情報を付与し、追いやすくする設定 ) auto_explain（実行計画を自動的にログに残す設定） 
 (追加)pgAudit
 pgauditとqpmはパラメタ以外も設定が必要（次ページ参考）

18. RDS および Aurora PostgreSQL ログの操作 
 https://aws.amazon.com/jp/blogs/news/working-with-rds-and-aurora-postgresql-logs-part-1/ 
 
 pgaudit(有効化手順)
 https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-postgresql-pgaudit 
 
 Query Plan Management(有効化手順) 
 https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Optimize.html 
 
 postgres document
 https://www.postgresql.jp/document/14/html/runtime-config.html 
 
 
 分かりやすかったブログ 
 https://www.sraoss.co.jp/tech-blog/category/pgsql/ 
 
 参考リンク

19. さいごに
 エンジニア採用中です。カジュアル面談、twitter相談、社員紹介等なんでもありです