2020年9月25日金曜日

index->lock の競合について 〜ベンチマークはちゃんとチューニングして〜

他に忘れないうちに書きたいこともあったのですが、世に出るまで書けないので、ソースと関係ない一般的なこと(バージョン5.7以降)を書きます。(書かない方のことは書けるようになる頃には忘れてしまうかも…)

index->lockの競合を直して欲しい。という人がいまだに居たりするのです。色々試しましたが、多分殆どの場合は理解不足・チューニング不足です。私自身はindex->lockの競合が不可避なベンチマークに結局会っていません。

特にMySQLとその他のRDBMSを比べる場合にはちゃんと最適化した負荷をかけないとMySQLが悪く見えるのでベンチマークをする際には気をつけて欲しいものです。

5.7で更新・参照並列性を高めるために導入された、index->lockのSXロック(Sロックは可能・SX/Xロックは不可)は、基本的にそのindexにpageを追加・削除するような処理をする際に保持されます。何かする度にpageの追加・削除をするような処理がindex->lockの競合を引き起こします。なので、index->lockの並列性を少し解決したところで、どうせ次のspace->latchとかの競合(index->lockより大きい範囲かも)になるので、この場合意味がありません。

「indexにpageを追加・削除するような処理を極力減らすことが唯一の解決策です。」


index->lockに関わる代表的な(性能が)悪い例が sysbench-tpcc です。sysbench向けのスクリプトですが、十分に処理を吟味すること無く、--tables なんてオプションを付けて誤魔化しているようです。
見ていきましょう。。。

ポイントは2つあります。

1.UPDATEが激しい場合は極力inplaceになるようにする。つまり、定義上固定長レコードにする。


レコードのサイズが変わる場合はサイズチェックが行われ、pageの追加・削除の可能性が出ますが、 固定長の更新の場合は、「その可能性はゼロ」です。最も性能上好ましいUPDATEです。 ここまではなんとなく知っている人も多いと思いますが、間違いやすいことがあります。

「真の固定長はNOT NULL」


nullableなカラムがあるとレコードにNULLを表現するための1ビットの領域が確保され、 NULLの場合には該当カラムの中身は0バイトになります。 つまり「NULL<->NULL以外の場合」のUPDATEでレコード長が変わりinplaceではなくなります。

sysbench-tpccでNULLからのUPDATEが起こらないように変更しましょう。 特異値を決めてNULLの代わりに使うようにします。 この場合は主に orders表 と order_line表 の初期レコードの問題みたいです。

--- tpcc_common.lua
+++ tpcc_common.lua
@@ -236,7 +236,7 @@
        o_w_id smallint not null,
        o_c_id int,
        o_entry_d ]] .. datetime_type .. [[,
-       o_carrier_id ]] .. tinyint_type .. [[,
+       o_carrier_id ]] .. tinyint_type .. [[ not null default 0,
        o_ol_cnt ]] .. tinyint_type .. [[,
        o_all_local ]] .. tinyint_type .. [[,
        PRIMARY KEY(o_w_id, o_d_id, o_id)
@@ -266,7 +266,7 @@
        ol_number ]] .. tinyint_type .. [[ not null,
        ol_i_id int,
        ol_supply_w_id smallint,
-       ol_delivery_d ]] .. datetime_type .. [[,
+       ol_delivery_d ]] .. datetime_type .. [[ not null default '1900-01-01',
        ol_quantity ]] .. tinyint_type .. [[,
        ol_amount decimal(6,2),
        ol_dist_info char(24),
@@ -510,7 +510,7 @@

       query = string.format([[(%d, %d, %d, %d, NOW(), %s, %d, 1 )]],
        o_id, d_id, warehouse_num, tab[o_id],
-        o_id < 2101 and sysbench.rand.uniform(1,10) or "NULL",
+        o_id < 2101 and sysbench.rand.uniform(1,10) or "DEFAULT",
         a_counts[warehouse_num][d_id][o_id]
         )
       con:bulk_insert_next(query)
@@ -558,7 +558,7 @@

       query = string.format([[(%d, %d, %d, %d, %d, %d, %s, 5, %f, '%s' )]],
            o_id, d_id, warehouse_num, ol_id, sysbench.rand.uniform(1, MAXITEMS), warehouse_num,
-        o_id < 2101 and "NOW()" or "NULL",
+        o_id < 2101 and "NOW()" or "DEFAULT",
         o_id < 2101 and 0 or sysbench.rand.uniform_double()*9999.99,
        string.rep(sysbench.rand.string("@"),24)
         )
TPC-Cの仕様上、これらの値は確かNULLである必要は無かったと思います。 他のベンチマークプログラムではNOT NULLになってたかも知れません。

2.同じ領域内で激しいINSERT/DELETE混合処理がある場合にはpage結合処理の閾値を下げる。


キーの順番で大きいものをINSERTし、小さいものをDELETEしていく場合には問題は起こらないと思います。

近いキー値のINSERT/DELETEの度にpageの追加・削除でバタバタしないように、 削除側の条件を下げて余裕をもたせます。 下げた分の割合でデータファイルは大き目になりますが、 性能には影響しないでしょう。 MERGE_THRESHOLD はこのために5.7から導入した方法です。(これも5.7から)

また、念の為明記しておきますが、

「二次索引のキー値の UPDATE は DELETE-INSERT」


ですので、激しく行う場合はチューニングしたほうが良いでしょう。

sysbench-tpccでは、new_orders表が主キー順ではないINSERT/DELETEが多いので 一応調整したほうがいいでしょう。(処理は多いが小さめに保たれる表なのでデメリットは少ない) とりあえず MERGE_THRESHOLD=30 くらいで。

--- tpcc_common.lua
+++ tpcc_common.lua
@@ -252,7 +252,7 @@
        no_o_id int not null,
        no_d_id ]] .. tinyint_type .. [[ not null,
        no_w_id smallint not null,
-       PRIMARY KEY(no_w_id, no_d_id, no_o_id)
+       PRIMARY KEY(no_w_id, no_d_id, no_o_id) COMMENT 'MERGE_THRESHOLD=30'
        ) %s %s]],
       table_num, engine_def, extra_table_options)
 
という感じで、他者と比較するベンチの場合にはちゃんとチューニングしてください。処理を。

※余談


ちなみにsysbenchに付属のoltp_common.luaも後者の問題があります。 二次索引のキーの変更を行う場合には、 その索引に MERGE_THRESHOLD=30 を付ければindex->lockの競合はなくなります。

--- oltp_common.lua
+++ oltp_common.lua
@@ -235,7 +235,7 @@
    if sysbench.opt.create_secondary then
       print(string.format("Creating a secondary index on 'sbtest%d'...",
                           table_num))
-      con:query(string.format("CREATE INDEX k_%d ON sbtest%d(k)",
+      con:query(string.format("CREATE INDEX k_%d ON sbtest%d(k) COMMENT 'MERGE_THRESHOLD=30'",
                               table_num, table_num))
    end
 end
というわけで、私はindex->lock競合は5.7でほぼ解決てる(というか別の競合に先にぶつかる)筈と思っているのですが…どうでしょうか?