Amazon Redshiftチューニングメモ(WIP)

仕事でRedshiftのチューニングをすることになりそうなのでメモ

※適宜更新

  • 2018/04/04 更新
  • 2018/04/11 更新
  • 2018/10/04 更新。嘘いっぱい書いてたのを訂正。分散キーに関する項目を拡充
  • 2018/11/07 更新。列圧縮に関する嘘八百を訂正。ソートキーに関して追記
  • 2020/02/06 改めて見直すと肝心なことが書かれていなかったので更新。

この記事が想定する事例

  • 検証環境では5秒~10秒程度で帰ってきているが、一定の負荷がある本番環境では何故かパフォーマンスが半分以下になる。
    • Redshiftは並列分散で、1つのタスクにリソースを全力投入することでパフォーマンスを出している。AWSガイドラインが提示しているチューニングは、実はリソース消費を低減させることに重きを置いている。(ソートキー、列圧縮タイプなどは副次作用もある)

Redshiftのチューニングの前に

色々な概念の紹介

クラスタ

https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html

Amazon Redshift データウェアハウスは、ノードと呼ばれるコンピューティングリソースのコレクションであり、これらはクラスターと呼ばれるグループを構成します。各クラスターは、1 つの Amazon Redshift エンジンを実行し、1 つ以上のデータベースを含みます。

1機の仮想DBサーバー。 クラスタ一つサーバーとして落としたり上げたりしたり、稼働時間=課金額になったりする。 クエリを投げるのもクラスタに対して投げる。

リーダーノード

https://doc.aws.amazon.com/ja_jp/redshift/latest/dg/c_high_level_system_architecture.html

クラスタの先頭に備えている、1機のコンピューティングリソース。 SQL構文解析SQL結果キャッシュ、WMLの機能(コンピューティングノード処理の同時処理数制限など)を担当。

ノード、コンピューティングノード

https://docs.aws.amazon.com/ja_jp/redshift/latest/mgmt/working-with-clusters.html

Amazon Redshift クラスターは、ノードで構成されています。クラスターごとに、リーダーノードと 1 つまたは複数のコンピューティングノードがあります。リーダーノードは、クライアントアプリケーションからクエリを受け取ってクエリを解析し、クエリ実行プランを作成します。次に、これらのプランの並列実行をコンピューティングノードと調整し、コンピューティングノードからの中間結果を集計します。最終的にクライアントアプリケーションに結果を返します。

演算、データ保持担当のコンピューティングリソース。 料金表を眺めたことがある方は、以下のインスタンスタイプに見覚えがあるかもしれない。

現在の名前 以前の名前
ds2.xlarge ds1.xlarge、dw.hs1.xlarge、dw1.xlarge
ds2.8xlarge ds1.8xlarge、dw.hs1.8xlarge、dw1.8xlarge
dc1.large dw2.large
dc1.8xlarge dw2.8xlarge

内部に「ノードスライス」という末端コンピューティングリソースを持つ。CPUコア1つにつき1スライス。

ノードスライス

コンピューティングノード内の一番最小のリソース割当単位。CPUや記憶領域(HDD・SSDやメモリ)のリソースセット。 コンピューティングノードのCPU 1 コアにつき 1 ノード存在する。テーブルの実データは、どれかのスライスに分散される。

料金表には出てこないが、パフォーマンスチューニングでは重要な概念。(詳しくは後述)

システムテーブル STV_TBL_PERM を用いれば、テーブルごとの各スライスの分散状況が見れる。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_TBL_PERM.html

データブロック と ゾーンマップ

Redshiftは、列ごとに、1 MB 区切りのブロックにデータを格納する。このブロックをデータブロックと呼ぶ。 各データブロックごとにゾーンマップと呼ばれるメタ情報があり、値の最大値・最小値やブロック内の値の個数などのメタ情報を保持する。ゾーンマップはメモリに保持される。

データブロックは、システムテーブル STV_BLOCKLIST から一覧が見れる。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html


チューニング:スライスと分散

  • 並列分散システムの弱点。データを一か所に集める演算が必要になったときにデータをネットワーク経由で全ノードに転送(再分散)
  • 1回あたりの再分散は意外なほど高速。だが、JOINやサブクエリを滅茶苦茶重ねたりすると、ネットワーク帯域や演算結果を保持するためのメモリを浪費する。重い再分散をスロークエリが数個あると、帯域が枯渇して急激にパフォーマンスが低下する。
  • 分散スタイルのチューニングは、行数・列数が多く、ソートキーなどでスキャン範囲を限定できないテーブルを含むGROUP BY/JOINに有効。

スライスへの分散

レコードが挿入された時、テーブルごとに設定された分散スタイルに従っていずれかのノードに分散される。

分散スタイルについて

  • EVEN分散 ・・・ ラウンドロビンで均等に分散される。何も指定しないときはこれとなる。
  • KEY分散・・・ 列を1つ指定し、その値によってどのノードに配分するか決定する。
  • ALL分散 ・・・ すべてのノードに分散する。(データは全ノードで重複して持つ)。INSERTやCOPYのときのトラフィック総量がスライス数分倍増。また、並列演算のリソースも食う。

スライスごとに並列で計算するので、均等に分散ができているほど、特定のスライスに負荷がかかりすぎるなどが発生せず高速に演算できる。

再分散

2つのテーブルを結合するとき、各結合するデータ同士が同じスライスに分散されている場合は、そのまま計算するが、違うスライスにあった場合は、全ノードにばらまきなおす「再分散」が起きる。 explainでSQLの実行計画を見ると、「DS_BCAST_INNER」など「BCAST」付きの結合をしていると出力される場合がある。これは再分散が発生している。 再分散を避ける設計は2つ。

  1. 分散キーを複数テーブル間で同一に設定する
  2. outer 側の結合テーブルをALL分散に設定する。

分散キー

分散スタイルで「キー分散」を選ぶときは、テーブルのうち1つの項目を指定する。 一般的に、レコード数の偏りがないものを選ぶとよい。(受注番号やidなど連番で重複のないもの) 上記の再分散を避けるため、「ヘッダー」-「明細」のようによく結合すうるテーブル間で共通のキーを設定するとよい。

分散キーを実行プランから測定する

分散キーは、テーブルのJOINで結合キーに指定していると再分散を防げる。 実際に効果があるかどうかは、explainで実行計画をみてテーブル同士の結合時の分散のしかたを見るとよい。 (コストに関してはあまりアテにならないので見ない)

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_data_redistribution.html

上記にある通り、 DS_BCAST_INNERDS_BCAST_BOTH が出ると結合のパフォーマンスが著しく悪くなる。

いくつかSQLを作って実行計画を見た感じ、2のテーブルをJOINする程度の簡易なクエリであれば DS_BCAST_INNER は発生せず、 少し複雑になって tableX <-> tableY <-> tableZ <-> tableV で結合するSQLが分散キーで不一致になると割と出るようになる。

クエリの実行ログから分散の処理データ量を見る

分散の回数、転送バイト数、処理した行数が見れる

-- トランザクションを初めておく
BEGIN;

-- 測定したいクエリを実行
SELECT col, count(*) FROM some_queries GROUP BY col;

-- 最後に想定したクエリのquery idを取得
SELECT PG_LAST_QUERY_ID();

-- BCASTな分散のログ
SELECT * FROM pg_catalog.stl_bcast WHERE query = <↑で見たqueryid>;
-- DISTな分散のログ
SELECT * FROM pg_catalog.stl_dist WHERE query = <↑で見たqueryid>;

脳死的分散スタイルの決め方

  • 条件:LEFT JOINするようなマスタデータ(1000万件の実績データに対して数件~10万件のマスタをあてるなど)
    • →選択:ALL分散
  • 条件: マートやFACT、実績データなど、参照のしかたでGROUP BY/WHERE/JOIN キーが不定

    • →選択:EVEN分散
  • 条件:EVENだとJOINが遅い、バッチなどでしか使わず結合、GROUP BYのキーなどが一定

    • →選択:KEY分散だけど脳死できない

分散キーのBADパターン : クエリを実行してみるとスライスがめっちゃ偏る

例)日次アクセス実績データ

path logday logym ua_category pvcount
/index.html 2018/01/01 201801 bot 3000
/index.html 2018/01/01 201801 pc 300
/index.html 2018/01/01 201801 sp 500
/index.html 2018/01/02 201801 bot 3300
/index.html 2018/01/02 201801 pc 300
/index.html 2018/01/02 201801 sp 500

危ないパターン:集計はだいたい月ごとでやるから、logymに distkey 設定したら再分散避けれるんじゃないか?(単純に集計単位となるキーをあてる)

  • 下記のSQLは確かに再分散を避けれるが、検索条件を含むスライスが1つしかなく、コンピューティングノードごとの並列処理がされない。
--日次のレポートつくるときのSQL
SELECT logday, ua_category, SUM(pvcount)
FROM taccesslogsummary
GROUP BY logday, ua_category
WHERE logym='201801'; -- ここ
  • logymのこの値を持つノードは分散キーにより1つだけ = このSQLの裏で稼働するコンピューティングノードが 1 つしかない。
  • 再分散を避けるより、EVEN分散にして全ノードで分散演算したほうが、データ量によっては速い

    • 特にアクセスログのような実績は「経営資料として担当組織別に出して」とか「特集別」とか「ユーザーのデバイス別」など軸がぶれっぶれなので再分散を防げる共通の軸が存在しないことが多い。
  • 分散キーはワイルドカードではなく、実行計画において再分散を避けるだけの効果しかない。

  • 実際に発行するクエリに対する効果とセットで見積もるべき。
  • SQLを実行してから、システムテーブル STL_PROJECT を見ると各スライスから読み出した行数を見れるので、そちらで計測するとよし。

チューニング:ソートキー

  • チューニングの奥義。ゆえに時と場合、代償が求められる。

  • 通常、INSERTすると一番最後のデータブロックに追記される形でデータが格納。

  • 値が自然とソート順になれば問題ないが、1つのデータブロック内で値の最大/最小値が大きくなるとWhere句による読み込みスキップが発生しづらくなる。

    • データブロックは1つごとにゾーンマップ(最大値、最小値、データ個数など)のメタ情報があり、スキャンする際にWHERE句などに設定される内容が最大値/最小値の範囲外であれば、データブロック(I/O)の読み込みをスキップする。
  • テーブルにソートキーを設定すると、最後にVACUUMした or テーブル0件から INSERT した時点までのデータが、ソートキー順に物理的に並ぶ。この物理的にソートされた列に対してWHERE句をかけると劇的に効く。

  • もちろんソートキーが設定されている項目をクエリで使ったときのみ有効。理屈では、ソートキー以外の列のゾーンマップは最大・最小値が相対的に拡大するため、そちらのみをフィルタするクエリの性能は数%劣化する。

  • また、VACUUMの際にsort、mergeといった非常に負荷の高いステップが追加される。

【2019/06/23追記】詳しい記事をかいた。


チューニング:列圧縮タイプ

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Compressing_data_on_disk.html

データの物理的な保存形式を指定する。単純な圧縮ではなくデータ配列の仕方に近く、様々な記録方式がある。(詳しくは公式ドキュメント参照) 最適なものを選べば、スライス内でのディスク→メモリへのページインにおけるディスクI/Oが減る。(・・・が、ディスクReadあたりのメモリに展開される容量が相対的に増える)

  • CREATE TABLE したとき、ソートキーを設定したものは一律 raw(圧縮なし)、それ以外のものはlzo(汎用圧縮)になる。
  • 一般的には高圧縮であればよいが、例えば1MBのデータブロックを読み出すと10GBにメモリ展開される場合、1行欲しい場合でも1データブロック単位での展開が強要されるため過多なメモリコピーが発生するし、スキャンする際はデータブロックが単位となるので、1データブロックで10GB相当のレコードを全件参照することになる。

  • 勘所としては下記のようなケース。

    • 「RAWに比べてLZO選択でディスクサイズが 1/10になる」
    • 「数バイト値に下手な圧縮をかけて容量が5%減程度とかであまり意味がない。RAWにしてINSERT・LOAD時のパフォーマンスを上げる」など。

列圧縮の効果のサマりかた

ディスクサイズのとりかた = STV_BLOCKLISTビューを参照

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STV_BLOCKLIST.html

-- 特定テーブルの列ごとのデータブロック(1MBのディスク書き込み領域)消費量をサマる
SELECT
  name,
  col,
  slice,
  count(*) AS "using disk(MB)",
  avg(num_values) AS "num values/1MB"
FROM
  pg_catalog_stv_blocklist
WHERE name ='tablename'
GROUP BY name, col, slice
ORDER BY name, col, slice;

colがテーブルの列のindex。ただし、末尾3列は暗黙的に追加される非表示列。 ここから、1record 読むあたりのI/O処理バイト数が読める。

Read/Writeの際には、別途列圧縮のための演算が各コンピューティングノードにかかる。

実測値

(2019/08/25追記) ディスク使用率に関する検証結果をまとめた。

packpak.hatenablog.com


WLM(Work Load Manager)

SQL実行したときのリソース割り当て管理をしてくれる。

Amazon Redshiftのワークロード管理(WLM)を使ってミックスワークロードを実行する | Amazon Web Services ブログ

  • 特定ユーザーグループが同時にSQLを実行できる数の上限
  • 特定ユーザーグループが利用できるメモリの上限

CPUが使用率低いのにSQLが実行されない・・・というボトルネックがある場合こいつを見直すとよいかもしれない。 むしろ運用上、①アプリ用グループ ②バッチ用グループ ③保守用グループ ④特権ユーザー用グループ とわけないといざというときにSQLが実行できなくなるので、もし困っているのであれば早めに検討したほうがよい。

デフォルトは同時実行数 5 。理論上は500まで設定可能。しかし、無制限に開放すると様々なリソースを浪費して逆に遅くなる。

クエリがキュー待ちになっているか確認する

システムテーブル STL_WLM_QUERY から、スーパーユーザーなら全ユーザーの実行履歴、通常ユーザーならばそのユーザーの分のSQL実行履歴が見れる。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_STL_WLM_QUERY.htmldocs.aws.amazon.com

その中で total_queue_time が 0 でなければ、そのクエリは同時実行数の制限によりキュー待ちとなっている。(単位はマイクロ秒)


VACUUM と ANALYZE

Redshift公式でも、定期実行が勧められている。

VACUUM

DELETEやUPDATEによりできた不使用空間のコレクト、ソートキーに基づいた再ソートを実施する。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/r_VACUUM_command.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/t_Reclaiming_storage_space202.html https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html

  • 削除フラグがついた無駄領域を削減することで、スキャンのミス率を低減する。
    • 2018年11月ぐらいのアップデートで、不要領域のVACUUMはバックグラウンドで自動実行されるようになった。ソートキーのついていないテーブルなら実行不要に。
  • ソートキーを設定しても更新をした時点では、新しいデータはソートキー順に並んでいない。ソートキー順に並べなおす。
  • なお、デフォルトだと、ソート率95%以上のテーブルはVACUUMをスキップする

ANALYZE

テーブルの統計情報を更新する。

https://docs.aws.amazon.com/ja_jp/redshift/latest/dg/vacuum-managing-volume-of-unmerged-rows.html

  • 統計情報は、各列ごとに作られる。
  • 統計情報はクエリの実行プランに反映。
  • 統計情報がある程度古い場合、初回SELECT時などにオートアナライズがかかる場合がある。
  • テーブルの全体行数に対する変更された行の割合が、パラメータanalyze_threshold_percent以下であればスキップする。
    • analyze_threshold_percent を 0 にするとANALYZEはスキップしなくるが、普通のSELECTやUPDATE・DELETEなどでも参照する時に必ずオートアナライズがかかり、都度更新がかかる環境やバッチ処理はむしろ劇的にパフォーマンス劣化する。永続的な設定をせず、SET コマンドを利用してANALYZE実行セッションのみ 0 書き換えを推奨。

チューニング:列サイズの縮小

大容量のVARCHAR型の利用を避ける

RedhisftはVARCHAR型の列を選択する時、メモリ上に型サイズそのままの領域を確保する・・・といわれおり、パフォーマンスに多大な影響を与えてきた。 過去何回か、対策したらしきアップデートがあったが、どうも日本社内のデータマートではあまり改善が見られなかった。

要件が絡む以上難しくなるが、VARCHAR型を持つ時、下記のようにチューニングできないか検討する。

  • マスタや検索項目で日本語を使いたいなら、別途コード化する。数値型や、ある程度の表意が求められるのであれば、数十バイト程度のかなり短いものにする。
  • もし数百バイトを超える字句を保存する場合は、SELECT時ソートキーによる絞り込み、VACUUM時のmergeによる負荷を検討する。
  • Redshift Spectrum を検討する。(単純にJOINすると意味がなくなるが、1テーブルを単純にSELECTするだけであればコンピューティングノードのメモリを消費しない)

そのほかTips

別記事かく

SQLパーサー作成メモ

備忘録メモ

Postgresql内部実装のSQLパーサー

https://wiki.postgresql.org/wiki/Query_Parsing

raw_parserという関数があるらしい。

postgres/parser.c at 9d4649ca49416111aee2c84b7e4441a0b7aa2fac · postgres/postgres · GitHub

構文別にノードツリーレベルまで展開してくれるが、おそらく意味解析は行われない。 自分は意味解析までほしいのでもうちょっと実装しないといけないが、いい感じにparseしてくれるなら使うかもしれない。

識別子の判別タイミング

Postgresqlは、date など普通はキーワードだろ?という単語も列名にできる。以下は、テーブル定義によってはエラーとならない。

SELECT date FROM ordertransaction;

構文の評価

例えばSQLにおいて、 ( ) は様々な意味合いを持つ。

  • 関数の引数部
  • サブクエリの始点終点
  • 式の結合優先度を決めるだけの演算子
SELECT * FROM (
  SELECT clientid, orderno, orderym, RANK() OVER(PARTITION BY clientid, orderym ORDER BY orderno) AS idx FROM ordertransaction
) Q ORDER BY clientid, idx;

パーサーは前から順に読み込み構文を判断する。 しかし ( ) のように一節を読み切った後に前まで読んでいた式をどう評価すべきか不確定なときがある。

RDBの実装を見たことはないが、おそらくメタメタなパターンマッチングを行わなければいけない気がする。

SELECT count(*) FROM master;
  1. SELECT まで読む → 構文のはじまりはSELECT、INSERT、WITHなど固定のキーワードから始まるため、これはSELECT句で確定
  2. count まで読む → SELECTのあとは、DISTINCTなどのキーワードか、各抽出列の指定(サブクエリ、関数、項目名、定数など)をとりえる。この時点では、count が列名なのか、関数名なのか不明(謎の識別子状態)
  3. ( まで読む → ここで count が関数名だと確定する?
  4. * まで読む → 各抽出列の指定(サブクエリ、関数、項目名、定数など)か、全列を指定するキーワード * をとりえる。パターンマッチングで唯一である * が確定 ...

単なる列指定するところでもサブクエリで複数階層化できるところがメタメタに難しそう。 いい感じの関数のインタフェース考えながら、EBNFとにらめっこして作ることになりそう

Javaおじさん初めてのGolangのinterface所感

延々とだらだらしていてたらいつか死ぬ気がしてきたので、いい加減アウトプットする。 明日続かないとしても今日書けばよいのだ。

Golangさわってみた

Javaを中心にOOPな言語をずっと触ってきて、ちょっと回帰的だがGolangSQLパーサーを描いている。

Golangは色々いわれているように、

  • package指向
  • 構文機能は手続き型レベルのしかない
  • 定番の書き方がググれば出てきてそのとおり実装するのがベストプラクティス

という、スクラッチコンポーネント作るとかどう考えても向いてねえな?な言語。 まあ異文化を痛感するのにちょうどいいだろうと思って、ちょっとリッチなSQLパーサを書いている。

Golangのintafaceに対するJavaの冗長さ

interface いいね。 Javaで例えるなら「にゃーん」と鳴くのを識別するのにオブジェクトを「猫」か「動物」か「発生源」かとか文脈によって違う名前でクラスを付けなければならなかった。 でも、実際コードに起こしてクラスを指定する際、どの文脈使うか決まっている。DIするならともかく、クラスのインスタンス化とか煩わしいことせずともサクッと関数呼び出すだけで直感的に使える。 奇跡的にも、オブジェクト指向原理主義が言うような定義の曖昧さによる述語の揺れは杞憂に過ぎず、熱心は学者先生さんは理論先行頭でっかちだとGopher君が目で訴えている。

実装がんばります

とはいえ、書いているコードがどうもJavaっぽい。 多相の表現が、どうもintafaceを構造体で実装しているだけだ。 しかもどのコードもスクラッチ気味。ググりながら、楽な書き方を覚えるしかないかな。

ちなみに、SQLパーサーが目指すものは例えば集計バッチで流れる一連のSELECT文を4つぐらい食わせて、「このSELECT文のこの項目は、このテーブルのこの項目をこの式で評価したものです。で、このテーブルの結果はまたこのSELECTで・・・」と複数再帰的に辿れるようにすること。 SQLパーサーを探していたが、なんか期待できるレベルまで分解するものがなかった。たぶんPostgreSQLとかCソースをチョチョイってやったほうが早い気がするけど、それはそれで沼っぽいしCあんまわかんないので避けた。

引き続き頑張っていこう。

【.NET】【WPF】Bindingを強制反映するやりかた

遊びでC#を触ったら、まんまと有給を潰してしまった。

毒舌に.NETのWPFのBinding

  • .NET Framework
    • Microsoftが提供している、ウィンドウアプリケーションのランタイム環境。 *ウィンドウ付きのアプリが(裏方にある膨大な概念を意識するのに比べて)いとも簡単に(小さいアプリなら)できてしまう。
  • WPF

  • Binding

    • 従来だと、テキストボックスの文字や有効/無効などのプロパティをコード上の変数とで代入したりこねたりする必要があったのを、XAMLに「{Binding プロパティ名}」って書いたら自動で関連付けてくれるようにする機能。
    • 今回一番悪口を言いたいところ

事故

やりたかったこと

  • テキストボックスのフォーカスが離れた時点で、テキストボックスの値を検証しフォーム上にエラーメッセージを出したい
public partial class ReplaceSetEntryWnd : Window

   public ReplaceSet Value { get; set; } // ビューモデル。定義は割愛

   // フォーカスを失った時に実行するイベントハンドラ
   private void Controls_LostFocus(object sender, RoutedEventArgs e)
   {
      _validateAndShowError();
   }
<!-- Valueのプロパティと連携 -->
<TextBox Name="txName" Text="{Binding Value.Name}" LostFocus="Controls_LostFocus"/>
<TextBox Name="txPattern" Text="{Binding Value.Pattern}" LostFocus="Controls_LostFocus"/>

起きたこと

  • Value.xxxに反映するのは、LostFocusイベントが終わった後。つまり、↑のコードは、テキストボックスからフォーカスが離れた時点では、編集直前のValueの状態が残ったままで _validateAndShowError() を実行してしまう。

対策

  • コントロールから、Binding用のクラスっぽいものを直接取得してコントロール → プロパティに強制送信する。
    private void UpdateSources()
    {
        txName.GetBindingExpression(TextBox.TextProperty).UpdateSource();
        txPattern.GetBindingExpression(TextBox.TextProperty).UpdateSource();
    }
  • ちなみに逆の プロパティ → コントロール強制送信はこれ。
    private void UpdateSources()
    {
        txName.GetBindingExpression(TextBox.TextProperty).UpdateTarget();
        txPattern.GetBindingExpression(TextBox.TextProperty).UpdateTarget();
    }
  • これがマジでイケてない。txNameとかを直接指定したコード組んで、ビューモデルとの連携を疎にする目的で導入したいのに、これができるんならじゃあと欲張ると、結局依存性を増やし記述がごっちゃになるハメに。
  • ちなみに、以下のようにマルチスレッドしながら遅延実行するコードも書いてみたが、.NETコントロールいじるコードで別スレッドからのアクセス違反になった。
new Task(() => {
   Task.Delay(20).Wait();
   _validateAndShowError();
}).Wait();
  • 下手にマルチスレッドするとこうなるの。。非同期でUIのロックを回避したいのに、結局シングルスレッドにせざるをえないってこれもうわかんねえな
  • バックグラウンドプロセス⇔UIの分業と同期をより細かい単位にするために、外スレッドからUI変更をキューイングしていい感じに更新するような仕組みかもしくはFLUXにインスパイヤされた新しいフレームワークがほしい
    • え?なに?Electorn?

2017/09/23 タイトル変えました

分析系システムの業務的なメモ②

ちょっと実務的な部分に絞って2枚目

データ的な特徴

ログデータ

アクセス数やPV数、UserAgentの解析をしたい。とすれば、ApacheとかのWebサーバーのアクセスログから拾って集めて、分析用のDBに片っ端から突っ込むことになる。

Google Analytics などの外部ツールでもこの系のアクセスログは蓄積できるが、内部で集計するためのデータソースにするには情報量の不足や取得までの仕組みの関係で厳しい。

想像のとおり、非常にデータ量が膨大であるため、どういう単位でデータを残すかが非常に悩ましい部分。しかもApacheのログなんかは、何か問題がった時の調査用にログ出力フォーマットを変えたりする。(ヘッダーサイズ出力とか) 柔軟な設計が試される部分。

保守業務で辛いところ

完全に愚痴

業務領域のデータにまつわる変更が全部分析DBに落ちてくる

インプットデータとして、全領域全システムの全テーブルの面倒を見ることになる。

例えば、業務領域の改修でとあるコード値が入力できなくなったとする。(もう使わないしいらないよね?)とすると、そのコード値がリリース時から分析に落ちてこないようになり、後日集計してから「〇〇コードの最新タイムスタンプがこの日で止まってるけどなんなの?この日以降の実績消えたの?」とユーザー問い合わせがくる。

集計処理の変更が滅茶苦茶怖い

5年も続けて保守してきた分析システムの集計はどうなっているだろうか? 一つの答えが、「300行SQLの集計が3個ぐらい連なったバッチ」のような、集計結果のチェーン味噌ダレ。

「とある属性値の選択ルールを、コード最小値から画面上で先頭のものに変えました」なんてのがめっちゃ怖い。レコード数増えたり減ったりしない??集計前後でjoin結果がめっちゃ変わらない?? ここらへんはなるべくポリシー化してできるならポリシーに従った集計を個別に実施して、レイヤ的な集計を実施していきたいが。。

現在、確認範囲を全て確認していっているが、ここらへんうまく自動化できないものかなと考えている。 自動テストしたい。

分析系システムの業務的なメモ

最近、分析系のシステムに携わっている。

色々と学ぶことがあったのでここにメモする。

分析システムの種類

分析システムをニーズから分類すると、「定型レポート」と「データの特徴量や関係性をデータマイニングするツール」に大別できるらしい。(受け売り)

定型レポート系

定型レポートは、アクセス実績とかの定型的な数字を出すためのツール。

例えば特集ページを打ったりしてイベントを実施して、キャンペーン対象のページのアクセスが普段よりこれぐらい上がりましたよー、などの数字を出したり、もっと一般的には、日常的なユニークユーザー(UU)数、コンバージョン(CV)数などを出したりして経営のコンディションを判断したり、サラリーマン的なできたよアピールにつかったりする。

知る限りでは、特定の業態にあった検索フォームを作成して、CSVかなんかにしたりグラフィカルな図に出力したりするツール。SalesforceとかWebForcusとかがパッケージとして有名かな?

データマイニングツール系

もう一つの大きい分野が、データサイエンスちっくに細かく傾向を割り出すツール。

「この商品購入するユーザーって、〇〇にも興味あったりするよね」など、レポーティングシステム化されていないが蓄積データから分析できそうなことをディメンションから切っていくのが主な使い方。

ある程度の規模に収まるんだったら「SQL叩けよ」で済みそうな気もするが、ベンダー間保守契約とか資産管理とかセキュリティとかそもそもそんな人材確保できんの?的な観点で、こういうのもツール化することが多いらしい。

用途的にSQLたたきたーい!みたいなことに近いので、下手にオレオレ製品を作るのはあまりないのかな?(調査不足) 今のところでいうと、 Tableauという製品が流行りのようだ。(今の仕事で使っているし、記事にも紹介が頭から出ていた。) (ガチ勢はSQLやらHadoopやら叩けるんだろうが、前述の通り云々)

基本的なアーキテクチャ戦略

グレーな範囲で今の現場のシステムの基本的な戦略を紹介。

多分バレたら怒られると思うけど、正直ショボいし一般的なものだと思うので大丈夫かもしれない。

分析用DBとデータ移送

データ5万件とかそういう規模だと別に気にしなくてもよいが、ある程度の規模になると、「業務用DB」と「分析用DB」でDBが物理的に分かれる。

理由は2つあって、

  • 1つは参照ロックで業務影響を与えないようにすること。分析のクエリって10分ロックかけるような重いものが普通で、分析システムにて秒でコミットガンガン飛ばそうとしているのに更新待ちになったりタイムアウトしたりと致命的なレベルで問題が発生するため。
  • 2つは分析データの時間軸確定のため。リアルタイム更新するデータを参照しちゃうと、1つめの検索と2つめの検索でちょっとデータ量や数値違って「これ何?」って上司や客先にいわれちゃう。なので、夜間に昨日までのデータだけコピーして集計!みたいなことをする。

DBを分けると、データ移送が必要になる。要するに、業務DBと分析DBで同じテーブル作ってデータをコピーする処理のこと。

データ量によって、全件移送(分析DBを一度クリアして全件コピー)、差分移送(最後に分析DBにコピーした時点から、現在までの更新分だけをコピー・過去データ更新する)の2つを選択する。

差分移送にはいくつか注意点がある。

  • 〇〇時以降のデータのみコピー!が基本的な考えになるが、検索の境界値となるタイムスタンプや連番の最小値を取ってからすぐ移送を開始してはいけない。一度トランザクションを終了し、業務システムが確実にコミットするであろう期間をあけて移送を開始する。
    • DBはコミットするまで更新後データは他のクエリ上から見えない。長引くトランザクションの影響で「2017/01/01 00:11」時点では「2017/01/01 00:10」のタイムスタンプが刻まれた処理が未コミットで移送漏れが起きるかもしれない。
    • Oracleなどトランザクションを切らないとトランザクション中に他のクエリが更新した内容が見れないこともある
  • 異常終了のリカバリをミスると、データが重複してインポートしてしまうことが考えられる。主キー重複だと辛い。
    • 更新キーを予め設定するのもありかな。

集計処理

毎日の夜とか月1とかでよく流す。以下の目的をもって作られる。

  • その日時点の業務データを集計 or 変換して日ごとに蓄積する。(次の日でレコード更新するとデータが変わってしまうため)
  • 分析クエリが複雑になったり無理のあるテーブルのjoinなどでパフォーマンス劣化が考えられるとき、あらかじめ問題になりそうなデータを集計しておく。

気を付けたいのが、集計期間を指定するときに、WHERE句などの中にCURRENT_DATEとか組み込まないで外部パラメータ化すること。

例えば、日付用のテーブルとして「集計開始タイムスタンプ = 2017/01/01 00:00:00」「集計終了タイムスタンプ = 2017/01/02 00:00:00」という値を設定してそこを参照するようにすると、 障害でその日集計できなかったとしても、上記パラメータを設定して後日再集計ができる。

【Linux】rsyncで半角英数記号(半角スペース含む)のみのファイル名のみを同期する

rsyncは、ファイル同期を行うLinuxコマンド。 主→従へ完全にファイルの権限やタイムスタンプをコピーする。 デフォルトでは全てのファイルが対象(再帰的なコピーは-rオプションが必要)だが、名前にフィルタリングルールを適応することができる。

rsync -rtv --include='[a-zA-Z0-9!-.:-@[-`{-~]' --exclude='[^ ]' src/ dest/

半角スペース入りを対象外にする場合

rsync -rtv --include='[a-zA-Z0-9!-.:-@[-`{-~]' --exclude='*' src/ dest/

-rtvについて

-rtvは割愛。

--include と --excludeについて

取込対象を指定する --include--exclude は上記のようになんちゃって正規表現が使える。

あとは*でワイルドカードで指定ができる。

適応ルールは下記のとおり。

  • 無指定の場合、全ファイルが対象
  • --include=...-exclude=... の指定順で処理していき、最初にマッチした条件のみが適応。

例:「src/java」と「src/resources」と「src/pom.xml」だけをコピー対象として、他は除外する

rsync --include="java/" --include="resources/" --include="pom.xml" --exclude=* src/ dest/

ひっかけ例1:まず『名前が「contents」であるディレクトリを対象にする』それ以外のものは『デフォルトで対象にする』(除外ルールないので結局全部対象)

rsync --include="contents/" src/ dest/

ひっかけ例2:まず『すべてのパターンを除外する』それ以外のものから『a,b,cいずれかを含むファイルのみを対象とする』(結局全部除外するパターン)

rsync --exclude="*" --include="[abc]" src/ dest/