MySQL 8.0 のメモリ関連パラメータの概要

理論的には、MySQL によって使用されるメモリ = グローバル共有メモリ + max_connections × スレッド固有のメモリです。

つまり、innodb_buffer_pool_size + innodb_log_buffer_size + thread_cache_size + table_open_cache + table_definition_cache + key_buffer_size + max_connections * (thread_stack + sort_buffer_size + join_buffer_size + read_buffer_size + read_rnd_buffer_size + binlog_cache_size + tmp_table_size) となります。

以下では、グローバル メモリ パラメータとスレッド固有のパラメータを分類し、関連するパラメータの機能を簡単に紹介します。

グローバル共有メモリ

innodb_バッファプールサイズ

innodb_buffer_pool_size パラメータは、MySQL データベースにとって最も重要なパラメータの 1 つです。InnoDB ストレージ エンジンへの影響は、MyISAM ストレージ エンジンのキー バッファ キャッシュへの影響に似ています。主な違いは、InnoDB バッファ プールはインデックス データをキャッシュするだけでなく、テーブル データもキャッシュし、データ ファイルのデータ構造情報に従って完全にキャッシュすることです。これは、Oracle SGA のデータベース バッファ キャッシュに似ています。したがって、SHOW ENGINE innodb status で見つかったバッファ プール サイズは 16K 倍にする必要があります。

InnoDB バッファ プールのヒット率は、(Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% で計算できます。

innodb_change_buffering

変更バッファリングは、MySQL 5.5 で追加された新機能です。変更バッファリングは挿入バッファの拡張機能です。挿入バッファは挿入に対してのみ有効ですが、変更バッファリングは挿入、削除、更新 (削除 + 挿入)、およびパージに対して有効です。インデックス ブロック (セカンダリ インデックス) のデータを変更する場合、インデックス ブロックがバッファー プールに存在しないと、変更された情報は変更バッファーにキャッシュされます。インデックス スキャンによって必要なインデックス ブロックがバッファー プールに読み込まれると、変更バッファー内の変更された情報とマージされ、適切なタイミングでディスクに書き戻されます。

目的は、ランダム IO によって引き起こされるパフォーマンスの低下を減らすことです。簡単に言えば、ランダム IO を可能な限りシーケンシャル IO に変換することです。 SSD は現在普及しています。SSD のランダム アクセスとシーケンシャル アクセスのパフォーマンスがほぼ同じである場合、変更バッファリング機能によってパフォーマンスが大幅に向上することはありません。ただし、安価な機械式ハード ドライブの場合、このパラメーターはパフォーマンスの向上に役立ちます。

変更バッファリングは、パラメータ innodb_change_buffering によって制御されます。

• すべて: バッファ挿入、削除マーク操作、およびパージ。
• none: いかなる操作もバッファリングしません。
• 挿入: 挿入操作をバッファリングします。
• 削除: 削除マーク操作をバッファリングします。
• 変更: 挿入と削除マークの両方をバッファリングします。
• purges: バックグラウンドで実行される物理的な削除操作をバッファリングします。
  

このメモリは Innodb バッファ プールに割り当てられるため、合計メモリを計算するときにカウントする必要がないことに注意してください。

innodb_change_buffer_max_size

バッファ プール内の変更バッファの最大パーセンテージを示します。デフォルトは 25%、最大値は 50% です。システム内で重大な挿入、更新、およびアクティブな削除が行われる場合は、max_size を増やします。データを変更しない純粋なレポート システムの場合は、パラメータ値を減らすことができます。

innodb_log_buffer_size

これは、InnoDB ストレージ エンジンのトランザクション ログで使用されるバッファーです。パフォーマンスを向上させるために、情報はまず Innofb ログ バッファーに書き込まれます。innodb_flush_log_trx_commit パラメータで設定された対応する条件が満たされると (またはログ バッファーがいっぱいになると)、ログはファイルに書き込まれます (またはディスクに同期されます)。 innodb_flush_log_trx_commit パラメータは、以下に説明するように、0、1、または 2 に設定できます。

• 0: ログ バッファ内のデータは 1 秒ごとにログ ファイルに書き込まれ、同時にファイル システムがディスクに同期されます。ただし、各トランザクションのコミットによって、ログ バッファからログ ファイルへのフラッシュやファイル システムのディスクへのフラッシュはトリガーされません。このモードは最も高速ですが、安全性は低くなります。mysqld プロセスがクラッシュすると、前の 1 秒間のすべてのトランザクション データが失われます。
• 1: 各トランザクションがコミットされると、ログ バッファー内のデータがログファイルに書き込まれ、ファイル システムからディスクへの同期もトリガーされます。このモードは最も安全ですが、最も遅くなります。
• 2: トランザクションのコミットは、ログ バッファのログ ファイルへの更新をトリガーしますが、ディスク ファイル システムのディスクへの同期はトリガーしません。このモードは 0 よりも高速で安全です。最後の 1 秒間のすべてのトランザクション データは、オペレーティング システムがクラッシュした場合、またはシステムの電源が失われた場合にのみ失われる可能性があります。
  

スレッドキャッシュサイズ

スレッド プール キャッシュ サイズは、クライアントが切断した後に現在のスレッドをキャッシュするために使用され、新しいスレッドを作成せずに新しい接続要求に迅速に応答できるようになります。これにより、特に短い接続を使用するアプリケーションの場合、接続を作成する効率が大幅に向上します。接続スレッド キャッシュのヒット率は、(接続数 - Threads_created) / 接続数 * 100% で計算できます。次の MySQL ステータス値を使用して、スレッド プールのサイズを適切に調整することもできます。

mysql> 'Thread%' のようなグローバル ステータスを表示します。
+-------------------+------+
| 変数名 | 値 |
+-------------------+------+
| スレッドキャッシュ | 2 |
| スレッド接続数 | 1 |
| 作成されたスレッド | 3 |
| 実行中のスレッド | 2 |
+-------------------+------+
セット内の 4 行 (0.01 秒)

Threads_cached が減少しても Threads_connected が減少せず、Threads_created が増加し続ける場合、thread_cache_size のサイズを適切に増やすことができます。

テーブルオープンキャッシュ

table_open_cache は、テーブル ファイルのファイル ハンドル情報をキャッシュするために使用されるテーブル キャッシュのサイズを指定します。クライアント プログラムが MySQL にクエリを送信すると、MySQL はクエリに含まれる各テーブルのテーブル ファイル ハンドル情報を取得する必要があります。テーブル キャッシュがない場合、MySQL は頻繁にファイルを開いたり閉じたりする必要があり、システム パフォーマンスに一定の影響が出ることは間違いありません。MySQL がテーブルにアクセスするたびに、テーブル バッファーにスペースがあれば、テーブルが開かれてそこに配置されるため、テーブルの内容にすばやくアクセスできます。ここで設定するのはメモリ空間のサイズではなく、キャッシュできるテーブルファイルハンドル情報の数であることに注意してください。

ピーク時のステータス値 Open_tables と Opened_tables を確認することで、table_open_cache の値を増やす必要があるかどうかを判断できます。 Open_tables は現在開いているテーブルの数であり、Opened_tables は開いているすべてのテーブルの数です。 table_open_cache を盲目的に大きな値に設定することはできません。値が大きすぎると、シェルのファイル記述子を超えてしまいます (ulimit -n で確認してください)。その結果、ファイル記述子が不足し、パフォーマンスが不安定になったり、接続に失敗したりする可能性があります。 open_tables が table_open_cache と等しく、opened_tables が増加している場合は、table_open_cache の値を増やす必要があります (上記のステータス値は、SHOW GLOBAL STATUS LIKE 'Open%tables' を通じて取得できます)。 Open_tables の値が table_cache の値に近く、Opened_tables がまだ増加している場合は、MySQL が新しいテーブルに対応するためにキャッシュされたテーブルを解放していることを意味します。このとき、table_cache の値を増やす必要がある場合があります。ほとんどの場合、適切な値は次のとおりです。

• 開いているテーブル / 開いているテーブル >= 0.85
• オープンテーブル / テーブルキャッシュ <= 0.95
  

MySQL データベースを一定期間本番環境で実行し、パラメータ値を Opened_tables の値より大きく調整し、高負荷の極端な状況でも Opened_tables よりわずかに大きい値を維持するようにすることをお勧めします。

テーブル定義キャッシュ

table_definition_cache は table_open_cache に似ています。前者は frm ファイルをキャッシュします。ドキュメントでは後者について説明されていません。ibd/MYI/MYD のはずです。

ステータス値:

Open_table_definitions: キャッシュされたテーブル定義ファイル.frmの数

Opened_table_definitions: 履歴にキャッシュされた frm ファイルの合計数

キーバッファサイズ

key_buffer_size はインデックス バッファのサイズを指定します。これにより、インデックス処理の速度、特にインデックス読み取りの速度が決まります。ステータス値のKey_read_requestsとKey_readsを確認することで、key_buffer_sizeの設定が適切かどうかを知ることができます。 key_reads / key_read_requests の比率は可能な限り低くする必要があり、少なくとも 1:100、1:1000 が望ましいです (上記のステータス値は、SHOW STATUS LIKE 'key_read%' を使用して取得できます)。 key_buffer_size は MyISAM テーブルでのみ機能します。 MyISAM テーブルを使用しない場合でも、内部の一時ディスク テーブルが MyISAM テーブルである場合は、この値を使用する必要があります。詳細を取得するには、チェック ステータス値 created_tmp_disk_tables を使用できます。

最大接続数

MySQL 接続の最大数。この値を増やすと、mysqld に必要なファイル記述子の数が増えます。サーバーに多数の同時接続要求がある場合は、この値を増やして並列接続の数を増やすことをお勧めします。もちろん、これはマシンのサポート能力に基づいています。接続数が多いと、MySQL は接続ごとに接続バッファーを提供するため、より多くのメモリを消費します。したがって、値を適切に調整し、盲目的に値を増やすべきではありません。値が小さすぎると、エラー 1040: 接続数が多すぎるというエラーが表示されることがよくあります。「conn%」ワイルドカードを使用して、現在の状態の接続数を表示し、値のサイズを判断できます。 max_used_connections / max_connections * 100% (理想値 ≈ 85%) max_used_connections が max_connections と同じ場合、max_connections の設定が低すぎるか、サーバーの負荷制限を超えています。10% 未満の場合、設定が高すぎます。

スレッド/セッション/接続専用メモリ

binlog_cache_size

各セッションに割り当てられたメモリは、トランザクション処理中にバイナリ ログ キャッシュを保存するために使用され、バイナリ ログの記録効率が向上します。デフォルト値は 32K です。大規模なトランザクションがなく、DML がそれほど頻繁でない場合は、小さく設定できます。トランザクションが大きく、数が多く、DML 操作が頻繁に行われる場合は、適切に増やすことができます。

データベースにおけるbinlog_cache_sizeの使用状況は、次のように確認できます。binlog_cache_disk_useは、binlog_cache_sizeによって設計されたメモリ不足のために、バイナリログをキャッシュするために一時ファイルが使用された回数を示します。binlog_cache_useは、binlog_cache_sizeがキャッシュに使用された回数を示します。

tmp_table_size と max_heap_table_size

tmp_table_size は、各スレッドに割り当てる必要がある内部メモリ一時テーブルの最大サイズを指定します。 (実際の制限は、tmp_table_size と max_heap_table_size の最小値です。) メモリ内の一時テーブルが制限を超えると、MySQL はそれを自動的にディスクベースの MyISAM テーブルに変換し、指定された tmpdir ディレクトリに保存します (デフォルト)。

mysql> "tmpdir"のような変数を表示します。
+---------------+-------+
| 変数名 | 値 |
+---------------+-------+
| tmpdir | /tmp/ |
+---------------+-------+

クエリ ステートメントを最適化するときは、一時テーブルの使用を避けてください。避けられない場合は、これらの一時テーブルがメモリに格納されていることを確認してください。必要に応じて、group by ステートメントが多数あり、メモリが大量にある場合は、tmp_table_size (および max_heap_table_size) の値を増やします。この変数は、ユーザーが作成したメモリ テーブルには適用されません。

内部ディスクベースの一時テーブルの合計数とメモリ内に作成された一時テーブルの合計数 (Created_tmp_disk_tables と Created_tmp_tables) を比較できます。一般的な比率は次のとおりです。

作成された_tmp_disk_tables/作成された_tmp_tables<5%

max_heap_table_size は、ユーザーが作成できるメモリ テーブルのサイズを定義します。この値は、メモリ テーブルの最大行値を計算するために使用されます。この変数は動的な変更をサポートします。つまり、@max_heap_table_size = xxx を設定します。

上記はMySQL 8.0のメモリ関連パラメータの詳細な概要です。MySQL 8.0のメモリパラメータの詳細については、123WORDPRESS.COMの他の関連記事に注目してください。