コスト最適重視でAurora PostgreSQLのログ分析基盤を作ってみた #jawsug_tokyo

JAWS-UG東京 Presents 400での登壇資料です。

https://jawsug.connpass.com/event/348840/?utm_campaign=event_participate_to_follower&utm_source=notifications&utm_medium=twitter

Transcript

1. 2025/4/23 クラスメソッド株式会社 のんピ コスト最適重視でAurora PostgreSQLの ログ分析基盤を作ってみた
2. ⾃⼰紹介 2 • 2017年 前職(SIer)⼊社 インフラエンジニア ◦ 仮想化基盤および⾃社DCの運⽤保守 ◦ ⾦融機関のインターネット系システムの構築 • 2019年 クラウドメインの部署へ異動 ◦ 主に基幹システムの⼤規模AWS移⾏を担当 • 2021年 クラスメソッド⼊社 SA ◦ AWSコスト最適化⽀援 ◦ 1,000万PV超のECサイトリプレース⽀援 ◦ 300TBファイルサーバーの移⾏⽀援 • 所属 ◦ クラウド事業本部コンサルティング部 • 名前（ニックネーム） ◦ ⼭本涼太 (のんピ) • 好きなAWSサービス ◦ Amazon FSx for NetApp ONTAP ◦ AWS Transit Gateway ◦ AWS CDK • 趣味 ◦ Instagramで流れてきたコアラの画像から コアラの名前と動物園を当てること
3. こんなことありませんか? みなさん 3
4. CloudWatch Logsに流しているDBのログに 対する課⾦が⾼すぎる！！！！ 4
5. ここでいうDBのログの例 5 • エラーログ • スロークエリログ • 接続ログ • デッドロックログ • 監査ログ • autovacuumログ • チェックポイントログ • レプリケーションログ • 統計情報ログ
6. ここでいうDBのログの例 (詳細) 6 項目 説明 活用するシチュエーション エラーログ PostgreSQLサーバーが出力するエラー、警告、通知、デバッグのログ • アプリケーションエラーのトラブルシューティング • サーバークラッシュの原因調査 • 設定ミスの特定 • 予期しないサーバー再起動の調査 スロークエリログ 実行時間が長いクエリのログ log_min_duration_statement を有効にして出力 • パフォーマンス最適化 • 非効率なクエリの特定 • アプリケーションのボトルネック分析 接続ログ データベースへの接続 /切断イベントのログ log_connections / log_disconnections を有効にして出力 • 不正アクセスの検出 • 接続のトラブルシューティング デッドロックログ ロック獲得待ちがdeadlock_timeoutの設定値より長い処理のログ log_lock_waits を有効にして出力 • アプリケーションのデッドロックの特定 • デッドロックが頻発するクエリの最適化 • トランザクション設計の改善 監査ログ データベースに対する操作（ SELECT, INSERT, UPDATE, DELETE, 各種DDLなど）を記 録するログ log_statement もしくは pgAuditを有効にして出力 • セキュリティ監査 • 不正操作の検出 • 変更の追跡 autovacuumログ autovacuum処理に時間がかかっている実行状況のログ Aurora PostgreSQLの場合は log_autovacuum_min_duration が存在しない • バキューム処理の遅延調査 チェックポイントログ チェックポイント処理の実行状況のログ Aurora PostgreSQLの場合はチェックポイントが存在せず、 log_checkpoints も存在 しない • I/Oスパイクの調査 レプリケーションコマンドログ レプリケーション関連の SQLのログ log_replication_commands を有効にして出力 • レプリケーション設定の検証 統計情報ログ 性能に関する統計情報のログ log_statement_stats / log_parser_stats / log_planner_stats / log_executor_stats を有効にして出力 • クエリプラン変更の調査 • 統計情報収集の最適化 • 実行計画のトラブルシューティング
7. 特に運⽤の中でよく⾒るであろうログ 7 項目 説明 活用するシチュエーション エラーログ PostgreSQLサーバーが出力するエラー、警告、通知、デバッグのログ • アプリケーションエラーのトラブルシューティング • サーバークラッシュの原因調査 • 設定ミスの特定 • 予期しないサーバー再起動の調査 スロークエリログ 実行時間が長いクエリのログ log_min_duration_statement を有効にして出力 • パフォーマンス最適化 • 非効率なクエリの特定 • アプリケーションのボトルネック分析 接続ログ データベースへの接続 /切断イベントのログ log_connections / log_disconnections を有効にして出力 • 不正アクセスの検出 • 接続のトラブルシューティング デッドロックログ ロック獲得待ちがdeadlock_timeoutの設定値より長い処理のログ log_lock_waits を有効にして出力 • アプリケーションのデッドロックの特定 • デッドロックが頻発するクエリの最適化 • トランザクション設計の改善 監査ログ データベースに対する操作（ SELECT, INSERT, UPDATE, DELETE, 各種DDLなど）を記 録するログ log_statement もしくは pgAuditを有効にして出力 • セキュリティ監査 • 不正操作の検出 • 変更の追跡 autovacuumログ autovacuum処理に時間がかかっている実行状況のログ Aurora PostgreSQLの場合は log_autovacuum_min_duration が存在しない • バキューム処理の遅延調査 チェックポイントログ チェックポイント処理の実行状況のログ Aurora PostgreSQLの場合はチェックポイントが存在せず、 log_checkpoints も存在 しない • I/Oスパイクの調査 レプリケーションコマンドログ レプリケーション関連の SQLのログ log_replication_commands を有効にして出力 • レプリケーション設定の検証 統計情報ログ 性能に関する統計情報のログ log_statement_stats / log_parser_stats / log_planner_stats / log_executor_stats を有効にして出力 • クエリプラン変更の調査 • 統計情報収集の最適化 • 実行計画のトラブルシューティング
8. 特定ログだけCloudWatch Logsへエクスポートしたいところだが 8 Aurora PostgreSQLは先述のログを PostgreSQLログ としてまとめてしまう 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUser Guide/USER_LogAccess.Concepts.PostgreSQL.html
9. では、CloudWatch Logsにエクスポートしなければ良いのでは? 9 ログは最⼤7⽇までしか保存できない 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html
10. ほとんどのログをCloudWatch Logsに流す形に なりがち つまりは 10
11. CloudWatch Logsの課⾦が膨れる ということは 11
12. この悩みを聞いてよく挙げられる構成 12 Logsに出⼒したログをData Firehoseを介してS3バケットにPUTする形
13. 果たしてこれは正解なのか?   13
14. CloudWatch Logsの料⾦体系を確認する 14 • データ取り込み料⾦が⽐較的⾼い • 保存料⾦はログが⾃動圧縮されることも後押しして⽐較的安い 項目 コスト 1 USD = 150円換算 データ取り込みサイズ (Standard) 0.76 USD/GB 114円/GB データ取り込みサイズ (IA) 0.38 USD/GB 57円/GB 保存 (圧縮後のデータサイズ) 0.033 USD/GB 4.95円/GB (圧縮率15%の場合は0.74円) Logs Insights (スキャン量) 0.0076 USD/GB 1.14円/GB ※ 2025/4/22時点の東京リージョンの料金
15. CloudWatch LogsとS3のストレージ料⾦の⽐較 15 Logsのデータ取り込みサイズほどの差はない 項目 コスト 1 USD = 150円換算 Logs 保存 (圧縮後のデータサイズ) 0.033 USD/GB 4.95円/GB (圧縮率15%の場合は0.74円) S3 Standard 保存 0.023 USD/GB 3.45円/GB S3 Standard IA 保存 0.0125 USD/GB 1.86円/GB S3 Glacier Instant Retrieval 保存 0.004 USD/GB 0.6円/GB S3 Glacier Deep Archive 保存 0.00099 USD/GB 0.15円/GB ※ 2025/4/22時点の東京リージョンの料金 ※ ストレージサイズに応じて料金が階層的に変動する場合最 初の階層の料金を記載
16. この悩みを聞いてよく挙げられる構成 16 ログの保存料⾦が若⼲安くなるが、Data Firehoseの料⾦がプラスでかかる = 4円安くするために5円払うイメージ Logsのデータ取り込みサイズにかかる課⾦の逆転は難しい 5KB単位のデータ取り込み : 0.036 USD/GB (5.4円/GB)
17. CloudWatch Logsの取り込み料⾦に対する フォローが必要 つまりはコスト削減のためには 17
18. 私の経験 18 Logs Insightを実⾏すると数TBに対してスキャンが実⾏され、⼀回の実⾏で数千円 から1万円程度の課⾦が発⽣することも (当時はLogsのフィールドインデックス機能GA前) 監査ログ取得の関係でログを月10TBペースで出力
19. アプローチの整理
20. 考えられる⽅法 20 1. CloudWatch Logs Infrequent Access (Logs IA)に流す 2. DBインスタンスのログファイルをS3に流す仕組みの⾃作
21. AuroraのログファイルはDBインスタンス内に出⼒される 21 ログファイルを取得するためにはDBクラスター内の個々のDBインスタンスにアクセスが必要 抜粋 : https://docs.aws.amazon.com/AmazonRDS/latest/Aurora UserGuide/Aurora.Overview.html
22. Pros/Cons 22 項目 Pros Cons Logs IAに流す • 手間がかからない • ログ欠損のリスクが少ない • メトリクスフィルターやサブスクリプションフィル ターを使用できない • ログの取り込み料金は Logs Standardと比較 して半額程度しか削減できない S3に流す仕組みの自作 • ログ取り込みコストの大幅削減が可能 • Athenaのパーティション分割による ログ分析時のコストの減少 • 処理の中でログの種別ごとに出力先を変更するこ とも可能 • PostgreSQLの場合有効 • ログ欠損の可能性がある • ログをリアルタイムで確認できない • 特定の文字列が含まれるログが出力された場 合の通知実装のハードルが高い • 仕組みのメンテナンスが必要
23. DBインスタンスのログファイルを S3に流す仕組みの⾃作 圧倒的にコスト最適化が重要視されるのであれば 23
24. DBインスタンスのログファイルをS3に流す仕組み の実装
25. 前提 25 • 紹介するアーキテクチャの導⼊を積極的にお勧めするものではない • Consに挙げた内容はよく理解した上で導⼊すること ◦ 特にAurora MySQLはローカルストレージ使⽤率が15%以上 or 24時間経過後にログを削除 し始めるため、ログ⽋損のリスクが⾼い • ログを⾒た上でアクションする運⽤が全くイメージ着いていないなら 「ログを取得しない」選択肢もある ◦ 「ログの⾒⽅が分からない」は勉強すれば良いだけではある ◦ 「このログを取る意味が全く分からない」は再考の余地あり コストとログ運⽤のバランスを考えて導⼊しよう
26. アーキテクチャ 26
27. ポイント1: LastWrittenによる絞り込みと重複ログファイルの抽出 27 S3バケット上に存在しないものをのみをPUTして、Lambdaのコストを削減
28. ポイント2: DownloadCompleteLogFile を使⽤する 28 AWS SDKがあるのは download_db_log_file_portion だが以下が不便 • ⼀回の実⾏で最⼤ 1 MB分までしかダウンロードできない ◦ ページングのマーカー管理が必要 • ⽇本語が⽂字化けすることもある 抜粋 : https://boto3.amazonaws.com/v1/documentation/api/1.26.91/reference/services/rds/client /download_db_log_file_portion.html
29. ポイント2: DownloadCompleteLogFile を使⽤する 29 ⾃分でHTTPリクエストを組み⽴てる必要はあるが DownloadCompleteLogFile の⽅が逆に楽
30. ポイント3: stderrに出⼒ 30 ログの出⼒形式は stderr か csvlog を選択可能 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html
31. ポイント3: stderrに出⼒ 31 ただし、csvlog を指定する場合、stderr と csvlog の両ファイルが⽣成される = DBインスタンスのローカルストレージ圧迫につながる
32. ローカルストレージのサイズはDBインスタンスクラスごとに固定 32 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQ L.Managing.TempStorage 枯渇したがためにスケールアップするのは もったいない
33. Q. Aurora Serverless v2のローカルストレージは? 33 A. ローカルストレージは存在する ただし、ローカルストレージの空きを⽰す FreeLocalStorage メトリクスは なし また、ローカルストレージへの書き込み量によってもACUが増減する 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/aurora-serverless-v2.setting-capacity.html#aurora-serverless-v2.viewing.monitoring
34. ポイント4: S3オブジェクトの圧縮 34 S3は自動で圧縮を行わないため Lambda関数内でチャンクごとに分割して gzip level 6で圧縮 圧縮率は1.5%ほどになることも
35. S3オブジェクトの圧縮をするとスキャン量が減り、コスト削減に 35 ⾮圧縮の場合 gzip圧縮の場合
36. この仕組みはAWS CDKで⼀発でデプロイできる様にしています 36
37. コスト試算 37 SFnやLogsの料金合わせても理論上は30 USD/month ほどで運用可能 117万円/月が4,500円/月になるなら検討の価値を感じる 項目 値 アーキテクチャ Arm リクエスト数 6 / hour 10分に一回起動 各リクエストの実行時間 10,000 msec 平均10秒想定 メモリサイズ 128 MB デフォルト エフェメラルストレージサイズ 512 MB デフォルト ログファイルのフィルタリング用 Lambdaのコスト = 0.07 USD / month 項目 値 アーキテクチャ Arm リクエスト数 60 / hour 1分に1ログファイル出力される想定 各リクエストの実行時間 40,000 msec 平均40秒想定 メモリサイズ 1,024 MB 圧縮処理の高速化のために増強 エフェメラルストレージサイズ 2,048 MB 最大の1GiBのログファイルに対応 RDSログのS3バケットへの PUT用Lambdaのコスト = 23.47 USD / month
38. これで完了！！！ 38
39. ではない 39
40. ログは利⽤されて初めて活きる 40
41. 再掲 : 特に運⽤の中でよく⾒るであろうログ 41 項目 説明 活用するシチュエーション エラーログ PostgreSQLサーバーが出力するエラー、警告、通知、デバッグのログ • アプリケーションエラーのトラブルシューティング • サーバークラッシュの原因調査 • 設定ミスの特定 • 予期しないサーバー再起動の調査 スロークエリログ 実行時間が長いクエリのログ log_min_duration_statement を有効にして出力 • パフォーマンス最適化 • 非効率なクエリの特定 • アプリケーションのボトルネック分析 接続ログ データベースへの接続 /切断イベントのログ log_connections / log_disconnections を有効にして出力 • 不正アクセスの検出 • 接続のトラブルシューティング デッドロックログ ロック獲得待ちがdeadlock_timeoutの設定値より長い処理のログ log_lock_waits を有効にして出力 • アプリケーションのデッドロックの特定 • デッドロックが頻発するクエリの最適化 • トランザクション設計の改善 監査ログ データベースに対する操作（ SELECT, INSERT, UPDATE, DELETE, 各種DDLなど）を記 録するログ log_statement もしくは pgAuditを有効にして出力 • セキュリティ監査 • 不正操作の検出 • 変更の追跡 autovacuumログ autovacuum処理に時間がかかっている実行状況のログ Aurora PostgreSQLの場合は log_autovacuum_min_duration が存在しない • バキューム処理の遅延調査 チェックポイントログ チェックポイント処理の実行状況のログ Aurora PostgreSQLの場合はチェックポイントが存在せず、 log_checkpoints も存在 しない • I/Oスパイクの調査 レプリケーションコマンドログ レプリケーション関連の SQLのログ log_replication_commands を有効にして出力 • レプリケーション設定の検証 統計情報ログ 性能に関する統計情報のログ log_statement_stats / log_parser_stats / log_planner_stats / log_executor_stats を有効にして出力 • クエリプラン変更の調査 • 統計情報収集の最適化 • 実行計画のトラブルシューティング
42. 横串で⾒たり、統計を取ったりよりも ピンポイントで⾒る機会の⽅が多い 42
43. アドホックなログの分析であればAthena 43 • サーバーレスのクエリサービス • データレイクに対してSQL形式のクエリを実⾏する • サーバの管理やインフラの構成などの⼿間をかけずに、 瞬時にデータ分析を始めることが可能 • Apache Trino のオンデマンド実⾏の場合は課⾦単位は スキャンされたデータ量 スモールスタートしやすいAthenaがマッチ ※ Athenaだけに任せるのではなく、横串でみたい場合や、 SIEMがあるならSIEMに載せるのはもちろんアリ
44. Athenaでログ分析をする際の考慮事項 44 1. ピンポイントで対象のログ種別を集計しようにもコストがかかる ◦ ログ種別ごとにパーティションが別れていない 2. 特定の列のみの抽出する場合でも実⾏時間が⻑くなりがち 3. マルチラインのログレコードが存在する
45. ピンポイントで対象のログ種別を集計しようにもコストがかかる 45 Athenaはパーティションを切ることでスキャン範囲を調整することができる = パーティションが区切られていなければスキャン範囲が広がる = コストがかかる パーティションが YYYY/MM/DD/HH/ のみ スロークエリのログだけ抽出したいのに全量 スキャンされてしまう
46. ログの種別によって出⼒先ログファイルを振り分けられるか 46 残念。エラーログもスロークエリログも監査ログも単⼀のログファイルに出⼒ 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.html
47. PostgreSQLのソースコードを確認する 47 Aurora PostgreSQLがPostgreSQL互換を謳っており、バージョンも対応しているのであれば今後の対応も望み薄か https://github.com/postgres/postgres/blob/master/sr c/include/utils/elog.h#L498 ログ種別によってログ出⼒先を変更する パラメーターはない スロークエリログでもereport()を呼び出している and ログレベル以外の属性も定義されていない ereport()はereport_domain()を、 ereport_domain()はerrstart()を呼び出している and errstart()内でログの振り分けはしていない https://github.com/postgres/postgres/blob/master/sr c/backend/tcop/postgres.c#L1360 https://github.com/postgres/postgres/blob/master/sr c/backend/utils/error/elog.c#L343
48. 特定の列のみの抽出する場合でも実⾏時間が⻑くなりがち 48 列指向のファイル形式ではない場合、クエリで参照される列のみの読み込みが できない = 時間もお⾦もかかる 1つのカラムだけ抽出したいのに 全量スキャンされてしまう
49. マルチラインのログレコードが存在する 49 例) stderrに出⼒している場合の監査ログ
50. RegexSerDe では複数⾏をパースできない 50 InputFormat でレコードを読み込んだ後にデシリアライズする 抜粋 : https://hive.apache.org/docs/latest/serde_27362059/#regex = 正規表現でマルチラインのレコードを⼀つにまとめることはできない
51. ETL処理が必要 つまりは 51
52. AWSにおけるETLを実施する基盤 52 今回は1ログファイルは解凍後でMAX1GB程度であるため、Lambdaで⼗分 抜粋 : https://pages.awscloud.com/rs/112-TZM-766/images/AWS-ETL-Solutions-202107.pdf#page=35
53. ETLで実施する処理 53 1. S3上のログオブジェクトのGET 2. マルチラインのログレコードのパース 3. パースしたログファイルのParquetへの変換 4. ログ種別ごとのParquetファイルの作成 5. ParquetファイルのS3へのPUT
54. ETLで実施する処理 54 1. S3上のログオブジェクトのGET 2. マルチラインのログレコードのパース 3. パースしたログファイルのParquetへの変換 4. ログ種別ごとのParquetファイルの作成 5. ParquetファイルのS3へのPUT
55. マルチラインのログレコードのパース 55 愚直にその⾏頭がタイムスタンプで始まらない 場合は、前の⾏と結合 1GiBのログ = 5,693,643 ⾏ほどのパースで1分 少々で完了
56. パースしたログファイルのParquetへの変換 56 S3上のオブジェクトの処理といえば、AWS SDK for Pandas 抜粋 : https://aws-sdk-pandas.readthedocs.io/en/stable/ • AWS のサービス(S3/Athena/Glue/Redshift など)と Pandas を簡単に連携させるためのライブラリ • データの読み書き、ETL 処理、データカタログ管理などを Python コードで効率的に実⾏可能 • Athena クエリや Redshift 操作などをPythonで実⾏するこ とも可能
57. と思っていた時期が僕にもありました   57
58. AWS SDK for Pandas / Polars / DuckDBの性能⽐較 58 LambdaでS3バケット上のCSVファイルをParquetへ変換する際の処理時間とメモリ使⽤量 抜粋 : https://pages.awscloud.com/rs/112-TZM-766/images/AWS-ETL-Solutions-202107.pdf#page=35
59. DuckDBを選ばない理由ないのでは? 単純にParquetに変換するだけなら 59
60. 2万⾏程度のレコードで実際に試した結果 60 DuckDBの⽅が12倍速い
61. DuckDBの簡単な紹介 61 • OLAP特化のSQLデータベースエンジン • 列指向のベクトル化された処理により、並列で⾼速な データ分析を実現 • シングルバイナリとして動作するため導⼊が簡単 • ⼤規模データや複雑な分析クエリでもインメモリデータ ベースより⾼速で処理できる • S3バケット上のオブジェクトと直接読み書き可能 • DataFrameとの相互変換も可能 抜粋 : https://duckdb.org/
62. DuckDBでCSVファイルをParquetに変換してS3にPUTする⽅法 62 CTASで作ったテーブルをコピーするだけ AWS SDKでPUTする必要はない
63. ログ種別ごとのParquetファイルの作成 63 ログ種別ごとのフィルター条件のオブジェクトを作成してループするだけ
64. ETL処理後にスロークエリログを抽出 64 実⾏時間は15分の1に、スキャン量は232,448分の1に before after
65. ログの種類でパーティションを切っているため、スキャン量は変わらず少ない 全カラムの取得 65 before after
66. まとめ
67. ログ取得の⽬的と、重要視する評価軸を定めよう 67 • ⽬的がないログ取得は、ランニングコストも対応コストも、もったいない ◦ ログ取得の意味がないのあれば取得しないのも選択肢 ◦ コスト重視なら (トラブルの発⽣確率 × トラブル時の対応コスト) + ((1 - トラブルの発⽣確率) + (環境構築のイニシャルコスト + 平常時のランニングコスト) が最⼩になるようにしよう • ⽬的と重要視する評価軸を定めることで、芯が通った適切な判断ができる • 判断をする際にはトレードオフとなるものも整理し、評価しよう