Oracleのパラレルクエリが処理を分担する様子を観察してみた

1. はじめに

少し前置きが長いですが、ご容赦を。

10年以上前に開かれたOracle Database 10gのセミナーで、Oracleのパラレルクエリについて以下のような話を聞いた記憶があるのですが、この内容がずっと気になっていました。

• まず、処理対象データをDOPの数で分割してスレーブプロセスに割り当てる。（以下の図はDOP=2の場合）

• さらに、分割されたデータを、それぞれ1/2、1/3、1/6に分割する。 

• それぞれのスレーブプロセスは1/2⇒1/3⇒1/6の順番で処理する。

• もし、あるスレーブプロセスが割り当てられた全データを処理完了した時点で、他のスレーブプロセスに未処理のデータが残っている場合、未処理のデータを処理が完了したスレーブプロセスに割り振り直す。この対応により、特定のスレーブプロセスの処理遅れを回避し、全体の処理完了が遅れることを軽減する。

 

並列処理についてあまり詳しくなかった当時は「Oracleすごいなぁ」という感想を持っていました。しかし、いろいろ経験を積んでから改めて考えると、効率の良い方法ではないことも分かり、本当にそういう風に動くのか疑問を持つようになりました。 

また、この挙動について明示的に説明された資料を見つけることもできませんでした。以下の資料のp19にはそれらしい図はあるのですが。

• ここまでできる!!Oracle Databaseのパラレル処理 

そこで、今回はOracleパラレルクエリにおけるデータの分担の様子について観察してみた結果を、ブログに記録しておこうと思います。

ちなみに、サポート範囲外の機能の使い方をしているので、厳密に正しいかは保証できない点はご了承ください。

2. 環境準備

今回は以下の環境で動作を確認しています。仮想マシンには2CPU、2GBメモリを割り当てています。

• OS環境：Virtual Box 6.0.2上のCentOS 7.6.1810 (x86_64)

• DB：Oracle Database 18c 18.3.0.0 (Linux x86-64)

今回の作業を実施するDBユーザは以下のように作成しています。

CREATE USER test IDENTIFIED BY ****** QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE TO test;

このDBユーザに以下のようなテーブル、データを作成します。

-- 12個のパーティションを持つテーブル「ptab」を作成
CREATE TABLE ptab (
    part_id NUMBER(2)
)
PARTITION BY RANGE (part_id) (
    PARTITION p01 VALUES LESS THAN (2),
    PARTITION p02 VALUES LESS THAN (3),
    PARTITION p03 VALUES LESS THAN (4),
    PARTITION p04 VALUES LESS THAN (5),
    PARTITION p05 VALUES LESS THAN (6),
    PARTITION p06 VALUES LESS THAN (7),
    PARTITION p07 VALUES LESS THAN (8),
    PARTITION p08 VALUES LESS THAN (9),
    PARTITION p09 VALUES LESS THAN (10),
    PARTITION p10 VALUES LESS THAN (11),
    PARTITION p11 VALUES LESS THAN (12),
    PARTITION p12 VALUES LESS THAN (13)
);
-- 1パーティション毎に100件、全体で1,200件のデータを挿入
BEGIN
    FOR v_part_id IN 1..12
    LOOP
        FOR v_i IN 1..100
        LOOP
            INSERT INTO ptab VALUES (v_part_id);
        END LOOP;
    END LOOP;
    COMMIT;
END;
/
-- 統計情報収集
exec dbms_stats.gather_table_stats(ownname => 'TEST', tabname => 'PTAB');
-- 並列度を2に設定
ALTER TABLE ptab PARALLEL 2;

また、どのデータいつ、どのスレーブプロセスに処理されたかを観測するために、以下のような関数も準備します。

-- 時刻を取得する関数
CREATE OR REPLACE FUNCTION get_time
RETURN VARCHAR2
IS
BEGIN
    RETURN to_char(current_timestamp, 'HH24:MI:SS.FF3');
END;
/
-- セッションIDを取得する関数
-- ただし、p_part_id = waited_idの場合は約10倍時間が掛かるようにする
CREATE OR REPLACE FUNCTION get_sid(
    p_part_id NUMBER,
    waited_id NUMBER
)
RETURN NUMBER
IS
    v_dummy NUMBER := 0;
    v_loop_cnt NUMBER := 0;
BEGIN
    IF p_part_id = waited_id THEN
        v_loop_cnt := power(10, 6);
    ELSE
        v_loop_cnt := power(10, 5);
    END IF;
    FOR v_i IN 1..v_loop_cnt
    LOOP
        v_dummy := mod(v_dummy + 1, 100);
    END LOOP;
    RETURN sys_context('USERENV', 'SID');
END;
/

3. 動作確認

Oracleパラレルクエリにおけるパーティション分割はブロックベースの場合とパーティションベースの場合があります。今回は前者1パターン、後者2パターンの合計3ケースを見てみたいと思います。

3.1. ブロックベースの場合

ブロックベースのデータ分割を行う1例として次のようなSQL文を実行してみます。

SQL> set linesize 100
SQL> set pagesize 100
SQL> column start_time format a15
SQL> column end_time format a15
SQL> set time on
20:58:06 SQL> set timing on
20:58:08 SQL> SELECT
20:58:35   2      part_id,
20:58:35   3      sid,
20:58:35   4      min(read_time) start_time,
20:58:35   5      max(read_time) end_time
20:58:35   6  FROM
20:58:35   7      (
20:58:35   8      SELECT /*+ NO_MERGE */
20:58:35   9          part_id,
20:58:35  10          get_time read_time,
20:58:35  11          get_sid(part_id, 0) sid
20:58:35  12      FROM
20:58:36  13          ptab
20:58:36  14      )
20:58:36  15  GROUP BY
20:58:36  16      part_id,
20:58:36  17      sid
20:58:36  18  ORDER BY 3;

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
         3        278 20:58:37.109    20:58:38.429
         5         40 20:58:37.110    20:58:38.428
        11         40 20:58:38.441    20:58:39.728
         2        278 20:58:38.443    20:58:39.694
         9        278 20:58:39.707    20:58:40.965
         7         40 20:58:39.742    20:58:41.020
        10        278 20:58:40.978    20:58:42.232
         8         40 20:58:41.033    20:58:42.307
         1        278 20:58:42.246    20:58:43.504
        12         40 20:58:42.320    20:58:43.606
         6        278 20:58:43.517    20:58:44.759
         4         40 20:58:43.620    20:58:44.900

12行が選択されました。

経過: 00:00:07.83
20:58:44 SQL>

何をしているかというと、各行ごとにget_sid関数とget_time関数を呼び出し、それがどのスレーブプロセス（セッションID）でいつ読み込まれたかを確認しています。最後に集計とソートをして見やすくしています。

NO_MERGEヒントはブロックベースのデータ分割にするためにつけています。

また、今回はget_sid関数の第2引数が「0」のため、基本どのデータも同じだけ処理時間が掛かるはずです。

これがブロックベースのデータ分割されていることは、実行計画を確認すれば分かります。（PX BLOCK ITERATORという操作が出てきます。今回は省略します。）

SQL文の結果を見ると、12個のパーティションがきれいに2つのスレーブプロセスに分割されて実行されていることが分かります。図示すると以下のような感じです。処理されているパーティションの順番はバラバラですが、ブロックベースのデータ分割がされているので仕方ないですね。

 次に、パーティション5のデータの処理が遅いケースを試してみます。

20:58:44 SQL> SELECT
21:14:57   2      part_id,
21:14:57   3      sid,
21:14:57   4      min(read_time) start_time,
21:14:57   5      max(read_time) end_time
21:14:57   6  FROM
21:14:57   7      (
21:14:57   8      SELECT /*+ NO_MERGE */
21:14:57   9          part_id,
21:14:57  10          get_time read_time,
21:14:57  11          get_sid(part_id, 5) sid
21:14:57  12      FROM
21:14:57  13          ptab
21:14:57  14      )
21:14:57  15  GROUP BY
21:14:57  16      part_id,
21:14:57  17      sid
21:14:57  18  ORDER BY 3;

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
         5         17 21:14:58.773    21:15:11.527
         3        278 21:14:58.774    21:15:00.075
        11        278 21:15:00.089    21:15:01.351
         2        278 21:15:01.363    21:15:02.614
         9        278 21:15:02.627    21:15:03.899
         7        278 21:15:03.912    21:15:05.166
        10        278 21:15:05.180    21:15:06.428
         8        278 21:15:06.441    21:15:07.700
         1        278 21:15:07.713    21:15:08.968
        12        278 21:15:08.981    21:15:10.233
         6        278 21:15:10.246    21:15:11.498
         4        278 21:15:11.511    21:15:12.759

12行が選択されました。

経過: 00:00:14.02
21:15:12 SQL>

想定通り、パーティション5だけ処理に13秒程度掛かっています。セッションID=17のスレーブプロセスがパーティション5を処理している間、セッションID=278のスレーブプロセスは残りのパーティションをすべて処理しています。図示すると以下の通りです。

これを見る限り、1/2、1/3、1/6にデータを分割するというのは正しくなく、もっと細かい単位に分割されているように見えますね。

3.2. パーティションベースの場合

次に、データ分割がパーティション単位で行われた場合を見てみます。まずは、各パーティションの処理に掛かる時間が均等な場合です。

21:15:12 SQL> SELECT
21:30:50   2      part_id,
21:30:50   3      get_sid(part_id, 0) sid,
21:30:50   4      min(get_time()) start_time,
21:30:50   5      max(get_time()) end_time
21:30:50   6  FROM
21:30:50   7     ptab
21:30:50   8  GROUP BY
21:30:50   9      part_id,
21:30:50  10      get_sid(part_id, 0);

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
        11        291 21:30:52.656    21:30:53.909
        10        291 21:30:53.935    21:30:55.189
         8        291 21:30:55.208    21:30:56.475
         6        291 21:30:56.495    21:30:57.768
         4        291 21:30:57.802    21:30:59.097
         2        291 21:30:59.115    21:31:00.395
        12         40 21:30:52.656    21:30:53.967
         9         40 21:30:53.988    21:30:55.258
         7         40 21:30:55.279    21:30:56.555
         5         40 21:30:56.579    21:30:57.871
         3         40 21:30:57.894    21:30:59.197
         1         40 21:30:59.217    21:31:00.508

12行が選択されました。

経過: 00:00:08.01

今回、都合があって結果をソートしていないので分かり辛いですが（ソートを入れた場合、挙動が異なる。3.3.で後述。）、図示すると以下の通りになります。概ね想定通りですね。今回は3.1.であったブロックベースの場合とは異なり、処理順番も分かり易いです。

 

次に、3.1.と同様、最初に処理されるパーティション12が他のパーティションより時間が掛かるようにしてみましょう。

21:38:44 SQL> SELECT
21:38:59   2      part_id,
21:38:59   3      get_sid(part_id, 12) sid,
21:38:59   4      min(get_time()) start_time,
21:38:59   5      max(get_time()) end_time
21:38:59   6  FROM
21:38:59   7     ptab
21:38:59   8  GROUP BY
21:38:59   9      part_id,
21:38:59  10      get_sid(part_id, 12);

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
        12         40 21:39:01.332    21:39:14.026
        11        278 21:39:01.219    21:39:02.465
        10        278 21:39:02.478    21:39:03.726
         9        278 21:39:03.739    21:39:04.999
         8        278 21:39:05.013    21:39:06.271
         7        278 21:39:06.284    21:39:07.541
         6        278 21:39:07.554    21:39:08.807
         5        278 21:39:08.820    21:39:10.079
         4        278 21:39:10.091    21:39:11.350
         3        278 21:39:11.363    21:39:12.622
         2        278 21:39:12.635    21:39:13.888
         1        278 21:39:13.901    21:39:15.146

12行が選択されました。

経過: 00:00:13.96
21:39:15 SQL> 

今回も図示すると以下のようになります。こちらも想定通りですね。

3.3. ケース3.2.にソートを加えた場合

最後に、ケース3.2.のパーティション分割の場合にソートを加えた場合を見てみます。各パーティションに均等に時間が掛かる場合はスキップして（想定通りの動きのため）、パーティション12にだけ時間が掛かる場合をまずみてみます。

21:39:15 SQL> SELECT
21:48:38   2      part_id,
21:48:38   3      get_sid(part_id, 12) sid,
21:48:38   4      min(get_time()) start_time,
21:48:38   5      max(get_time()) end_time
21:48:38   6  FROM
21:48:38   7     ptab
21:48:38   8  GROUP BY
21:48:38   9      part_id,
21:48:38  10      get_sid(part_id, 12)
21:48:38  11  ORDER BY 3;

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
        11        304 21:48:40.182    21:48:41.415
        12         51 21:48:40.296    21:48:52.693
        10         51 21:48:52.707    21:48:53.965
         9        304 21:48:52.707    21:48:53.972
         8         51 21:48:53.978    21:48:55.863
         7        304 21:48:54.024    21:48:56.156
         6         51 21:48:55.896    21:48:58.342
         5        304 21:48:56.190    21:48:58.769
         4         51 21:48:58.382    21:49:00.926
         3        304 21:48:58.798    21:49:01.327
         2         51 21:49:00.951    21:49:03.485
         1        304 21:49:01.346    21:49:03.677

12行が選択されました。

経過: 00:00:23.56
21:49:03 SQL>

注目すべきなのは、セッションID=51がパーティション12を処理し終わるまで、セッションID=304は先に進んでいません。結果として、全体としての処理時間も3.1.と3.2.のケースにおける14秒程度から23秒程度まで伸びてしまっています。図示すると通りです。

 

これは、想像なのですが、ソートをパラレル処理する場合、ソート前のデータをサンプリングしてデータをどのように再分割するか決めることが原因なのではないかと思います。ソート前のサンプリングするために、最初の2パーティションの結果を待って処理を先に進めるのではないでしょうか。

ちなみに、スレーブプロセスに最初に処理されるパーティション12ではなく、2番目に処理されるパーティション10に時間が掛かる場合は、以下の通りです。

21:49:03 SQL> SELECT
23:35:13   2      part_id,
23:35:13   3      get_sid(part_id, 12) sid,
23:35:13   4      min(get_time()) start_time,
23:35:13   5      max(get_time()) end_time
23:35:13   6  FROM
23:35:13   7     ptab
23:35:13   8  GROUP BY
23:35:13   9      part_id,
23:35:13  10      get_sid(part_id, 10)
23:35:13  11  ORDER BY 3;

   PART_ID        SID START_TIME      END_TIME
---------- ---------- --------------- ---------------
        11        279 23:35:13.753    23:35:15.041
        12         45 23:35:13.753    23:35:15.032
        10        279 23:35:15.331    23:35:38.658
         9         45 23:35:15.493    23:35:18.270
         8         45 23:35:18.321    23:35:20.816
         7         45 23:35:20.868    23:35:23.363
         6         45 23:35:23.414    23:35:25.917
         5         45 23:35:25.969    23:35:28.479
         4         45 23:35:28.531    23:35:31.029
         3         45 23:35:31.079    23:35:33.581
         2         45 23:35:33.631    23:35:36.136
         1         45 23:35:36.187    23:35:38.694

12行が選択されました。

経過: 00:00:25.10
23:35:38 SQL>

こちらは、以下の図の通りセッションID=279がパーティション10を処理している間、セッションID=45は処理を継続するという理想的な動作になっています。

 

4. まとめ

今回、パラレル処理がデータをどう分担するのかの様子を見てみました。

以前に聞いた、1/2、1/3、1/6に分割するという動作は確認できませんでした。ただ、今回確認できた動作の方が効率が良いので、特に問題ではありませんが。

また、3.3.の1番目のケースはスレーブプロセスがまだ処理データが残っているのに処理待ちになってしまうという点では、非常に興味深いものがあります。Oracleのパラレルクエリは奥が深いですね。

VARCHARとNVARCHARのデータ型ミスマッチをしてもSQL Serveだとなぜ速いのか？

0. はじめに

SQL Serverなんか嫌いだ！

いきなり、本音をつぶやいてしまいましたが、SQL Serverについて受けた問い合わせをいろいろ調べているうちに得られた情報をブログに書こうと思います。（本当はもっと面白いネタがあったのですが…）

SQL Serverに限らず、よくあるSQL文の書き方のミスの１つに、データ型ミスマッチ というものがあります。例えば、以下のように、文字列型のカラムidと数値リテラルを=で比較してしまうケースが当たります

SELECT * FROM test WHERE id = 1

SQLにおいては、文字列型と数値型のデータを=で比較する場合、内部で暗黙的に文字列型のデータを数値型に変換した上で比較することになっています。
（以下のSQL文は暗黙変換のイメージです）

SELECT * FROM test WHERE to_number(id) = 1 -- Oracleの場合

こうなると、カラムidにインデックスを作成しても、このSQL文ではインデックスが利用されずに処理時間が長くなってしまいます。

SQL Severにおいて、VARCHAR型とNVARCHAR型のデータを=で比較する場合も、VARCHAR型のデータをNVARCHAR型に暗黙変換されます。そのため、遅くなるのが普通なのですが、実際には問題ないケースが存在します。

そこで、今回はVARCHAR型とNVARCHAR型のデータミスマッチをしてもインデックスが利用できるケースについて、どう動いているのかを追ってみたいと思います。

 1. インデックスが利用できるケースの再現

本記事の手順はすべてREAD COMMITED SNAPSHOTを有効にしたデータベース上で確認をとっています。

まず、以下のスクリプトでテスト用テーブルとデータ(5,000万件)を作成します。

CREATE TABLE test (
    id1    VARCHAR(10), -- クラスタ化索引あり列
    id2    VARCHAR(10), -- 索引なし列
    dummy VARCHAR(100), -- データ量かさ増し用のダミー列
    CONSTRAINT pk_test PRIMARY KEY CLUSTERED (id1)
);

-- 5,000万件のテストデータ生成
DECLARE @i INT = 0;
WHILE @i < (50000000 / 5000)
BEGIN
    -- 5,000件ずつデータ生成
    INSERT INTO test
    SELECT
        right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
        right('00000000' + cast(a.num + 5000 * @i AS VARCHAR), 8),
        replicate('A', 100)
    FROM
        -- sys.all_columnsを使って5,000件データ生成
        (SELECT TOP 5000 row_number() over (order by object_id) num FROM sys.all_columns) a;
    SET @i = @i + 1;
END;

このテーブルについて、まずはVARCHAR型のデータ同士を比較する以下のSQL文を実行してみます。

SELECT
    *
FROM
    test
WHERE
    -- VARCHAR = VARCHAR
    id1 = '00000123';

すると、実行プランは以下のようにインデックスを利用できており、実際の実行も一瞬で終わります。これは想定通りの動作です。

次に、VARCHAR型とNVARCHAR型のデータを比較する以下のSQL文を実行してみます。

SELECT
    *
FROM
    test
WHERE
    -- VARCHAR = NVARCHAR
    id1 = N'00000123';

# ちなみに、このSQL文ではNVARCHAR型のリテラルを指定していますが、
# JavaプログラムからJDBCでアクセスする場合に、条件を
# PreparedStatement#setStringで渡すと、NVARCHAR型になるため、
# 似たような挙動になります。

本来は、VARCHAR型がNVARCHAR型に変換されて比較されるためインデックスが有効活用できず、フルスキャンが起こるはずですが、実際にはそうならず、インデックスを利用して一瞬で実行は完了します。以下がこのSQL文の実行プランです。

ちなみに、インデックスが作成されていないカラムid2に対して検索すると、フルスキャンが発生し、手元の環境では実行完了に90秒程度を要します。

SELECT
    *
FROM
    test
WHERE
    -- 索引がない列の検索
    id2 = '00000123';

2. 内部的な動作の確認

VARCHAR型とNVARCHAR型を比較するケースにおいて、なぜインデックスが利用でき高速なのかを探るために、詳細を見ていきたいと思います。

まずは、プラン詳細の述語の部分に注目してみます。

【述語】の部分を確認すると、CONVERT_IMPLICITが利用されており、内部では想定通りVARCHAR型のカラムid1に対してNVARCHAR型への暗黙型変換が行われていることが分かります。

ただし、インデックスを利用したデータ絞り込みの条件【シーク述語】を確認すると、SQL文の上では等価条件にも関わらず範囲選択が行われています。分かりやすく書き直すと以下のような条件ですね。

[Expr1004] < id1 AND id1 < [Expr1005]

この [Expr1004]と[Expr1005]がどこから出てきたかは、GUIではなく実行プランのXMLデータを直接見てみると何となく分かります（飽くまで何となくですが）。以下がその抜粋です。

<DefinedValue>
  <ValueVector>
    <ColumnReference Column="Expr1004" />
    <ColumnReference Column="Expr1005" />
    <ColumnReference Column="Expr1003" />
  </ValueVector>
  <ScalarOperator ScalarString="GetRangeThroughConvert([@1],[@1],(62))">

@1はSQL文で指定されたN'00000123'を示しています。
また、 GetRangeThroughConvertの第3引数(62)はVARCHAR型を表しています。

つまりは、 GetRangeThroughConvertはN'00000123'～N'00000123'というNVARCHAR型の範囲をVARCHAR型に変換した場合、どの範囲に該当するかを求めているのではないかと想像できます。

この関数はSQL Serverの内部関数なので仕様は公開されておらず、ドキュメントも見つけられませんでした。そのため、[Expr1004]と[Expr1005]が具体的にどういった値になるかは特定できませんが、それを推測するために、以下のWITH (UPDLOCK)付きのSQL文がどのキーに対してロックを取得(Lock:Acquired)するか確認します。

SELECT
    *
FROM
    test WITH (UPDLOCK)
WHERE
    -- VARCHAR = NVARCHAR
    id1 = N'00000123';

この時のSQL Profilerの取得内容は以下になります。

(ad143ec0fd9a)と(d4505477816e)という2つのキーに対してロックを取得していることが分かります。これが具体的にどのレコードを指すのかは、以下のSQL文で調べることができます。

SELECT TOP 2
    *
FROM
    test
WHERE
    %%lockres%% IN (
        '(ad143ec0fd9a)',
        '(d4505477816e)'
        );

 結果は以下のようになり、id1 = '00000123', '00000124'の2レコードであることが分かります。

また、元のSQL文の実行時ライブクエリ統計において、「読み取った行数」が2件になっていることでも、この2件の範囲をインデックスシークしていることが分かります。

つまり、id1 = N'00000123'というVARCHAR/NVARCHARの等価条件は、少なくとも、id1 <= '00000123' AND '00000124' <= id1というVARCHAR同士の範囲条件に読み替えられて、インデックスシークが利用されていると想像されます。
（<=と<の違いを考えると、実際にはもう少し広い範囲をGetRangeThroughConvertは返しているのではないかと思いますが）

以上のような動作で、VARCHAR型とNVARCHAR型のミスマッチにも関わらず、インデックスが利用可能で高速になります。

3. ならVARCHAR/NVARCHAR型のミスマッチを気にしなくて良いかというと…

インデックスシークで読み取る件数が若干増えるとはいえ高速に動作するのであれば、VARCHAR型とNVARCHAR型のミスマッチは気にしなくて良いかというと、残念ながらNGなケースがあります。

特に問題になるのが複合キーによる検索の場合です。id1とid2が複合主キーになっている以下のテーブルで見てみます。

-- id1とid2の複合主キー
CREATE TABLE test02 (
    id1   VARCHAR(10),
    id2   VARCHAR(10),
    dummy VARCHAR(100),
    CONSTRAINT pk_test02 PRIMARY KEY CLUSTERED (id1, id2)
);

このテーブルについて、以下のSQLを投げた場合、実行プランとしてはインデックスシークが選択されるのですが、実際には遅いです。

SELECT
    *
FROM
    test02
WHERE
    id1 = N'1'
AND id2 = N'00000123';

 プラン詳細を見ると【シーク述語】において、複合キーの1番目のカラムid1でしか絞り込みが行われていません。これは、「? < id1 < ? & ? < id2 < ?」という条件が指定された場合、複合インデックスが適切に利用できないケースに似ています。

また、別の問題として、先に確認したようにデータ型ミスマッチがある場合、適切にデータ型を合わせた場合に比べ、インデックスシーク範囲が広く、余分なロックが取得されるという問題もあります。

なので、やはりできる限りデータ型は一致させるべきです。

4. さいごに

今回は、SQL文でよくあるミスの1つであるデータ型ミスマッチが、SQL Serverにおいてあまり問題とならないケースとその推測される原因についてご紹介しました。加えて、問題が発生するケースについても示しました。

ここからは個人的な感想なのですが…

GetRangeThroughConvertの仕組みを知ったときは、SQL Server頑張っているなぁという第一印象を持ちました。しかし、本来はSQL文の記述の仕方を修正することで対応すべき問題であることを考えると、あまりうれしくない機能のようにも思えます。

特に、AP開発者の人が、 この仕組みのおかげでデータ型ミスマッチはいけないことだと気づくのが遅くなる、もしくは気づかないというのは、そのAP開発者にとって不幸なのではないでしょうか。

ということで、やっぱりSQL Serverは嫌いです。

（私のSQL Server嫌いは今回の件だけの話ではないのですが）

RDBMS(Oracle、SQL Server、MySQL)でJSONを扱ってみた

1. はじめに

複雑な構造のデータやデータ定義が頻繁に変わるデータを容易に格納できるという点で、NoSQLの中でもMongoDBやCouchbase、MarklogicなどのJSON形式でデータを保持できるドキュメント指向DBの利用は広がりつつあります。

ただし、JSON形式のデータを保持できるというだけであれば、昨今のメジャーなRDBMSにおいても機能的には可能になってきています。また、SQLにおけるJSONの扱いはSQL:2016という標準で規定されているため、各製品で利用できる関数や記法などはある程度似ています。ただ、実際には標準への準拠状況や製品独自の表記/オプションにおいて少なくない差異がある状況になっています。

そこで今回(?)は、メジャーなRDBMSの内でもOracle、SQL Server、MySQLの３DBにおける基本的なJSONの取り扱い方を簡単に見ていきたいと思います。

# PostgreSQLも見ようと思ったのですが、力尽きた…

マニュアルなどは当たっていますが、隅々まで読み込んだわけではないので、間違いやより良い方法があるかもしれません。その点はご容赦ください。また、最初に調べ始めたのがOracleのため、若干Oracle基準の説明になっている部分がありますが、ご承知おきください。

2. 動作確認環境

• Oracle Database 18c (18.3.0) for Linux x86-64 (Enterprise Edition)
• SQL Server 2017 for Linux (Evaluation Edition)
• MySQL 8.0.12 for Linux x86-64 (Community Edition)

3. 使用データ

今回の動作確認では以下のデータを使っていきます。

 {
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}
{
    "book_id" : 2,
    "title" : "ビジョナリー・カンパニー",
    "publisher" : "日経BP社",
    "pages" : 475,
    "publish_dt" : "1995-09-26",
    "author" : [{"name" : "ジム・コリンズ", "role" : "著"},
                {"name" : "山岡 洋一", "role" : "翻訳"}]
}
{
    "book_id" : 3,
    "title" : "世界の経営学者はいま何を考えているのか",
    "publisher" : "英治出版",
    "pages" : 352,
    "publish_dt" : "2012-11-13",
    "author" : [{"name" : "入山 章栄", "role" : "著"}]
}
{
    "book_id" : 4,
    "title" : "イノベーションへの解",
    "publisher" : "翔泳社",
    "pages" : 373,
    "publish_dt" : "2003-12-13",
    "author" : [{"name" : "クレイトン・クリステンセン", "role" : "著"},
                {"name" : "マイケル・レイナー", "role" : "著"},
                {"name" : "玉田 俊平太", "role" : "監修"},
                {"name" : "山岡 洋一", "role" : "翻訳"}]
}
{
    "book_id" : 5,
    "title" : "機械との競争",
    "publisher" : "日経BP社",
    "pages" : 176,
    "publish_dt" : "2013-02-07",
    "author" : [{"name" : "エリック・ブリニョルフソン", "role" : "著"},
                {"name" : "アンドリュー・マカフィー", "role" : "著"},
                {"name" : "村井 章子", "role" : "翻訳"}]
} 

4. 動作確認

4.1. データ挿入

JSONデータを格納するテーブルを作成し、データを挿入します。

* Oracle

CREATE TABLE book (
    book_id   NUMBER(3),
    book_json VARCHAR2(4000),
    CONSTRAINT pk_book PRIMARY KEY (book_id),
    CONSTRAINT c_book_01 CHECK (book_json IS JSON)
);

INSERT INTO book VALUES (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

OracleではVARCHAR2、CLOB、またはBLOBのデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、「IS JSON」制約を利用します。

* SQL Server

CREATE TABLE [book] (
    [book_id] INT,
    [book_json] nvarchar(4000),
    CONSTRAINT [pk_book] PRIMARY KEY ([book_id]),
    CONSTRAINT [c_book_01] CHECK (ISJSON([book_json]) = 1)
);

INSERT INTO [book] VALUES (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

SQL Serverではnvarcahrまたはnvarchar(max)のデータ型でJSONを保持します。対象の列に正しいJSONデータが入っていることを確認するために、正しいJSONデータを受け取ったときに1を返す「ISJSON」関数を利用します。

* MySQL

 CREATE TABLE book (
    book_id int,
    book_json json,
    CONSTRAINT pk_book PRIMARY KEY (book_id)
);

 INSERT INTO book VALUES (
1,
'{
    "book_id" : 1,
    "title" : "キャズム",
    "publisher" : "翔泳社",
    "pages" : 360,
    "publish_dt" : "2014-10-04",
    "author" : [{"name" : "ジェフリー・ムーア", "role" : "著"},
                {"name" : "川又 政治", "role" : "翻訳"}]
}');

MySQLではJSONデータ型が準備されているので、JSONデータの格納にはそれを用います。個人的には一番分かり易いかなと思います。

4.2. フィールドの値(スカラー値)の取得

各JSONデータからbook_idとtitleというフィールドの値を取得します。

* Oracle 

// ドット表記法 (Oracle)
SELECT b.book_json.book_id, b.book_json.title FROM book b;

Oracleにはドット表記法という記法があり、(JSONデータを含む列名).(JSONフィールド名)で該当の値を取得することができます。とても簡単ですね。ただ、取得できる値は一律に文字列型になってしまいます。上の結果でBOOK_IDの出力結果が左寄せになっていることからも分かると思います。
ちなみに、テーブル別名を付けないとORA-00904エラーになります。

// JSON_VALUE関数の利用 (Oracle)
SELECT
    json_value(book_json, '$.book_id' RETURNING NUMBER) book_id,
    json_value(book_json, '$.title') title
FROM
    book

SQL:2016ではJSONデータからスカラー値を取得するJSON_VALUEという関数があります。Oracleの場合はRETURNING句を付けることで返るデータ型も指定することができます。出力結果でBOOK_IDが右寄せになっていることが確認できます。
標準とはいえ、正直面倒くさい表記法ですね。

// JSON_TABLE関数の利用 (Oracle)
SELECT
    x.*
FROM
    book b,
    json_table(b.book_json, '$'
        COLUMNS (book_id NUMBER PATH '$.book_id',
                 title   VARCHAR2(80 CHAR) PATH '$.title')) x

先のJSON_VALUEのSQL文では、JSON_VALUE関数が2回呼ばれていますが、実際にJSONデータ1つに対して解析も2回行われるそうです。無駄ですね。この非効率を回避するために、JSON_TABLEという関数を使うことができます。これもSQL:2016の一部です。上のSQL文の結果は先のクエリと同じです。
ただ、ぶっちゃけ分かり易いSQL文かというと…

* SQL Server

// JSON_VALUE関数の利用 (SQL Server)
SELECT
    json_value([book_json], '$.book_id') [book_id],
    json_value([book_json], '$.title') [title]
FROM
    [book];

SQL Serverでは、Oracleのドット表記法のような記法はなく、基本SQL:2016で定義された関数を用いてJSONデータから値を取得します。
ちなみに、SQL ServerのJSON_VALUE関数には、OracleのRETURNINGのようなデータ型を指定するオプションはありません。

// 結果をJSON形式で返す (SQL Server)
SELECT
    json_value([book_json], '$.book_id') [book_id],
    json_value([book_json], '$.title') [title]
FROM
    [book]
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER;

SQL Serverでは「FOR JSON」オプションを指定することで、結果をJSON形式で返すことができます。
ただし、アプリ側がJSON形式でデータを欲しいことは多々ありますが、複数のJSONデータをカンマでつなぎ1行という形式が使いやすいかというと、どうなんでしょうね。

// OPENJSON関数の利用 (SQL Server)
SELECT
    [b].[book_id],
    [x].[key],
    [x].[key],
    [x].[value]
FROM
    [book] [b]
CROSS APPLY
    openjson([b].[book_json]) [x]
WHERE
    [x].[key] IN ('title', 'pages');

SQL ServerにはJSON_TABLE関数はありません。JSONデータを表データに変換するには代わりにOPENJSON関数を利用します。ただ、結果はいわゆる縦持ちモデルで返ってくるので注意が必要です。

* MySQL

 // -> 記法 (MySQL)
SELECT
    b.book_json->'$.book_id',
    b.book_json->'$.title'
FROM
    book b

MySQLでは(JSONデータが格納された列)->(JSONパス式)という形でデータを取得することができます。Oracleのドット表記法と似ていますが、「->」の後ろがJSONパス式であることと、出力結果にダブルクォートが付くことに違いがいあります。ダブルクォートを外す関数UNQUOTEも準備されていますが少し試してみて想定通り動かなかったです（調査が不足しているのでしょう）。

// JSON_EXTRACT関数の利用 (MySQL)
SELECT
    json_extract(b.book_json, '$.book_id') book_id,
    json_extract(b.book_json, '$.title') title
FROM
    book b

MySQLにはJSON_VALUE関数はありませんが、代わりにJSON_EXTRACTという関数が準備されています（SQL:2016標準には含まれていません）。使い方はほぼ一緒ですね。

// JSON_EXTRACTは複数項目を一度に処理でき (MySQL)
SELECT
    json_extract(b.book_json, '$.book_id', '$.title')
FROM
    book b

MySQLではJSON_EXTRACT関数の1回の呼び出しで、複数のフィールドの値を取得することができます。ただし、本当に処理が効率的に行われているかは分かりません。また、結果が配列で返ってくるのは仕方ないとは言え、あまり便利ではないですね。
今回は触れませんが、MySQLにもJSON_TABLE関数は準備されています。

4.3.  フィールドの値(オブジェクト)の取得

各JSONデータからauthor(複数の著者に関しての情報を配列で保持)の1番目の値を取得します。

* Oracle

// JSON_QUERY関数の利用 (Oracle)
SELECT json_query(book_json, '$.author[0]') from book

スカラー値(数字や文字列、日付など)ではなく、JSONオブジェクトを取得する場合は、JSON_QUERY関数を使います。

* SQL Server

// JSON_QUERY関数の利用 (SQL Server)
SELECT
    json_query([book_json], '$.author[0]')
FROM
    [book];

 

JSON_QUERYの使い方は、Oracleとほぼ一緒です。

* MySQL

 // JSON_EXTRACTによるJSONオブジェクトの取得(MySQL)

SELECT
    b.book_json->'$.author[0]'
FROM
    book b

 

Oracle/SQLServerの場合、取得対象がスカラー値かJSONオブジェクト/配列かでJSON_VALUEとJSON_QUERYを使い分ける必要がありますが、MySQLの場合はどちらでもJSON_EXTRACTもしくは「->」表記を使えます。メリデメあって良し悪しは一概には言えませんが。 

4.4. 配列からのデータ取得

著者名のリストを取得します。

* Oracle

 // 配列の取得 (Oracle)
SELECT json_query(book_json, '$.author.name' WITH WRAPPER) from book

JSON_QUERYはJSONオブジェクトだけではなく、配列も返すことができます。

* SQL Server

// 配列の取得(SQL Server) - うまくいかない
SELECT
    json_query([book_json], '$.author.name')
FROM
    [book];

これをSQL Serverでやる方法は分かりませんでした。JSONデータは所詮文字列なので頑張ればできるのですが、JSON関数をうまく使ってできないのでしょうか。

* MySQL

// 配列の取得 (MySQL)
SELECT
    b.book_json->'$.author[*].name'
FROM
    book b

MySQLでは[*]という表記をパス式で配列のすべての要素にアクセスできます。

4.5. 絞り込み

出版社(publisher)が「翔泳社」である書籍のタイトル(title)を取得します。

* Oracle

// JSON_EXISTS関数の利用 (Oracle)
SELECT b.book_json.title FROM book b
WHERE json_exists(b.book_json, '$?(@.publisher == "翔泳社")');

単純な絞り込みであればWHERE句でJSON_VALUEを使えばいい気もしますが(もちろん使えます)、OracleにはJSON_EXISTSという関数も用意されています。

* SQL Server  

// JSON_VALUE関数による絞り込み (SQL Server)
SELECT
    json_value([book_json], '$.book_id') [book_id],
    json_value([book_json], '$.title') [title]
FROM
    [book]
WHERE
    json_value([book_json], '$.publisher') = '翔泳社';

SQL ServerにはJSON_EXISTS関数はないので、JSON_VALUE関数を使って絞り込みを実装します。
単純なケースであれば、個人的にはこっちの方が分かり易い気もします。

* MySQL

// JSON_CONTAINSによる絞り込み (MySQL)
SELECT
    b.book_json->'$.book_id',
    b.book_json->'$.title'
FROM
    book b
WHERE
    json_contains(b.book_json, '"翔泳社"', '$.publisher')

MySQLでもJSON_EXTRACTもしくは「->」表記による絞り込みはできますが、JSON_CONTAINSという関数を使った絞り込みも可能です。ちなみにこの「CONTAINS」の意味ですが、「翔泳社という文字をpublisherに含む」という意味ではなく、「（複数存在するかもしれない）puslisherの中に、翔泳社に一致するフィールドが最低１つ含まれる」という意味です（今回は各JSONデータの中に1回しかpublisherが出現しないので違いが分かり辛いですが）。

4.5. 集約(配列の作成)

出版社(publisher)ごとに書籍のタイトルを集約し、配列で返します。

* Oracle

 // JSON_OJECT関数とJSON_ARRAYAGG関数の利用 (Oracle)
SELECT
    json_object(
        'publisher'  VALUE x.publisher,
        'titles'     VALUE json_arrayagg(x.title))
FROM
    book b,
    json_table(b.book_json, '$'
        COLUMNS (publisher VARCHAR2(80 CHAR) PATH '$.publisher',
                 title   VARCHAR2(80 CHAR) PATH '$.title')) x
GROUP BY
    x.publisher;

このSQL文では、データを集約し配列を生成するためのJSON_ARRAYAGG関数と、結果をJSON形式で返すために表データ⇒JSONデータの変換を行うJSON_OBJECT関数を利用しています。

*SQL Server

SQL Serverでこれを実現する方法は分かりませんでした。もちろん文字列処理をすれば可能なのは分かりますが。

* MySQL

// JSON_OBJECTとJSON_ARRAYAGGを利用 (MySQL)
SELECT
    json_object('publisher', x.publisher, 'titles', json_arrayagg(x.title))
FROM
    (
    SELECT
        b.book_json->'$.publisher' publisher,
        b.book_json->'$.title' title
    FROM
        book b
    ) x
GROUP BY
    x.publisher

MySQLには若干表記が異なりますが、Oracleと同じJSON_OBJECTとJSON_ARRAYAGG関数が用意されています。
本当は副問い合わせなしで書きたかったのですが、どうもGROUP BYの中では「->」表記はGROUP BYの中では使えないようですね（JSON_EXTRACTも）。なぜだろう…

5. まとめ

今回、Oracle、SQL Server、MySQLのJSONの扱いを比較してみましたが、扱った範囲だと、以下のような感じでしょうか。

• OracleとMySQLは大体やりたいことができそう。あとは好みかな。
• SQL Serverは対応しているJSON関数やJSONパス式の範囲が狭く、まだこれから。

あと、今回は索引が利用できるかなどの性能面は全く見ていないので、実際に利用するにはもう少し調査が必要でしょう。

あと、今回は基本、JSONデータから表形式データを取得するケースがメインでしたが、それは準備されている関数がJSONデータ⇒SQL値の変換関数が多いことから来ています。JSONデータをそのまま処理してJSONデータとして返すにはSQL:2016の関数群(JSON_*)を使うのはちょっと違うのでしょうね。

そういうことをするには、MySQLなどではMySQL Shell for Javascript/Pythonなど使えるのでしょうか。

KVMでRAC+DataGuadの環境を作ってみよう Part3

まだまだ終わりません。あと、タイトルにRACというキーワード加えました。

（７）共有ディスクの設定

RACはシェアードディスクタイプのクラスタなので、 dg01とdg02で共有するディスクイメージをホストOS上で作成します。サイズは50GBとします。

# dd if=/dev/zero of=/vmdata/ss01.img \
  bs=`expr 1024 \* 1024` count=`expr 50 \* 1024`

仮想OSのローカルディスク用イメージとは異なり、QCOW2形式ではなくRAW形式を用います。よく考えれば当たり前ですが、複数の仮想ゲストで動的割当てファイルを共有することはできないようです（最初は気付かずにGrid Infrastructureのroot.shで失敗しまくりました）。

次に仮想ゲストの起動オプションに共有ディスク設定を追加します。

# /usr/libexec/qemu-kvm -k ja \
  -hda /vmdata/dg01root.img \
  -hdb /vmdata/dg01swap.img \
  -drive file=/vmdata/ss01.img,cache=none \
  -boot c -m 4096 -vnc :1 \
  -net nic,macaddr:=54:52:00:12:01:01,vlan=0 \
  -net tap,ifname=tap0,script=addif.sh,downscript=no,vlan=0 \
  -net nic,macaddr:=54:52:00:12:01:02,vlan=1 \
  -net tap,ifname=tap6,script=addif.sh,downscript=no,vlan=1 \
  -net nic,macaddr:=54:52:00:12:01:03,vlan=2 \
  -net tap,ifname=tap11,script=addif.sh,downscript=no,vlan=2

 今まではディスク指定に-hdXというオプションを使ってきましたが、cache=noneを指定するために-driveオプションを利用しています。この指定がないとやはりroot.shが失敗します。

以上の設定を行い仮想ゲストdg01を起動すると、共有ディスクが/dev/sdcとして認識されます。なので同様にss02.img、ss03.imgを作成し、dg03からdg06に追加してきます。

２度書きますが、共有ディスク用のイメージでは以下の２点が重要です。これを守らなくともOracle Universal Installerによる事前チェックは通ることがあるので要注意です。

• 動的割当を利用しない
• cache=noneオプションでキャッシュを無効にする 

 （８）Oracleインストール前のゲストOSの設定

インストール要件をクリアしていくために設定を入れていきますが、ここは簡単に。

•  必要なRPMパッケージのインストールに関してはOracle Linux 6 Update3のDVDの中にある「oracle-rdbms-server-11gR2-preinstall-1.0-6.el6.x86_64.rpm」をyum経由でインストールすれば自動でインストールされるっぽい。マニュアルに比べるとbcコマンドやXクライアントも追加で入ります。ちなみに、oracleユーザも勝手に作ってくれるけどuid=54321になるのが気に入らないので、後で削除。
• NTPで時刻合わせ。/etc/sysconfig/ntpdで-xオプションでslewモードにすることを忘れずに（インストーラがチェックしてくれますが）。
• ユーザ作成とディレクトリ設定は以下を参考に。あとは、パスワード設定とSSH透過ログインの設定を。

groupadd -g 1000 oinstall
groupadd -g 1031 dba
groupadd -g 1020 asmadmin
groupadd -g 1021 asmdba
useradd -u 1100 -g oinstall -G asmadmin,asmdba grid
useradd -u 1101-g oinstall  -G dba,asmdba oracle
mkdir -p /u01/app/grid
mkdir -p /u01/app/11.2.0/grid
chown -R grid:oinstall /u01
mkdir -p /u01/app/oracle
chown oracle:oinstall /u01/app/oracle
chmod -R 775 /u01

• 共有ディスクのオーナー、パーミッション設定は【こちら】を参考に/etc/udev/rules.d/99-oracle-asmdevicesruleに以下の内容を記述。ちなみにこの設定方法はRHEL6系に固有らしく、後のインストーラによる要件チェックでは正しく設定してもWaringを喰らうようです。

KERNEL=="sdc"、SUBSYSTEM=="block"、ENV{DEVTYPE}=="disk", NAME+="oracleasm/disk1", OWNER="grid", GROUP="asmadmin", MODE="0660"

•  ネットワークでは/etc/hostsとデフォルトゲートウェイの設定を。今回はSCAN用にDNSは利用しないのでSCAN用IPアドレスは１つのみ指定します。
• /etc/sysctl.confと/etc/security/limits.confの設定は、今回はインストーラが作成する修正スクリプトに任せます（lmits.confの設定はログインし直さないと有効にならないので、本来は修正スクリプトに頼らず、事前設定が良いみたいですが）。

以上の設定を６台全部に行います。実際は、１台で設定した後に前回記述した方法で仮想ゲストをクローニングしています。

ASMLibやhanguptimer、MTUなど設定すべき項目はまだまだありますが、今回は無視します。

（９） Oracleのインストール・DBの作成

環境が正しく整えば、インストール＆DB作成は楽勝ですね。dg01&dg02にRAC環境を作ります。

• Grid Infrastructureの拡張インストール（クラスタ名：cluster01）
• Databaseのソフトウェアのみのインストール
• Examplesのインストール（自分の趣味です）
• RACデータベースの作成（DB名：ora11ga、インスタンス名：ora11ga1/ora11ga2)

今回はここまでです。次回はフィジカルスタンバイデータベースを作成していきます。もうKVMの話は出てこないかもです。

KVMでDataGuard環境を作ってみよう Part2

冬休みは終わりましたが続きますorz

 （４） 仮想マシンイメージの作成とゲストOSのインストール

まずは仮想マシンイメージファイルを以下のコマンドで作成します。今回はハードディスク容量が400GB弱しかないのに６仮想ゲスト＋３DBを作成する必要があるため、容量節約のため動的割り当てファイルを使います。また、Oracleインストールにはある程度のスワップ領域が必要ですが、実際は使わないため、スワップ単体でファイルを作成し、もしサイズが大きくなってしまっても再作成できるようにしておきたいと思います。

# qemu-img create -f qcow2 /vmdata/dg01root.img 15G
# qeme-img create -f qcow2 /vmdata/dg01swap.img 4GB

VirtualBoxがvmdk形式のファイルもいけたので、KVMもいけるかと思い試してみましたが駄目でした （そりゃそうか） 。

仮想ファイル作成後は、仮想ゲストを起動して、OSインストールイメージからOSをインストールします。起動コマンドは以下になるようです （一部実際に利用したのと違うのですが、後述します）。

# /usr/libexec/qemu-kvm -k ja \
                        -hda /vmdata/dg01root.img \
                        -hdb /vmdata/dg01swap.img \
                        -cdrom /root/V33411.iso -boot d \
                        -m 4096 -vnc :1

ここで重要なのが、「-k ja」というオプションです。単に日本語キーボードを使いますよということを指定しているだけなのですが、これがないとデフォルトは英語キーボードとみなされるようですが、コロンとセミコロンのキーを押してもセミコロンになりコロンが入力できない、Shift-^がEscになるなど困ったことになります。特にviで設定ファイルを編集しようとすると保存するときに詰みます。

（実は始めはこのオプションに気付かなかったために、設定ファイルの編集でedやcatを使うという面倒くさいことをやっていました。ちなみに「KVM vnc コロン」とかでGooglingするとsetxkbmapの話ばかりが上位に来ていますが、そもそもX-Window-System絡んでいないのにどう関係しているんでしょうね。）

 あと、「-vnc :1」 でコンソール画面をVNC経由で操作できるようになるのですが、このような表記をするとVNCサーバは127.0.0.1でリッスンするため、ローカルからの接続しか出来ません。今回はSSH転送を利用したので問題ないですが、リモートから繋ぎたい場合は「-vnc dg01:1」のようにホスト名 （リッスンするホスト名orIPアドレス）を明記する必要があるようです。

上述のコマンドを実行した後は、UltraVNC Viewerなどで接続すればOSインストール画面が表示され、OSインストールを行うことが出来ます。ちなみに最初はRealVNC Viewerで接続していたのですが、接続直後にコネクションが切断されるという事象が発生していました。そのためUlraVNCViewerを使っています。

（５） 仮想ゲストのクローニング

今回は仮想ゲストが６台必要ですが、OSインストールを６回繰り返すのは面倒なので、１つインストールしたところで仮想ゲストのイメージをコピーすることで手間を省きたいと思います。

ただ、RedHat系のLinuxではいくつかの設定ファイルでハードウェアの識別IDが直書きされるため、そのままコピーしても上手く起動しなかったり、認識したとおりのデバイス名にならないなどの問題があります。なので、それらの設定を消しこんでから、ホストOS上でcpコマンドでコピーします。

• /etc/sysconfig/network-scripts/ifcfg-*  （MACアドレス指定）
•  /etc/udev/rules.d/70-persistent-net.rules  （MACアドレス指定）
• /etc/fstab  （パーティションUUID指定）
• /boot/grub/grub.conf  （パーティションUUID指定）

 ここではまず、dg01～dg03のネットワーク通信の確認をしたいので、この３ゲストだけ先に作ってしまいます。（本当はOracleインストールに必要な設定＆rpmインストールを終えてからコピーしたいのですが、ネットワーク確認を優先させます。）

# cp /vmdata/dg01root.img /vmdata/dg02root.img
# cp /vmdata/dg01root.img /vmdata/dg03root.img
# cp /vmdata/dg01swap.img /vmdata/dg02swap.img
# cp /vmdata/dg01swap.img /vmdata/dg03swap.img

（よくよく考えればcpするだけならパーティションのUUIDは変わらないはずなので、/etc/fstabと/boot/grub/grub.confの修正は必要なかったのかも知れません。）

（６） 複数の仮想ゲスト起動とネットワーク疎通確認

仮想ホストが３つ出来たところで、ネットワーク設定含め仮想ホストを起動します（前回作成したaddif.shと同じディレクトリで実行します）。

• dg01

# /usr/libexec/qemu-kvm -k ja \
  -hda /vmdata/dg01root.img \
  -hdb /vmdata/dg01swap.img \
  -boot c -m 4096 -vnc :1 \
  -net nic,macaddr:=54:52:00:12:01:01,vlan=0 \
  -net tap,ifname=tap0,script=addif.sh,downscript=no,vlan=0 \
  -net nic,macaddr:=54:52:00:12:01:02,vlan=1 \
  -net tap,ifname=tap6,script=addif.sh,downscript=no,vlan=1 \
  -net nic,macaddr:=54:52:00:12:01:03,vlan=2 \
  -net tap,ifname=tap11,script=addif.sh,downscript=no,vlan=2

• dg02

# /usr/libexec/qemu-kvm -k ja \
  -hda /vmdata/dg02root.img \
  -hdb /vmdata/dg02swap.img \
  -boot c -m 4096 -vnc :2 \
  -net nic,macaddr:=54:52:00:12:02:01,vlan=0 \
  -net tap,ifname=tap1,script=addif.sh,downscript=no,vlan=0 \
  -net nic,macaddr:=54:52:00:12:02:02,vlan=1 \
  -net tap,ifname=tap7,script=addif.sh,downscript=no,vlan=1 \
  -net nic,macaddr:=54:52:00:12:02:03,vlan=2 \
  -net tap,ifname=tap12,script=addif.sh,downscript=no,vlan=2

• dg03

# /usr/libexec/qemu-kvm -k ja \
  -hda /vmdata/dg03root.img \
  -hdb /vmdata/dg03swap.img \
  -boot c -m 4096 -vnc :3 \
  -net nic,macaddr:=54:52:00:12:03:01,vlan=0 \
  -net tap,ifname=tap2,script=addif.sh,downscript=no,vlan=0 \
  -net nic,macaddr:=54:52:00:12:03:02,vlan=1 \
  -net tap,ifname=tap8,script=addif.sh,downscript=no,vlan=1 \
  -net nic,macaddr:=54:52:00:12:03:03,vlan=2 \
  -net tap,ifname=tap13,script=addif.sh,downscript=no,vlan=2

-netオプションが１仮想ホストにつき６つ出てきますが、２つ１組で１つの仮想NICの設定に対応します。これで仮想OSを起動した後に、VNC経由でコンソールに接続し、IPアドレスやホスト名などのネットワーク設定を入れていきます。

仮想ホスト起動コマンドのオプションについていくつか補足を。

まず、netオプション内でMACアドレスを指定していますが、複数仮想ホストを扱う場合は必須の設定になります。Webの情報では最近のKVMはMACアドレスは重複しないように自動採番されると記載されていることが多いです。確かに、指定しなかった場合、１番目のNICは54:52:00:12:34:56なら２番目のNICは54:52:0:12:34:57と＋1されて重複しません。しかし、重複回避は１仮想ホスト内での話であり、２番目の仮想ホストではしっかりと１番目の仮想ホストと同じMACアドレスを割当てようとします。つまり指定しないとdg01のeth0とdg2のeth0でMACアドレスが被ります。

ifnameで仮想NICに接続するTAPインターフェース名を指定します。実際にTAPインターフェースは仮想ホスト起動時に作成されますが、ネットワーク（ブリッジ） に接続する処理は前回作成したaddif.shが担います。そのスクリプトファイルはscript=...で指定します。仮想ホスト終了時にTAPインターフェースは自動削除されますが、その際に実行するスクリプトはdownscript=...で指定します。今回は何もしないのでnoを指定しています。

最後ですが、なぜかVLANの設定が必要です。パブリックネットワークにはVLAN ID=0を、インターコネクトネットワークにはVLAN ID=1を、データ変更転送用ネットワークにはVLAN ID=2を割当てています。これがないと、仮想OSを1つ起動させた状態では、VNC経由のコンソール画面がログインプロンプト表示後にグレーアウトするという不具合が、仮想OSを２つ以上起動させた状態では、qemu-kvmプロセスがCPUコアを100％使い果たしてしまうという問題に遭遇しています。前者の原因は分かりません。後者はスパニングツリー（L2レベルでのループ）によるブロードキャストストームが原因ですが、何処がループになっているのかは理解できていません。 （試しにvlan指定を取り払い、br0～br4に対してSTPを有効にすると問題なく動作します。本来はこっちが本筋な設定なのかも知れません。）

色々問題ありましたが、ここまでくれば、dg01～dg03はパブリックネットワークとデータ転送用ネットワークを通して互いに通信できますし、dg01とdg02はインターコネクトネットワーク経由で通信できますが、dg03は通信できないという想定通りの挙動が得られます。


今回はここまでです。次回はOracleインストール用に仮想OSの設定を行い、RACをインストールします。 

KVMでDataGuard環境を作ってみよう Part1

（０）　前置き

Oracle DatabaseにはReal Application Clusters（RAC）以外にも可用性を高める仕組みとしてData Guardと呼ばれる機能があります。ざっくり言うと、マスタDBへのデータ変更をスタンバイDBに適用するレプリケーションタイプの冗長構成になります。

仕事柄、RACを組むことは時々あります（最近は若い人に任せっきりですが）。しかし、今までData Guardは組んだことがないので冬休みを機に挑戦してみたいと思います。ただ、冗長構成ということで台数が必要ですが、そんなに物理マシンが手元にあるわけではないので、KVMを利用して仮想マシンを複数台用意して、 その上で構築したいと思います。

目指すのは以下のような環境です。

dg01～dg06が今回作成する仮想マシンです。dg01&dg02、dg03&dg04、dg05&dg06の組でそれぞれRACデータベースを作成し、それぞれプライマリDB、スタンバイDB（物理）、スタンバイDB（論理）とします。RACなのでDB接続を受けるパブリックネットワーク以外に、インターコネクトネットワークも必要になります（今回は異なるDB間のインターコネクト通信は出来ないものとしました）。加えて、適用すべきデータ変更の情報を転送するためのネットワークを別に用意します。

今回利用する物理ハードのスペックは以下になります（借り物です）。

• CPU：Xeon 5430 2.66GHz（４コア）×２ソケット
• メモリ：32GB
• HDD：146GB×４ （RAID5）、実施 400GB

また、仮想化ソフトについては私は普段はVirtualBox使いなのですが、今回は今までに使ったことがないという理由でKVMを採用してみようと思います。OSはなぜかOracleLinux6.3を使います。あと、KVMでは最近libvirtと呼ばれるフロントエンドを利用することが前提のようですが、KVMのコマンドを直接叩いてみたいのでlibvirtは使いません（既に後悔していますが）。

以下、簡単に顛末を書いていきますが、厳密な手順書でない点、不明・不具合がある点、Data Guardとは関係ないメモも含まれる点、そもそも書き始めた時点でData Guardの構築が完了していないため致命的な間違いがあるかもしれない点はご了承ください。

（１）　物理ホストのOSインストール

物理ホストへのOSインストールは特に変わったことをせずに、Minimal構成でインストールします（昔は最小構成でインストールするとその後のrpmインストールが大変でしたが、最近はDVD自体がyumのリポジトリになっているので、最小構成インストールも怖くないですね）。

一点、パーティション構成だけは以下のようにしました。

• /boot：200MB
• /：15GB
• スワップ：1GB

仮想OSのイメージなどを置くパーティションはOSインストール後に作成します。

 （２）　物理ホストOSインストール後の基本設定

OSインストール後にはいくつか追加の設定を入れていきます。

• ネットワーク設定（最小構成ではインストール時にホスト名しか設定されない）
• IPv6無効化
• SELinux無効化
• 不要なサービス停止
• 必要なrpmインストール
• パーティション作成

必要なrpmパッケージについては先に述べたとおりDVDがyumリポジトリになっているので、/etc/yum.repo.d/以下に、baseurl=file:/media/dvd、gpgcheck=0、enabled=1のエントリを作ってあげれば、普通にyum installで依存関係を自動解決するインストールが使えるようになります（/media/dvdはDVDのマウント先）。今回は以下のパッケージをインストールしました（依存するrpmパッケージは記載していないです）。

• bridge-utils
• qemu-kvm
• qemu-kvm-tools
• openssh-clients：SCPサーバとして動作させるため
• telnet：ポート疎通確認用
• bind-utils：dig/nslookupが使いたいため
• mlocate：locateコマンドが使いたいため
• sysstat：sarやiostatが使いたいため

あと、ディスク容量の空きを利用して390GB程度の仮想ホストイメージ用のパーティションを作成します。ただ、fdiskでパーティションを表示すると「パーティション？は、シリンダ境界で終わっていません。」というエラーが表示されます。パーティションの作成・マウントは正常に出来るみたいです。Webの情報を見るとfdiskのバグで実際は正常らしいですが、もう少し確認が必要でしょう。

また、ext4からはデフォルトでバリア機能が有効になっているので/etc/fstabでマウントオプションにnobarrierを忘れず書いておきます。以前にバリア機能の有効/無効でどれだけ差が出るか確認したことがありますが、MySQLのデータロードで1.5倍程度性能差が出るので、nobarrierは性能が求められるところでは必須だと認識しています（代わりに何を失うのかはよく理解する必要がありますが）。

  （３）　仮想ホスト用のネットワーク設定

少し本題に近づいていますが、仮想ホスト用にブリッジの設定を入れます。

今回は、パブリックネットワーク×１、インターコネクトネットワーク×３、データ変更転送用ネットワーク×１のため、合計５つのブリッジを作成します。また、パブリックネットワークは物理NICを通して外部と通信できるように設定します。具体的には/etc/sysconfig/network-scripts/に以下の設定を入れます。

•  ifcfg-br0 （パブリックネットワーク用、IPADDRは物理ホストのIPアドレス、HWADDRは物理NICのMACアドレス）

DEVICE=br0
BOOTPROTO=static
HWADDR=00:23:7D:EE:80:1A
IPADDR=192.168.79.162
NETMASK=255.255255224
NM_CONTROL=yes
ONBOOT=yes 
TYPE=Bridge

• ifcfg-br1～ifcfgbr3 （インターコネクト用）

DEVICE=brN (N=1..3)
BOOTPROTO=static
ONBOOT=yes 
TYPE=Bridge

•  ifcfg-br4 （データ変更転送用）

DEVICE=br4
BOOTPROTO=static
ONBOOT=yes 
TYPE=Bridge

•  ifcfg-eth0 （物理NIC）

DEVICE=eth0
BOOTPROTO=static
HWADDR=00:23:7D:EE:80:1A
NM_CONTROL=yes
ONBOOT=yes
TYPE=Ethernet
BRIDGE=br0

ポイントとしては、ネットワークの数だけブリッジを作成し、外部NIC経由で通信できるブリッジに関しては、IPアドレス設定をそのブリッジに移して、物理NICではそのブリッジに接続するという設定を残してIPアドレス設定を消すことでしょうか。（なぜブリッジにIPアドレスを設定するとホストOSと通信できるようになるのかはよく理解していません。ブリッジはデフォルトでホストOSと接続されているのでしょうか？）

ブリッジを作成したところで、KVMゲストはこれらのブリッジとTAPインターフェースを通して接続されます（他にもuser/vde/socketと３つ手段があるようですがよく理解していません）。TAPインターフェース自体はKVMゲスト起動時に自動的に作成されますが、どのブリッジに接続するかは管理者側で設定する必要があります。設定方法の１つとして、TAPインターフェース名を渡すとブリッジに接続するコマンド（brctl addif）を実行するスクリプトを用意しておく方法があるようです。

今回はパブリックネットワークにtap0～tap5、プライベートネットワークにtap6～tap11、データ変更転送用ネットワークにtap12～tap17のそれぞれのTAPインターフェースを利用することとします。すると、上記のスクリプトは以下のようになります（実行ファイルとTAP-ブリッジの関連の情報ファイルを分離しています）。

• addif.sh

#!/bin/sh

LIST_FILE=tap.list

ifname=$1
brname=`grep "${ifname}," ${LIST_FILE} | cut -d, -f2`
 
ifconfig ${ifname} up
brctl addif ${brname} ${ifname}

•  tap.list

tap0,br0
tap1,br0
tap2,br0
tap3,br0
tap4,br0
tap5,br0
tap6,br1
tap7,br1
tap8,br2
tap9,br2
tap10,br3
tap11,br3
tap12,br4
tap13,br4
tap14,br4
tap15,br4
tap16,br4
tap17,br4

このスクリプトはKVMゲストを起動させるときに利用します。


今回はここまでです。次回はKVMゲストへのOSインストール、KVMホストの複製、ネットワークの疎通確認を記載します。

MySQL EE + WSFCってどうなんだろう（価格だけ）

別にこの記事の件でお金貰っているわけではないですが（そもそも貰えない）、日本オラクルとパートナ関係にある会社に勤めてる人間が書いているので、中立かと言うと疑問も残るので、その辺あしからず。

MySQLの高可用性オプションとしてクラスタソフトによるHA構成はよくある話で、RHEL+LifeKeeper（もしくはClusterProなど）+MySQL Standard Editionといった構成は良く聞きます。しかし、最近MySQL Enterprise EditionならWindows Server Failover Clusterがサポートされるって話を知りました。

MySQL Enterprise High Availability http://www-jp.mysql.com/products/enterprise/high_availability.html

だったら、MySQLのエディションをSEからEEにすることによる費用増加がクラスタソフトの値段を超えなければ得なんじゃないかと言う疑問が出てきたので、ちょこっと計算してみたいと思います。

IAサーバ２ソケット２台の構成で概算します。値段は２台分、ライセンス費用は初期費用に、年間サブスクリプション＆保守は年間コストにつけています。RHELは３年サブスクリプションがありますがここでは無視して単年サブスクリプションで計算しています。あと定価です（ここ重要）。
 

MySQL SE+LifeKeeperの場合
No	製品	初期費用	年間コスト
1	Red Hat Enterprise Linux Server Standard(最大仮想化ゲスト数:1)		203,280
2	LifeKeeper for Linux & MySQL ARK	1,216,000	304,000
3	My SQL SE		434,782
	合計	1,216,000	942,062

 

MySQL EE+WSFCの場合
No	製品	初期費用	年間コスト
1	Windows Server 2008 Enterprise 日本語版（25 CAL付）	1,440,000
2	My SQL EE		1,086,958
	合計	1,440,000	1,086,958

というわけで、結論は安くなりませんでした。

• WSFCはWindows ServerのEnterpriseでしか使えないのが結構利いている気もします。
• RHELでStandardを選んでいることに根拠はないです。Premiumを選ぶと、年間コスト＋１４万で、前者・後者の年間コストはほぼトントンになります。
• 初期＋２０万＆年間１５万でMySQLをSEからEEに替えられるなら安いと見る向きもあるかもしれません。（個人的にはお客様を納得させるには苦しい言い訳のような気がしますが）
• あとWindowsに縛られるのが嫌だと言うお客さんだと、そもそも後者は選択肢にないですね。
• 加えて、ここでは考慮してませんがユーザ数が増えた場合にWindows ServerのCALをどうするかという話が残っています。
• そもそもでいえば、前者は実績が比較的豊富で安心感があるっていうのも事実かもしれません。
• 後者を薦める場合は、Windowsによる管理のし易さを何処まで訴求できるかかなぁ。（確かにSQL Server+WSFCは簡単だからなぁ）

何回も言いますが、これらの価格は値段です。製品によっては仕切率が数十％という製品やボリュームライセンスなどあるので、購入する際は必ずベンダーさんから正式な見積を取って判断してください。このページはMySQL EE+WSFCなんてあるんだ程度に捉えてください。
（個人的には定価と実売値の二重価格は何とかならんもんかと思いますが、代理店経由販売とかベンダ間の情報戦などがあり解決はされないのでしょう。）

でも、技術者としてはMySQL + WSFCは一度は組んでみたいなぁ。

最後に今回の価格の情報源は以下です。

• RHEL http://jp.redhat.com/resourcelibrary/articles/articles-red-hat-enterprise-linux-purchasing-guide 
• Windows Server http://www.microsoft.com/japan/presspass/detail.aspx?newsid=3331
• MySQL http://www-jp.mysql.com/products/
• LifeKeeper http://lk-quotation-tool.sios.com/