PKをauto_incrementとUUIDにしたときのinnodb insert速度比較
innodbのprimary keyにUUIDとauto_incrementを指定した時の速度差の原因についてこちらの記事にとても分かりやすくまとまっていました
この記事では、PKをUUIDにしたときのinsert速度低下の原因として以下が挙げられています。
- UUIDがランダムなため、BTreeのページ分割頻度が高くなってしまうこと、格納効率が悪くなってしまうこと
- ランダムなUUIDに比べ、auto_incrementは新規レコード追加時にバッファプール内にキャッシュされたページがヒットする率が高いこと
勉強がてら、実際に検証環境でinsert速度を比較してみたいと思います。
検証環境
以下の検証環境を用意しました
- MySQL (docker環境)
- version: 8.0.30
- information_schema_stats_expiry: 0 (information_schemaにすぐに情報を反映させるため)
- innodb_buffer_pool_size: デフォルトの128MB
- insertするプログラム
- goで8並列にbulk insertするアプリを作成
- 1レコードにはPKとmediumtextのカラム2個、3万レコードをinsertする
- PKは以下を個別に測定
- auto_incrementなint
- ランダムなint
- uuid_v4
- ulid
検証結果
各計測結果は3回実施したものを平均しました
やはり、シーケンシャルなauto_incrementとULIDがほぼ同じぐらいの速度で、 ランダムintとUUIDはシーケンシャルなPKよりは遅くなるという結果になりました。
bulk insertのイテレーションごとに、data_lengthの推移をとってみると上図のようになりました。 data_lengthはPKのindexとページサイズで(おそらくBTreeのサイズと同じ..?)
グラフを見ると、ランダムなPK(黄色と赤色)はシーケンシャルなPK (青色と緑色)に比べ、サイズが変わる頻度が高いように見えます。
これはクラスタインデックスに対し、ランダムなページに新規レコードが割り当てられることによる、ページ分割頻度の高さが影響しているのかなという印象です。
また、バッファプールのヒット率も測定してみました。 gihyo.jp こちらの記事によると、バッファプールのヒット率は以下の式で計算できます。
(1 - ( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests )) * 100
シーケンシャルなPKのほうがヒット率は高くなるかなとおもいましたが、結果としてはrandom intがULIDよりもヒット率が高くなる結果となってしまいました。
intとULID(varchar)ではそもそもデータサイズが違うのでキャッシュヒット率に影響しているのかもしれません。 また再測定してみたいですね。
おわりに
今回の調査でinnodbやバッファプールについて勉強になりました。元記事の著者に感謝です。