2025/4/23 クラスメソッド株式会社 のんピ コスト最適重視でAurora PostgreSQLの ログ分析基盤を作ってみた 

⾃⼰紹介 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 

CloudWatch Logsに流しているDBのログに 対する課⾦が⾼すぎる！！！！ 4 

ここでいうDBのログの例 5 ● エラーログ ● スロークエリログ ● 接続ログ ● デッドロックログ ● 監査ログ ● autovacuumログ ● チェックポイントログ ● レプリケーションログ ● 統計情報ログ 

ここでいう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 項目 説明 活用するシチュエーション エラーログ 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 を有効にして出力 ● クエリプラン変更の調査 ● 統計情報収集の最適化 ● 実行計画のトラブルシューティング 

特定ログだけCloudWatch Logsへエクスポートしたいところだが 8 Aurora PostgreSQLは先述のログを PostgreSQLログ としてまとめてしまう 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUser Guide/USER_LogAccess.Concepts.PostgreSQL.html 

では、CloudWatch Logsにエクスポートしなければ良いのでは? 9 ログは最⼤7⽇までしか保存できない 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html 

ほとんどのログをCloudWatch Logsに流す形に なりがち つまりは 10 

CloudWatch Logsの課⾦が膨れる ということは 11 

この悩みを聞いてよく挙げられる構成 12 Logsに出⼒したログをData Firehoseを介してS3バケットにPUTする形 

果たしてこれは正解なのか?   13 

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時点の東京リージョンの料金 

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 ログの保存料⾦が若⼲安くなるが、Data Firehoseの料⾦がプラスでかかる = 4円安くするために5円払うイメージ Logsのデータ取り込みサイズにかかる課⾦の逆転は難しい 5KB単位のデータ取り込み : 0.036 USD/GB (5.4円/GB) 

CloudWatch Logsの取り込み料⾦に対する フォローが必要 つまりはコスト削減のためには 17 

私の経験 18 Logs Insightを実⾏すると数TBに対してスキャンが実⾏され、⼀回の実⾏で数千円 から1万円程度の課⾦が発⽣することも (当時はLogsのフィールドインデックス機能GA前) 監査ログ取得の関係でログを月10TBペースで出力 

アプローチの整理 

考えられる⽅法 20 1. CloudWatch Logs Infrequent Access (Logs IA)に流す 2. DBインスタンスのログファイルをS3に流す仕組みの⾃作 

AuroraのログファイルはDBインスタンス内に出⼒される 21 ログファイルを取得するためにはDBクラスター内の個々のDBインスタンスにアクセスが必要 抜粋 : https://docs.aws.amazon.com/AmazonRDS/latest/Aurora UserGuide/Aurora.Overview.html 

Pros/Cons 22 項目 Pros Cons Logs IAに流す ● 手間がかからない ● ログ欠損のリスクが少ない ● メトリクスフィルターやサブスクリプションフィル ターを使用できない ● ログの取り込み料金は Logs Standardと比較 して半額程度しか削減できない S3に流す仕組みの自作 ● ログ取り込みコストの大幅削減が可能 ● Athenaのパーティション分割による ログ分析時のコストの減少 ● 処理の中でログの種別ごとに出力先を変更するこ とも可能 ● PostgreSQLの場合有効 ● ログ欠損の可能性がある ● ログをリアルタイムで確認できない ● 特定の文字列が含まれるログが出力された場 合の通知実装のハードルが高い ● 仕組みのメンテナンスが必要 

DBインスタンスのログファイルを S3に流す仕組みの⾃作 圧倒的にコスト最適化が重要視されるのであれば 23 

DBインスタンスのログファイルをS3に流す仕組み の実装 

前提 25 ● 紹介するアーキテクチャの導⼊を積極的にお勧めするものではない ● Consに挙げた内容はよく理解した上で導⼊すること ○ 特にAurora MySQLはローカルストレージ使⽤率が15%以上 or 24時間経過後にログを削除 し始めるため、ログ⽋損のリスクが⾼い ● ログを⾒た上でアクションする運⽤が全くイメージ着いていないなら 「ログを取得しない」選択肢もある ○ 「ログの⾒⽅が分からない」は勉強すれば良いだけではある ○ 「このログを取る意味が全く分からない」は再考の余地あり コストとログ運⽤のバランスを考えて導⼊しよう 

アーキテクチャ 26 

ポイント1: LastWrittenによる絞り込みと重複ログファイルの抽出 27 S3バケット上に存在しないものをのみをPUTして、Lambdaのコストを削減 

ポイント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 

ポイント2: DownloadCompleteLogFile を使⽤する 29 ⾃分でHTTPリクエストを組み⽴てる必要はあるが DownloadCompleteLogFile の⽅が逆に楽 

ポイント3: stderrに出⼒ 30 ログの出⼒形式は stderr か csvlog を選択可能 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.overview.parameter-groups.html 

ポイント3: stderrに出⼒ 31 ただし、csvlog を指定する場合、stderr と csvlog の両ファイルが⽣成される = DBインスタンスのローカルストレージ圧迫につながる 

ローカルストレージのサイズはDBインスタンスクラスごとに固定 32 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraPostgreSQL.Managing.html#AuroraPostgreSQ L.Managing.TempStorage 枯渇したがためにスケールアップするのは もったいない 

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 

ポイント4: S3オブジェクトの圧縮 34 S3は自動で圧縮を行わないため Lambda関数内でチャンクごとに分割して gzip level 6で圧縮 圧縮率は1.5%ほどになることも 

S3オブジェクトの圧縮をするとスキャン量が減り、コスト削減に 35 ⾮圧縮の場合 gzip圧縮の場合 

この仕組みはAWS CDKで⼀発でデプロイできる様にしています 36 

コスト試算 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 

ではない 39 

ログは利⽤されて初めて活きる 40 

再掲 : 特に運⽤の中でよく⾒るであろうログ 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 

アドホックなログの分析であればAthena 43 ● サーバーレスのクエリサービス ● データレイクに対してSQL形式のクエリを実⾏する ● サーバの管理やインフラの構成などの⼿間をかけずに、 瞬時にデータ分析を始めることが可能 ● Apache Trino のオンデマンド実⾏の場合は課⾦単位は スキャンされたデータ量 スモールスタートしやすいAthenaがマッチ ※ Athenaだけに任せるのではなく、横串でみたい場合や、 SIEMがあるならSIEMに載せるのはもちろんアリ 

Athenaでログ分析をする際の考慮事項 44 1. ピンポイントで対象のログ種別を集計しようにもコストがかかる ○ ログ種別ごとにパーティションが別れていない 2. 特定の列のみの抽出する場合でも実⾏時間が⻑くなりがち 3. マルチラインのログレコードが存在する 

ピンポイントで対象のログ種別を集計しようにもコストがかかる 45 Athenaはパーティションを切ることでスキャン範囲を調整することができる = パーティションが区切られていなければスキャン範囲が広がる = コストがかかる パーティションが YYYY/MM/DD/HH/ のみ スロークエリのログだけ抽出したいのに全量 スキャンされてしまう 

ログの種別によって出⼒先ログファイルを振り分けられるか 46 残念。エラーログもスロークエリログも監査ログも単⼀のログファイルに出⼒ 抜粋 : https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/USER_LogAccess.Concepts.PostgreSQL.html 

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 列指向のファイル形式ではない場合、クエリで参照される列のみの読み込みが できない = 時間もお⾦もかかる 1つのカラムだけ抽出したいのに 全量スキャンされてしまう 

マルチラインのログレコードが存在する 49 例) stderrに出⼒している場合の監査ログ 

RegexSerDe では複数⾏をパースできない 50 InputFormat でレコードを読み込んだ後にデシリアライズする 抜粋 : https://hive.apache.org/docs/latest/serde_27362059/#regex = 正規表現でマルチラインのレコードを⼀つにまとめることはできない 

ETL処理が必要 つまりは 51 

AWSにおけるETLを実施する基盤 52 今回は1ログファイルは解凍後でMAX1GB程度であるため、Lambdaで⼗分 抜粋 : https://pages.awscloud.com/rs/112-TZM-766/images/AWS-ETL-Solutions-202107.pdf#page=35 

ETLで実施する処理 53 1. S3上のログオブジェクトのGET 2. マルチラインのログレコードのパース 3. パースしたログファイルのParquetへの変換 4. ログ種別ごとのParquetファイルの作成 5. ParquetファイルのS3へのPUT 

ETLで実施する処理 54 1. S3上のログオブジェクトのGET 2. マルチラインのログレコードのパース 3. パースしたログファイルのParquetへの変換 4. ログ種別ごとのParquetファイルの作成 5. ParquetファイルのS3へのPUT 

マルチラインのログレコードのパース 55 愚直にその⾏頭がタイムスタンプで始まらない 場合は、前の⾏と結合 1GiBのログ = 5,693,643 ⾏ほどのパースで1分 少々で完了 

パースしたログファイルの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 

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 

DuckDBを選ばない理由ないのでは? 単純にParquetに変換するだけなら 59 

2万⾏程度のレコードで実際に試した結果 60 DuckDBの⽅が12倍速い 

DuckDBの簡単な紹介 61 ● OLAP特化のSQLデータベースエンジン ● 列指向のベクトル化された処理により、並列で⾼速な データ分析を実現 ● シングルバイナリとして動作するため導⼊が簡単 ● ⼤規模データや複雑な分析クエリでもインメモリデータ ベースより⾼速で処理できる ● S3バケット上のオブジェクトと直接読み書き可能 ● DataFrameとの相互変換も可能 抜粋 : https://duckdb.org/ 

DuckDBでCSVファイルをParquetに変換してS3にPUTする⽅法 62 CTASで作ったテーブルをコピーするだけ AWS SDKでPUTする必要はない 

ログ種別ごとのParquetファイルの作成 63 ログ種別ごとのフィルター条件のオブジェクトを作成してループするだけ 

ETL処理後にスロークエリログを抽出 64 実⾏時間は15分の1に、スキャン量は232,448分の1に before after 

ログの種類でパーティションを切っているため、スキャン量は変わらず少ない 全カラムの取得 65 before after 

まとめ 

ログ取得の⽬的と、重要視する評価軸を定めよう 67 ● ⽬的がないログ取得は、ランニングコストも対応コストも、もったいない ○ ログ取得の意味がないのあれば取得しないのも選択肢 ○ コスト重視なら (トラブルの発⽣確率 × トラブル時の対応コスト) + ((1 - トラブルの発⽣確率) + (環境構築のイニシャルコスト + 平常時のランニングコスト) が最⼩になるようにしよう ● ⽬的と重要視する評価軸を定めることで、芯が通った適切な判断ができる ● 判断をする際にはトレードオフとなるものも整理し、評価しよう 

No content