MySQL で Innodb_buffer_pool_size を設定する方法

Innodb_buffer_pool_size の設定方法

InnoDB テーブル、インデックス、その他の補助バッファーのキャッシュ データは、バッファー プール内のメモリ領域に格納されます。バッファ プールのサイズはシステムのパフォーマンスにとって重要です。バッファー プールを大きくすると、同じテーブル データへの複数のアクセスによるディスク I/O が削減されます。専用データベース サーバーでは、バッファ プール サイズをコンピュータの物理メモリ サイズの 80% に設定することが可能です。

バッファ プール関連パラメータの説明

1) システム変数パラメータ

• Innodb_page_size

• InnoDB ページ サイズ (デフォルトは 16KB)。ページには多くの値が含まれており、ページ サイズにより簡単にバイトに変換できます。

• #Innodb_buffer_pool_chunk_size

##innodb_buffer_pool_chunk_size InnoDB バッファ プールのサイズ変更操作のチャンク サイズを定義します。デフォルトは 128M です。最大値を設定可能 innodb_buffer_pool_size / innodb_buffer_pool_instances
• innodb_buffer_pool_instances

InnoDB バッファプールの分割領域数の中へ。
• Innodb_buffer_pool_pages_data

InnoDB バッファ プールに含まれるデータのページ数。この数には、ダーティ ページとクリーン ページが含まれます。圧縮テーブルを使用する場合、報告される Innodb_buffer_pool_pages_data 値が Innodb_buffer_pool_pages_total より大きくなる可能性があります (Bug #59550)。
• Innodb_buffer_pool_pages_total

InnoDB バッファ プールの合計サイズ (ページ単位)。圧縮テーブルを使用する場合、報告される Innodb_buffer_pool_pages_data 値が Innodb_buffer_pool_pages_total より大きくなる可能性があります (Bug #59550)
##2) 実行中のステータス変数

Innodb_buffer_pool_pages_flushed

• バッファ プールからページをフラッシュするリクエストの数。
Innodb_buffer_pool_read_requests

• メモリからロジックを読み取るリクエストの数を示します。
Innodb_buffer_pool_reads

• InnoDB はバッファ プールからの論理読み取り数を満たすことができないため、ディスクから直接読み取る必要があります。
Innodb_buffer_pool_wait_free

• 通常、InnoDB バッファ プールへの書き込みはバックグラウンドで行われます。 InnoDB がページの読み取りまたは作成を必要とするときに、使用可能なクリーン ページがない場合、InnoDB はまずダーティ ページをいくつかフラッシュし、操作が完了するのを待ちます。このカウンターは、これらの待機中のインスタンスをカウントします。正しく設定されている場合、innodb_buffer_pool_size はより小さい値になるはずです。

• #バッファ プール関連のパラメータを適切に設定する

1. innodb_buffer_pool_size はデフォルトでシステム メモリの 80% に設定され、次のルールに従って調整されます。実際の状況に合わせて

mysql> show global status like 'Innodb_buffer_pool_pages_data';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| Innodb_buffer_pool_pages_data | 1894874 |
+-------------------------------+---------+
1 row in set (0.00 sec)

mysql>  show global status like 'Innodb_buffer_pool_pages_total';
+--------------------------------+---------+
| Variable_name                  | Value   |
+--------------------------------+---------+
| Innodb_buffer_pool_pages_total | 1965960 |
+--------------------------------+---------+
1 row in set (0.00 sec)

# 计算是否应该添加内存
使用率 = Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%
当结果 > 95% 则增加 innodb_buffer_pool_size
当结果 < 95% 则减少 innodb_buffer_pool_size， 可适当较少，当然独享业务机器多了也没啥问题

2. innodb_buffer_pool_instances のデフォルト設定は 8、最小値 1、最大値 64

マルチバッファプールを持つシステムの場合-ギガバイトの範囲を減らすことで、キャッシュ ページの読み取りと書き込みを行う別のスレッドの競合により、バッファ プールを個別のインスタンスに分割することで同時実行性を改善できます。この機能は通常、バッファ プール サイズが数 GB の範囲のシステムで使用されます。複数のバッファ プール インスタンスは、innodb_buffer_pool_instances 構成オプションを使用して構成され、innodb_buffer_pool_size 値を調整することもできます。 特に InnoDB バッファ プールが大きい場合、多くのデータ リクエストはメモリから取得することで満たされます。複数のスレッドがバッファー プールに同時にアクセスしようとすると、ボトルネックが発生する可能性があります。複数のバッファ プールを有効にして、この競合を最小限に抑えることができます。バッファ プール内のページは、ハッシュ関数を使用して各ページをバッファ プール内の場所にランダムにマッピングすることによって格納または読み取られます。各バッファ プールは、独自のフリー リスト、フラッシュ リスト、LRU、およびバッファ プールに接続されているその他すべてのデータ構造を管理し、独自のバッファ プール ミューテックスによって保護されます。

複数のバッファ プール インスタンスを有効にするには、innodb_buffer_pool_instances 構成オプションを 1 (デフォルト) より大きく、最大 64 (最大) の値に設定します。このオプションは、innodb_buffer_pool_size が 1GB 以上に設定されている場合にのみ有効です。指定した合計サイズがすべてのバッファー プールに割り当てられます。最高の効率を得るには、各バッファー プール インスタンスが少なくとも 1GB になるように innodb_buffer_pool_instances と innodb_buffer_pool_size の組み合わせを指定します。

概要:

# cat /etc/my.cnf
[mysqld]
......
innodb_buffer_pool_size = 系统内存%80
innodb_buffer_pool_instances = 大于8的情况下，每个缓冲池实例至少1GB
......

参考:

https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters .html

• ##https://dev.mysql.com/doc/refman/5.7/en/server-status-variables.htm

# https://dev.mysql.com/doc/refman/5.7/en/innodb-buffer-pool-resize.html
innodb_buffer_pool_size パラメータを設定します

• 使用キャッシュ インデックスとデータのメモリ サイズについては、もちろん多ければ多いほど良いのですが、メモリ内でのデータの読み書きが非常に高速であるため、ディスクへの読み書きが軽減されます。

データが送信されるか、チェックポイント条件が満たされると、メモリ データがディスクに一度に更新されます。

ただし、メモリはオペレーティング システムやデータベースの他のプロセスによっても使用されます。経験に基づいて、innodb-buffer-pool-size をサーバーの利用可能なメモリの合計の 75% に設定することをお勧めします。 。正しく設定されていない場合、メモリが無駄に使用されたり過剰に使用されたりする可能性があります。

对于繁忙的服务器， buffer pool 将划分为多个实例以提高系统并发性， 减少线程间读写缓存的争用。buffer pool 的大小首先受 innodb_buffer_pool_instances 影响， 当然影响较小。

1.Innodb_buffer_pool_pages_data： Innodb buffer pool缓存池中包含数据的页的数目，包括脏页。单位是page。

show global status like 'Innodb_buffer_pool_pages_data';

2.Innodb_buffer_pool_pages_total： innodb buffer pool的页总数目。单位是page。

show global status like 'Innodb_buffer_pool_pages_total';

3.show global status like 'Innodb_page_size'; 查看@@innodb_buffer_pool_size大小，单位字节

SELECT @@innodb_buffer_pool_size/1024/1024/1024; #字节转为G

4.在线调整InnoDB缓冲池大小，如果不设置，默认为128M

set global innodb_buffer_pool_size = 4227858432; ##单位字节

计算Innodb_buffer_pool_pages_data/Innodb_buffer_pool_pages_total*100%

• 当结果 > 95% 则增加 innodb_buffer_pool_size， 建议使用物理内存的 75%

• 当结果 < 95% 则减少 innodb_buffer_pool_size， 建议设置大小为： Innodb_buffer_pool_pages_data * Innodb_page_size * 1.05 / (1024*1024*1024)

以上がMySQL で Innodb_buffer_pool_size を設定する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。