LoginSignup
1
0
@SVC34

DB2DBA: Db2 11.5新機能 - Machine Learning Optimizer (Technology Preview)

Last updated at Posted at 2023-04-12

概要

今日様々な分野でAIの活用が広がっていますが、データベースにおいてもAIの統合が進んでいます。現在Db2でもDB内のデータを用いて機械学習を行うことが出来るようになっています。

さらに、Db2 V11.5からは新たなAIの活用方法として、機械学習モデルを用いる新しいオプティマイザ (MLオプティマイザ) が提供されています。

Db2 V11.5ではTechnology Preview扱いの機能であるため、IBMによるサポートは提供されません。また、仕様については今後大きく変更される可能性があります。V11.5.4での公開時から多くの変更が行われていますが、この記事はV11.5.6時点の機能を前提にしています。

データベースにおいてオプティマイザの機能は多岐に亘りますが、V11.5のMLオプティマイザはカーディナリティの見積もり精度を改善することを最初のターゲットとしています。一例としては、複数列の間に相関がある場合にそれを検出した上でカーディナリティを算出できるようになっています。

2つの列の値に相関がなく独立している場合、2つの列グループのカーディナリティは個々のカーディナリティの掛け算に近い値となります。一方で相関がある場合、組み合わせの数は個々のカーディナリティの掛け算よりも小さくなります。例えば通常の会社組織における従業員テーブルの「所属部署」と「職種」には相関があり、特定の所属部署に特定の職種が偏ることはイメージできるかと思います。相関が考慮されず列組み合わせのカーディナリティが実態より大きく見積もられる場合、効率的でないアクセスパスが選択されてしまう可能性があります。

MLオプティマイザは、表の構造より自動生成したトレーニング用SQLとサンプリングデータから機械学習モデルを作成し、これを用いてカーディナリティの予測を行います。

使い方

事前準備

機能の有効化

以下のレジストリ変数を設定します。

db2set DB2_ML_OPT=ENABLE:ON

レジストリ変数設定後はDBの再起動 (DEACTIVATE & ACTIVATE) が必要です。

SYSTOOLS表スペースの作成

SYSTOOLSPACEがない場合は事前に作成してください。

CREATE TABLESPACE SYSTOOLSPACE IN IBMCATGROUP 
  MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP
  EXTENTSIZE 4

CREATE USER TEMPORARY TABLESPACE SYSTOOLSTMPSPACE IN IBMCATGROUP 
  MANAGED BY AUTOMATIC STORAGE USING STOGROUP IBMSTOGROUP
  EXTENTSIZE 4

表とストアドプロシージャの作成

MLオプティマイザが利用する表とストアドプロシージャを作成するためのDDLが提供されているため、これを実行します。DDLはsqllib内のmiscディレクトリに配置されています。

$ cd $DB2_HOME/misc
$ db2 –tf MLOptimizerCreateTables.ddl
$ db2 –td@ -f MLOptimizerRoutines.ddl

機械学習モデルの管理

モデルの作成

モデルディスカバリーの機能により、Db2は対象とする表の選定から機械学習モデルの作成までを自動的に行います。

ユーザーがマニュアルで機械学習モデルを作成するには、対象の表と列を指定して以下のストアドプロシージャを実行します。以降は、指定した列を述部に含むSQLに対してMLオプティマイザが利用されるようになります。

CALL SYSTOOLS.DEFINE_MODEL('MYSCHEMA', 'MYTABLE', 'COL1,COL2,COL3', NULL)

各種参考資料では以下のように記載されていますが、この第4引数OUT_TEXTはOUTとして定義されているので、この形で実行するにはSQL PLを書いてDEFINE OUT_TEXT VARCHAR(n)としておく必要があるはずです。通常は上記のようにNULLにしてしまった方が使いやすいと思います。

CALL SYSTOOLS.DEFINE_MODEL('MYSCHEMA', 'MYTABLE', 'COL1,COL2,COL3', OUT_TEXT)

NULLの場合でも、結果のメッセージはちゃんと返してくれるようです。

$ db2 "CALL SYSTOOLS.DEFINE_MODEL('DB2INST1', 'TEST1', 'COL1,COL2', NULL)"

  Value of output parameters
  --------------------------
  Parameter Name  : OUT_TEXT
  Parameter Value :

Successfully trained model.

列名区切りのカンマの後ろにスペースがあるとエラーになるようです。

モデルの再トレーニング

レコード数やデータの分布が大きく変化した場合はモデルの再トレーニングが必要になりますが、RUNSTATSを実行すれば再トレーニングも自動的に行われます。

モデルの確認

モデルのメタデータはSYSTOOLS.TABLE_MODELS表に格納されており、この表にクエリを投げることでどの表・列を対象としたモデルが存在するか確認することができます。VERSIONID = 1のレコードがアクティブな最新バージョンのモデルの情報となります。また、ISTRAINED = 1はモデルのトレーニングが成功していることを表します。

SELECT
    SUBSTR(SCHEMANAME, 1, 8) SCHEMA
    ,SUBSTR(TABLENAME, 1, 8) TABLE
    ,SUBSTR(COLIDS, 1, 8) COLIDS
    ,VERSIONID
    ,ISTRAINED
    ,TIMEOFCREATION
FROM
    SYSTOOLS.TABLE_MODELS

SCHEMA   TABLE    COLIDS   VERSIONID   ISTRAINED TIMEOFCREATION
-------- -------- -------- ----------- --------- --------------------------
DB2INST1 EMPLOYEE 7,4                1         1 2023-04-09-14.38.13.556316
DB2INST1 TEST1    0,1                0         1 2023-04-10-16.33.04.494147
DB2INST1 TEST1    0,1                1         1 2023-04-10-16.33.52.290272

モデルの削除

SYSTOOLS.TABLE_MODELS表から対象のスキーマ・表のレコードを削除してください。対象の表をDROPしても自動的に削除は行われません。また、モデルを完全に無効化するにはDBのDEACTIVATE & ACTIVATEが必要です。

DELETE FROM SYSTOOLS.TABLE_MODELS
WHERE SCHEMANAME = 'MYSCHEMA' AND TABLENAME = 'MYTABLE'

このあたりはまだTechnology Preview版のためかやや不便な点ですね。

使用例

テストデータ

今回は列の間に相関のあるデータとして、極端なケースですが2つの列の値が常に同じデータを用意しました。1から100までの値が100回繰り返されており、計10,000件のレコードとなっています。

$ db2 "select * from test1"

COL1        COL2
----------- -----------
          1           1
          2           2
          3           3
          ~           ~
        100         100
          1           1
          2           2
          3           3
          ~           ~
        100         100
          1           1
          2           2
          3           3
          ~           ~
          ~           ~
        100         100

  10000 record(s) selected.

COL1, COL2はどちらもカーディナリティが100ですが、同じ値をとるため列組み合わせのカーディナリティも100となります。このような相関関係をMLオプティマイザが認識し、従来のオプティマイザよりも正確な見積もりを行えるかどうかを検証してみます。

従来のオプティマイザ

結果行数の見積もりは0.9となっています。つまり、COL1, COL2は独立していると見なされており、列組み合わせのカーディナリティは100 * 100 = 10,000に近い値と計算されてしまっています。

$ db2expln -d sample -t -q "select * from test1 where col1 = 100 and col2 = 100" -g

 Optimizer Plan:

   Rows
 Operator
   (ID)
   Cost

 0.926941
  RETURN
   ( 1)
 171.333
    |
 0.926941
  TBSCAN
   ( 2)
 171.333
    |
  10000
 Table:
 DB2INST1
 TEST1

従来のオプティマイザ（列グループ統計あり）

Db2では従来から列の組み合わせに対する統計情報を取得する機能を提供しているため、こちらも確認してみます。

$ db2 "runstats on table test1 on columns ((col1, col2)) with distribution"

列グループ統計を使用した場合、先ほどのSQLでの結果行数の見積もりは実態に近い96となりました。

$ db2expln -d sample -t -q "select * from test1 where col1 = 100 and col2 = 100" -g

Optimizer Plan:

    Rows
  Operator
    (ID)
    Cost

  96.2778
  RETURN
   ( 1)
  171.333
    |
  96.2778
  TBSCAN
   ( 2)
  171.333
    |
  10000
 Table:
 DB2INST1
 TEST1

うまくいっているようですが、次に実際のデータには存在しない値の組み合わせを試してみたところ、同じ結果が出てしまっています。1

$ db2expln -d sample -t -q "select * from test1 where col1 = 100 and col2 = 0" -g

Optimizer Plan:

    Rows
  Operator
    (ID)
    Cost

  96.2778
  RETURN
   ( 1)
  171.333
    |
  96.2778
  TBSCAN
   ( 2)
  171.333
    |
  10000
 Table:
 DB2INST1
 TEST1

MLオプティマイザ（列グループ統計なし）

結果行数見積もりは約30と、従来のオプティマイザでの列グループ統計あり・なしの中ほどの値となりました。これだけでは、従来の列グループ統計より精度が低いように見えます。

$ db2expln -d sample -t -q "select * from test1 where col1 = 100 and col2 = 100" -g

Optimizer Plan:

    Rows
  Operator
    (ID)
    Cost

  29.8625
  RETURN
   ( 1)
  171.333
    |
  29.8625
  TBSCAN
   ( 2)
  171.333
    |
  10000
 Table:
 DB2INST1
 TEST1

一方、存在しない値の組み合わせを試してみたところ、実態の0に近い約1.3まで低下するという結果が得られました。これは、従来のオプティマイザよりも安定した性能が得られる可能性を示しています。

$ db2expln -d sample -t -q "select * from test1 where col1 = 100 and col2 = 0" -g

Optimizer Plan:

    Rows
  Operator
    (ID)
    Cost

  1.29667
  RETURN
   ( 1)
  171.333
    |
  1.29667
  TBSCAN
   ( 2)
  171.333
    |
  10000
 Table:
 DB2INST1
 TEST1

今回検証に用いたSQLは単純なものであるためアクセスパス自体はいずれも同じで結果行数見積もりだけが変化していますが、より複雑なSQLでは索引や結合順序の選択を左右することになります。

制約

SQLにおける制約

開発中の機能であるため、SQLの述部のパターンによってはMLオプティマイザが未対応のものがあります。未対応のものについては、従来のオプティマイザが評価を行います。

Db2 V11.5.6～では以下がMLオプティマイザ未対応となっています。2

複数列での非等値結合

SELECT * FROM T1, T2
WHERE T1.C1 <= T2.C0
  AND T1.C1 > T2.C1

式

SELECT * FROM T1 WHERE MOD(T1.C4, 10) = 1

パラメータマーカー

SELECT * FROM T1 WHERE T1.C3 > ?

パラメータマーカーは広く使われているものの、その仕組み上最適なアクセスパスを選択することが難しいものになっています。SQLコンパイル時に値がセットされていない状態でアクセスパスを決定するため、セットする値に最適なものではなく平均的なアクセスパスを選ばざるをえないためです。特に上記SQLのような範囲検索では値によってヒットするレコード数が大きく変動するため、そのリスクが大きいです。3

こちらは今後のバージョンアップでの対応を期待します。4

その他の制約

特に注意が必要と思われるもののみ記載します。

  • DB内に作成できるモデルの数は100がデフォルトの上限
    • 100を超えるには db2set DB2_ML_OPT="ENABLE:ON;MAX_MODELS:200" を実行し、DBをDEACTIVATE & AVTIVATE
  • モデル作成時に指定出来る列の数は20が上限
  • 表に対してALTERを行った場合はモデルの再作成が必要

参考資料

  1. 列グループの分散統計を取得できれば改善できそうなものの、現在そのような機能は提供されていないようです。SYSSTAT.COLGROUPDISTというカタログビューはあるのですが。なお、z/OS版であれば列グループの分散統計に対応しています。 ↩

  2. いずれも元々従来のオプティマイザでは正確な見積もりが難しいものです。 ↩

  3. BINDパラメータや最適化ガイドラインでのREOPT ONCE等、一定のチューニング手段は提供されています。 ↩

  4. z/OS版のMLオプティマイザ (IBM Db2 AI for z/OS) はパラメータマーカーに対応できています。V11.5時点のLUW版とは仕組みが異なり、実行されたSQLの履歴情報から実際にセットされた値を取得・分析することで、これを実現しています。また、z/OS版はLUW版に先行して2018年から正式提供されています。 ↩

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0