MySQL 8.0 は遅くなってきてる？何故？（２）

前のエントリの続きです。

念を押しておきますが、このブログの「内容は個人の考えであって、所属組織とは方針が異なる」と考えてください。

さて、MySQL 8.0.xの単スレッド性能がどんどん遅くなってきた要因は幾つかありそうなので切り分けていきたいと思います。

まずは、数年前のエントリ「やはりC++はCよりも遅い？」の影響をできるだけ正確に見積もりたいところです。実行バイナリの最適化レベルを合わせて比較して初めて、ロジックの劣化が判るわけです。コンパイラのオプションの範疇でできるだけ最大の最適化を行って計測したいところです。いくつか試した結果、clangのPGO+LTO が手軽な中では最も効果があったのでそれで同じ計測をしてみましょう。(GCCのPGO+LTO と clangのPGOのみ はこれよりも少し劣ったのでとりあえず。)

---

(補足)
PGO は、一旦ターゲットとなる処理をプロファイリング用のビルドで実行してから、その結果を基に本ビルドする方法です。ソースコードが構造化すればするほど、どのようにCPUネイティブのバイナリにするかの意図が伝えづらく、プログラムの流れ（メインパスのアセンブラコードはアドレス順に真っ直ぐでコンパクトな方がいい）が曖昧で、本筋はプロファイリングして与えなくては最適解とならなくなっていきます。

只PGOを用いただけでは、オブジェクトファイル(*.o)単位でしか最適化されません。ホットなコード・領域はできるだけコンパクトに順番にしたほうがいい(CPUキャッシュの効率化等のため)のですが、オブジェクトファイル単位でバラけては効果半減です。オブジェクトファイルを纏めて実行ファイルにリンクするときにも配置の最適化を行うのがLTOだと思っておいてください。

8.0.18くらいからでしょうか、cmake/fprofile.cmake というファイルが存在して、コメントにやりかたが記述されています。なので、以下の手順がわからなくても最新の 8.0.x ではcmakeのオプションだけでPGO+LTOビルドができます。どの程度動作サポートされているかはまだ不明なので今の所、自己責任（ちゃんと自分で十分テストして）でお願いします。

今回も 5.6 からやります。
以前のバージョンにはPGOビルドのcmakeオプションは無いので、自力でやります。
clang で PGO+LTOをするには、コンパイラのオプション
-fprofile-generate=[output dir]
を付けたビルドでターゲットの処理を実行して、
> llvm-profdata merge -output=default.profdata *.profraw
みたいにして、profdata 形式に纏めてから
-flto -fprofile-use=[output dir]
を付けてビルドします。

clangでPGOは動くのに、LTOできない場合は、llvm*-gold というパッケージが足りないのかも知れません。

---

で、計測してみました。プロファイリングありの実行は結構重いので、プロファイリング用の処理だけは本処理の800万件程度ではなく、200万件程度に減らしてます。
結構綺麗な結果が得られました。(5.7の一部がビルドできませんでしたが主旨に影響ないのでそのまま)

あくまで、今の所このINSERT INTO SELECT文に特化した最適化だけですが、現状と比較すれば5.6とも遜色ない結果が得られることがわかりました。（まだ数％遅いですが）

なので、数年前のエントリ「やはりC++はCよりも遅い？」の影響は非常に大きく、しかも、実行ファイルのその性能劣化はC++の標準規格が新しいほど劣化が大きいと疑われます。
標準規格の変遷もグラフに記述しました。各標準規格に変更後、GCCの普通のビルドの性能が5、6バージョンかけて遅くなって安定する様子がなんとなく見て取れると思います。

要するに8.0.xの開発では、ソースコードの反最適化が随時行われていたわけです。リポジトリのヒストリから解ると思いますが、バグフィクス時にも積極的に新しい方のC++規格にご丁寧に書き換えたがる人も多いようで、それも、5.7との差が広がっていくのを後押ししているのではないでしょうか。逆に5.7の性能が維持されていたのはやはり、C++標準規格を変えずに新機能追加も少なかったからではないでしょうか。

参考に8.0.37のコミュニティー版バイナリの計測も緑丸で示しています。コミュニティー版はGCCでの普通のビルドであると思われます。（少し遅いのは-DDISABLE_PSI_MEMORY=ONの差でしょう。）

性能劣化の説明としては、
8.0.xではソースコードの反最適化を進めているにも関わらず、提供されるバイナリの最適化レベルは据え置きなのでどんどん遅くなってきた。
と言えると思います。

まだ数％遅いぶんは別の原因（多分ソースコードの変更ロジック自体による）と予想しますが、今回の要素よりもかなり小さいのでこの最適化問題が解決してから踏み込むことにします。

…それにしても、もっと早く綺麗に証明できていれば…。悔やまれます。

---

私がMySQLをチューニング・ベンチマークし始めてからもうすぐ２０年経とうとしています。すべてのユーザーが高い性能のMySQLを使えるように色々足掻いてきて、本家の開発者までやらせてもらっています。以前はソースコードを改善する本家の開発者になることがベストの手段だと考えていましたが、直した性能を維持するフェーズに入ってきてソースコードの問題だけではなくなって、MySQLの性能のためのベストの役割は最早違う立場にあるのかも知れません。

すべてのユーザーが性能最適なバイナリを使えるように何ができるか今の立場から模索をスタートしていきます。

MySQL 8.0 は遅くなってきてる？何故？（１）

いろいろありますが、今後のことを考える前にまずは、バージョン8.0.xの現状を一旦整理・理解してから決めようと思います。

念を押しておきますが、このブログの「内容は個人の考えであって、所属組織とは方針が異なる」と考えてください。

MySQL内部の人は、クラウドとか最新のサーバーとかしか利用していないのかも知れず、MySQL 8.0 が日に日に遅くなっていることに気づいていない人しかいないのでしょう。しかし、数年前のローカルPCで動かすと年々動作が鈍くなっているのを感じます。マイナーバージョンアップで単スレッド性能が下がり続けるなんて商用システムではリスキーです。

証明が難しく、ずっと放置せざるを得なかったのですが、非常に重要な事柄ですので今一度、オープンになっているソースを基に分析をしてみます。

まず、測るモノサシを決めましょう。以前のエントリ「MySQLバージョンアップによるInnoDB性能劣化可能性事件簿」の「(3) Adaptive Hash Index 事件 (5.7.8〜)」でも触れましたが、

「並列性の低いバッチ処理（二次索引があるテーブル同士の"INSERT SELECT"文とか）では結構加速がかかる場合があるみたいです。」

ということで、クライアントとの処理が少なく、Adaptive Hash Index(以下AHI)の効果が高く、よりInnoDBの比重が大きいわけで、単スレッドの性能に(多分)一番敏感な処理です。

このINSERT SELECTを使って、5.6も含めて比較していきます。

まず表構成から。
主キー以外の二次索引が一意索引だとAHIを効果的に使うみたいです。（InnoDBの索引エントリ追加の手順上、AHIでスキップできる二次索引検索があるということです。）

create table t1 (
 id_1 int(11) not null auto_increment,
 id_2 varbinary(11) not null,
 id_3 varbinary(11) not null,
 primary key (id_1),
 unique key ukey (id_2,id_3)
) /*!80023 AUTOEXTEND_SIZE=64M */ engine=innodb;

※character_setやcollationのあるデータだと文字コード変換が内部で発生して、特にAHIが遅くなったりするので、5.6と差を無くするために varbinaryを使用。
※AUTOEXTEND_SIZEは、8.0.23以降ファイルサイズ拡張もredo logに残すようになって重くなったので、その頻度を減らさないと前より遅いので追加。

データですが、id_2、id_3 を、floor(rand()*1000000) みたいに乱数で埋めます。ランダムでも多少はぶつかって、一意性制約で少し弾かれますが無視。800万件くらい入れます。
このt1から、

create table t2 like t1;

なt2に対して、

insert into t2 select * from t1;

する時間を測ります。（シンプルですよね？）

オプションはこんな感じ。できるだけ5.6と差が出ないように。

--innodb_buffer_pool_size=16G
--innodb_log_file_size=4G
--innodb_log_buffer_size=512M
--innodb_flush_log_at_trx_commit=0
--skip-log-bin
--innodb_flush_method=O_DIRECT
--innodb_io_capacity=5000
--skip-innodb-doublewrite
--character_set_server=latin1
--performance_schema=OFF
--loose_innodb_log_writer_threads=OFF
--loose_innodb_stats_persistent=OFF
--loose_innodb_undo_log_truncate=OFF
--loose_innodb_redo_log_capacity=8G

測る対象は、https://github.com/mysql/mysql-server.gitで公開されているリポジトリで、各バージョンを遡ってみましょう。こうして、オープンソースなので過去のバージョンも全部見られるわけです。（古いソースのビルドを通すのは変なエラーが出て大変ですが…）

> git log --decorate | grep "tag: mysql-5.7"

とか

> git log --decorate | grep "tag: mysql-8.0"

とかで出てくるものが、そのバージョンのソースです。（抜けてるバージョンもありますが、tag:を付けてない時期があるのでしょうね。。。）

まずは、GCCで普通にビルドして測ることにします。
でも、判っていてビルド設定で回避可能な劣化は回避します。また、後に直るものも結果の平準のために遡って直してビルドします。これで他の劣化要素が解りやすく可視化されるはずです。

以下はソース・ビルドの調整内容。githubのリポジトリで説明しています。

---

(1) PSI_MEMORY が performance_schema=OFF にしても重いのを回避 (5.7.5〜)

特に、InnoDB に適用してからが重い

commit b24685d0be5251cc2a4dc91116d49d622a735844
Author: Vasil Dimov 
Date:   Wed Jun 11 13:15:48 2014 +0300

    WL#7777 Integrate PFS memory instrumentation with InnoDB

5.7.5 から重くなってます。
それ以降は全部、-DDISABLE_PSI_MEMORY=ON のcmakeオプションを利用して無効化しています。

(2) AHI が重いバグ (5.7.2〜5.7.7)

AHIが、

commit 9cce0af3a1d16252836a1b7695df37b6b4dc3fe7
Author: Marko Mäkelä 
Date:   Wed May 29 14:24:24 2013 +0300

    WL#6871 record locking cleanup.

で遅くなり、(5.7.2)

commit 00ec81a9efc1108376813f15935b52c451a268cf
Author: Marko Mäkelä 
Date:   Thu Jun 11 13:19:50 2015 +0300

    Bug#21198396 REINTRODUCE ADAPTIVE HASH INDEX FIELD PREFIXES
    TO SPEED UP SEQUENTIAL INSERTS

で直ります。(5.7.8)
5.7.2〜5.7.7 はこの修正をバックポートしてビルドしてます。

(3) log_sys が重いバグ (8.0.11〜8.0.21)

log_sysが

commit 6be2fa0bdbbadc52cc8478b52b69db02b0eaff40
Author: Paweł Olchawa 
Date:   Wed Feb 14 09:33:42 2018 +0100

    WL#10310 Redo log optimization: dedicated threads and concurrent log buffer.

で大幅更新されますが、(tag: が見える範囲でいうと 8.0.11 から)
CPUの空回りが多くなり重くなります。（スケールは良くなるのですが…）

commit 44ef2de0bcdcf9b3963388f6ed509661bbb0a890
Author: Yasufumi Kinoshita 
Date:   Fri Jul 10 11:47:21 2020 +0900

    Bug#31389135: LOG_SYS SHARDED RW-LOCK CAUSES OVER -2% REGRESSION FOR CPU BOUND OLTP_UPDATE_NON_INDEX

で直ります。(8.0.22)
※他にもBUG#28062382、BUG#28616442となってる変更も多少関係あります。
8.0.11〜8.0.21 はこれらの修正をバックポートしてビルドしてます。

(4) 軽い必要があるhash関数まで重くしたバグ

というか関数自体にも性能以外のバグがあったのでこの辺のバージョンは危険かも (8.0.36で完全に直る。)

AHIとかbuffer poolページのid引きとかまで

commit b11a175924194d574238f42068f09b15924ae2f8
Author: Marcin Babij 
Date:   Wed Apr 6 22:42:24 2022 +0200

    Bug #16739204   IMPROVE THE INNODB HASH FUNCTION
    Bug #23584861   INNODB ADAPTIVE HASH INDEX USES A BAD PARTITIONING ALGORITHM FOR THE REAL WORLD

が変えてしまい重くなる。(8.0.30)
（何故、遅くなる変更を平気でするのか。。。）
性能に影響が大きい部分は

commit 624f5847ef56c6a47e864c504d8bbc74335ba213
Author: Yasufumi Kinoshita 
Date:   Wed Dec 7 14:47:25 2022 +0900

    Bug#34870256: New hash function causes performance regression

で従来レベルの軽い関数に戻る。(8.0.34)
8.0.30〜8.0.33 はこの修正をバックポートしてビルドしてます。

---

以上ででできるだけ平らな結果が出るように工夫して計測した結果がこれです。
（データロード毎に３回実行。全体は３回で、合計９回の実行時間の平均）
（/proc/cpuinfo は、"model name      : Intel(R) Core(TM) i7-9700K CPU @ 3.60GHz" です）

…
5.7は最近のバージョンまで大体同じ性能ですが、
8.0はどんどん遅くなっていってます。
何故でしょうか？
次のエントリで解明してみようと思います。

では、また。

未定ですが今後について

MySQL-8.0リリース直後くらいから２度目の中の人になっていますが、MySQL-8.0のEOLくらいで一旦何らかの区切りをつけようと考えています。具体的なことは何も決めてないのですが。昨年後半から大病を患って自身の命の有限を実感したのも契機となりました。

性能に関しては守れたことも多いですが、守れなかったことも多いと思っています。個人的には、守ったり、新しい性能バグを直したりするだけで精一杯で、それ以上性能に踏み込むのは理解が得られず多少不完全燃焼です。会社として選択できない手段もあるのでしょう。性能に関する考え方や精度が異なるのかも知れません。他の理由もあります。

区切りをつけた後は、公開される8.0EOLのソースリポジトリをフォークして、誰にも気を使うこと無く自分の考えで、性能の再調査・解説・検証改善をしっかりして此処などで公開していこうと思っています。少なくとも１年は無収入でもそのようなことをしてみるつもりです。もちろん安全を考えて危険な変更はしません。バグも直すかも。まぁ、そうしなくては、技術的に成仏できない気がしています。この20年近くの集大成になるといいなと。

外部にも性能問題を理解してる人が昔より少なくなってしまったような気がします。単純な事柄は過去に解決済みなこともありますが、最近は多分事情が複雑なんだと推察しています。その解明も含めて、コミュニティ性能フリークの知識強化も含めて役に立つ内容になったらと願います。

とりあえず当面は、MySQLが今後も続くように中に何か残せたらと思っています。
8.0EOLが近づいたらもっと具体的にお伝えできるようにします。

今はそのような感じで。