調査はさらに深淵へ。。。

より詳しい説明や経緯は応援サイト

Patreonの私のページ

で有料会員向けに公開しているのですが、グラフだけ共有します。

 

どうも小手先のヒンティングでは回避できない様相です。
原因を特定するためにはLLVMの深淵に向かわなければいけなくなってきました…

MySQL RP-8.0 が現状、世界で最もCPU性能スケールが高いMySQLです！

MySQL RP (Restore Performance) として、成果をgithubで公開しながら(利用はMySQL Community Editionと同様のGPLライセンス) 個人でMySQLの性能回復を研究しています。

これまでは、第１段階として、OLTP処理で競合しそうな要素を狙ったSQLでのCPU性能スケールテストを行って、MySQL-8.0で問題のある箇所を修正してきました。RP008で目立った問題は解決したと思われるので、
第２段階として、PGOビルドなしでもシングルスレッド性能で、5.7のノーマルビルド（公平のため -DDISABLE_PSI_MEMORY=ON オプション付きビルド）を超えることを目指します。そうすれば限定句が少し取れて「世界で一番CPU性能が高いMySQL」となります。

(※MySQL-5.7のPSI_MEMORYのコードはperformance_schema=OFFでも非常に重く、5.7の性能を不当に下げています。)

< まず、お願い >

現状の比較結果を簡単に紹介しますが、その前にこの活動の持続のためのご協力のお願いです。

一つの手段は、Patreonのサポートサイトの有料会員になっていただくことです。今回の結果のもっと詳しい説明もありますし、活動の中間報告なども随時しています。RPxxxの各変更も、どの様に分析したかも説明してます。

もう一つの手段は、、、
「よくある事情」により、この活動は求職活動の一環と言う側面も暫くあるので、この活動(とういか生活…)の持続性のために、採用選考・採用面接などの実績もあると助かります。このRP開発活動との折り合いが良ければ実際副業にしますので。 buildup.db@gmail.com まで一報をお願いします。

あとは、結果が出始めたので、この活動について機を見て話題に上げて宣伝していただけると有り難いです。

よろしくお願いいたします。

 

< 現状の比較結果 >

では現状（第１段階完了）のベンチマーク結果を簡単に。

まずTPC-Cですが、ほんの少ししかベースの8.0と差がないので割愛します。

TPC-CはMySQLのOLTP性能を測る指標としては今はもう緩すぎるようです。個別の要素を調べなければなりません。

（※この20年弱、TPC-C同等処理のベンチマークをOLTP性能の指標にして性能改善を進めてきましたが、既に8.0では十分なスケールが得られているため、より厳しい指標でなければ、もう緩いTPC-Cでは性能が下がっても分からないようです。そのせいで、遅いコードを平気で書く人達が気づかなかった。これは我々開発に関わった人達のミスです。反省しています…）

というわけで、例によって Dimitriさんの BMK-kit の中から各個別要素に一番負荷をかけるSQLを選んでテストしていたわけです。

今までと同様 "Xeon(R) CPU E5-2699 v4 (22cores;44threads)" で 2ノードNUMA、
単純化してできるだけ純粋なCPUスケールを見るために、ストレージは ramfs、
REDOログの書き込み待ち設定 innodb_flush_log_at_trx_commit=0 で io wait を極力排除しています。

(まず、CPU性能を優先してるのは、誤魔化しようがないからです。IOは多少 Durability を犠牲にすれば可能なので後回し。)

比べたのは、これらのバイナリ、

• MySQL 8.4 Community Edition (mysql-8.4.5-linux-glibc2.28-x86_64.tar)

• MySQL 9.3 Community Edition (mysql-9.3.0-linux-glibc2.28-x86_64.tar)

• Percona Server 8.4 (Percona-Server-8.4.5-5-Linux.x86_64.glibc2.31.tar.gz)

• MariaDB 11 (mariadb-11.8.2-linux-systemd-x86_64.tar.gz)

あとは、私がノーマルビルド(但し -DDISABLE_PSI_MEMORY=ON)した、"MySQL-5.7.44"、"MySQL-8.0.42"、"MySQL RP-8.0[RP008]"。そして、MySQL RP-8.0[RP008] は PGOビルドも比較しています。

 

１．主キーランダム検索性能スケール

セカンダリ索引の範囲スキャンで、索引に無い値を取得するクエリは、１クエリで多数の主キーランダム検索を発生することができます。クライアント通信のラグなどを抑えて、より純粋な性能です。

BMK-kit でいうと、sb_exec/sb11-OLTP_RO_10M_8tab-uniform-s_ranges1-Rsize100-SecIDX-notrx-socket.sh 相当。

 

２．lock_sysの性能スケール

上記と同様のクエリに "LOCK IN SHARE MODE" 句を足せば、競合しないロック処理のみで lock_sys に負荷をかけられます。

 

３．log_sysの性能スケール

REDO log バッファへの書き込みは mini-transaction 単位で、それは１レコード毎です。
１クエリで多数のレコードを更新すれば、多数のmini-transactionを発生させて、
log_sys に負荷をかけられます。

BMK-kit でいうと、sb_exec/sb11-OLTP_RW_10M_8tab-uniform-upd_noidx1-upd_range100-notrx-socket.sh 相当。

 

４．purge_threadsの性能スケール

参照整合性取る必要のなくなった古いUNDOレコードを消すのがバックグラウンドのPurge処理です。innodb_max_purge_lag_delay を使えば、フォアグラウンドの処理がPurgeを置いていかなくなり、Purgeの処理性能が現れます。１クエリで多数のセカンダリ索引キー値を更新すれば、主キーレコードの更新やセカンダリキーレコードの削除・追加などで、大量のUNDOレコードを生成することになりPurge処理が増やせます。このテストは、purge_threads を変更して平衡するスループットを調べます。

BMK-kit でいうと、sb_exec/sb11-OLTP_RW_10M_8tab-uniform-upd_idx1-upd_range100-notrx-socket.sh 相当の最後の値です。

２.と３.は、１.を直したことで性能が高くなっているようです。
というわけで、
１.と４.の問題は、他のMySQLコードでは何も直していないようですので、
現状、世界で一番と言っても良いでしょう。：）

しかし低並列では、RP008ノーマルビルドは5.7ノーマルビルドの真の性能(-DDISABLE_PSI_MEMORY=ON)にまだ負けるので、それが次の課題です。

(PGOビルドはバイナリの再現性が少し良くないので、維持管理上不便なので。不便だとバグを取りこぼしたりビルドによって挙動が違う可能性があったり少し不安要素ですので。処理によっては遅くなるものもあるかも知れない。安全のためにはコード側でコントロールしたいところ。)

うまく行けば、PGOビルドよりも速くなるかも。

そして、ここからがこの活動の本番なのです。何か良い方法をこれから考えます。時間は掛かるかも知れませんが、応援してください。

第２段階のその後はIO性能とか何か劣化を取りこぼしてる処理など（有料会員優先で）募集したりします。

では、ご期待ください。

１つ目のベンチマークの性能スケールを改善しました。[MySQL RP]

 私の個人活動、MySQL RP (Restore Performance) で、１つベンチマーク結果を十分改善しました。区切りが良いので簡単に報告だけ。

 ベンチマークは、DimitriKさん ( http://dimitrik.free.fr/blog/ ) がメンテして公開している sysbench バイナリ & スクリプト BMK-kit ( http://dimitrik.free.fr/blog/posts/mysql-perf-bmk-kit.html ) でいうところの、sb11-OLTP_RO_10M_8tab-uniform-s_ranges1-Rsize100-SecIDX-notrx-socket.sh 相当で、

セカンダリ索引の１００行スキャンで、索引カラム以外のデータを返すクエリ

で、内部的に主キー検索が１００回起こる、純粋主キー検索に近い性能が出る処理です。特殊なクエリですが、実は基本的な処理のベンチマークになってるみたいです。(クライアント通信１回で100回主キー検索が発生するので)　Adaptive Hash Index も結構効きます。

 マシンは、Xeon(R) CPU E5-2699 v4 (22cores;44threads) の 2NUMA ノード構成 というレトロなのにコアが多い構成で、性能スケールは厳しい構成です。（7,8年前の超最高スペックサーバーが個人でもPC相当の価格で購入可能になりましたね） 厳しいので、ここでスケールすれば、何処に持っていってもスケール問題は出ないはず。

で、現状こんな結果です。 (X軸がスレッド数、Y軸がTPSです)

(AHI=off)

(AHI=on)

他に特別遅くなってるベンチマークがあれば見ていきます。でも参照系処理のスケールはこれでかなり直ってると思います。

より詳しい説明や、解析・修正の経緯は応援サイト
Patreonの私のページ
で有料会員向けに公開しています。

このような感じでこれからも続けていきますので宜しくおねがいします。
ブログでは、このように大きな案件の簡単な結果のみにします。

MySQL性能回復活動(MySQL RP)を独自に始めます

ようやく、MySQL性能向上開発に専念する体制が整いました。やっと開発作業に戻れます。

実質無所属なのは人生初なので、各種社会保障関係の手続きとか、どのサイトを使って組み合わせるか、どういう設定でアカウントをつくるかなどなど、開発環境も含めて全部自分で決めなければならず、結構時間を要してしまいました。すみません。

結果、本家在籍時と同様の使い勝手で自由に開発できるようになりました。

名前は、『MySQL RP (Restore Performance)』 としました。 ソースコードはMySQLのオープンソースライセンス(GPLv2ベース)で提供します。最初は劣化がもう増えない、劣化が少ないと思われる 8.0 からforkして直していきます。まだ空っぽですが、数日中に更新が増えていきます。
バイナリ提供は余裕が出てから考えます。まだ無理です。既存の互換バイナリ提供者がpullしてくれるようになれば、それもいいかなと思います。

フルタイムで独自活動を続けていくために、支援者も募集します。
buildup-db と言う名前で Patreon に登録しました。(※)
MySQL RP の性能改善修正毎に、ベンチマーク結果、問題解析、ソース変更点の説明を有料会員向けに行っていきます。内容に興味のある方、最新コードで性能向上を体験できた方は、ご支援ください。

長年、影に隠れてきましたが遂に一人で細々と矢面に立つ羽目になりました。
ご期待に応えられるよう善処します。
よろしくおねがいします。

 

※6月4日現在、Patreonのアカウントにトラブルがあったようです。問い合わせ中です。Patreon側がアカウントの内容をレビュー中なのかも知れません。しばらくお待ちください。 

※6月17日現在、Patreonのアカウントが復活しました。github側で進んだ分の解説をしていきます。

今日から『自由』にMySQLの性能改善始めます。（MySQL開発チームを退職しました）

前回の在籍も含めると、累計9年半、本家MySQLチームでInnoDBの性能改善をターゲットに開発の仕事してきました。5.7でのb-tree index scaleや、8.0の初期の新機能で入ってしまった性能問題の修正など貢献できましたが、ここ数年は開発が進む度に導入される性能劣化に追いつけなくなってしまいました。悪いコードを見つけて直そうとしても抵抗が大きいのも大きな要因です。（遅くしたいのでしょうか？遅いことが認識できないのでしょうか？）

この度、体制変更で退職を勧められたのを機に、別の営みでMySQL/InnoDBの性能を改善していくことにしました。どうせ、性能劣化に(私よりも)無頓着な現体制では私が性能を改善することは困難なので、成果は出ないでしょう。（直しても、同時に導入される新機能・修正による劣化に改善分を喰いつぶされることもしばしばあった、と考えています。キリがありません。そもそも、性能に悪い新機能を通すための性能改善回復ではないのに…）
8.0以降の性能に最も満足していないのは私自身です。このままでは終われません。

というわけで、
これは「終わり」ではなく「始まり」です。

これからは、8.0の性能回復を個人で、できればフルタイムでコツコツやっていきます。支援が生活持続上十分に集まるようなら、そのままセキュリティ問題修正なども続けて、延長サポート終了後も8.0性能修正版を使えるようにメンテ・性能改善していきます。(8.0の修正が終了して且つ8.4も相当性能に直せたら8.4への移行も検討します)

会社やチームに気を遣って表現・説明を抑えることも、
理解できない人に際限なく説明して許可を求める必要も、
悪いコードを認めたくない作者に邪魔されることも、
公開の別ブランチならばもうないのです！(あったとは明言はしない。一応。)
なので、本家在籍中よりもスムーズに直していけると思います。（思えば、会社には性能改善に役立つものは何もなく、もう障害・妨害しかないと感じていました。）

体制（各種アカウント開設等）は近日中に整えます。本編は日本語英語併記でやろうと思ってます。
当面は、
github上にブランチを作成して公開。(GPL MySQLなので GPL)
支援者のみに、
ベンチマーク解析内容・修正内容解説を随時公開（これが本編に当たる）
していきます。

改善点がある程度落ち着いてきてから（当初は何も受け付けられませんが）、
いずれ個別ベンチマークの処理改善要望も募っていこうと思います。

乞うご期待です。

* なんで8.0かというと、「性能改善」が多く「汚染」はまだ少なく、EOL間際なので今後も「汚染」は増えづらいから、です。まずは、ここから直して、徐々に進んでいきます。

* 量子コンピュータとか技術革新で暗号通信が安全でなくなったら基幹はオンプレ回帰するはずで、そのときにクラウドでしかまともに動かないというのは不味いと思います。

* そもそも既存の古いオンプレでスケールしなくなったら、クラウドでもスケールしなくなると思うのですが、何を見て開発してるのか。。。

MySQL 8.0 の速いバイナリを作ってみよう

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

前のエントリでは、MySQL 8.0は、clangのPGO+LTOでビルドしないと本来の性能が出ない。ということを証明しました。その後、PGO+LTOといってもプロファイリングをどうしたらいいのかと、デスクトップマシンの空き時間でひたすらビルドとtpcc(ramfs)を繰り返した結果、興味深いことがわかりました。

tpccのようなある程度複雑なベンチマークは、
ベンチマークそのもの（この場合tpcc）をプロファイリングするよりも、
mysql-testのスクリプトを組み合わせて工夫したほうが性能が出る
ということです。（少なくとも私の環境で、ではですが）

つまり、
ビルドしてテストスクリプトが流せる環境であれば、総合的に最適に近いバイナリが生成できるということです。誰でもビルドできます。多分。しかも、公開ソースツリーだけでです。
（何故本家がそういう最適化バイナリを配布しないかもよくわからないですね。配布版が遅いビルドだとMySQL自体のプレゼンスが下がると思うのですが…）

このまま放って置いても8.0のCommunity Editionの最速バイナリが公式提供されるか怪しいので、それに近いものを作るための手順を公開しようと思います。大人の事情で自分ビルドを直接使えない人も話題に上げることで公式提供が早まるかもしれないので、色々試して皆で話題にしてみたり、要望してみたりしても…

※
clang PGO+LTO ビルドのテストもしてみましたが（私の環境での）唯一の違いは、ソース中の"__FILE__"シンボルの展開にパスが含まれない（ファイル名のみ）ことです。何が起こったかというと、performance_schema.error_log の subsystem列 で"Repl"となるべきものが"__FILE__"のパース(コンパイル時)違いで"Server"になってしまうエラー出力がある。という程度でした。本質的な問題にはならなそうです。（エラー出力のsubsystem判断の一部はパスの区切り文字が無いと、__FILE__からのbasename抽出ができないみたい。バグとして報告済みなのでいつか治るでしょう…）

最適に近いビルドの手順を説明する前に、練習としてまず、clangで普通のビルドをどうするかの説明をします。その中で、どのようなオプションを使うか決めてください。それを踏まえて、（現状私の環境で暫定ベストの）最適化ビルドの説明をします。今回はLinux(x86_64) clang環境だけで、それ以外の環境では事情が異なるかもしれませんが似た結論になると予想します。

---

練習: clangでノーマルビルド

まず練習として、用途に必要な機能を含むように普通のビルドをclangでできるようにしましょう。汎用的にするために、できるだけ Community Edition の配布バイナリと機能同等なビルド想定からスタートします。
githubの8.0ブランチのルートディレクトリをカレントに始めます。

#clang でビルドできるようにします。一応私の環境は clang13 です。
export CC=clang
export CXX=clang++

#とりあえず、64bitアーキテクチャ汎用で。お好みでアーキテクチャ限定してみても速くなるかも。
export CFLAGS="-O2 -g -pipe -m64 -mtune=generic"
export CXXFLAGS="-O2 -g -pipe -m64 -mtune=generic"

#私の場合です。git cleanとかでどうせ全部消せるので、ビルドを整理して分けたりしません。
#次の本番のやり方にも多少影響するのでとりあえすこれで。
cmake . -DFORCE_INSOURCE_BUILD=1 \
        -DBUILD_CONFIG=mysql_release \
        -DINSTALL_LAYOUT=STANDALONE \
        -DFEATURE_SET=community \
        -DPLATFORM=linux-custom \
        -DWITH_ROUTER=OFF \
        -DWITH_AUTHENTICATION_LDAP=ON \
        -DWITH_AUTHENTICATION_FIDO=ON \
        -DWITH_AUTHENTICATION_KERBEROS=ON \
        -DWITH_CURL=system \
        -DWITH_TIRPC=bundled \
        -DWITH_NUMA=ON \
        -DWITH_BOOST=~/boost_1_77_0 \
        -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0 \
        -DMYSQL_UNIX_ADDR=/opt/mysql-8.0/mysql.sock

-DWITH_BOOST=~/boost_1_77_0

必要なバージョンのboostのソースを展開して指定してください。ビルドはしなくていいです。

-DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0

インストール先を指定します。この場合省略すると /usr/local/mysql

-DMYSQL_UNIX_ADDR=/opt/mysql-8.0/mysql.sock

デフォルトUNIXソケットファイルを指定します。この場合省略すると /tmp/mysql.sock

-DWITH_MECAB=</path/to/custom/mecab>

Community Edition では含まれるのですが、私の環境ではmecabの配布パッケージがないので省略しました。利用する場合は、lib/libmecab.a とか、include/mecab.h の存在するパスを指定してください。無い場合はビルドします。ipadicも置いておきます。多分。

必要なライブラリがインストールされていない場合はエラーで止まりますので、
入れるか、オプションを外すかしていきます。
このcmakeオプションが次の本番のベースになります。続けて…

#WITH_TIRPC=bundled でビルド途中で怒られないように、シンボリックリンクを作っておきます。
#（私の環境では必要ですが、要らない環境もあると思います。）
(cd tirpc; ln -s lib64 lib)

#ここまでエラーなく来ればビルドはできるはずです。
#※エラーが起きたら確認できるように一応 VERBOSE=1 を付けておきますが無くてもいいです。
#※"8"は私の環境でのCPU数です。並列数は環境に合わせて。
make -j8 VERBOSE=1

途中でなにかあったら解決してください。。。
因みに、100%まで終わった状態で
make install すれば使えますし、
make package で .tar.gz にパッケージできます。

最後に、次で必要となるので、mysql-testが動くようにしてください。
とりあえず、

(cd mysql-test; ./mtr innodb.innodb)

が動くようならOK。本番の準備はできています。プロファイリングで使います。
perlが入っていれば動くはずですが、何か必要なモジュールがあったかもしれません。 

次は、この環境で最適化バイナリをビルドしてみましょう。
ちなみに、clangが利用するllvmXXに対応する、llvmXX-goldと言うパッケージが必要になるので、環境に入れておいてください。

---

本番: clang で PGO+LTO ビルドして最適に近いバイナリを作る

cmakeのオプションは練習のものベースで。指定の追加ぶん以外は変えないようにしましょう。
※幾つかある"8"は私の環境でのCPU数です。並列数指定は環境に合わせて。

#練習と同様
export CC=clang
export CXX=clang++
export CFLAGS="-O2 -g -pipe -m64 -mtune=generic"
export CXXFLAGS="-O2 -g -pipe -m64 -mtune=generic"

#一応一旦、練習時のファイルは全部消したほうがいいかも。
(git clean -xfd)

#練習と同様のものに -DFPROFILE_GENERATE=1 を足します。
cmake . -DFORCE_INSOURCE_BUILD=1 \
        -DBUILD_CONFIG=mysql_release \
        -DINSTALL_LAYOUT=STANDALONE \
        -DFEATURE_SET=community \
        -DPLATFORM=linux-custom \
        -DWITH_ROUTER=OFF \
        -DWITH_AUTHENTICATION_LDAP=ON \
        -DWITH_AUTHENTICATION_FIDO=ON \
        -DWITH_AUTHENTICATION_KERBEROS=ON \
        -DWITH_CURL=system \
        -DWITH_TIRPC=bundled \
        -DWITH_NUMA=ON \
        -DWITH_BOOST=~/boost_1_77_0 \
        -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0 \
        -DMYSQL_UNIX_ADDR=/opt/mysql-8.0/mysql.sock \
        -DFPROFILE_GENERATE=1 -DDISABLE_PSI_MEMORY=ON -DWITH_UNIT_TESTS=OFF

-DFPROFILE_GENERATE=1

ビルドされたものを実行すると、（clangの場合）../profile-data/ に *.profraw の形式でプロファイル結果が残るようになります。カレントでビルドしてるので、git管理下の外にできます。(個人的に便利と思っているので)

-DDISABLE_PSI_MEMORY=ON

performance_schema でメモリ確保のカウントができなくなります。performance_schema=OFF でも何故か重いので外します。5.7.xでは、OFFでも10%程度重いのが、8.0.xでは2%程度に減っていますが、意味がないので性能重視なら不要です。mysql-testの中でデバッグ目的で使われているために、いくつかテストが通らなくなったりします。どうせmallocの合計は実際の消費量からは目安程度の意味しか無く、8.0.xでは色々欠けているので実用性は無いと思うのですが、mysql-testが通らないと気持ち悪い人や、それでもこの機能が必要な人は、このオプションを外してもいいです。

-DWITH_UNIT_TESTS=OFF

余計なプロファイルデータが混じらないように、使わないものは一応外しておきます。

先ほどと同様、プロファイル用のビルドをします。

#WITH_TIRPC=bundled でビルド途中で怒られないように、シンボリックリンクを作っておきます。
#（私の環境では必要ですが、要らない環境もあると思います。）
(cd tirpc; ln -s lib64 lib)

#ここまでエラーなく来ればビルドはできるはずです。
#※エラーが起きたら確認できるように一応 VERBOSE=1 を付けておきますが無くてもいいです。
#※"8"は私の環境でのCPU数です。並列数は環境に合わせて。
make -j8 VERBOSE=1

ちゃんとビルドできたら mysql-test の処理を流してプロファイリングします。

#../profile-data/ に ビルド中の実行のものもできてしまうので、気になるので一旦消します。
rm ../profile-data/*.profraw

#テスト自体の結果は関係ありません。プロファイリング処理があるせいで幾つか失敗しますが、強制で全部流してます。
#suite はこの組み合わせが（私の環境ですが）現時点での汎用暫定ベストです。
(cd mysql-test ; ./mtr --accept-test-fail --clean-vardir --force --max-test-fail=0 --mem --mysqld=--binlog-format=row --parallel=8 --retry=0 --skip-rpl --suite=binlog,collations,connection_control,encryption,gcol,gis,innodb,innodb_fts,innodb_gis,innodb_undo,innodb_zip,jp,json,main,sysschema,x)

#できた ../profile-data/*.profraw を利用できるように纏めます。
#偶にエラーが出る .profraw が混ざりますが、それを消すか、全部消してmysql-testをやり直すかします。
(cd ../profile-data ;llvm-profdata merge -output=default.profdata .)

PGO+LTO でビルドします。まずはcmake。

# 中途半端に前の設定が残らないようにキャッシュを消す。（重要）
rm CMakeCache.txt

#練習と同様のものに今度は -DFPROFILE_USE=1 を足します。
cmake . -DFORCE_INSOURCE_BUILD=1 \
        -DBUILD_CONFIG=mysql_release \
        -DINSTALL_LAYOUT=STANDALONE \
        -DFEATURE_SET=community \
        -DPLATFORM=linux-custom \
        -DWITH_ROUTER=OFF \
        -DWITH_AUTHENTICATION_LDAP=ON \
        -DWITH_AUTHENTICATION_FIDO=ON \
        -DWITH_AUTHENTICATION_KERBEROS=ON \
        -DWITH_CURL=system \
        -DWITH_TIRPC=bundled \
        -DWITH_NUMA=ON \
        -DWITH_BOOST=~/boost_1_77_0 \
        -DCMAKE_INSTALL_PREFIX=/opt/mysql-8.0 \
        -DMYSQL_UNIX_ADDR=/opt/mysql-8.0/mysql.sock \
        -DFPROFILE_USE=1 -DDISABLE_PSI_MEMORY=ON -DWITH_UNIT_TESTS=OFF

-DFPROFILE_USE=1

（clangの場合）../profile-data/default.profdata を利用して PGO(+LTO) ビルドします。

-DDISABLE_PSI_MEMORY=ON

前の -DFPROFILE_GENERATE=1 のものと合わせます。

-DWITH_UNIT_TESTS=OFF

前の -DFPROFILE_GENERATE=1 のものと合わせます。

cmake の結果、LTOがちゃんと使われるか確認します。駄目な場合は、なんとか解決してください。(私の場合は llvmXX-goldパッケージが無いことでLTOがcmake中に変なエラーで落ちてました)

grep WITH_LTO CMakeCache.txt
#こんな出力になるはず
# WITH_LTO:BOOL=ON
# WITH_LTO_DEFAULT:INTERNAL=ON

先ほどと同様、最適バイナリをビルドします。

#WITH_TIRPC=bundled でビルド途中で怒られないように、シンボリックリンクを作っておきます。
#（私の環境では必要ですが、要らない環境もあると思います。）
(cd tirpc; ln -s lib64 lib)

#ここまでエラーなく来ればビルドはできるはずです。
#※エラーが起きたら確認できるように一応 VERBOSE=1 を付けておきますが無くてもいいです。
#※"8"は私の環境でのCPU数です。並列数は環境に合わせて。
make -j8 VERBOSE=1

途中でなにかあったら解決してください。。。

因みに、100%まで終わった状態で
make -j8 install すれば使えますし、
make -j8 package で .tar.gz にパッケージできます。

※プロファイリング絡みのmakeは、整合性のためか意図的に全部ビルドし直しになるので -j オプションで並列指定してください。。。（これが、練習で一回ビルドしてもらった理由です。練習で一回通したほうがやり直しが少なくスムーズかと。）

それでは、8.0 本来の性能をぜひ享受してください！
（もしかしたら、5.7配布バイナリや、8.0EE版バイナリより速いかも。）

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するスレッドがいくつかあります。

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

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

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

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

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

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 なんてオプションを付けて誤魔化しているようです。
見ていきましょう。。。

ポイントは２つあります。

１．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になってたかも知れません。

２．同じ領域内で激しい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でほぼ解決てる（というか別の競合に先にぶつかる）筈と思っているのですが…どうでしょうか？

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

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

復帰しました。

今月から中の人に復帰しました。よろしくお願いします。

弊害として、このブログの更新が減ると思われます。こちらもよろしくお願いします。

前職は丸４年在籍しましたが、技術的にも社会的にもビジネス的にもその会社でなければできないことはもう無くなったと感じて次の道を考え始めていたところ、復帰のお誘いがありましたので受けました。

ここ数年、データベース以外の分野でのトライアルで培った技術は何らかの形でMySQL/InnoDBの将来のために役立っていくと思います。が、ずっと未来の話で皆さん忘れた頃かもしれないです…

Pythonを突き詰めたらPerlを使ってた話

あ…ありのまま 今　起こった事を話すぜ！

「自作Pythonモジュールを使うスクリプトの性能を突き詰めていたと
思ったら　いつのまにかPerlを使っていた」

な…　何を言っているのか　わからねーと思うが
恐ろしいものの片鱗を　味わったぜ…

#!/usr/bin/perl -e$_=$ARGV[0];$ENV{'LD_PRELOAD'}=s|[^/]+$|hogehoge.so|r;exec('python',@ARGV)
import sys
import os
print sys.argv
print os.environ['LD_PRELOAD']

LD_PRELOADで、同じ場所にあるhogehoge.soをpython起動時に直接ぽく読み込みたかった（Cythonからpxdの設定だけで直で呼びたい関数があった）わけです。

shebangは、引数1個までで128バイト以下じゃないといけないのか。初めて知りました^^;。(Linuxだけか？) awkだと引数を再連結して全部拾い直すと128バイトに収まらない…無念。(引数なしなら可能)

#!/usr/bin/awk BEGIN{a=ARGV[1];sub(/[^/]+$/,"hogehoge.so",a);system("LD_PRELOAD="a"\tpython\t"ARGV[1])}

ワンライナー系の課題はPerlが強くてコンパクトなのですね…
私の技量の問題なのかな…

Webサイトの導線について 〜大量のコンテンツを興味ある人に効率よく届けるために〜

今回のエントリは、今までと同じことを別の視点から書くことになってるだけかもしれないけど、今まで良く理解できなかった人にも分かりやすくなっていると幸いです。

社内社外問わず、どうも問題意識がサービスの提供側ほど漠然として希薄な印象なので、確認の為にメモを残しておきます。

※以下はあくまで私見であり、所属組織や他のメンバーとは関係ありませんが、一般論として十分な説明となっているかとは思います。

AI系技術は、ちゃんと適用分野の深い理解がないと最適に利用することは難しそうな例でもあります。

＜Webサイトの導線基本構造＞

現在の一般的なWeb上のコンテンツ閲覧と、実世界、例えば実店舗で商品を見て回ることで、Web側がが劣ることは結構ある。現物が見られないことは永遠に超えられなさそうな壁ではあるけども、

「広範囲の俯瞰を伴う自由な導線」

これが、Webサイト全般に欠けているものかと思う。訪問者は正に「木を見て森を見ず」の状態を常に強いられるわけです。（私はこれが嫌で、あてのないWebブラウジングはしないのです。）

新着順、人気順など、決まった順番を決めつつ、カテゴリ、価格帯など、検索条件で絞って出力を見る。（まぁ、SQLのSELECT文でORDER BYとWHEREの調整をすることと一緒だが。）

※キーワード検索なども多少自由になった検索条件程度。文字情報が少ないコンテンツの検索の助けにはあまりならない。

さらに個々のコンテンツ同士は、各種「売り上げランキング」「個人クリック履歴」「この商品を買った人はこんな商品も買っています」等のレコメンド等の複数のリストで参照し合う。（私は他人の買っていないものを欲しがるので、どれもクリックした記憶は殆ど無い…）

加えて、各種サーチエンジンから直接個々のコンテンツにショートカットしたりもする。

これが、一般的なWebサイトの導線基本構造かと思います。

＜Webサイトの収納力が産む弊害＞

さて、ここで問題になるのは、物理空間配置に縛られないWebサイト特有の膨大な収容能力が産む弊害です。

コンテンツが数万件程度なら、ソートとカテゴリ等既存の検索条件（よくある左ペイン）だけで、例えば全検索結果の先頭の数１００件で殆どのコンテンツへの導線を網羅できているかも知れませんが、コンテンツ数が、その１０倍、１００倍、それ以上となったらどうでしょう？

極端な話、コンテンツが数千万件ある場合には、「よくある左ペイン」だけでは殆どのコンテンツが誰も辿りつけない「死にコンテンツ」となってしまいます。

というわけで、コンテンツが多ければ多いほど、個々のコンテンツページにある、ランキングやレコメンド等のリスト表示に期待される導線拡張の役割が大きくなるわけですが…

単純に考えればお分かりの通り、まず、明らかにランキングは「導線拡張」には寄与しません。余程の外的要因が無い限り、左ペインから辿り着ける（ことのあった）コンテンツのみが残っています。このランキング表示に残っていることが原因でクリックされたりしてランキングが維持されたりもします。過去の人気コンテンツへの導線維持の機能しか果たしません。

では、通常のレコメンドリストがその役割を果たせるかというと…
難しいと言わざるを得ません。

レコメンドリスト生成には様々な手法があり、個人のクリック履歴からランダムに出力するだけの単純なものから、各コンテンツや各閲覧端末の関係を有向グラフ状に集計してそれを元に表示するような難しい物までコンテキストの深さは様々です。

しかし、誰かが訪問していないものは表示できない（※）ことに変わりはなく、導線視点で言うと、
ある時期に存在して利用された導線の維持の機能がやはり基本になります。

（※「表示できない」というよりは、当てずっぽうになってしまうのでレコメンドとしての「精度が他の表示より)格段に落ちる」という方が正確かも。）

ここまで説明した導線だけでは、

「登録時に新着リストに載った時期にあまりクリックされないと死にコンテンツ確定」

を避けることができません。

※なので、サイトによっては幾つも同じコンテンツを定期的に新規登録したりとか、意味不明なことが行われたり。。。

導線拡張だけに特化して考えると、単純にランダム表示したり、履歴の無いコンテンツまで雑な類推で出力しても、

「なぜ今見てるコンテンツに対してこれらのコンテンツリストが出力されているのか？」

という繋がりが論理的にも直感的にも希薄であれば、訪問者が中身を見る確率・クリックする確率はそれだけ低くなってしまうと考えられます。（例：「おすすめ」というタイトルでランダム表示）

＜AI系技術による導線拡張＞

ここで逆に考えます。

「なぜ今見てるコンテンツに対してこれらのコンテンツリストが出力されているのか？」

がある程度、訪問者とコンセンサスが取れれば、ランダムより相当見てもらえるのではないでしょうか。

例えば、

「人気/売り上げランキング」

「この商品を買った人はこんな商品も買っています」

等は、その並び方自体に情報があり、あまり興味がなくてもそのタイトルの下のリストの先頭が見える頃には、頭の中はその情報を受け取る準備ができていて、先頭の何個かを見て「ふ〜ん」という程度には脳まで届くと思います。興味があればスクロールしても貰えるでしょう。

しかし「おすすめ」というランダムの場合には、「おすすめ」というタイトルで、（どういう観点だろう？）
と最初は待ち構えますが、ランダムだ(もしくは、意味がわからない並び)と判ったら、基本スキップされるのではないかと予想されます。ニーズに偶然フィットしてクリックされることはあるかも知れませんが、導線の利用率は低いと思います。

つまり、超多数のコンテンツ間の十分な導線確保のためには、

• 互いに並びが似ていないリストで
• 見ただけで並び方がタイトルに合っているか確認可能なものを
• できるだけ多種類

表示することが有効と考えられます。

「見ただけで並び方がタイトルに合っているか確認可能」 としたのは、複雑すぎるのもランダム（無意味）と取られるからです。各リストのタイトルも重要と思われます。

という文脈でいうと、
レコメンドと称して履歴から何らかの範囲のランキングを表示するいう枠組みだけでは、複雑な方法でも異なるリストをそう何個も生成できない（＆タイトルを決められない）ので、別の観点のリスト生成法があると（全コンテンツに行き渡るような）導線の拡張にとても有効です。

そこで、AI系技術による何らかの特徴量抽出＆類似性ランキングで整理することにより、お手軽に導線拡張ができます。例えば、画像を何らかのベクトルに変換するものを使ってトップ画像の類似性ランキングを各コンテンツ基準で生成して「似た画像の商品」みたいなタイトルを付けて表示するだけで、並びにある程度の納得性があれば、意外と見てもらえると思います。

違う学習済みモデルでも同じようにやってみて、その数種の画像類似リストの観点にそれぞれふさわしいタイトルをつけて差別化することができれば、それだけで導線がかなり増やせるわけです。

画像に内容の特徴がそれほど現れないテキストメインのコンテンツでも、リストに並べたタイトル等に納得性があれば、「似た内容の記事」みたいなタイトルでリスト表示しても良いと思いますが、画像ほどの直感的説得力は無いかも知れません。

下手に混合するよりも、単純化して納得できるタイトルのリストに分離したほうが訪問者に伝わりやすいでしょう。

※余談
と言う状況ですので、コンテンツが非常に多いサイトの場合には、導線拡張の効果（コスパ？）は「似た画像」リストのほうが「レコメンド」リストよりも高くなると考えられ、導入の優先順位はその特徴量類似ランキングの方が高くなるでしょう。このように導線拡張を既に他手法で行った状態をベースに追加でレコメンドの効果・導入コストが語られるようになると、現状のレコメンド手法選びは大きく変わる可能性があるので注意が必要です。（別路線の導線拡張にそれでも特化するか、追加導線拡張を諦めて純粋なレコメンドに特化するか。。。いずれにせよ、似た画像軸という落とし所を奪われた状態でのレコメンドリスト生成のハードルはかなり上がるのではないかと。）

そして、私がこの記事を書いている現在在席しているサイジニアでは、上記のレコメンドリストの提供サービスを事業の一つとしています。加えて、画像の特徴量ベースにしたリスト毎日生成もサービスラインナップに加えているので、自分では特徴量類似性ベースのリストをですぐには試せないサイトさんは試験導入してみても良いかも知れません。

＜その先のコンテンツブラウズ導線の模索＞

前述の余談にあるような、

直感的納得度の高い特徴量空間を持つAI系の学習済みモデルが一般的に利用されることによる、Webサイト周りの関連事業の技術再編可能性

については私は専門じゃないので語れませんが、コンテンツブラウズへのAI系技術利用を既存の「タイトル付きリスト羅列」からもう少し深めるトライアルは行っています。

つまり、冒頭で述べた、
「広範囲の俯瞰を伴う自由な導線」
の一部的でも、この特徴量空間を利用して上手く提供できないかというものです。

画像で旅する https://trip.deqwas.net
というサイトを立ち上げています。

とりあえずは、リストの後半ほど意図的により遠くの特徴的なコンテンツをピックアップすることで、少しでも俯瞰範囲を広めようとしています。

この興味深い使い心地を言語化するのが今度は難しくなってしまうのですが、真似してやってみるでもいいので、新しいインターフェースの可能性を拡散して皆で議論していきたいです。

Webサイトに限らず、個人の画像ライブラリをデータベース化して閲覧するツールなんか作ってみて公開するほうが分かりやすいでしょうか？

いつか時間ができたら…
DBの一般機能として超多次元ベクトルが扱えるようになったら、サンプルとして作ってみるとか？

ではまた。

groonga-httpdで公開用全文検索WebAPI突貫工事

今回は急に細かい小さい話です…

 groongaには、groonga-httpd という nginx と融合したプログラムがあり、 デフォルトではhttpでフルアクセスで管理からできるようになっていますが、 検索部分だけを外出しするのは簡単です。

 /etc/groonga/httpd/groonga-httpd.conf
を

@@ -51,18 +51,37 @@
   # You must specify base path on memory file system for performance.
   # groonga_cache_base_path /dev/shm/groonga-httpd-cache;

+  server_tokens off;
+
+  #検索部分だけ公開するエントリ
+  server {
+    listen 50054;
+    server_name _;
+
+    # Only select command
+    location / {
+      if ($arg_q = "") {
+        return 404;
+      }
+
+      add_header Access-Control-Allow-Origin *;
+      proxy_pass http://127.0.0.1:10041/d/select?table=Item&match_columns=Terms.texts&limit=100&output_columns=_key,brand,iname,img&sort_keys=-_score&query=$arg_q;
+    }
+  }
+
+  #ここから元のエントリ（非公開ポート）
   server {
     listen 10041;
-    server_name localhost;
+    server_name localhost 127.0.0.1;

     location /d/ {
       groonga on;
....

という風にして「?q=[検索クエリ]」のURIオプションを受け入れるようにできます。

嵌ったのは、proxy_passに変数が入ってると、「localhost」の名前解決が上手く行かないので直接IPアドレスを使わなければいけない点だけでした。(nginxの何か固有の仕様？)

というわけで、とりあえず

「画像で旅する」

に全文検索機能を付けてみたメモでした。
え、mroonga？ 出力時に必要な項目も一緒に入れてしまったらMySQLまで使う必要が無…