https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0101?page=1

mysql> SHOW GLOBAL STATUS LIKE 'InnoDB\_buffer\_pool%';
mysql> SHOW GLOBAL STATUS LIKE 'Innodb\_pages%';

パッファープールヒット率 =
(1 - ( Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests )) * 100

InnoDB Bufferpool Activity Metrics

https://dev.mysql.com/doc/mysql-em-plugin/en/myoem-metric-innodb-bufferpoolactivity-category.html

MySQL のメモリーの使用方法

https://dev.mysql.com/doc/refman/8.0/ja/memory-use.html

• MySQLはパフォーマンスを向上させるためにバッファとキャッシュを割り当てる

InnoDB バッファプール

• テーブル、インデックスおよびその他の補助バッファのキャッシュされた InnoDB データを保持するメモリー領域
• 大容量読み取り操作の効率を高めるため、バッファープールは複数行を保持できるページに分割
• キャッシュ管理の効率のために、バッファープールはページのリンクリストとして実装
• まれにしか使用されないデータは、LRU アルゴリズムのバリエーションを使用してキャッシュからエージアウト
• InnoDB は、malloc() 操作を使用して、サーバー起動時にバッファプール全体にメモリーを割り当て
• innodb_buffer_pool_size システム変数は、バッファープールサイズを定義
  • 推奨される innodb_buffer_pool_size 値は、システムメモリーの 50 から 75%
• バッファプールが小さすぎると、ページがバッファプールからフラッシュされるときに、後で再度必要になるだけであるため、過剰なチャーニングが発生する可能性がある

バッファプールの入れ替え挙動

https://speakerdeck.com/wa6sn/mysql-101-for-developers?slide=18

https://dev.mysql.com/doc/refman/8.0/ja/innodb-performance-midpoint_insertion.html

• リードアヘッドや完全なテーブルスキャンによって新しいブロックが取り込まれたとしても、頻繁にアクセスされる（「ホット」な）ページをバッファプール内に保持するため、InnoDBは厳密なLRUアルゴリズムを使用するのではなく、バッファプールに取り込まれた後に再びアクセスされないデータの量を最小限に抑える技術を使用している
• 新しく読み取られたブロックは、LRU リストの途中に挿入され、バッファプールで初めてアクセスされたときにリストの前部（最も最近使用された端）に移動される
• したがって、一度もアクセスされないページはLRUリストの前部に到達することがなく、厳密なLRUアプローチよりも早く「老化」する。この配置により、挿入点の下流にあるページは「古い」と見なされ、LRU のエビクションの望ましい対象となる
• innodb_old_blocks_pctは、LRUリスト内の「古い」ブロックの割合を制御。デフォルト値は37で、元の固定比率3/8。
• 値の範囲は5（バッファプール内の新しいページが非常に早く老化）から95（バッファプールの5%のみがホットページ用に予約され、アルゴリズムが従来のLRU戦略に近くなる）
• innodb_old_blocks_time は、ページへの最初のアクセス後にページがLRUリストの前端（最も最近使用された端）に移動されることなくアクセスできる時間窓（ミリ秒単位）を指定。innodb_old_blocks_time のデフォルト値は1000。この値を増やすと、ますます多くのブロックがバッファプールから早く老化する可能性がある。

バッファプール LRU アルゴリズム

https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool.html

• ページの読込みは、SQL クエリーなどのユーザーが開始する操作、または InnoDB によって自動的に実行される先読み操作が要求した場合に発生する
  • デフォルトでは、クエリーによって読み取られたページはすぐに新しいサブリストに移動
  • mysqldump または WHERE 句のない SELECT ステートメントなどにおいて実行されるテーブルスキャンは、新しいデータが再度使用されない場合でも、大量のデータをバッファプールに取り込み、同等の量の古いデータを削除する可能性
• 古いサブリストのページにアクセスすると、「「若い」」になり、新しいサブリストの先頭に移動
• 先読み操作のためにページが読み込まれた場合、最初のアクセスはすぐに発生せす、ページが削除されるまでまったく行われない可能性もある

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

https://dev.mysql.com/doc/refman/8.0/ja/innodb-multiple-buffer-pools.html

• スレッドからバッファプールへの読み込み、書き込みの競合を減らし、並列性を向上させるために、バッファプールは個別のインスタンスに分割される
• インスタンスの数はinnodb_buffer_pool_instancesにて設定される
  • 1（デフォルト）から64（最大）の値にて設定する
• 最高の効率を得るには、バッファプールインスタンスが少なくとも1GBになるように指定する

https://dev.mysql.com/doc/refman/8.0/ja/innodb-buffer-pool-resize.html#innodb-buffer-pool-chunk-size

• バッファプールインスタンスサイズはinnodb_buffer_pool_chunk_size
• innodb_buffer_pool_size = innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances

synch/mutex/innodb/buf_pool_mutex

https://docs.aws.amazon.com/AmazonRDS/latest/AuroraUserGuide/ams-waits.bufpoolmutex.html

• スレッドがメモリ内のページにアクセスするために InnoDB バッファ プールのロックを取得したときに発生する待機イベント
• 該当スレッドがバッファプールのデータ構造にアクセスする際に必要とされるミューテックス（相互排他ロック）の取得を待っている状態を表す

なぜ排他ロックがいる？

• InnoDBのバッファプールは、ディスク上のデータページをメモリ内にキャッシュするための主要な領域です。このプール内のページに対するアクセス（読み込み、書き込み、更新など）は、一貫性を保つために同期が必要です。
• バッファプールにページをロードするときやページをアンロードするとき、複数のトランザクションが競合しないようにするためにミューテックスが使用されます。
• バッファプール内で特定のページを探したり、ページの内容を変更する操作も、データの整合性を保つためにミューテックスの獲得を要求します。

ミューテックスとは？

• 複数のプロセスやスレッドが共有リソースへのアクセスを競う際に、一度に一つのプロセスまたはスレッドのみがそのリソースを使用できるように制限するために使用される同期メカニズムです。
• ミューテックスの操作
  • ロック（Lock）: リソースに対する排他的アクセス権を獲得します。ミューテックスが既に他のスレッドによってロックされている場合、この操作を試みるスレッドは、ミューテックスが解放されるまでブロック（待機）されます。
  • アンロック（Unlock）: リソースへのアクセス権を放棄し、他のスレッドがそのリソースを使用できるようにします。
• プログラミングにおいて、例えば、複数のスレッドが同じデータベースの同じレコードにアクセスしようとする場合、ミューテックスを使用してそのレコードへのアクセスをコントロールすることが一般的です。これにより、一つのスレッドがデータを更新している間に他のスレッドが同時にそのデータを読み書きすることを防ぎます。

発生する要因

• 必ずしもバッファプールに空きがないことを意味するわけではありません。
• バッファプールのミューテックスを競合していることを示しており、複数のクエリがバッファプールに同時にアクセスしようとしたときに発生します。
  • 複数のクエリが同時にバッファプールをアクセスしようとすると、バッファプール内のリソース（ページやデータ構造）への同時アクセスを制御するためにミューテックスが使用されます。これにより、他のスレッドがミューテックスの解放を待つ必要が生じ、待機イベントが発生します。
    -バッファプールが飽和している場合（多くのページが頻繁に置換される場合）、バッファプールのデータ構造に対する操作が増加し、それに伴ってミューテックスの競合も増加する可能性があります。
  • 参照しているテーブルのサイズが大きい場合、より多くのページがバッファプール内でアクティブになります。これにより、それらのページを管理するためのミューテックス操作が頻繁に必要とされ、結果としてミューテックスの競合が発生することがあります。

ミューテックスの数

• MySQLの以前のバージョンにおいて、バッファプール全体を保護する単一のミューテックス（global mutex）として使用されていました。しかし、この設計はスケーラビリティの問題、特に多数のCPUコアを持つシステムでの並行性のボトルネックを引き起こす原因となりました。
• MySQL 5.5以降のバージョンでは、バッファプールのスケーラビリティと並行処理能力を向上させるために、buf_pool_mutex の設計が見直されました。新しい設計では、バッファプールは複数のインスタンスに分割され、各インスタンスには独自のロックやラッチが割り当てられるようになりました。これにより、複数のスレッドが異なるバッファプールインスタンスを同時に操作できるようになり、システムの全体的なパフォーマンスが向上しました。

テーブル単位の情報取得

https://gihyo.jp/dev/serial/01/mysql-road-construction-news/0091
https://dev.mysql.com/doc/refman/8.0/ja/table-wait-summary-tables.html

各テーブルごとにディスクから読み込まれたページ数

table_io_waits_summary_by_table テーブルは、wait/io/table/sql/handler インストゥルメントによって生成されるすべてのテーブル I/O 待機イベントを集計します。 グループ化はテーブル単位です。

SELECT 
    OBJECT_SCHEMA AS `Database`, 
    OBJECT_NAME AS `Table`,
    COUNT_READ AS `Read Operations`, # ディスクからの読み込み操作の数
    COUNT_STAR AS `Total Operations`, # そのテーブルに対するすべてのI/O操作の数
    SUM_TIMER_WAIT AS `Total Wait Time` # 待機時間の合計
FROM 
    performance_schema.table_io_waits_summary_by_table
WHERE 
    OBJECT_SCHEMA NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys')
ORDER BY 
    COUNT_READ DESC;

• 累積になるので、特定の期間の間の増加を知りたい場合は差分で確認する
• COUNT_READはディスクからの読み込み時にカウントされる。バッファプール内のデータを参照した場合はカウントされない

すべてのテーブルにおけるロック待機の情報を取得

table_lock_waits_summary_by_table テーブルは、wait/lock/table/sql/handler インストゥルメントによって生成されるすべてのテーブルロック待機イベントを集計します。 グループ化はテーブル単位です。

SELECT OBJECT_SCHEMA, OBJECT_NAME, 
       COUNT_READ_NORMAL AS Read_Locks, # 読み込みロック（共有ロック）のための待機が発生した回数
       COUNT_WRITE_NORMAL AS Write_Locks, # 書き込みロック（排他ロック）のための待機が発生した回数
       SUM_TIMER_WAIT/1000000000000 AS Wait_Time_Sec
FROM performance_schema.table_lock_waits_summary_by_table
WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY SUM_TIMER_WAIT DESC;

ページクリーナー

• バッファプールからページを削除する処理
  • flushed:ディスクへのフラッシュ（書き出し）
    • 「dirty」ページ
  • evicted:メモリからの追い出し
• ページクリーナーの処理時間が予定を超えた場合にエラーログに出力される

[Note] InnoDB: page_cleaner: 1000ms intended loop took 5462ms. The settings might not be optimal. (flushed=0 and evicted=2524, during the time.)
ページクリーナースレッドがバッファプール内のページをクリーンアップするために設定された目標時間（1000ミリ秒）に対して、実際にかかった時間が5462ミリ秒.ページクリーナーが指定された期間中にディスクにフラッシュ（書き出し）したページ数が0であり、メモリから追い出された（エビクトされた）ページ数が2524