MySQLで監視したほうがよさそうな変数Part2~InnoDB関連その1~
気づいたら11月を通り越して12月になっていました。 11月は仕事の方がなんだかんだ忙しくかなーり久しぶりの投稿となってしまいました。
というか最近MySQL触れてないんだよね・・・
今回はInnoDB編です。といってもInnoDBと一口に言っても監視すべき箇所は様々あるので何回かに分けていこうと思います。
それでは早速行ってみましょう。
| Variable name | type | Scope | Dynamic | Description |
|---|---|---|---|---|
| innodb_buffer_pool_size | varaible | Global | Y | InnoDBデータおよびインデックスをキャッシュするメモリ領域、MySQLで最も重要な変数のうちの1つ MySQL 5.7からオンラインでも変更可能となった |
| innodb_buffer_pool_instances | variable | Global | N | InnoDBバッファプールを分割する数 |
| innodb_page_size | variable | Global | N | InnoDBページあたりのデータサイズを制御する 4k,8k,16k,32k,64kから選択可能 デフォルトは16k |
| Innodb_buffer_pool_pages_data | status | N/A | N/A | InnoDBバッファプールに乗っているページ数 |
| Innodb_buffer_pool_bytes_data | status | N/A | N/A | InnoDBバッファプールに乗っているデータサイズ 単位はバイト |
| Innodb_buffer_pool_read_requests | status | N/A | N/A | InnoDBバッファプールに対して読み取りリクエストが発行され実際に読み込まれたページ数 |
| Innodb_buffer_pool_reads | status | N/A | N/A | InnoDBバッファプールに対して読み取りリクエストがあったが、バッファプール上だけでは解決できずディスクから読み取ったページ数 |
- innodb_buffer_pool_size
- innodb_buffer_pool_instances
- innodb_page_size
- Innodb_buffer_pool_pages_data/Innodb_buffer_pool_bytes_data
- Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads
innodb_buffer_pool_size
MySQLで最も重要なパラメータは？と聞かれれば僕はinnodb_buffer_pool_sizeと答えるでしょう。
この変数はMySQLが内部に持つメモリサイズを制御します。このメモリにはInnoDBテーブルのデータおよびインデックスがキャッシュされます。
すなわち、この値が大きければ大きいほどMySQLはインメモリデータベースのように振る舞うことが可能となります。
MySQL5.6以前ではオンラインで変更は不可能でしたが、MySQL5.7からはオンライン（つまりSET GLOBAL）でも変更可能となっています。
なお、innodb_buffer_pool_sizeのようにMySQLインスタンス全体で共有するメモリをグローバルメモリ、sort_buffer_sizeのように1スレッドに割り当てられるメモリをセッションメモリと表現したりします。
そのサーバーがMySQL専用のサーバーであればinnodb_buffer_pool_sizeにサーバーメモリの50~80%を割り当てるのが定石となっています。
ただし実際には割り当てたメモリの1.1~1.2倍程度のメモリが使用されます。過剰な割り当てはメモリスワップアウト/インを引き起こす原因となりますので注意が必要です。
またクエリを実行していなくてもスレッドが存在するだけで多少ですがメモリを消費しますので、接続数についても考慮しておきます。
仮に1スレッドに対して100KBメモリが必要だとすると、100本接続があるだけで100KB x 100 = 10000KB = 10MB消費することになります。
メモリに関してはOSコマンドも駆使して監視する必要があります。
下記はpsコマンドでMySQLがどの程度メモリを消費しているかを確認している様子です。
$ ps aux | grep -e ^USER -e ^mysql USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND mysql 1806 0.1 30.2 3444268 566208 ? Ssl 16:25 0:05 /usr/sbin/mysqld
RSSが物理メモリの消費量ですので、重点的にモニタリングしておきましょう。
これに加えてfreeコマンドで現在のサーバーメモリにどの程度余裕があるかも常に監視しておく必要があります。
innodb_buffer_pool_instances
InnoDBバッファプールがある程度大きい（数GBを超えてくる）ケースではinnodb_buffer_pool_instancesを適切に設定することでバッファプールの並列性を向上させることができます。
基本的にはデフォルト値である8で十分ですが、サーバーメモリが数百GBを超えるような場合では、この値を大きくすることも検討してみてください。
特にバッファプール上のmutexで詰まっているようなケースではその価値があります。
ただし、バッファプールの並列性はサービスの特性や実行されるクエリに依存してきますので、必ず本番データと同じデータ量およびクエリを使って検証することが大切です。
なお、最近知ったのですがAuroraではインスタンスサイズによってinnodb_buffer_pool_sizeが決まっているのですが、どうやらinnodb_buffer_pool_instancesも決まっているようです。
不思議なことにSHOW VARIABLESで見ると8で固定になっているのですが、SHOW ENGINE INNODB STATUSのINDIVIDUAL BUFFER POOL INFOセクションを見ると、バッファプールの分割数が増えていることが分かります。（innodb_read/write_io_threadsも同様）
innodb_page_size
InnoDBがバッファプールとディスク間で一度に転送するデータ量の単位をページ*1と呼びます。
innodb_page_sizeはそのページのサイズを制御する変数で、4k,8k,16k,32k,64kから選択可能です。
innodb_page_sizeのデフォルトは16kですが、これはHDDが主流だったころからの値です。
SSDのようにフラッシュストレージを利用している場合、この値を4kとか8kにするのも検討する価値があります。*2
フラッシュストレージを利用していることに加えて、OLTPのように1回の操作で必要となるデータが比較的小さい場合、この値を小さくすることで無駄なI/Oを減らせるため全体のパフォーマンスが向上する可能性があります。 逆にOLAPやバッチ処理のような1度に多くのデータにアクセスするような操作が多い場合はこの値をより大きな値にすることで、1度のI/Oで多くのデータにアクセスできるためパフォーマンスが向上するかもしれません。
ただしこの変数についてはいくつかの注意点があります。
まずinnodb_page_sizeはMySQLインスタンスを生成した後は変更ができません。
どうしても変更したい場合は、mysqldumpのようなユーティリティを使って論理バックアップを取得してからもう一度MySQLインスタンスを作り直す必要があります。*3
もう1つ注意が必要なのはページのサイズによって作成できるインデックスのサイズに制限があるということです。
MySQLにはinnodb_large_prefixというパラメータがありこれが有効*4だと作成できるインデックスの最大長を3072バイトまで拡張することができます。*5
しかし、innodb_page_sizeが4k、8kの場合ではそれぞれ768バイト、1536バイトがインデックスの最大長になります。
そもそも文字列などのサイズが大きい列にインデックスを張ること自体あまりよくない*6ことですが、このような制限があることは頭に入れておく必要があるでしょう。
Innodb_buffer_pool_pages_data/Innodb_buffer_pool_bytes_data
これらの変数は実際にバッファプール上にどの程度データがあるかを示しています。
Innodb_buffer_pool_pages_dataはバッファプール上にあるページ数を、Innodb_buffer_pool_bytes_dataはバッファプール上にあるデータ数(単位はバイト)を表していて、次の式が成り立ちます。
Innodb_buffer_pool_bytes_data = Innodb_buffer_pool_pages_data x innodb_page_size
実際にバッファプールにどの程度データが割り当てられているか確認したいときに使えます。
Innodb_buffer_pool_read_requests / Innodb_buffer_pool_reads
MySQLを運用していれば、バッファプールのヒット率を確認したいときがあるはずです。 例えば、急にクエリのレスポンスが悪くなったときなどはその時のバッファプールのヒット率を確認したりします。
バッファプールのヒット率とはすなわち、クライアントからのリクエストに対してどの程度メモリ上だけで解決できたかを示す指標です。 基本的にヒット率は99%以上（低くても97%以上）あることが望ましいでしょう。
この2つの変数からバッファプールのヒット率を計算することが可能です。
(hit rate) = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
すなわちバッファプールに対しての読み取りリクエスト数(Innodb_buffer_pool_read_requests)の内、メモリ上だけでは解決できずディスクまでデータを読みに行った回数(Innodb_buffer_pool_reads)の比率を全体から引いています。
注意しなくてはいけないのは、1回目にも述べたようにこれらの値は累積値なので、その時間帯のヒット率を計算したいときは差分を出したうえでヒット率を計算する必要がある点です。
というわけで今回はInnoDB関連で監視したい変数、その時にチューニングすべき変数についてみてきました。 次回はInnoDBのフラッシュ関連の変数についてまとめる予定です（予定は未定）。
