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万件。
それぞれを 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倍速とかちょっとやめて）
