【Amazon Redshift】列圧縮エンコーディングのディスクサイズ比較

AmazonRedshiftには「列圧縮エンコーディング」というチューニング用のパラメータがあり、 テーブル列ごとに適切なものを指定することでディスク保存サイズを低減できる。

読み込み対象あたりのディスクIO削減効果は、列圧縮エンコーディングの圧縮率が高いほど大きい。 （ただし、ソートキーなどで対策する範囲限定スキャンの粒度が大きくなってしまい、フィルタや分散の演算量が多くなってしまう）

列圧縮タイプの効果があるかは、実際にテーブルに登録されるデータの内容に大きく左右される。 本投稿は、これをいくつかのデータパターン別で検証したものになる。

結論

整数型・日付型・TIMESTAMP型

  • RAW一択
  • MOSTRYは効果がないどころか逆効果になる場合が多い。
  • LZOはサイズが変わらない。ZSTDは数％減るがCPU演算コストに見合わない
  • DELTAは、「レコードの前後で誤差-128～127に原則収まる」「実数値が2byte以上」の条件を満たせばディスクサイズが減る。

文字列型

  • 20バイトに収まる程度であれば raw（そのほかは効果がない）
  • 日本語項目を持つ長い字句は zstd
  • ユーザーエージェントやJSONなどある程度データが長く英数字主体（文字種のパターンが少ない）は lzo。CPUリソースに余りがあるならzstd

ANALYZE COMPLESSION はディスクサイズが最小になるものを提案するので、ある程度のデータ量があれば間違いなくzstd、圧縮しても変わらなかったらrawが提案される。 そうするとディスクIOがたかが5%～10%減程度なのにzstd採用したせいでCPU負荷がめっちゃ高くなりボトルネック化することがたびたび。

各圧縮タイプの簡単な解説

列圧縮タイプ 内容
BYTEDICT 値をコード値に変換してディスク書き込み、内容は別途辞書化。例えば20byteの値でも数byteコード化して書き込む。値のバリエーションが少ないほど高圧縮だが、バリエーションが一定以上だとバイトコード値が非常に大きくなり、ディスク使用量が指数的に増加する。（公式では、255個以下推奨）
DELTA 前レコードとの差分値を記録。連番やログテーブルのタイムスタンプに適する。ある程度値のサイズが大きくないとRAWと大差なくなる。
DELTA32K DELTAの2byte記録版。DELTAは差分が1byteを超えると拡張したフォーマットで記録するようになるため、1byte越えが頻出すると痛い。これは初めから2byte枠にする
LZO LZOという、繰り返しバイト表現をコード化する圧縮方式を使ってデータをシリアライズする。ある程度長い文字データであり英字主体（文字種のパターンが少ない）のものに有効。
MOSTLY8 大きな数値でも入る型にきた1byteで収まる数値を丸めて書き込む
MOSTLY16 大きな数値でも入る型にきた2byteで収まる数値を丸めて書き込む
MOSTLY32 大きな数値でも入る型にきた4byteで収まる数値を丸めて書き込む
RAW 圧縮しない。ただ、VARCHAR(255)型に1バイトしか書き込みしなかったときなどは、なるべくつめる？
RUNLENGTH まったく同じデータがレコード間で連続配置されているとき、{文字列 x 連続数}の形でディスク書き込みする。
TEXT255 単語（スペース区切り？）をバイトコード化・単語は別途辞書化する。英文など同単語が頻出するもののみ。
TEXT32K TEXT255はVARCHR(255)の列までしか指定できないが、TEXT32Kは33278まで可能
ZSTD あらゆるデータをそれなりに圧縮する。数値型や10バイト程度の文字でも1,2割の容量減、英数字の文章なら1/10、日本語も1/3程度に。ただしCPU使用率が非常に高く、いろんな列につかって大量のデータを処理するとCPUボトルネックにすぐひっかかる。

計測結果

検証した対象と条件

  • 下記とおりの組み合わせの 1列 CSVを用意。いずれもレコードは 1000万件。

    • 0 ～9,999,999 まで 1行単位で出力したもの
    • 0～9を羅列した10行を100万繰り返したもの
    • 0を1000行、1を1000行...9を1000行、といった1万行の組を1000回繰り返したもの
    • 0～100,000の数字から30種類作り、頻度x1の数字、頻度x2の数字、頻度x10の数字でランダムに出したもの
    • 日本語の都道府県を均等に1000万行繰り返し出力したもの
    • 日本語の住所を均等に1000万行繰り返し出力したもの
    • ユーザーエージェントを1000万件（めちゃくちゃ偏りあり）
    • SHA256のハッシュ値16進数文字列

  • それぞれを RedshiftのCOPYを利用して各データごと×(VARCHAR(255) or numeric(11))×列圧縮タイプのテーブルを作ってインポート

    • RedshiftのCOPYは、CSVのデータ順序を保ったままテーブルにコピーするのも利用している

  • svv_diskusage からデータブロック数(1Mバイト/個)をカウント

SELECT
  MAX(db_id) AS db_id,
  TBL,
  MAX(NAME) AS name,
  COUNT(*) AS "count using databloack(1M byte)", -- 合計
  SUM(CASE WHEN SLICE = 0 THEN 1 ELSE 0 END) AS "/slice0", -- スライス1
  SUM(CASE WHEN SLICE = 1 THEN 1 ELSE 0 END) AS "/slice1" -- スライス2
FROM SVV_DISKUSAGE
where name like '%テーブル名%'
      AND col < 1
      -- redshiftは、デフォルトで列を勝手に3つ追加する
      -- 必要な列数だけをカウント
GROUP BY TBL
ORDER BY TBL;
  • 測定日 2019/08/24

0 ～9,999,999 まで 1行単位で出力したもの

NUMERIC(11,0)型

列圧縮タイプ データブロック数
BYTEDICT 170
DELTA 94
DELTA32K 104
LZO 112
MOSTLY8 168
MOSTLY16 168
MOSTLY32 124
RAW 112
RUNLENGTH 170
ZSTD 93
  • LZOが全く機能していない
  • ZSTDの効果が高いのは、数値が大きくなってマルチバイトになると、類似バイトを圧縮する仕組みが作用したとおもわれる。
  • 何気にDELTAが働いている

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 2546
LZO 122
RAW 122
RUNLENGTH 198
TEXT255 168
TEXT32K 188
ZSTD 90
  • やはりZSTDが強いが、実データのバイト数は大したことがないのでRAWでいいかも。
  • BYTEDICTがえらいことになっている

0～9を羅列した10行を100万繰り返したもの

NUMERIC(11,0)型

列圧縮タイプ データブロック数
BYTEDICT 94
DELTA 94
DELTA32K 104
LZO 84
MOSTLY8 94
MOSTLY16 104
MOSTLY32 124
RAW 84
RUNLENGTH 170
ZSTD 84
  • 1バイト値はRAW一択

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 94
LZO 84
RAW 84
RUNLENGTH 142
TEXT255 112
TEXT32K 132
ZSTD 84
  • 1バイト値はRAW一択

0を1000行、1を1000行...9を1000行、といった1万行の組を1000回繰り返したもの

NUMERIC(11,0)型

列圧縮タイプ データブロック数
BYTEDICT 94
DELTA 94
DELTA32K 104
LZO 84
MOSTLY8 94
MOSTLY16 104
MOSTLY32 124
RAW 84
RUNLENGTH 84
ZSTD 84
  • 1バイト値はRAW一択

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 94
LZO 84
RAW 84
RUNLENGTH 84
TEXT255 112
TEXT32K 132
ZSTD 84
  • 1バイト値はRAW一択
  • RUNLENGTHが仕事するかと思ったらそうでもなかった。やっぱ使いにくいなお前な・・・

0～100,000の数字から30種類作り、頻度x1の数字、頻度x2の数字、頻度x10の数字でランダムに出したもの

NUMERIC(11,0)型

列圧縮タイプ データブロック数
BYTEDICT 94
DELTA 164
DELTA32K 142
LZO 104
MOSTLY8 164
MOSTLY16 136
MOSTLY32 124
RAW 104
RUNLENGTH 166
ZSTD 96
  • 受注金額の分散をイメージしたが、データのつくり方が悪かったかもしれない
  • 数値型は、ひとまずRAWにしておけば無難

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 94
LZO 114
RAW 114
RUNLENGTH 168
TEXT255 142
TEXT32K 162
ZSTD 100
  • やっぱり、データのつくり方が悪かったかもしれない

日本語の都道府県を均等に1000万行繰り返し出力したもの

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 94
LZO 86
RAW 86
RUNLENGTH 220
TEXT255 192
TEXT32K 210
ZSTD 84
  • 最大12バイトの文字列
  • 下手に圧縮するよりもRAWにしておいたほうが無難

日本語の住所1000パターンを均等に1000万行繰り返し出力したもの

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 1354
LZO 265
RAW 265
RUNLENGTH 554
TEXT255 563
TEXT32K 546
ZSTD 165
  • ZSTD以外壊滅
  • ZSTDも、CPUリソースのあまり具合によっては微妙
  • 日本語に対して、LZOが本当に仕事してくれない

ユーザーエージェントを1000万件（めちゃくちゃ偏りあり：873パターンのみ）

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 100
LZO 96
RAW 1218
RUNLENGTH 100
TEXT255 842
TEXT32K 1130
ZSTD 88
  • ZSTDが無双していると思いきや、LZOが元データ比1/11の圧縮率。CPUの負荷比で考えるとLZO一択。

SHA256のハッシュ値16進数文字列

VARCHAR(255)型

列圧縮タイプ データブロック数
BYTEDICT 2546
LZO 1338
RAW 1338
RUNLENGTH 1354
TEXT255 1754
TEXT32K 2652
ZSTD 730
  • 1単語、ほぼ繰り返しパターンなしだとこんな感じ
  • やはりZSTDの圧縮率が最強

おまけ：DATE / TIMESTAMP

TIMESTAMP型

列圧縮タイプ データブロック数
BYTEDICT 128
DELTA 94
DELTA32K 104
LZO 84
RAW 84
RUNLENGTH 84
ZSTD 84
  • RAWでよい。

DATE型

列圧縮タイプ データブロック数
BYTEDICT 94
DELTA 94
DELTA32K 104
LZO 84
RAW 84
RUNLENGTH 84
ZSTD 84
  • RAWでよい。

全体の考察

  • 無駄に長い文字列型に対して、LZOやZSTDが効果大。そのほかはだいたい地雷、理論値のケースに奇跡的にZSTD超えするぐらい
  • そんな長い文字って、Redshift格納するべきなんだっけ？（BIで出すの？？？意味ある？？？？）
  • あとはテストデータが非常に偏っており、有意すぎるのか実際のところが測れていないのかあまりよくわからない結果になった。本番相当のデータをどっかで入手して再チャレンジしたい。
  • 列圧縮タイプが検索クエリにどう影響を与えるのか見たいが、最適化の仕組みが複雑すぎてちょっと無理ゲ―かも。（機械学習でいきなり7,8倍速とかちょっとやめて）

【Vue】Vue＋vuecli+Vuex+Vuetify+TypeScript+ClassComponent+Golang+ginでもろもろやったときの参照文献

プライベートで、タイトル通りの技術を使ってS3をアプリから登録して簡単に閲覧できるビュワーを作成した。

GitHub - pakuyuya/s3-web-browser

最近のWeb技術は独自のスキームが進んでいて追っかけていなかった身としてはなかなか踏み出せなかったが、 手を出してみれば、洗練されている上チュートリアルも充実、品質の高いものばかりなのですぐに作れた。

同様な構成を考えている人のために、構築例と参考にしたURLをまとめる。

構成

  • サーバーサイド

    • Golang + シンプルなWebフレームワークgin
    • クライアントサイドのコードで生成したhtml、js、cssをpage系のリクエスト処理関数で読み込み表示
    • URL に/api/ プリフィクスがついているものはシンプルなWebAPIを提供。クライアントサイドのリソース取得や操作はこれを介する。
    • バックにPostgresqlあり。
    • S3との接続はAWS CLI
    • コード分割は正直下手だとおもう

  • クライアントサイド

  • ビルドスクリプト

    • Docker-composeで作成

参考リンクまとめ

Vuecli関連

vuecli公式 Vue CLI

  • ツールをインストールして動かすまでのガイド。
  • 起動したら対話形式でほしい機能を選んでいくだけ

TypeScript関連

ライブラリを yarn add / npm install したけど型エラーになる

  • yarn add @types/ライブラリ名 / npm install @types/ライブラリ名 して、インストールできたら tsconfig.json の types にライブラリ名を追記する
  • できなければ、↓の方法で回避

ウェブエンジニア珍道中 typescriptでnodeのfsを動かそうとしてハマったこと - ウェブエンジニア珍道中

  • @types/ライブラリ名 がない場合の対処法。

Vue関連

Vue公式ガイド はじめに — Vue.js

  • だいたいの構文は載っている
  • 基本的な機能は読んでいけばなんとなくわかる
  • 「単一ファイルコンポーネント」の章が実質、実装の手引き

vue-class-component TypeScriptではじめるVueコンポーネント（vue-class-component） - Qiita

  • VueをTypeScriptのclassスタイルで無理やり実装するようにするコンポーネント
  • 従来の.vueファイルの書き方を大きく変えるため、ググラビリティをちょっと下げる

環境変数ファイル Modes and Environment Variables | Vue CLI

  • ビルド環境ごとに設定ファイルを変える方法のデファクト
  • 環境変数名に VUE_APP_ を接頭語としてつけないとだめらしい。

Vuex関連

Vuex公式ガイド https://vuex.vuejs.org/ja/guide/

  • 基本的な機能は載っている

vuex-class-component vuex-class-componentを使ってVuexをクラススタイルでタイプセーフに書いてみよう ｜ DevelopersIO

  • VuexをTypeScriptのclassスタイルで無理やり実装するようにするコンポーネント
  • 従来の.vueファイルの書き方を大きく変えるため、ググラビリティをかなり下げる

Qiita https://qiita.com/hikaruna/items/fff8ed49556e659b9a06

  • 実装サンプル記事
  • 大変助かりました。 NewsStore.CreateProxy

Vuetify関連

Vuetify公式Document Material Component Framework — Vuetify.js

  • すべてが載っている
  • サンプルはちょっと貧弱

Qiita 【Vue.js】Vuetify と TypeScript を使用した環境を構築してサンプルプロジェクトを立ち上げるまでの手順 - Qiita

  • type.dがないとかで怒られる事象の解決の参考に。
  • tsconfig.json のtypes にvuetify追加
  • vuecliで自動fix対応まだっぽい

Qiita Vue でダッシュボードの雛形を作る最速の道 - Qiita

  • ダッシュボードなUIの枠組み作成の手順を詳細に公開いただいている記事。
  • 自作ツールの枠組みとして大いに参考にさせていただきました。
  • Vue 2.xベースで記事が作成されており、現行バージョンのVue 3.xでは廃止されたコンポーネントあり。

iconのCSSでないぞ問題

  • public/index.html に追加

Golangまとめ

Golangの文法

Go modules

Postgresqlにdatabase/sqlでつなぐ

gin まとめ

公式兼最強のドキュメント・サンプル

セッションを扱う用の拡張

Cookie based なセッションでstruct設定したら「securecookie: error - caused by: gob: type not registered for interface:」エラーになった

作ってみて

  • Vue
    • 数年前からちょいちょい使っている
    • 楽でサンプル豊富。
    • 色んな機能やツールを導入するのがしんどかったけど、今やvue cli ですべてボイラーテンプレートしてくれる。マジ敷居低い
  • Vuetify
    • Vue版 Bootstrap + jQuery UI。Dialogもサポートしてる。神か
    • 最初は「card・・・？caption・・・？」ってなるけど実はルール単純。 CSSを知っていれば、チュートリアルといくつかのサンプルをほいほいコピペしていってすぐ組める
    • マジ強力なフレームワークだと思う。
  • TypeScript
    • 使い込むほど「お前やっぱJavaScriptだな？？」ってなって安易な型安全なぞ焼け石に水と知る（jsonとかリクエストパースするやつ、型普通にすり抜けてきて残念な気持ちになる）
    • 今回ServerSideをGolangにしたため相互のインタフェースが乖離したことを型で検知できなかったが、Isomorphicにすると違ったのかもね。
  • Vue class component
    • TypeScriptを使うなら、これを導入しない手はない
    • JavaScriptベースなVueのチュートリアルとは違った書き方になり、ググラビリティが下がるの注意
  • Vuex
    • 最高
    • db-update→reflesh→commit→$store.stateを見てるVue更新 の流れを強制できる。コードがすっきり。
    • 双方向バインディングがn:mで発生するのを防いでくれる

  • Vuex class component

    • TypeScriptを使うなら、型安全性から導入推奨
    • ただ、CreateProxy とかを組み込まなきゃ動かないとか、使い方がピュアなVuexと異なる。乗り越えればなんてことない。

  • Golang

    • 直近１年でちょくちょく触れるようになった
    • 手癖殺してとりあえずベストプラクティスに従っていけば「ｱｯｱｯｱｯ」って言いながらGolang道に吸い込まれる。オススメ。タノシイヨ。理想の世界だよ。
    • test文化はいい文化。意識すると無駄にモジュール化が進んだり面倒くさいと気づいて作り直すループに入ったり。
    • Go modulesでだいぶ楽になった気がする。GOPATHの中に入るやつ、あれちょっとすごかった。
  • gin
    • ミニマリズムとHTTPの極致
    • どんな処理も手で実装したいマンとして非常にマッチ
    • 例えばログイン認証とかフレームワークで用意しといてよ・・・とかまでリッチな機能が欲しい人は、うーん、Golangじゃなくていいかも・・・。

【Amazon Redshift】CREATE VIEW文をCOMMENT文付きで自動生成するSQL

AWS Redshift utilsにビューのDDLを自動生成するSQLが公開されているが、 COMMENT文も欲しかったので過去に自作したものを公開。 ただ、SQL文に改行やインデントがはいらず、なんか微妙な感じ。

WITH v_generate_view_ddl AS
(
  SELECT derived_table4.schemaname AS schemaname,
         derived_table4.tablename AS tablename,
         derived_table4.seq,
         derived_table4.ddl
  FROM (SELECT derived_table3.schemaname,
               derived_table3.tablename,
               derived_table3.seq,
               derived_table3.ddl
        FROM ((((((SELECT n.nspname AS schemaname,
                          c.relname AS tablename,
                          0 AS seq,
                          ('--DROP VIEW "'::VARCHAR(65535) + n.nspname::VARCHAR(65535) + '"."'::VARCHAR(65535) + c.relname::VARCHAR(65535) + '";'::VARCHAR(65535))::VARCHAR(65535) AS ddl
                   FROM pg_namespace n
                     JOIN pg_class c ON n.oid = c.relnamespace
                   WHERE c.relkind = 'v'
                   UNION
                   SELECT n.nspname AS schemaname,
                          c.relname AS tablename,
                          2 AS seq,
                          ('CREATE VIEW "'::VARCHAR(65535) + n.nspname::VARCHAR(65535) + '"."'::VARCHAR(65535) + c.relname::VARCHAR(65535) + '"'::VARCHAR(65535))::VARCHAR(65535) AS ddl
                   FROM pg_namespace n
                     JOIN pg_class c ON n.oid = c.relnamespace
                   WHERE c.relkind = 'v')
                   UNION
                   SELECT n.nspname AS schemaname,
                          c.relname AS tablename,
                          5 AS seq,
                          '('::VARCHAR(65535) AS ddl
                   FROM pg_namespace n
                     JOIN pg_class c ON n.oid = c.relnamespace
                   WHERE c.relkind = 'v')
                   UNION
                   SELECT derived_table1.schemaname,
                          derived_table1.tablename,
                          derived_table1.seq,
                          ('\011'::VARCHAR(65535) + derived_table1.col_delim + derived_table1.col_name + ' '::VARCHAR(65535) /* + derived_table1.col_datatype + ' '::varchar(65535) + derived_table1.col_nullable + ' '::varchar(65535) + derived_table1.col_default + ' '::varchar(65535) + derived_table1.col_encoding*/)::VARCHAR(65535) AS ddl
                   FROM (SELECT n.nspname AS schemaname,
                                c.relname AS tablename,
                                100000000 + a.attnum AS seq,
                                CASE
                                  WHEN a.attnum > 1 THEN ','::VARCHAR(65535)
                                  ELSE ''::VARCHAR(65535)
                                END AS col_delim,
                                '"'::VARCHAR(65535) + a.attname::VARCHAR(65535) + '"'::VARCHAR(65535) AS col_name
                         FROM pg_namespace n
                           JOIN pg_class c ON n.oid = c.relnamespace
                           JOIN pg_attribute a ON c.oid = a.attrelid
                           LEFT JOIN pg_attrdef adef
                                  ON a.attrelid = adef.adrelid
                                 AND a.attnum = adef.adnum
                         WHERE c.relkind = 'v'
                         AND   a.attnum > 0
                         ORDER BY a.attnum) derived_table1)
                   UNION
                   SELECT n.nspname AS schemaname,
                          c.relname AS tablename,
                          299999999 AS seq,
                          ')'::VARCHAR(65535) AS ddl
                   FROM pg_namespace n
                     JOIN pg_class c ON n.oid = c.relnamespace
                   WHERE c.relkind = 'v')
                   UNION
                   SELECT n.nspname AS schemaname,
                          c.relname AS tablename,
                          300000000 AS seq,
                          'AS'::VARCHAR(65535) AS ddl
                   FROM pg_namespace n
                     JOIN pg_class c ON n.oid = c.relnamespace
                   WHERE c.relkind = 'v')
                   UNION
                   SELECT n.schemaname AS schemaname,
                          n.viewname AS tablename,
                          500000000 AS seq,
                          definition::VARCHAR(65535) AS ddl
                   FROM pg_views n
                   --TABLE COMMENT
                   UNION
                   SELECT schemaname,
                          tablename,
                          seq,
                          'COMMENT ON VIEW ' || schemaname || '.' || tablename || ' IS ''' || description || ''';' AS ddl
                   FROM (SELECT n.nspname AS schemaname,
                                c.relname AS tablename,
                                800000000 AS seq,
                                d.description AS description
                         FROM pg_namespace AS n
                           INNER JOIN pg_class AS c ON n.oid = c.relnamespace
                           INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
                           INNER JOIN pg_description d
                                   ON a.attrelid = d.objoid
                                  AND d.objsubid = 0
                         WHERE c.relkind = 'v'
                         AND   a.attnum > 0)
                   -- COLUMN COMMENT
                   UNION
                   SELECT schemaname,
                          tablename,
                          seq,
                          'COMMENT ON COLUMN ' || schemaname || '.' || tablename || '.' || attrname || ' IS ''' || description || ''';' AS ddl
                   FROM (SELECT n.nspname AS schemaname,
                                c.relname AS tablename,
                                800000000 + a.attnum AS seq,
                                a.attname AS attrname,
                                d.description AS description
                         FROM pg_namespace AS n
                           INNER JOIN pg_class AS c ON n.oid = c.relnamespace
                           INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
                           INNER JOIN pg_description d
                                   ON a.attrelid = d.objoid
                                  AND a.attnum = d.objsubid
                         WHERE c.relkind = 'v'
                         AND   a.attnum > 0)) derived_table3
        ORDER BY 1,
                 2,
                 3) derived_table4
)
SELECT *
FROM v_generate_view_ddl
WHERE schemaname NOT IN ('pg_catalog','information_schema','pg_internal','public')
ORDER BY schemaname,
         tablename,
         seq

下記を参考に、ある程度改行付きのものを作成した。でもなんか不格好。

amazon-redshift-utils/v_generate_view_ddl.sql at master · awslabs/amazon-redshift-utils · GitHub

WITH v_generate_view_ddl AS
(
  SELECT derived_table4.schemaname AS schemaname,
         derived_table4.tablename AS tablename,
         derived_table4.seq,
         derived_table4.ddl
  FROM (SELECT derived_table3.schemaname,
               derived_table3.tablename,
               derived_table3.seq,
               derived_table3.ddl
        FROM
        (
            (
                (
                    SELECT n.nspname AS schemaname,
                        c.relname AS tablename,
                        0 AS seq,
                        '--DROP VIEW "' + n.nspname + '"."' + c.relname + '";' AS ddl
                    FROM
                    pg_namespace n
                        JOIN pg_class c ON n.oid = c.relnamespace
                    WHERE c.relkind = 'v'
                )
                UNION
                SELECT n.nspname AS schemaname,
                       c.relname AS viewname,
                       200000000 AS seq,
                       CASE
                         WHEN c.relnatts > 0 THEN 'CREATE OR REPLACE VIEW ' + QUOTE_IDENT(n.nspname) + '.' + QUOTE_IDENT(c.relname) + ' AS\n' +COALESCE(pg_get_viewdef (c.oid,TRUE),'')
                         ELSE COALESCE(pg_get_viewdef (c.oid,TRUE),'')
                       END AS ddl
                FROM pg_catalog.pg_class AS c
                    JOIN pg_catalog.pg_namespace AS n ON c.relnamespace = n.oid
                WHERE relkind = 'v'
            )
            --TABLE COMMENT
            UNION
            SELECT schemaname,
                   tablename,
                   seq,
                   'COMMENT ON VIEW ' || schemaname || '.' || tablename || ' IS ''' || description || ''';' AS ddl
            FROM (
                SELECT n.nspname AS schemaname,
                       c.relname AS tablename,
                       800000000 AS seq,
                       d.description AS description
                  FROM pg_namespace AS n
                    INNER JOIN pg_class AS c ON n.oid = c.relnamespace
                    INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
                    INNER JOIN pg_description d
                            ON a.attrelid = d.objoid
                           AND d.objsubid = 0
                  WHERE c.relkind = 'v'
                  AND   a.attnum > 0
            )
            -- COLUMN COMMENT
            UNION
            SELECT schemaname,
                   tablename,
                   seq,
                   'COMMENT ON COLUMN ' || schemaname || '.' || tablename || '.' || attrname || ' IS ''' || description || ''';' AS ddl
            FROM
            (
                SELECT n.nspname AS schemaname,
                       c.relname AS tablename,
                       800000000 + a.attnum AS seq,
                       a.attname AS attrname,
                       d.description AS description
                  FROM pg_namespace AS n
                    INNER JOIN pg_class AS c ON n.oid = c.relnamespace
                    INNER JOIN pg_attribute AS a ON c.oid = a.attrelid
                    INNER JOIN pg_description d
                            ON a.attrelid = d.objoid
                           AND a.attnum = d.objsubid
                  WHERE c.relkind = 'v'
                  AND   a.attnum > 0
            )
        ) derived_table3
        ORDER BY 1,
                 2,
                 3
    ) derived_table4
)
SELECT *
FROM v_generate_view_ddl
WHERE schemaname NOT IN ('pg_catalog','information_schema','pg_internal','public')
ORDER BY schemaname,
         tablename,
         seq

【Ruby】メモリに余裕があるはずなのにCannot allocate memory で落ちたらFork()を疑う

事象

  • Amazon Linux上
  • RubyVM起動で、メモリ使用率が単体50%のプロセスを実行中、急遽「Cannot allocate memory」のエラーログが出力されて落ちる

追加調査してわかった事象

  • 過去、バッチ実行中、一瞬（１分弱？）だけRubyのプロセスがそれまで使用していたメモリ使用率2倍をマークしてすぐに落ち着く

Fork()の疑い

  • Ruby には Fork()関数が実装

  • 挙動は、Linuxカーネルのfork()に準じる

    • 今のプロセスを保有しているメモリごと複製して新プロセスを作成する
    • すぐに別処理に移った場合、該当のメモリはすぐに開放

  • OSのベースが 10%ほどメモリ占有　＋　もとのプロセスが50%占有　のところに、さらにプロセス複製で50%を確保しようとしたら不足で落ちた可能性

  • Railsなどの大御所はもちろん、マルチプロセスの常套手段として利用されている。

対策

  • マシンをスケールインする
  • プロセスのメモリ所要量を低減する

【AWS Redshift】導入前に知っておくべきこと

AWS Redshift

AWS Redshiftを導入する前に知っておくべき、AWS Redshift の特性、長所、他所を開発・運用してきた中で要所っぽいところをいくつかTips的にまとめた。 字量が非常に多くて申し訳ないが、参考になれば。

RDBに比べて有用なケース/苦手なケース

  • 下記のケースに合致する。

    • SQL文をベースとした、複雑で演算コストの高いETL（分析用途用のデータ加工処理の通称）の実行
    • BIツールのような、3～5列程度の列を利用した参照クエリの実行。

  • 下記のケースは向かない。

    • 短時間で非常に多くのクエリを実行するアプリケーション（1秒に5～10クエリなど）のバックエンド
    • 短時間で非常に多くのCommitを実行するアプリケーション（Webフレームワークが勝手に）のバックエンド
    • 一度に多くの列を取得するクエリを発行するアプリケーション（CSV出力など）のバックエンド

性能について

クエリの性能

  • 1つのSQL実行にあたり、秒単位のオーバーヘッドがかかる。
    • Webサイトのように、不特定多数のユーザーからアクセスするシステムのバックグラウンドとしては致命的。
  • 取得する列数が増えるほど性能劣化。15列を超えたぐらいでもう目に見えるほど遅い。
  • 主キーやインデックスが設定できないため、適切にインデックスを設定したRDBと比較してデータ取得が遅い。（ソートキーで代用可）
  • SQLの解析も他のRDBに比べて多少気になるぐらい遅い。

  • SELECT文発行によるデータ取得が、他RDBに比べて2～3倍程度遅い。(UNLOADは別)

  • 逆に、取得する列が４，５程度であれば、GROUP BY などRDBが苦手とする演算でも非常に高速

  • RDBでインデックスを利用した場合には負けるが、そうでないINSERT-SELECTが高速。（特に、GROUP BYやサブクエリを多用する重量級SQLにて顕著に差異が出る）

チューニングについて

  • RDB経験者がチューニングにあたると、必ずと言っていいほど失敗する。
    • RDBは主キー、インデックスありきでチューニングし、また列指向と行試行では効率的な演算がほぼ真逆。
    • インデックスのようにソートキーを使うとハマる。（別記事参照）
    • 分散キーを主キーのように設定してハマる。
  • このあたりのチューニングにも、インデックス作成感覚ではすまない大きな犠牲が伴う。
  • まずはすべてデフォルト(EVEN分散、ソートキーなし)で作り、後からクエリを測定してチューニングをしていくのが無難。

サポートするSQLについて

  • 基本的にPostgresql 8.02 を基準としているが、一部使えない構文や機能がある。
  • ALTER TABLE による列定義変更は、大きな制約がある。
    • ADD COLUMN、DROP COLUMNは可能
    • MODIFY COLUMN は、VARCHARの列サイズ増加などに限って有効。
  • たいていは、別テーブルを CREATE TABLE して ALTER TABLE RENAME で交換、依存するビューを再作成・・・の流れになる。

COPY (CSVなどテキストファイル取込処理) / UNLOADの制約

  • PostgresqlのCOPYとは違い、CSV出力・取込などはS3に直接実行する。
    • 結構早い
  • COPY やUNLOADには、AWSのIAMからユーザーを作ってのACCESSKEY/SECRETKEY指定か、IAMRoleをクラスタに付与してのarn指定かなどで実行。

クエリ同時実行制限について

  • WLM（WorkLoad Management）により、デフォルトではクラスタあたりのSQL同時実行数が「5」に制限されている。
    • Redshiftは、1クエリあたりのリソース消費が激しく、また排他制御に問題がありSQL同時実行数が増えると メモリ不足を引き起こしてDisk書き込み・クエリ性能低下に至ったり、CPU不足によりスループット低下したりする。
  • 同時実行数はいくつかの枠に分割することができる。デフォルトの全枠共有だと、「ユーザーからのクエリが終わらず、バッチがいつまでたっても実行できない・・・」とかになる。注意。

ディスク容量について

  • 基本的に、インスタンスタイプを上げるとかインスタンス数を増やすとかしてランニングコストを上げるしかない。
  • 別手段として、Redshift Spectrum（中身はAthena）を利用してS3に置いたものをクエリするようにするしかないが、こちらは1TBスキャンごとに$5と馬鹿にならない別料金をとられる。（そのうえ、Redshiftとはクエリ特性が違う）
  • 列圧縮エンコーディングによりちびちび節約する手段もあるが・・・基本的には向かない。

運用について

定期的なVACUUM・ANALYZE必須

  • バッチで週一、または夜間余裕のある時に

毎月最低1回はクラスタ再起動が自動でかかる。

  • AWS公式の縛りで、土曜日の JST 22:30頃にクラスタが再起動する。
  • 通常は月1回だが、毎週のように再起動がかかるときもある。
  • AWS ConsoleでRedhisft＞各クラスタに入り、「クラスタのステータス」タブで直近の再起動予定が確認できる。
  • 設定により延長できるが、最大45日まで。しかも手運用2月に1回は覚悟しておくべきだろう。

クラスタ再起動直後、ANALYZEによる統計情報が全て無効になっていることがある。

  • 時々ある事象。
  • オートアナライズでいつもよりクエリが滅茶苦茶遅い・・・ということがざらにある。

VACUUM は同時に 1つしか流せない

  • AWS Redshiftの制約。特にソートキー付きのテーブルはVACUUMが前提となるが、非常に時間がかかる上直列、他のクエリに大きな影響がでるほど負荷が高い。

バックアップについて

  • AWS Consoleよりクラスタ無停止でスナップショットを取得することができる。
  • スナップショットはS3に置かれ、S3の体系で課金

docker-compose+fluentdでバッチ処理をいい感じにログ収集しつつな構成サンプル

Docker

眠いのでもう必要なことだけをまとめる。

この記事について

  • Docker-Composeを使った簡易なバッチシステムのテンプレート構築。
  • 今回のサンプルは以下の構成。
    • 1つのLinuxマシンに、Docker-composeを利用してPostgresql と fluentdを立てて、同ホストにて docker-compose up で一回起動するようなバッチを使い各位通信する。
    • 1つのマシンに全サービスを詰め込んだため、networkなどに別マシンに分離したときはいらない設定が含まれる。許されよ

ソースコード

Github: https://github.com/pakuyuya/note-docker/tree/master/03.recipts/compose-batch-with-fulentd/docker-batch-fulentd

ディレクトリ構成

docker-batch-fulentd
    ├─batch     # バッチシステム
    │  ├─sql     # バッチで利用するSQLファイル
    │  ├─src     # Golangのソースコード
    │  ├─Dockerfile                    # バッチバイナリビルド用のDockerfile
    │  └─docker-compose.yml  # バッチ実行の yml
    └─daemons
        ├─db        # Postgresql 初期化・設定ファイル
        │  └─init   # init script
        ├─fluentd # Fulentd 初期化・設定ファイル
        │  └─conf
        └─docker-compose.yml  # 事前起動しておく各サービス用

batchのつくり

batch/docker-compose.yml

version: '3'

services:
  batch:
    build: ./   # Dockerfileをビルド
    env_file:
     - ./local.env   # 環境変数に.envファイルを利用
    command: /app/sample-batch -SqlFile /app/sql/BAT0000_update-to-diable-invalid-users.sql  # 実行コマンド
    logging:
      driver: fluentd  # ログドライバをfluentdに指定。標準出力を下記オプション通り転送するように
      options:
        tag: docker.sample_batch
        fluentd-address: 192.168.10.4:24224   # コンテナ名で名前解決する方法が、ついに見つからなかった。。
                                                                     # サービス起動するたびに docker network inspect daemons_default でIPを確認しIPアドレスを
                                                                     # 同yamlなら/etc/hostsに追記される。または、手コマンドでgateway付のdocker networkを作成
                                                                     # DockerホストのマシンのIPに投げてport fowardingからたどり着くか。（未検証）
# ネットワーク設定。
# 同マシンにて事前起動しているデーモン用のDocker-composeのサービスに接続したかったが
# Dockerのnetwork（docker-composeが自動作成する）が同じでないとpingは通れど
# アプリケーションレベルの通信が疎通しなかった。
# 下記設定により、既存のネットワークに乗っかる形で接続する。
networks:
  default:
    external:
      name: daemons_default

batch/Dockerfile

# Dockerコンテナをビルドするための構文がちがちなスクリプトファイル。
# 各コマンドの仔細は、申し訳ないが他者様のドキュメントをあたってほしい。
# MultiStage buildを利用。FROMを複数回書くと、一番最後のものだけがコンテナに残る。コンテナサイズの低減が目的
# コンテナイメージのベースにAlpineを用いているのは、もちろんコンテナイメージを小さくするため。
FROM golang:1.11.11-alpine as builder
ENV CGO_ENABLED=0   # GOのライブラリ用。GCCを使わないようにする
COPY ./src /app
WORKDIR /app
RUN apk update \
    && apk add --no-cache git \
    && go build

FROM alpine:3.10
COPY --from=builder /app/sample-batch /app/sample-batch
COPY ./sql /app/sql
RUN apk add --no-cache ca-certificates

バッチの実装は、引数で指定されたSQLファイルを読み込みSQL実行するシンプルなもの。

main.go

func run(args []string) error {
    app := cli.NewApp()
 
    app.Flags = []cli.Flag{
        cli.StringFlag{
            Name: "SqlFile",
            Value: "",
        },
    }
    app.Action = func(c *cli.Context) error {
        InfoLog("Batch started")

        // 引数取得
        sqlfile := c.String("SqlFile")
        if sqlfile == "" {
            return errors.New("Parameter 'SqlFile' is required.")
        }
     
        // DB接続
        db, err := sql.Open("postgres", GetConnectionString())
        if err != nil {
            return err
        }
        defer db.Close() // defer: returnなど関数が終わるときに実行
     
        // SQLファイル読み取り
        file, err := os.Open(sqlfile)
        if err != nil {
            return err
        }
        defer file.Close()
        bytes, _ := ioutil.ReadAll(file)
        query := string(bytes)
     
        InfoLog("Read SQL from `%s` and run query...", sqlfile)
        InfoLog("Query: `%s`", query)
     
        // SQL実行
        result, err := db.Exec(query)
        if err != nil {
            return err
        }
        // 影響行数取得
        rowsAffected, _ := result.RowsAffected()
        InfoLog("%d rows affected", rowsAffected)
        InfoLog("Batch finished")
     
        return nil
    }

    return app.Run(args)
}

func InfoLog(message string, args ...interface{}) {
    nowTime := time.Now()
    const timefmt = "2006/01/02 15:04:05"
 
    msg := fmt.Sprintf(message, args...)
 
    fmt.Printf("[INFO] %s: %s\n", nowTime.Format(timefmt), msg)
}

func GetConnectionString() (string) {
    return fmt.Sprintf("host=%s port=%s user=%s password=%s dbname=%s sslmode=disable", os.Getenv("DB_HOST"), os.Getenv("DB_PORT"), os.Getenv("DB_USER"), os.Getenv("DB_PASSWORD"), os.Getenv("DB_NAME"))
}

事前起動しておくDaemonたち

daemons/docker-compose.yml

version: '3'
services:
  #backendのPostgresql.
  db:
    image: postgres:11-alpine
    container_name: db
    ports:
      - 5432:5432
    volumes:
      - ./db/init:/docker-entrypoint-initdb.d
      - ./dbdata:/var/lib/postgresql/data
    environment:
      POSTGRES_USER: root
      POSTGRES_PASSWORD: root
      POSTGRES_INITDB_ARGS: "--encoding=UTF-8"
    hostname: db
    restart: always
    user: root
  #不要だがメンテ用にpgadminが欲しかった
  pgadmin4:
    image: dpage/pgadmin4:3.3
    container_name: pgadmin4
    ports:
      - 80:80
    volumes:
      - ./pgadmin:/var/lib/pgadmin/strage
    environment:
      PGADMIN_DEFAULT_EMAIL: root
      PGADMIN_DEFAULT_PASSWORD: root
    hostname: pgadmin4
    restart: always
  #fluentd.
  fluentd:
    image: fluent/fluentd:v1.5-1
    container_name: fluentd
    ports:
     - "24224:24224"
    volumes:
     - ./fluentd/conf/fluentd.conf /fluentd/etc/fluentd.conf
    ports:
     - "24224:24224"
     - "24224:24224/udp"
# network設定。IPアドレスを指定したかったので色々configをいれた。driver: bridgeは必須。
networks:
  default:
    driver: bridge
    ipam:
      driver: default
      config:
        - subnet: 192.168.10.0/24

実行する

# 事前に、DockerとDocker-Composeをインストールしておく
# 言い忘れたけどCentOS7

#デーモンを事前起動
cd docker-batch-fulentd/daemons
docker-compose up -d

# バッチ起動（スクリプト修正して実行するときは--buildもいれる）
cd ../batch
docker-compose up --build

# ログ確認のためにコンテナに入る
docker exec -it fluentd /bin/sh

cd /fluentd/logs
ls
cat <lsでみたファイル名>

気づいたこと

  • Fluentdってログ出力に対して障害点増えるのでは？（Pluginに変なのいれたらすぐ不安定になるらしい）
    • 「Docker/Kubernetes 実践コンテナ開発入門」でKubernetesの世界を見たけど　あそこまで重厚なことまでして冗長化するか？と運用リスクを考えて、最後まで決定を引き伸ばしそう。
    • ファイル名が完全にコントロールできて実行ホストが1つとかだったら、別に他のバッチと同様ローカルへのファイル出力でもいいよなー。
      • とはいえ、ポータビリティはリソースに余力のある別ホストへの実行も可能にする・・・とはいえ、いまのところそ単一のホストに集中していることは、そんなに困っていない。
  • バッチ実行するだけならdocker-composeじゃなくてもいいよね。
    • 環境変数に全部パラメータバインドしてから実行するならdocker-composeでもいいけど
    • 柔軟にパラメータかシェルで docker コマンドを引数モリモリでやっても別にいいんじゃないかなー、人間がうつんじゃないしとか思ってきた次第

勉強になりました（参考サイト）

【AWS Glue】事前調査

AWS AWS Glue

AWS Glue とは

  • サーバーレス（オンデマンド実行・実行時間課金）なETL向けサービス
  • 実態は、フルマネージドなPyhton/ApacheSparkの仮想実行環境。
  • AWS手製のライブラリ/ランタイムがAWS上のリソースの取得やクエリにめっちゃくちゃ強く（例：S3のデータをロード、Redshiftにクエリ、Athenaにクエリ）、SQL感覚で大規模のデータ加工スクリプトを書ける。
  • RedshiftやAthenaにテーブル作ると、自動で収集してカタログという形でメタデータを作る。コードのテンプレート生成に使ったりする。

調べる

2019/06次点では、決定版みたいなドキュメントはなさそう

公式ドキュメント

Future Tech ブログ

ジョブについて

  • [1] S3にPythonやScalaのスクリプトを置く [2] AWS GlueからJobを作成してキック の流れらしい。

  • 現在、ジョブには２つのタイプがある

    • Sparkタイプ
      • 重量級で従来タイプ
      • 実行してからインスタンスの起動に1分～10分ほどかかる （インスタンス立ち上げのため）
        • 開発エンドポイントというずっと立ち上がりっぱなし（＋課金しっぱなし）のインスタンスを別途立ちあげることはできる・・・がかなりランニングコストが高い(DPU 1 つにつき0.44$/時。 標準の5つであれば、5 * 0.44 * 24 * = $52.8 / 日。30日で$1584。Redshiftの2TBを1つ立てられる)
      • 機能はフル
      • 多くの「Glue使ってみた」記事は、こちらの機能を使っている
    • Python Shellタイプ

  • 1DPU(CPUみたいなの)が1時間稼働したとき、$0.44課金

  • 1ジョブごと走るごと、どんなに短く終わっても最低10分走った金額が加算。20～30回デバッグ実行するだけで$10に。（たかい！）

開発について

  • AWS Consoles にGlueのジョブエディタがついており簡易に作成ができる

  • また、GUIから入力／出力するカタログ内テーブルを選んで、テンプレートからスクリプト自動生成もできる

  • ジョブ実行も、AWS Consolesから可能。ログはCloudWatchに出力。