2022-12-29

innodb_buffer_pool_sizeの違いによるアクセス速度の変化

MySQLのinnodb_buffer_pool_sizeはインデックスやテーブルデータのインメモリキャッシュサイズであり、かなりパフォーマンスに影響するパラメータとして知られています。

RDSなんかだと、デフォルトでDBメモリサイズの 3/4になるっぽいのですが、MySQLを自前で構築すると、デフォルトとして128MBになってしまうので、ぜひパラメータを調整したいところです。

今回は先日作った検証環境上でinnodb_buffer_poolサイズを変えながらリクエストをさばく速度に差が出るのかを計測してみたいと思います。

計測条件

システム構成
- nginx + rails + mysqlをdocker compose上に構築し、k6からリクエスト
- DB
  - メモリ1GB
  - 10万レコードのテーブルと30万レコードのテーブルをrailsからselect
計測指標
- avg http req duration P90 [s]: リクエストの応答にかかった時間の平均の90パーセンタイル
- http req per sec [count]: 秒間リクエスト数
動かすパラメータ
- innodb_buffer_pool_sizeを 128MB (デフォ)、800MB (今回のメモリの80%)、2GB (今回のメモリの200%) それぞれ計測

結果

avg http req duration P90 [s]は 800MB < 2GB < 128MB という結果となりました。(少ないほどよい)

http req per sec [count]は 800MB > 2GB > 128MB という結果となりました。(大きいほど良い)

innodb_buffer_pool_sizeはメモリの80%がよいとされているのですが、予想通りの結果となりました。

buffer_pool_sizeがRAMの量をオーバー(今回計測の2GB)の場合、スワップ領域を使うため、スワップインアウトにコストがかかるため遅くなるとのことです。

2022-12-23

webパフォーマンスチューニング実験環境を作る

2023年はWebパフォーマンスチューニングを趣味の一環としてやっていきたい気持ちがあって実験環境を作りたくなったので作りました。

nginx、rails、redis、mysqlというまあまあ普通の構成で、すべてdocker compose上のコンテナとして作りました。

Webパフォーマンスチューニングをするにあたって、性能を計測するツールが必要になります。 httpの負荷試験ツールとしてはapache benchとかjmeterとか、自作とかいろいろあります。

負荷試験をするにあたって、やはり実際のワークロードに従ったアクセスを行いたいという思いがあります。

たとえばECサイトなら、ログイン、商品閲覧、カートイン、購入、ログアウトなどです。

しかしこのようなステートを持ったアクセスを負荷試験環境で行うには、そこそこの自由度を持ったシナリオを書く必要があります。

そこで、k6 というツールを使用することにしました。

これはJavaScriptでシナリオを記述できるため、プログラマフレンドリーで、自由度の高いシナリオを記述できそうです。

実際、以下のような単純な処理でhttp getとresponseのステータス検証ができました。

const res_get_items = http.get(`${TARGET_BASE_URL}/items`, {
});
    
check(res_get_items, {
  'is status 200': (r) => r.status === 200,
});

特にrailsはtemplate viewに記述されているCSRF tokenをpost時のheaderとpayloadに指定しなければCSRF検証エラーになってしまうので、htmlからtokenを抜き出す必要があるのですが、JavaScriptで正規表現を使って楽にtokenを抽出できました。

k6を入れたコンテナをdocker compose上に構築し、httpリクエストをnginxに向かって実行するようにしました。

計測してみる

せっかくなのでN+1問題の計測をしてみようと思います。

商品テーブル(Item)に、1対多で商品画像(ItemImage)、1対1でカテゴリ(ItemCategory)が紐づいているテーブルを作ります。

そして、商品一覧を表示するページを作ります。 1ページには500個の商品を表示し、1商品に対し、商品画像とカテゴリ名を出す必要があります。

各商品に対し、紐づく画像とカテゴリ名をロードしないといけないので、N+1問題が発生します。

通常、N+1問題に対してはeager loadという対策が有効です。今回はeager load ありとなしで計測してみました。

実際にk6で計測してみた結果、以下のようになりました。

vus(並列数?)は100で、duration(計測時間)は10sとしました。

http_req_duration avgは各リクエストの平均応答時間を表し、eager loadありのほうが11sほど速くなりました。

http_reqs は秒間リクエスト数を表していて、eager loadありのほうがおおよそ8倍のリクエストをさばけました。

また何か計測してみたいですね。

2022-12-04

PKをauto_incrementとUUIDにしたときのinnodb insert速度比較

innodbのprimary keyにUUIDとauto_incrementを指定した時の速度差の原因についてこちらの記事にとても分かりやすくまとまっていました

techblog.raccoon.ne.jp

この記事では、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やバッファプールについて勉強になりました。元記事の著者に感謝です。