DBのメモリについて簡単にまとめてみた

DBの勉強の一環でDBメモリについて調べてみた。
SQL実践入門を読んでいて少し公式を調べつつ自分なりにまとめた記事です。

DBのバッファの種類

バッファは基本的にメモリに領域を保持している

DBはストレージの遅さをメモリを活用してカバーしているため、

ユーザーが用途に応じて割り当てサイズを変更することができるがそれぞれに対してトレードオフが存在する

データキャッシュ

アクセス時にディスクにあるデータ(テーブルデータやインデックスデータ)をキャッシュする領域。

ディスクからデータを取得する場合はメモリよりも遅く、SQL文のパフォーマンスが下がる。

キャッシュとしてメモリにデータを保持していれば高速でデータを返すことが可能になるため、

主にパフォーマンスのために用いられる。ここに保持されていない場合はディスクにデータを探しにいく。

基本的にDBはデータの検索を重視しているため、データバッファの方がログバッファよりも多いメモリ割り当てがされていることが多い(更新よりも取得の方がデータの対象数が多い傾向にあるため)

名称

PostgreSQL	MySQL
共有バッファ	バッファプール

詳細に知りたい場合

参考URL:

MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.5.1 バッファプール

第101回　InnoDBバッファプールの状態を確認するさまざまな方法 | gihyo.jp

MySQLのバッファプールの詳細

バッファプールはLRU(最低使用頻度)アルゴリズムが採用されている。

大容量読み取り操作の効率を高めるため、バッファープールは複数行を保持できるページに分割される。ページとはディスクとメモリーの間で一度に転送するデータ量を表す単位であり、ページには1つ以上のレコードを含めることができる。バッファプールはページのリンクリストとして実装される。

図で言うと使用頻度の高いデータほど上にいき、使用頻度の低いデータほど下にいく。

• バッファプール(データキャッシュ)の5/8を最近アクセスされた新しいデータをNewSublistとして領域が割り当てられる
• バッファプール(データキャッシュ)の3/8を最近アクセスされていない古いデータをOldSublistとして領域が割り当てられる

ここで新しいページを挿入する際にはOldSublistの先頭に新しいページを挿入する。(OldSublistとNewSublistの中間の位置)

新しいデータであるが、まだあまり使われていないデータとみなし、OldSublistの先頭に挿入する。

データが読み込まれるとNewSublistの先頭に移動する。逆にアクセスされないデータはOldSublistの下層に追い込まれ、そのうち新しいページの挿入の領域確保のため、削除される。

ログバッファ

更新処理の実行時のキャッシュである。一旦この領域に更新データを溜めて、後でまとめてディスクに書き込みに行く(コミット)。低速なディスク書き込みを後回しにすることでユーザーからはすぐに書き込みが完了したとレスポンスを返すことができる。よってデータのディスクへの書き込みは非同期となる。

ログバッファはデータキャッシュに対してメモリ割り当てサイズが小さい。更新系の処理がメインの使用用途であれば、こちらの割り当てを大きくした方が望ましい。メモリ資源は有限なため、検索と更新のどちらに重きを置くかは使用するアプリケーションに依存する。

名称

PostgreSQL	MySQL
トランザクションバッファ	ログバッファ

参考URL:

MySQL :: MySQL 8.0 リファレンスマニュアル :: 15.5.4 ログバッファ

ワーキングメモリ

ソートやハッシュなどの特定の処理に利用される作業用の領域である。

ORDER BY, 集合演算, ウィンドウ関数などを利用する際に使用される。

データ量が多くなり、メモリ領域が不足した場合はDBはディスクのストレージを利用する。

よって、低速なディスクを利用することによるパフォーマンスの低下が突然起きることとなる。(TEMP落ち)

PostgreSQL	MySQL
ワークバッファ	ソートバッファ

MySQLでの確認方法

SHOW GLOBAL VARIABLES WHERE Variable_name IN (
	'innodb_buffer_pool_size', 
	'innodb_log_buffer_size',
	'sort_buffer_size',
	'tmp_table_size',
	'join_buffer_size'
);

まとめ

• データキャッシュの領域が多ければ、その分、キャッシュを多く保持できるため検索性能が上がる
• ログバッファの領域が多ければその分、ディスク書き込み頻度が下がり更新性能が上がる。(障害時にダウンするとメモリが消えるため、データ不整合のリスクは上がる)
• ワーキングメモリの領域が多ければ、TEMP落ちしにくくなり、処理がメモリ内で完結するため高速な処理を実現できる。(メモリ資源は有限のため、適切に設定)