MySQL の最適化: InnoDB の最適化

勉強の計画は簡単に中断され、継続するのが困難です。先日、社内で事業の方向性を調整するための会議があり、NodeJS を学ぶことが推奨されました。以前から NodeJS については少し知っていましたが、深く勉強していませんでした。 Node の構文は、基本的にクライアント側の Js の構文と同じです。過去 6 か月間、クライアント側の開発はほとんど行われていません。私は JS については十分な知識を持っていますが、今のところこの分野についてはよくわかっていません。知識は使われたり、失われたりするようです。頻繁に使われなければ、すぐに忘れられてしまいます。そこで、JS の関連知識をもう一度見直しました。 Node のサーバーとソケットについて学びました。 MySQL の計画は棚上げになりました。日曜日は食べて、飲んで、寝ました。午前中は怠けていたので、やっと午後まで頑張れました。では、さっそくMySQLの最適化シリーズを続けていきましょう。今回はInnoDBの最適化項目を見ていきましょう。

InnoDB のプライマリ インデックスはクラスター化インデックスであり、インデックスとデータは同じテーブルスペースを共有します。 InnoDB の場合、データはインデックスであり、インデックスはデータです。 InnoDB のキャッシュ メカニズムと MyISAM の最大の違いは、InnoDB はインデックスをキャッシュするだけでなく、データもキャッシュすることです。

1. InnoDB キャッシュプール

InnoDB バッファ プールは、InnoDB のパフォーマンスを向上させる鍵です。データ、インデックス、さらにはその他の管理データ (メタデータ、行レベルのロック) をキャッシュできます。関連するパラメータ オプションを表示するには、「innodb%pool%」のような show variables を使用できます。

mysql> 'innodb%pool%' のような変数を表示します。
+-------------------------------------+----------------+
| 変数名 | 値 |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | オフ |
| innodb_buffer_pool_dump_now | オフ |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | オフ |
| innodb_buffer_pool_load_at_startup | オフ |
| innodb_buffer_pool_load_now | オフ |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

innodb_バッファプールサイズ

innodb_buffer_pool_size は、InnoDB バッファプール (InnoDBBufferPool) のサイズを設定するために使用されます。デフォルト値は 128M です。InnoDB バッファプールのサイズは、InnoDB の全体的なパフォーマンスに大きな影響を与えます。現在の MySQL サーバーが MySQL サービス専用である場合は、このパラメータのサイズを可能な限り増やすことができます。

innodb_バッファプールインスタンス

innodb_buffer_pool_instance のデフォルト値は 1 です。これは、InnoDB バッファ プールが 1 つの領域に分割されていることを意味します。このパラメータ値を適切に増やすと、InnoDB の同時実行パフォーマンスが向上します。

innodb_追加メモリプールサイズ

InnoDB がデータ ディクショナリやその他の内部データを格納するために使用するキャッシュ サイズを指定します。デフォルト値は 2M です。InnoDB テーブルの数が多いほど、このパラメータを大きくする必要があります。

2. InnoDBキャッシュプールの内部構造

InnoDB は、データとインデックスをキャッシュするためのバッファ プールをメモリ内に保持します。キャッシュ プールは、非常に長いリンク リストと考えることができます。リンク リストは 2 つのサブリストに分かれています。1 つのサブリストには、長い間アクセスされていないデータ ページである古いページ データが保存されます。もう 1 つのサブリストには、最近アクセスされたデータ ページである新しいページ データが保存されます。デフォルトでは、古いページはリンク リスト全体のサイズの 37% を占めており、これは innodb_old_blocks_pct パラメータを通じて確認できます。

mysql> 'innodb_old_blocks%' のような変数を表示します。
+------------------------+-------+
| 変数名 | 値 |
+------------------------+-------+
| innodb_old_blocks_pct | 37 |
| innodb_old_blocks_time | 1000 |
+------------------------+-------+

古いページと新しいページの交差点は中点と呼ばれます。

ユーザーがデータにアクセスすると、InnoDB はまず InnoDB キャッシュ内のデータを検索します。キャッシュ プールにデータがない場合、InnoDB はハード ディスク上のデータを InnoDB キャッシュ プールに挿入します。キャッシュ プールがいっぱいの場合、LRU アルゴリズムを使用して期限切れの古いデータをクリアします。

3. InnoDB キャッシュ プールを予熱します。

MySQL サーバーが起動されてから一定期間が経過すると、InnoDB は頻繁にアクセスされるデータ (業務データ、管理データ) を InnoDB キャッシュに格納します。つまり、InnoDB キャッシュ プールには、頻繁にアクセスする必要があるデータ (ホット データと呼ばれる) が格納されます。 InnoDB キャッシュ プールのサイズが数十 GB または数百 GB の場合、MySQL を再起動すると、以前の InnoDB キャッシュ プールのホット データを InnoDB キャッシュ プールにどのようにロードしますか?

InnoDB キャッシュ プールを InnoDB 自体だけで予熱すると、時間がかかります。業務が忙しいシステムでは、長時間のダウンタイムは重大な運用上の事故であり、許容できません。幸いなことに、MySQL バージョン 5.6 では、サービスをシャットダウンするときにホット データをハード ディスクに保存することがサポートされています。MySQL を再起動すると、ハード ディスク上のホット データが最初に InnoDB キャッシュにロードされます。これにより、ウォームアップ時間が短縮され、業務が忙しく同時実行性が高い場合に効率が向上します。

mysql> '%innodb%pool%' のような変数を表示します。
+-------------------------------------+----------------+
| 変数名 | 値 |
+-------------------------------------+----------------+
| innodb_additional_mem_pool_size | 8388608 |
| innodb_buffer_pool_dump_at_shutdown | オフ |
| innodb_buffer_pool_dump_now | オフ |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | オフ |
| innodb_buffer_pool_load_at_startup | オフ |
| innodb_buffer_pool_load_now | オフ |
| innodb_buffer_pool_size | 134217728 |
+-------------------------------------+----------------+

シャットダウン時の innodb_buffer_pool_dump

デフォルトはオフです。パラメータをオンにして MySQL サービスを停止すると、InnoDB キャッシュ内のホット データがハードディスクに保存されます。

起動時に innodb_buffer_pool_load を実行する

デフォルトはオフです。このパラメータをオンにすると、MySQL サービスが起動したときに、MySQL はローカル ハード ディスク上のホット データを InnoDB キャッシュ プールにロードします。

innodb_buffer_pool_dump_now

このパラメータはデフォルトでは無効になっています。このパラメータを有効にすると、MySQL サービスが停止したときに、InnoDB キャッシュ プール内のホット データが手動でローカル ハード ディスクに保存されます。

innodb_buffer_pool_load_now

デフォルト設定はオフです。このパラメータをオンにすると、MySQL サービスが起動したときに、ローカル ハード ディスク上のデータが InnoDB キャッシュ プールに手動でロードされます。

innodb_buffer_pool_ファイル名

InnoDB の予熱機能が有効になっている場合、MySQL サービスが停止すると、MySQL は InnoDB キャッシュ プール内のホット データをデータベース ルート ディレクトリに保存します。デフォルトのファイル名はこのパラメータの値です。

InnoDB キャッシュを有効にした後、次のコマンドを使用して、現在の InnoDB キャッシュ プールの予熱のステータス情報を表示できます。

'innodb_buffer%' のようなステータスを表示します。
+---------------------------------------+--------------+
| 変数名 | 値 |
+---------------------------------------+--------------+
| Innodb_buffer_pool_dump_status | 開始されていません |
| Innodb_buffer_pool_load_status | 開始されていません |
| Innodb_buffer_pool_pages_data | 218 |
| Innodb_buffer_pool_bytes_data | 3571712 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_bytes_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 1 |
| Innodb_buffer_pool_pages_free | 7973 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 8191 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead | 0 |
| Innodb_buffer_pool_read_ahead_evicted | 0 |
| Innodb_buffer_pool_read_requests | 1497 |
| Innodb_buffer_pool_reads | 219 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 1 |
+---------------------------------------+--------------+

ここの英語は比較的簡単なので説明しません。

4. InnoDBリアルタイム監視

mysql> show engine innodb status\G