2020年12月31日木曜日

MySQL 8.0 の InnoDB の log_sys周り の話

思い出せるうちに思い出せる範囲で…

例によって世に出る頃には全く違うことに取り組んでいるので、忙しくしてると何も書かずに終わってしまうのですが、こんなご時世、年末年始休暇があっても何も用事がなく折角なのですこし書き残します。本来は本家開発者のブログで英語で書くべきなんですが、込み入った話を英語で書く労力をかけるくらいなら次の問題解決にかけたほうがいいので、とりあえず日本語で書き残します…

私がMySQL界を離れている間にリリースされた8.0になってlog_sysのデザインが新しくなり、スケールが良くなったのですが、既存のハードを利用する大半のユーザーには、未だ荒かった実装のせいでデメリットの方が大きかったと思います。2019末くらいには悪い挙動と原因はある程度分かっていましたが、修正リリースは2020後半になってしまいました。

既存のスペックのハードウェアと、CPUコア多数搭載の最新ハードとでは、ベストの性能を出すために共通している部分も多いのですが、違う部分もどうしてもあります。8.0.22での log_sys の大半の修正は共通にできたのですが、writer_thread と flusher_thread の利用に関してだけは、どうしても利害が相反してしまったので仕方なくinnodb_log_writer_threads というオプションを導入せざるを得ませんでした。

CPUコア数が(死ぬほど)多いとCPUコアが余ったり各コアのCPU使用率に余裕があったりするわけで、
CPUコアや使用率を無駄にしてでも余ったCPUコアに仕事を振ってスケールを上げる
というのが目先のベストの戦略かと思います。しかし、CPUコア数の余剰分が無い(まぁ、普通予算が余らなければオーバースペックなCPUにはしないので普通無い)場合はCPUは十分使い切れるので、その戦略で設計されたソフトウェアは「只遅いだけ」のものとなります。

というわけで、共通部分のCPU無駄食いは直したのですが、writer_thread と flusher_thread については「高スペックで必要」、「低スペックでは邪魔」なので、innodb_log_writer_threads オプションがあります。

問題はCPUリソースなので、IOがボトルネックの場合にはON/OFFで差は出ないと思いますが、
全体でのCPU使用率が高い(つまり、全CPUが元々ちゃんと働いている。又は他のコンポーネントと相乗り)場合に、
innodb_log_writer_threads はOFFにしたほうが良いでしょう。

実は、innodb_log_writer_threads 以外で修正した部分の方が影響は大きいので、基本8.0.22にしさえすれば更新系の性能問題は減ると思います。

細かい話では、
logのflushとcheckpointの頻度が環境のスペックが高いほど激増してたのですが、(writeも増えるが、InnoDBでlogのwriteは必ずOSのIOレイヤーでバッファリングされるので問題はない。 というか細かく書き出したほうが下位レイヤーでの最適化が働く余地があるのでいいこともあるらしい。)flushとchoekpointを5.7相当のストラテジーに戻した。
ということや、あとは、
8.0では5.7とは違ってlog_bufferへの書き込みは常に並列化されているので(innodb_log_writer_threads=OFFでも常に)、その書き込みがどこまで連続で終了しているか(最初の未書き込み手前までしかwriteできない)確認していく別スレッド(closer_thread)も存在したのですが、高スペックハードでもCPU無駄食いが酷かったので工夫して廃止しています。

まぁ、性能ギリギリで利用している人は居ないのでだれも気づかないのでしょうかね。
とにかくこれで、log_sys は当面直す箇所は無いと思います。

以前も log_sys の書き込み量が8.0で増えていたのを調査して直したこともありますが(8.0.20)、そもそもユーザーは誰も気づいていなかったようで直しても性能に関する反響は聞きません。
(InnoDBのログの32ビット整数表現は、上位ビットが0で埋まっているほど少ないバイト数で収まります。UNDO spaceのIDは5.7では1バイト表現できたのが、8.0になってマイナス表現側のIDを使うようになったので、UNDO space IDが4バイトになってしまってログの量が結構増えてたので、フォーマットを拡張して、マイナス表現側も2バイト程度には収まるように拡張しました。UNDO space ID自体は同じ長さまで戻ったわけではないですが、全体としてログのバイト数は5.7程度に戻ったと思います。)

自分で探して自分で直すのみですね。

なにはともあれ、念願のlog_sys修正が一段落したので次にすすめるわけです。(まだ、8.0新機能やInnoDB以外の8.0更新部分との相性が悪い部分はあるかも知れないですが)

何故「念願」だったかというと。。。。
前述の「CPU無駄食いスケール優先」は単純な性能の問題だけではなく、「他の性能劣化を隠してしまう」特性もあるからです。リソース消費と性能がちゃんと比例していないモッサリとした特性だと、裏でシングルスレッド性能劣化し放題だからです。

というわけで次の話題もあるのですが、ここまで。

次の話題は、リリースまでソースコード修正の話は書けないのですが、一般技術論は書いてもいいような気もするのだけれども、それが核心だから…さてどうしましょうかね。

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でほぼ解決てる(というか別の競合に先にぶつかる)筈と思っているのですが…どうでしょうか?

2020年7月10日金曜日

次の次のリリースこそは…

性能フリーク(更新系)の皆様には8.0.22では違いに気づいてもらえる筈です。まだ何も言えませんが。誰も気づかないでしょうかね…。