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が近づいたらもっと具体的にお伝えできるようにします。

今はそのような感じで。

ディスクフルは危険かも

(7/3追記)以下の話題に加えて色々見つかったものも含めてディスクフル関連の問題は内部開発ツリーではとりあえず全部修正済みにできたので次かその次のリリースでは全部直ってると思います。(5.7,8.0共に)

今回は性能の話ではありません。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で直る予定） 万が一そこでクラッシュすると、リカバリでデータが壊れる可能性があるので。。。

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までも再現できずにデータが壊れる可能性があります。

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が多い処理なのだから普通あるだろ）、テーブルがある程度正規化されていて常識的な個数の場合は、テーブル１個あたりファイルサイズが最低64Mになっても困らないですよね？

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

ALTER TABLE xxx AUTOEXTEND_SIZE=64M;

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

/*!80023 AUTOEXTEND_SIZE=64M */

を足しておきましょう。

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

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

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

どんどん遡ります。

(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"のバイナリが欲しい」と言えば作ってもらえるかも？（いや、作ってもらえないかも。無責任には断定できないですが。）

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

 

以上です

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

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

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

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

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

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

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

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

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

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

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

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

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