#phpcon ©2022 RAKUS Co., Ltd. PostgreSQL + TimeScaleDBで ログ管理検討 2022/09/25 PHPカンファレンス2022 山内覚

#phpcon 自己紹介 ● 2019/07 株式会社ラクス入社 ● 業務内容 ○ サービスの新バージョンリリースに向けた作業 ○ ミドルウェアの検証 ● やってきたこと ○ PHPバージョンアップ(7.3→8.0) ○ PostgreSQLのバージョンアップ（9.6→12) ○ OSバージョンアップ

#phpcon ラクスのサービス ● 6サービスをPHP+PostgreSQLで作成している

#phpcon 時系列データについて

#phpcon 時系列データとWebアプリケーション ● 時系列データ ○ 時間とともに記録されるデータ ○ 取引記録 ○ センサーから取得したデータ ○ 操作ログ ● Webアプリケーション ○ データはリレーショナルデータベースで管理することが一般的 ○ 時系列データを扱いたい場合がある

#phpcon 時系列データとリレーショナルデータベース ● 時系列データ ○ 時間とともにデータが増え続ける ○ 一日のデータ量が多いかつ保存期間が長い場合はデータ量が膨大になる ○ データに対する操作は追加と削除のみで更新は必要ない ● リレーショナルデータベース ○ トランザクションや結合を使った複雑なデータ処理が得意 ○ テーブルが大きくなると追記や検索が遅くなる ○ トランザクションや結合が必要ない時系列データを扱うには機能過多かつデー タ量が増えるたびに遅くなってしまう

#phpcon 弊社サービスと時系列データの問題

#phpcon 特定サービスと時系列データの問題 ● システムに対する操作を操作ログとしてPostgreSQLに保存 ● 通常操作では大きく増えないがバッチ処理により大量の操作ログ が記録される場合がある ● 操作ログによりDBサイズの増大やメンテナンス時間の増加が発生 している

#phpcon 特定サービスのDBサイズに対する操作ログの割合 ● 操作ログがDBサイズ の50%以上を占める 環境が約1/4

#phpcon 操作ログが増える問題の対応方法検討 ● 記録内容の削減 ○ バッチ処理など似た内容の操作はまとめて記録する ○ 今までより情報が減ってしまう ● PostgreSQL以外で管理する ○ NoSQLなどで管理する ○ バックアップなどの運用が複雑になってしまう ● 圧縮 ○ ミドルウェアの機能で操作ログを圧縮する ○ 処理時に解凍が必要になり処理遅延が発生する 　⇒　TimeScaleDBで圧縮した場合の効果と検索時間を検証

#phpcon TimeScaleDBの紹介

#phpcon TimeScaleDBの紹介 ● TimeScaleDBとは ○ PostgreSQLで時系列データを扱うための拡張機能 ○ アメリカのTimeScale社が開発 ● 主な機能 ○ 時刻列によるテーブルパーティショニング ○ 一定期間を過ぎたデータの圧縮 ● 利点 ○ SQLをそのまま使える ○ ダンプ／リストアといったメンテナンス機能を使える ○ レプリケーションにも対応

#phpcon TimeScaleDBのライセンス 圧縮機能はTimescale Licenseで使える [Timescale License] TimeScaleDBを使って Database as a Searvice を提供しない場合は自由に使え る ※使用する場合はライセンス本文のご確認をお願いします。 https://www.timescale.com/legal/licenses 機能 できること ライセンス Apache-2 ・時刻列でのテーブルパーティショニング Apache License 2.0 Community ・パーティションテーブルの圧縮 ・パーティションテーブルの並び替え、移動 Timescale License

#phpcon TimeScaleDBのリリース頻度 ● リリース頻度 ○ 2.8.0 (2022-08-30) ○ 2.7.2 (2022-07-26) ○ 2.7.1 (2022-07-07) ○ 2.7.0 (2022-05-24) ○ 2.6.1 (2022-04-11) ○ 2.6.0 (2022-02-16) リリース頻度が高く活発に開発されている v2.5.0以降はPostgreSQL14にも対応 https://docs.timescale.com/timescaledb/latest/overview/release-notes/

#phpcon TimeScaleDBのアーキテクチャ ハイパーテーブル (親テーブル) チャンク (子テーブル) 09/08 - 09/13 圧縮なし チャンク (子テーブル) 09/15 - 09/21 圧縮なし 一定期間を過ぎたチャンクを圧縮する設定が可能 PostgreSQLの継承による パーティショニングで実現 チャンク (子テーブル) 08/25 - 08/31 圧縮済み チャンク (子テーブル) 09/01 - 09/07 圧縮済み ・・・ INSERT、UPDATE、DELETEが可能 ・INSERTは可能 ・UPDATE、DELETEは不可 ・削除する場合はチャンクごと削除する 　※DROP TABLE と同等のため、高速かつ不要領域が発生しない

#phpcon TimeScaleDBのデータ圧縮の考え方 ・検索頻度が多い ・検索条件にさまざまなカラムを指定する傾向がある ・全カラムの情報を取得する場合が多い 　※データ取得時に全カラムの解凍が必要 　⇒ 圧縮しない方が効率的 ・検索頻度が少ない ・検索条件は少なくなる傾向がある ・特定カラムのデータのみを取得することが多くなる 　※ 古いデータは特定カラムの合計値のみ必要と 　　いった場合は、そのカラムのみを解凍すれば良い 　⇒ 圧縮する方が効率的な場合が出てくる チャンク 09/08 - 09/13 圧縮なし チャンク 09/15 - 09/21 圧縮なし チャンク 08/25 - 08/31 圧縮済み チャンク 09/01 - 09/07 圧縮済み ・・・ 新しい 古い

#phpcon TimeScaleDBの圧縮メカニズム 時刻 ユーザID 操作ID 操作対象 12:00:02 10001 15001 売上記録 12:00:02 10002 15002 見積記録 12:00:01 10001 15001 売上記録 12:00:01 10002 15002 見積記録 時刻 ユーザID 操作ID 操作対象 [12:00:02, 12:00:02, 12:00:01, 12:00:01] [10001, 10002, 10001, 10002] [15001, 15002, 15001, 15002] [売上記録, 見積記録, 売上記録, 見積記録] ・各カラムのデータを配列に変換して 1レコードに収める ・カラムごとにデータを圧縮する 　⇒ カラムごとに似た値が入るため圧縮率が上がる 圧縮前 圧縮後

#phpcon TimeScaleDBの圧縮メカニズム(分割カラム) 時刻 ユーザID 操作ID 操作対象 12:00:02 10001 15001 売上記録 12:00:02 10002 15002 見積記録 12:00:01 10001 15001 売上記録 12:00:01 10002 15002 見積記録 ・分割カラムを指定するとその値ごとにレコードが分かれる 　⇒ 他のカラムは似た値が揃うため圧縮率が上がる ・分割カラムは圧縮しないため検索条件に指定すると検索が速くなる 時刻 ユーザID 操作ID 操作対象 [12:00:02, 12:00:01] [10001, 10001] 15001 [売上記録, 売上記録] [12:00:02, 12:00:01] [10002, 10002] 15002 [見積記録, 見積記録] 圧縮前 圧縮後

#phpcon TimeScaleDBによる圧縮効果と 検索時間の測定

#phpcon 測定に使ったデータ 項目 内容 テストデータ 特定サービスの操作ログ ログ件数 約620万件 サイズ 5.64GB ログの保存期間 約3ヶ月 PostgreSQL v12.11 TimeScaleDB v2.7.2 時刻列の分割単位 7日(デフォルト)

#phpcon 圧縮効果の測定 No 分割カラム 圧縮前の サイズ(GB) 圧縮後の サイズ(GB) 圧縮後の サイズ割合(%) 1 なし 5.64 0.60 10.64 2 ユーザID 5.64 0.54 9.57 3 操作ID 5.64 0.51 9.04 4 ユーザID、操作ID 5.64 0.54 9.57 ● 測定結果 ○ 圧縮効果が高い(圧縮後のサイズが約10%) ○ 分割カラムを指定すると圧縮率が少し変わる ● 測定の観点 ○ 分割カラムによる圧縮効果の違いを検証 　　　　※バッチ処理ではユーザID、操作IDが偏るため分割カラムに指定

#phpcon 検索時間の測定 ● 測定の観点 ○ TimeScaleDBを使わない場合とTimeScaleDBで圧縮した場合を比較 ○ 検索条件に分割カラムを指定した場合と指定しない場合を比較 ● 測定に使ったSQL ○ SELECT * FROM 操作ログテーブル ○ WHERE ○ 　　　date >= ‘開始時刻' and date < ‘終了時刻' ○ 　　　[and　分割カラム = xxx ] ○ ORDER BY date DESC ○ LIMIT 20;

#phpcon 検索時間の測定結果 TimeScaleDBなし TimeScaleDBあり No 検索条件 ※ WHERE句の指定 圧縮なし(ms) 圧縮あり(ms) 分割カラム: なし 圧縮あり(ms) 分割カラム: ユーザID 圧縮あり(ms) 分割カラム: 操作ID 圧縮あり(ms) 分割カラム: ユーザID、操作ID 1 日時 0.30 3.76 4,895.11 4,544.18 4,946.96 2 日時、ユーザID 0.35 3,969.16 2.71 3,379.89 3.00 3 日時、操作ID 0.26 3,965.80 3,697.63 2.17 2.91 4 日時、ユーザID、操作ID 0.25 3,974.10 2.70 2.41 3.31 5 日時、操作対象 (LIKE検索) 2,061.43 4.404.57 4,140.75 3,787.58 4,281.61 ● 測定結果 ○ 検索時に圧縮したデータの解凍が必要な場合は検索時間が大幅に増える ○ 分割カラムをWHERE句に指定した場合は解凍せずにレコードを絞り込めるた め高速に検索可能

#phpcon まとめ

#phpcon TimeScaleDBの圧縮効果と検索時間の測定 ● 圧縮 ○ データサイズが約１０％になり圧縮効果が高い ● 検索 ○ 圧縮データの解凍が必要になった場合は非常に遅くなる ○ 分割カラムをうまく使うことで検索を高速に行うことができる

#phpcon TimeScaleDBの圧縮機能の使いどころ ● 既存のアプリケーション ○ 古いデータはほとんど検索されないといった傾向があるなら導入可能 ○ そうでない場合はユーザに影響があるため使いづらい ● 新規で開発するアプリケーション ○ 既存ユーザへの影響を考慮する必要がないため導入しやすい ○ 圧縮していないデータと圧縮しているデータのUIを分けると良い ※3ヶ月前の圧縮済みデータはダウンロードのみにするなど ● データ分析基盤として使用 ○ 圧縮できるため大量のデータを少ないストレージで扱える ○ SQLを使ってデータ分析ができる

#phpcon ご清聴ありがとうございました。