2021年5月6日木曜日

ディスクフルは危険かも

今回は性能の話ではありません。MySQLがディスクフルで詰む可能性についてです。

とあるバグ(また新機能入れて共通パスを変更したせいで…既存に影響するのは入れなければバグ減るのに…)をきっかけに最近いろいろ調べていましたが、 一応情報共有したほうが良いと思うので可能と思われる限り共有します。 しかし、今後の対応は議論中でチームとして会社としての方針とは異なる可能性があるので、 現状を説明するに留めます。

しかしそもそも、ディスクフルまで使う容量設計は元来非常にマズいので 誰の環境も該当していないと思いますが、一応順を追って(遡って?)説明します。

とはいえ基本的な利用では問題は起きないはずなのです。

データファイル作成/拡張時にディスクフルで失敗なら、その作成/拡張の原因となるSQLがエラーで返る。 これが基本動作です。サービスが落ちることは無いです。

しかし、ファイル作成/拡張ではエラーが起きなかった場合は処理はそのまま通り、 その処理のトランザクションログは生成されCOMMITも成立しますが、 データページをWriteする時にENOSPCエラーとなる場合があります。

データページのWriteがどうしてもできない場合に、InnoDB は Abort するしかありません。 (ページを書き出せないのでその変更LSNまでのチェックポイントができず、 ログの上書きができずにどうせ止まってしまうので。傷口が拡がる前に止まる。 ということかと思います。)

そうしてAbortしても、ディスクフルで書けない訳なので、 そのままリカバリしても同じ理由でWriteエラーでAbortしてリカバリできません。

ストレージを拡張するか大きなものに移動してのリカバリができないのなら、ここで詰みます。 データの整合性も、お終いです。

というわけで、 ストレージを拡張するか大きなものに移動できない状況(そんなカツカツな状況があるか知らんけど)で、 クラッシュしても100%データの整合性を確保するためには、(というかそもそもクラッシュしないように) Write IO が ENOSPC を起こさないように使う必要があります。

何が ENOSPC の大元かというと、Sparse File です。 ファイルサイズは確保するけど、間欠的に内容を破棄してその分の容量を空き容量に回すことができますが、 破棄した空き領域に書き込む際に容量を食い、足りない場合に ENOSPC が発生します。 もちろんOS/FSがサポートしなければ使われないのですが、 Linuxでいうと、xfs、ext4 はサポートされているので対象です。 なので、利用法で避けることになります。

<ストレージ容量カツカツ(もう替えも足しもない)の場合に安全のため避ける設定>

(1) InnoDBの透過的ページ圧縮(表定義で COMPRESSION="zlib" とか指定するやつ)

ページ単位で圧縮して書き込み、空いた分の内容を破棄して空き領域に返す仕組みです。

容量を節約するのに使うと思いますが、容量が足りない場合は危ないです(矛盾?!)

既存圧縮ページよりも圧縮率が悪くなるページの上書きは空き容量を必要とするので ENOSPC が発生するかも知れません。

さらに、表単位のこの圧縮の設定はデータディクショナリにあるので、 そのフラグはリカバリ中(ディクショナリもリカバリ中)には参照できず、、、

リカバリ中に変更が発生したページはすべて非圧縮に戻ってしまいます。(更に空きが足りなくなる!)
※この対応は議論中です。簡単な変更で実用レベルに軽減はできると思います。

(2) AUTOEXTEND_SIZE= 指定の表(ibdファイル)の作成

どういうわけか、小さな表が(死ぬほど)多い場合の節約のためか、 ibdファイル作成時の最初のサイズ分は 未書き込みのページは 空き領域に返されます。 (ちなみに拡張時はSparseにはしないので安全です。)

とはいえデフォルトでは、初期ファイルサイズは数ページで、そのような小さなサイズでは問題は起きません。 が、8.0.23 でInnoDBでも効くようになった AUTOEXTEND_SIZE= 指定を大きく(max 64M)すると、 最初のファイルサイズもそのサイズで確保され、最初の未書き込みぶんがまるごと空き領域に返されるので(拡張時は大丈夫。念のため)、 ディスクフル近傍で表を作ったり書いたりを繰り返してると空き領域がショートして落ちるかも知れません。
(e.x. AUTOEXTEND_SIZE=64M とかで空の表をディスクフル近くまで沢山作成しても、全部の表を64Mまで埋める前に落ちる。)
※この対応も議論中というかレビュー中です。これは普通に空き領域に返さない様に直しても困る人は居ないと思う。。。のだが。。。

以上でした。

基本的には以上なのですが、 詳しくは言えませんが、そもそもディスクフルまで使うのは 特に 8.0.23、8.0.24 では止めたほうが良いと思います。(8.0.25で直る予定) 万が一そこでクラッシュすると、リカバリでデータが壊れる可能性があるので。。。

2021年3月9日火曜日

MariaDB 10.5 の性能は不正?

普段は基本的にMariaDBの動向は全く追って無いです。 でも先日、MariaDB 10.5 のfsync()発行が少なく性能が良いのは何故なのかちょっと見てほしいと言われて、 mariadb-10.5.9.tar.gz をざっと見たらあっという間に原因特定。

「fsync()を待つべきなのに待ってないから」
只の不正と判明。

動作としては、
innodb_flush_log_at_trx_commit = 1
でも
innodb_flush_log_at_trx_commit = 2
でも
並列度が上がると多くのトランザクションが
innodb_flush_log_at_trx_commit = 0
の動作と同等となってしまうようです。

待たないのだから速いに決まってる。こんな不正なものと比較されるのは腹立たしいです。
指定のLSNまでのwriteやflushを終わらせる log_write_up_to() という関数があるのですが、 そこに下記のような assertion codeを入れて動かせば直ぐに落ちると思います。

mariadb-10.5.9> diff -up storage/innobase/log/log0log.cc.orig storage/innobase/log/log0log.cc
--- storage/innobase/log/log0log.cc.orig        2021-03-02 12:04:30.167590939 +0900
+++ storage/innobase/log/log0log.cc     2021-03-03 10:34:30.113416497 +0900
@@ -797,6 +797,8 @@ void log_write_up_to(lsn_t lsn, bool flu
   if (flush_to_disk &&
     flush_lock.acquire(lsn) != group_commit_lock::ACQUIRED)
   {
+    /* should be flushed enough */
+    ut_a(lsn <= log_sys.get_flushed_lsn());
     return;
   }

@@ -812,6 +814,9 @@ void log_write_up_to(lsn_t lsn, bool flu
     write_lock.release(write_lsn);
   }

+  /* should be written enough */
+  ut_a(lsn <= log_sys.write_lsn);
+
   if (!flush_to_disk)
   {
     return;
誰かの処理が「終わる」のを待つのではなく、 誰かがwrite/flushしてる「最中」であれば先に進んでしまう構造みたいです。

MariaDB 10.5 が速いなんて率先して言ってるベンチマークは怪しい意図を感じますね。

(追記)
log_write_up_to()はWAL(Write Ahead Log)の原則を守るためにデータページの書き出し前にも呼ばれます。これが破綻しているということは、ログを書く前にデータページを書く可能性があるということでクラッシュするとログを書いてるLSNまでも再現できずにデータが壊れる可能性があります。

2021年1月30日土曜日

MySQLバージョンアップによるInnoDB性能劣化可能性事件簿

一般論ですが、どんな基盤ソフトでもCPUスケールを上げようとすれば、何らかの排他制御を細かく行うことになるのでCPUのパイプライン処理にブレーキをかけるアトミックな処理が増えて、バージョンが上がるとある程度はシングルスレッドの処理は重くなっていきます。前エントリのような言語の高度化により遅くなる事情もあります。(中には、Redisのように並列を捨てて排他処理を完全排除する潔い逆振りプロダクトもありますが。)

とはいえ、「これは(条件付きとはいえ)急に遅くなりすぎだろ!」と私も思うバージョン(回避策はある&一開発者の一存ではどうにもできない)があるので遡って何点か挙げて注意喚起したいと思います。

これらはある程度限られた条件で発生するので世間では怪奇現象扱いされている可能性もあります。

何故こんなことになるのかというと、基盤となるmysqld側の変更に上手くついていけなくなってるか、性能上メリットデメリットが両方ある変更で、HDDにはデメリットが大きいけどSSDではそうでもないので敢行してしまったものとか、あまりにハイスペックなハードでしか性能チェックをしていなくて…(そういう過去の過ちを可能な限り直すのが私の仕事ですが直せない箇所もある…)

というわけで、今後も直らないかも知れない地雷ポイントを利便性のために晒してしまおうという主旨です。更新が複数バージョンに跨ってると上手くリリースノートに反映されないんですよ。最初(1)の件で最近議論になりましたが、ほらブログに書けばいいじゃないか、みたいなこと言われたので…

…全部書いてやります。(ぇ

影響度が高い新しい方から挙げます。古い方は皆さんもう忘れたか諦めたかですよね?…よね? また、新しいものほど根が浅いので近い将来直ってる可能性はあります。将来のバージョン使う時はちゃんと確認しましょう。

(1) fallocate() log sync事件(Linux版のみ) (8.0.22〜) 回避策は8.0.23〜 ※近いうち直ると思います。

<条件>

これは、ストレージが遅いとデメリットの方が大きいかも知れない変更ですが、8.0.23だと回避策があり、それを使うと逆に速くなる(!)のですが、面倒で使わないと遅いまま。という類のものです。条件にヒットすると、file_per_tableでINSERTが沢山あって単調増加の処理が重くなります。データ取り込みとか。

まぁ、それでも前々エントリのlog_sys改善で相殺して気づかない人も多いかもですが。

そういう事情なので、ユーザーの手間を考えない開発陣はこのまま放置する方針みたい…

<仕組>

fallocate()してファイル拡張の処理を軽くする(メリットも大きい)のですが、リカバリを考えるとファイル拡張のログをトランザクションログに残さないと危険で、トランザクションログはWALの原則があるので、そのログがflushされるの待ってfallocate()します。fallocate利用自体はON/OFFできるのですが、ログの整合性を考えファイル拡張のログは常に出力される。つまりファイル拡張時は必ずlog flush syncが起きます。

というわけで、すべてのレコードINSERTにlog flush syncする可能性が発生します。(commitよりも細かい単位。innodb_flush_log_at_trx_commit設定も現状関係ない。)

現状、その可能性を最小化するのは拡張の頻度を減らすことのみです。8.0.23で従来NDBのものだったテーブル単位のAUTOEXTEND_SIZEパラメータを受け付けるようになったようで、それを大きくして設定して頻度を減らせば逆に速くなる可能性のほうが大きいです。

※単語の意味は違うけど、innodb_flush_log_at_trx_commit に従うようにすると混乱は少なくなるとも思うのだが。現状無視されます。そうだとしても、回避策のほうが結果速くなるから性能観点では下記回避策を推奨。

<回避策>

8.0.23以降で、ディスクスペースに余裕があって(INSERTが多い処理なのだから普通あるだろ)、テーブルがある程度正規化されていて常識的な個数の場合は、テーブル1個あたりファイルサイズが最低64Mになっても困らないですよね?

何も考えずに全てのfile_per_tableの通常InnoDBテーブルを最大値

ALTER TABLE xxx AUTOEXTEND_SIZE=64M;

してしまうこと(TABLESPACE使ってる場合はそっちも?)と、既存の通常InnoDBテーブルのCREATE TABLE文に(一時テーブルはlog発生しないので関係ない)、下位互換性も考慮して、

/*!80023 AUTOEXTEND_SIZE=64M */

を足しておきましょう。

というのは如何でしょうか?バージョン違いとかレプリケーションの整合性とかは自分で考えてください。

(※追記: log flush sync 自体は不要かも。ファイルサイズは増加しかしないし、既に増加してたら無視すればいいので。中身に関するログとの前後関係さえちゃんとしてれば。)

(※追記2: 何か意図があって追加されたものなのですが、他で代用可能で、且つ現状何の役にも立ってないので、近いうち戻ると思います。回避策はチューニングとして依然有効です。)


どんどん遡ります。

(2) 新 Doublewrite Buffer 事件 (8.0.20〜)

<条件>

素、若しくはRAID1、のHDDで、Doublewrite Bufferが有効な場合に8.0.20以降でデメリットが勝ると遅くなったかも知れません。

<仕組>

並列性や、SSDなどの特性も考慮してDoublewrite周りが完全改修されました。が、デフォルトが細かい単位で書き出しを区切るようになった(SSDなどではそのほうが速くて安全)ので、シークが遅い素HDDなどのデバイスでは遅くなる場合があります。

<回避策>

innodb_doublewrite_batch_size (8.0.20〜) を大きく設定します。従来値相当は 120 ですが調整してもいいかも。遅くなってない人は設定しないほうが良いかも知れません。

 

以降は、5.7に端を発するものです。知らないほうがいいかも?

(3) Adaptive Hash Index 事件 (5.7.8〜) ※これ以前の5.7.1〜5.7.7はもっと酷い。GA以前だから割愛。

<条件>

CHAR/VARCHAR などのcharactersetベースのキー値の二次索引を利用している場合に Adaptive Hash Index の効きが5.6よりも悪くて、恩恵を受けていた一部のバッチ処理等が遅くなるかも知れない。今更ですが。

<仕組>

5.7以降では、文字列データはUnicodeベースになったために、charactersetベースのデータの比較などはmysqld側を呼び出して行うようになりました。(乱暴な説明だが、大体合ってる。)

本来、Adaptive Hash Index 内部ではバイト単位比較を行っており、その中でも Adaptive Hash Index を利用するかどうかの判断が細かく行われていたのが、CHAR/VARCHAR等の文字列データキー値では部分一致での判断ができなくなってこの部分が飛ばされてしまいます。

※(補足)Adaptive Hash Index は全ての二次索引レコードへのショートカットを提供する役割がありますが、構造上並列性が低く、OLTPな処理ではメリットが薄く敬遠されます。しかし、そもそも並列性の低いバッチ処理(二次索引があるテーブル同士の"INSERT SELECT"文とか)では結構加速がかかる場合があるみたいです。

<回避策>

collationとか関係ない英数字のコードがキー値ならば、BINARY/VARBINARYに変えてしまえば5.6と同様にAdaptive Hash Indexが利用されます。

 

(4) PSI_MEMORY 疑惑 (5.7.1〜)

<条件>

5.7以降、PERFORMANCE_SCHEMA でメモリ確保開放の統計も取るようになりました。これが重くて、performance_schema=OFF にしてる人も多いと思いますが…

実は、OFFにしてもまだ重いのです。

この件について8.0では、メモリの確保開放が整理されてかなり頻度が減っているので(5.7よりは)影響はかなり小さいと思います。さらに新しい8.0では(8.0.23?)もっと改善してるかもしれません。

現状ほぼ、5.7固有の問題かも知れないです。

※これがある御蔭で、5.7よりも8.0の方が遅い部分は目立たなくなってるのかも……

……いや、聞かなかったことにしてください…

<仕組>

performance_schemaがONかOFFかチェックするコードが重いんだと思います。(!!!)

<回避策>

ビルド時に "-DDISABLE_PSI_MEMORY=ON" とすればメモリ確保開放周りのそのコードもカットされて軽くなります。まぁ、当然performance_schema=ONにしてもメモリ関連は見られなくなりますが。

※8.0以降ではメモリ確保周りは大きく変わってる(バリエーションが増えてる)ので"-DDISABLE_PSI_MEMORY=ON"しないほうが安全かも?(怪情報)

え、ビルドできない?

大量に購入頂いている大口のお客様なら、かかりつけの者がいると思うので、「5.7の"-DDISABLE_PSI_MEMORY=ON"のバイナリが欲しい」と言えば作ってもらえるかも?(いや、作ってもらえないかも。無責任には断定できないですが。)

矢張り、いずれにせよ、一度ビルドして速くなるか試してからをお勧めしておきます。いやお願いします。やたら反響が大きいと怒られるかもなので…

 

以上です

というわけで、私の力が及ばないモノを挙げてみました。他にも細かいモヤモヤポイントもありますが、書かなかったことは諦めてないもので危険な劣化ではないもの、又は調査中ということで、またいずれ。

2021年1月1日金曜日

やはりC++はCよりも遅い?

とりあえず、残りは細かいことが多いので後で書かないと思うので一般的なことだけ少し書き残しておきます。

大人の事情もあるので具体的なことは勘ぐらずに一般論の個人的な考えとしてお願いします。まぁ、今回の内容には直接の知財問題は無いとは思いますが。

例えば、ある程度大きなプログラムで、昔はCで書かれていて、バージョンが上がる度にプログラムは大きくなり、大きいプログラムのメンテナンスや開発効率のために、また更に新しい規格のC++の記法を用いたりしてバージョンアップをしていくことは往々としてよくある話かと思います。

記法が高度で複雑になるほど、最適化も難しくなるはずです。

C言語程度の単純な記法に対しては、「__builtin_expect()」 とか 「__attribute__()」 とか 「#pragma」 のいろいろな最適化指定も効きましたが、高度な記述の最適化に必要な高度なコンテキストをコメント等の記述でコンパイラに伝えることは難しいのか、挙げた以上のものはコンパイラのマニュアルにはありません。

代わりにあるのが、PGO (Profile-Guided Optimization) ビルドです。速くあってほしい最適化したい処理を実際に実行し、その結果を基に最適なコンパイルをするわけです。論理構造が同じであれば、
「PGOを使って初めて、100%の確率でC相当の記述の時代と同等の性能」
となるわけです。
そうでなければ、コンパイラの解釈により確率的に最適ではないコードが生成される可能性があり、多少遅くなります。そしてその性能劣化はプログラムが大きくなるほど、実行する処理の関係範囲が大きいほど紛れ込む可能性が増えます。

PGOビルドを使わなければ、見かけ上は全然関係ない変更なのに、日々変更を積み重ねる度に徐々に遅くなっていくように見えます。これが(大きな)C++のプログラムが処理によってはCレベルの時代よりも遅くなる大きな理由でしょう。

PGOビルドをベースにすると高度なC++記述の大きなプログラムの真の性能問題が見えるようになります。
※性能劣化したリビジョンを探す過去への真の旅が始まります。7、8年くらいは歴史を見ますか…(遠い目)。
※この、PGOしないことによる性能劣化ノイズは結構大きく、場合によっては劣化の半分はPGOで解決するようです。
 (まぁ、それより大きいものは非PGOビルドでも見えやすかったからでしょうが。)

一つだけアドバイスをするなら、PGOベースでそのような過去の旅をする場合、プログラムがスレッドをdetach(joinしないで管理投げっ放し)する場合にはGCCは上手くプロファイリングできないようなので、clangを使ったほうがいいでしょう。
※MySQLはdetachするスレッドがいくつかあります。

というわけで、具体的な旅の話は旅が終わって結果が世に出てからですね。例によって。いつの日か。

それでは、性能を追求する仲間たち(誰も居ないかな?)の良い旅を願います。

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では違いに気づいてもらえる筈です。まだ何も言えませんが。誰も気づかないでしょうかね…。