Lookerベスト・プラクティス：「データ読み取り」におけるデータベース最適化ガイド #looker

Lookerではトピック毎のベストプラクティスを個別にドキュメントやナレッジベースとしてまとめています。当エントリではその中から、Lookerで利用するデータベースにおいて『データを読み取るためのデータベース設定最適化』に関する内容を紹介したいと思います。

• Best Practice: Optimize Database Setup for Reading Data – Looker Help Center

目次

• 列指向データベースの最適化
• 行指向データベースの最適化
• まとめ

　

行指向データベースの最適化

SELECT文の実行において、列指向データベースを最適化するための推奨事項としては以下の様なものが挙げられます。

• 列のエンコードと圧縮を活用すること。この設定を踏まえておくことで、ストレージスペースが圧縮され、処理速度が向上します。逆に、この設定を指定しない場合だと、データベースリソースを必要以上に浪費し、クラスタのパフォーマンスが制限され、ストレージコストが増加します。
  列指向データベースであるAmazon Redshiftの場合、「列圧縮タイプ」がカラム毎に指定可能です。

また、Redshiftに取り込むデータは幾つかの圧縮形式にも対応しています。

• 全てのテーブルの統計情報を定期的に収集しておくこと。テーブルの分析を掛けておくことで、列レベルのメタデータが最新の情報にアップデートされます。Amazon Redshiftの場合、ANALYZEというコマンドを実行するとこの効果が得られます。現時点ではデフォルトでANALYZE処理が自動実行されるようになっています。

• テーブルを定期的にVACUUMしておくこと。この作業により、指定されたテーブルまたはデータベース全体の不要スペース再利用・データの再並び替えが行われます。Amazon Redshiftの場合、VACUUMというコマンドを実行するとこの効果が得られます。
  上記ANALYZE同様、VACUUMもVACUUM DELETE(不要スペースの再利用)についてはクラスタで自動実行される形となっています。(※データの並び替え[VACUUM SORT]については個別に対応が必要)

• クエリで返される列の数を絞ること。テーブル内の全カラムを引っ張ってくるのでは無く、必要最低限の列選択に留めましょう、ということですね。比較的小さな列のサブセットを操作する場合であれば、クエリはより高速に実行される事となります。

• クエリでのフィルターと集計を最大限に活用すること。結果を絞り込むクエリを発行させ、アクセス負荷を減らします。Lookerでは、Explore内においてデフォルトでフィルターを適用させるための仕組みとしてalways_filter、conditionally_filterがあります。

  • always_filter
  • conditionally_filter

　

行指向データベースの最適化

SELECT文の実行において行指向データベースを最適化するための推奨事項としては以下の様なものが挙げられます。

• データベーススキーマを可能な限り正規化しておくこと。トランザクショナルデータベースは、冗長性が最小限に抑えられたときに最高のパフォーマンスを発揮します。全てのデータベースを可能な限り小さく、正規化された「スノーフレークスキーマ」でデータを整理します。
  • スノーフレークスキーマとは ｜ BI用語集 ｜ ビジネスインテリジェンス (BI) のBIツールならNecto™（ネクト） | 公式サイト
  • Snowflake schema - Wikipedia

• データを再構築し、まばらにデータが設定されたテーブルの使用を減らすこと。いわゆる「疎行列」(sparse matrix: 成分のほとんどが零である行列、スパース行列とも言う)は全体的なパフォーマンスに影響を与える場合があります。
  • 疎行列 - Wikipedia 単一のまばらなテーブルにデータを保存するのでは無く、可能な限り列を個別のより多くのデータが入ったテーブルに分離します。このようなケースの場合、EVA(Entity Attribute Value)の設定を検討してください。
  • Entity–attribute–value model - Wikipedia
  • 5章 EAV (エンティティ・アトリビュート・バリュー) - Qiita
  • ある日 Entity Attribute Value に出会った - hkdnet - Medium
• 予め、データベース内で複雑な計算を処理させておくこと。複雑な処理を事前に実行させるために、データベース内に幾つかの集計テーブルを用意・保存することも合わせて検討してみてください。この際、可能な限り生データを使用することで、ドリルダウン機能とExploreの柔軟性が向上します。

• データベース内の全てのテーブルにインデックスが存在しているのを確認すること。インデックスを使用するとデータベースで列を並び替え、ディスク上の様々な行を素早く見つけることが出来ます。ただし、インデックスはディスク上のスペースを専有するため、余分なインデックスが追加されないように気をつけてください。

• 外部キーまたはタイムスタンプにインデックスを配置すること。一般的には、結合されたテーブルの外部キーと時系列テーブルのタイムスタンプにインデックスを配置することが奨励されています。

• データベース内のトランザクション分離レベルを下げること。これにより、レプリケーションの遅延が減少し、データベースのロック条件の使用が緩和されます。

  • トランザクション分離レベル - Wikipedia

　

まとめ

というわけで、Lookerのベストプラクティス『「データ読み取り」におけるデータベース最適化ガイド』の紹介でした。Lookerは基本的に「データを(プラットフォームに)持ってこない」形の構成になっているので、参照対象となるデータベースのチューニング、設定はデータベース側で行う必要があります。前以て出来ることはたくさんあるので、Lookerで効率良い可視化・分析を行うためにも準備はしっかりしておきたいところですね！