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はどんどん遅くなっていってます。
何故でしょうか？
次のエントリで解明してみようと思います。

では、また。