え!?86億行がここまで!SQLServerデータ圧縮の実力

ども、三和システムおちゃらけ担当uozy☆です。

もうすぐ7月ですね、いよいよ夏の到来です!!6歳から少年野球をやっていたuozy☆としては、夏といえば「野球」です!炎天下での地獄のノック、バッティング、ベーランからの粉ポカリ(濃い目)、これは格別でしたね。そしてスポーツ大好き三和システムには野球チーム「エナサンズ」があります!そしてそして、この「エナサンズ」はこの度草野球大会にエントリー致しました\(^o^)/ヨッ!!この大会、平日朝6:30プレイボール8:00終了というなんともアレなスケジュールなのであります。頑張ろう。。。 www.kusaon.jp


さて、今日はデータ圧縮に関するお話です。最近弊社で管理しているDBサーバーのディスク空き容量が足りなくなるという事象が発生しました。通常データ領域が足りなくなると過去データをパージしたりなんてことをすると思うのですが、このDBはデータ分析用なので過去データを捨てるという選択肢はあり得ません。そこでデータ圧縮を行う事にしました。
※なおこの機能はEnterpriseエディションで使用できます。

環境情報

SQLServer 2008 R2 Enterprise

2種類ある圧縮方式

  1. 行圧縮→固定長データ型を可変長で格納(行の圧縮の実装
  2. ページ圧縮→行圧縮+プレフィックスの圧縮+ディクショナリの圧縮(ページの圧縮の実装

上記の圧縮方式をテーブル、テーブルパーティション、インデックス、インデックスパーティションそれぞれ自由に設定することができます。つまりTableAはページ圧縮だけど、TableBのIndex1は行圧縮、という設定が可能になっています。

圧縮によるメリットデメリット

メリットとしてまずあげらるのは「①ディスクからの読み取り量が減る」って事ですね。DBの最大のボトルネックはディスクアクセスの遅さなので、データが圧縮されてる事で読み出しディスク量が減る=クエリが早くなる事につながります。また「②圧縮されたままメモリ上にキャッシュされる」のでメモリ空間内により多くのデータをキャッシュする事ができます。デメリットとしてはCPUリソースを使用する事です。圧縮・解凍というプロセスを実行するので当然といえば当然ですね。ちなみに圧縮方式毎のCPUリソースの使用量は、非圧縮<行圧縮<ページ圧縮となります。なお、圧縮が解除されるタイミングは次の場合です。

  1. フィルタ処理、並べ替え、結合のためにデータが読み取られる場合
  2. データが更新される場合

圧縮対象の選定

では、どのテーブルを対象にするのか?という話になると思いますが、まずはテーブルの状況を確認してみましょう。ManagementStudio上のDBインスタンス上で右クリック「レポート」→「標準レポート」→「上位のテーブルによるディスク使用量」これでどのテーブルがどれだけデータを使用しているのかが一目でわかります。 f:id:uozy:20150625173240p:plain

圧縮見積

さて対象が決まったら、次にどれくらい圧縮されるのかが気になるところです。SQLServerにはその為のシステムプロシージャも用意されています。
圧縮の見積を推定する(sp_estimate_data_compression_savings (Transact-SQL))

exec sp_estimate_data_compression_savings 'schema_name', 'object_name', NULL, NULL, 'ROW' ;

f:id:uozy:20150625174547p:plain

こんな感じです。1行目はテーブル自体で2行目はindexですね。この例だと、下記のような見積になりました。

  • 非圧縮状態でテーブル+indexで約3.5GB
  • 行圧縮するとテーブル+indexで約3.2GB
  • ページ圧縮するとテーブル+indexで約1.8GB

圧縮の実行

圧縮の実行コマンドは以下の通り。

--TABLEの場合
ALTER TABLE [schema_name].[table_name] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)
--INDEXの場合
ALTER INDEX [index_name] ON [schema_name].[table_name] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

実体験

uozy☆はこの前この要領でデータ圧縮を実行しました!!
延べ86億レコードを対象にし1TB→400GBまで圧縮出来ました\(^o^)/でも圧縮実行中は、終電を気にしながら、ただドキドキしながら待たなければならないのでもうやりたくはありません(´・ω・`)

(参考:)
圧縮されたテーブルおよびインデックスの作成
データ圧縮:キャパシティ プランニングとベスト プラクティス

ばいばーい\(^o^)/