あなたの春の掃除リストにあなたのデータベースを加えてください

あなたのデータベースの行を削除または更新するたびに、古いレコードは秘密にまだ背景に隠れているとハードドライブ上のスペースを取っている.
エーVACUUM プロセスは、あなたのラップトップでリサイクルビンを空にするようです.それはスペースをクリアし、索引付け時間を減らし、あなたのデータベースを清潔に保つ.
しかし、どのように理解することが重要ですVACUUM あなたが夕食パーティーの真ん中であなたの家を掃除するのと同じくらいを避けることができるように、働きます.
Postgresと他のデータベースがどのように、そしてどのように自分自身をきれいにするかを理解することによって、あなたはあなたのデータベースを低応答時間とデータベースサーバーの適切な量の保管のために調整することができます.
この投稿では次のようにします.

真空プロセスとは何か

完全対自動真空

このプロセスの変更と検査方法

PostgresがSQL文を実行する方法

Postgresテーブルの更新.SQLエディタ
データベースの寿命の間に、我々は上記のようなデータベースに何千もの変化をするかもしれません.しかし、レコードを削除することは実際にディスクスペースを解放しません.
Postgresはマルチバージョン同時実行制御(MVCCとしても知られている)を使用して、同時のデータアクセスと一貫性を保証します.
SQL文を実行するたびに、すべての行の代わりにデータのスナップショットを使用します.これは、ユーザーが並行トランザクションによって生成される矛盾したデータを表示するのを防ぎます.また、データを読み書きしようとするさまざまなセッションに対するロック満足を最小化します.
各トランザクションは、トランザクションIDBEGIN and COMMIT 文.ユーザーが行を挿入すると、トランザクションがコミットされない場合、他のユーザーは新しく挿入された行を見ることができません.
たとえば、下の画像では、ユーザーAは2つのレコードを空のテーブルに挿入します.ユーザBがAを走らせるならばSELECT ステートメントは、レコードがコミットされる前に0行を取得します.同様に、削除トランザクションステートメントがコミットされていない場合、別のユーザーはまだ削除ステートメントのテーブルデータを取得します.
MVCCデータアクセス例
我々が実行するときDELETE or UPDATE Postgreresは以下のようにします.

削除コマンド:postgresは物理的にタプルを削除しません既存のタプルをマークするDEAD .

更新コマンド:アップデートステートメントはDELETE プラスINSERT 文.したがって、既存のタプルをDEAD を挿入し、新しいタプルを挿入します.

あなたが頻繁にDML(挿入、更新、削除)声明を持っているならばDEAD タプルは不要なスペースを取ることができます.スペースを解放するには、以下のメンテナンスプロセスを実行しなければなりません.

死んだタプルを取り除く

デッドタプルを指すインデックスタプルを削除する

統計情報を更新

SQL文がどのように実行され、メンテナンス要件がどのようになっているのかを知ることができますVACUUM プロセス.

真空によるデッドタプルのクリーニング

PostgresはVACUUM 除去のためのメンテナンスプロセスDEAD タプルそれは更なるデータ挿入で再利用のために死んだタプルによって占められるスペースを回収します.
The VACUUM 他のDMLトランザクションと同時に実行できます.なぜなら、テーブルに排他ロックを適用しないからです.死んだタプルを除去するために以下の操作を行う.

Postgresは、ターゲットテーブルのすべてのページをスキャンし、デッドタプルのリストを作成します.必要に応じて古いタプルを凍結します.

デッドタプルリストを参照することで、デッドタプルを指すインデックスタプルを削除します.

それ以降のターゲットテーブルのシステムカタログだけでなく、統計情報を更新VACCUM 処理また、FSM(フリースペースマップ)とVM(可視マップ)を更新します.

Postgresの真空の例

インパクトを発揮しますVACUUM 例のテーブルを作成し、いくつかの値を削除し、VACUUM コマンド.この例ではpg_freespacecamp 宇宙利用の改善を監視する拡張

データの自動生成された一連のテーブルを作成する

create table SampleTable(id1 int, id2 int);

insert into
    SampleTable
values (
    generate_series(1,100000),
    generate_series(1,100000)
);

を使用して測定空き領域の使用

create extension pg_freespacemap;

SELECT
  count(*) as npages,
  round(100 * avg(avail) / 8192, 2) as avg_fs_ratio
FROM
  pg_freespace(
    'SampleTable');

SQLエディタ
我々が作成したテーブルはハードドライブ上のスペースの443ページを使用し、データのすべてが順次追加されたため、それは0の完全な空き容量比を持っています.
今、私は100より大きい値ですべてのレコードを削除します.しかし、我々が上記のコマンドを再実行するならば、ページと自由空間比率の数は変わりません.

delete from SampleTable where id1 > 10;

未使用のページを削除するには、vacuumコマンドを実行しなければなりません.

VACUUM sampletable;

SQLエディタ
今我々は我々が使用されるページの数を見ることができるスペースの使用状況を追跡するときに443から1になっている!しかし、このページのフリースペースと使用スペースの比率も0から49 %に増加しました.このスペースをオペレーティングシステムに戻すことができますVACUUM FULL .

真空フルは何ですか？

真空図
The VACUUM プロセスは将来の使用のために死んだタプルを取り除きますが、それはオペレーティングシステムにスペースを返しません.
したがって、バルクデータの削除や更新を行う場合、これらのデッドタプルによって占有されるスペースによってあまりにも多くのストレージを使用することがあります.The VACUUM FULL プロセスは、オペレーティングシステムにスペースを返します.次の作業を行います.

VACUUM FULL テーブルの排他ロックを取得します.

新しい空のストレージテーブルファイルを作成します.

ライブタプルを新しいテーブルストレージにコピーします.

古いテーブルファイルを削除し、ストレージを開放します.

これは、すべての関連付けられたインデックスを再構築し、システムカタログや統計情報を更新します.

どのように実行してくださいVACUUM FULL コマンドは以前のサンプルテーブルに影響を与えます.

vacuum full SampleTable;

SELECT
  count(*) as npages,
  round(100 * avg(avail) / 8192, 2) as avg_fs_ratio
FROM
  pg_freespace(
    'SampleTable');

SQLエディタ
空き容量比は、現在50 %から0 %まで下がっています.

Postgresの真空性能

VACUUM ターゲットテーブルのすべてのページをスキャンする必要があるので、クリーニングは高価です.あなたが100万行で大きなテーブルを持っているならば、それはあなたのデータベース資源に有害でありえます.リソースを保存するには、Postgresは可視性マップを使用します.Postgresの各テーブルには、テーブル内のページがデッドタプルを持つかどうかを判断するVMがあります.ページがデッドタプルを持たない場合、バキューム処理はページをスキップします.
たとえば、下の画像では、4ページのテーブルがあります.つのページには、デッドタプルがあります.可視マップは、特定のページにデッドタプルを定義するビットマップを使用します.

ビット0 :ページ上のデッドタプルは、したがってスキップVACCUM 処理

ビット1 :ページは、デッドタプルから成ります;したがって、VACCUM その特定のページ.

デッドタプルを追跡するためのPostgresの可視性マップ

Postgres自動バキュームデーモン

Postgres自動バキュームデーモンを使用して、Vaccum処理を自動化します.デフォルトでは、1分ごとに実行されます.vaccumが目覚めるとき、それは3人の労働者を呼び出します.これらの作業者は、ターゲットテーブル上のVaccum処理を行う.
PGRADES設定を問い合わせることで、Postgresでの自動バキュームプロセスのさまざまな設定を確認できます.

select name,
    setting,
    category,
    short_desc
from pg_settings 
where name like '%autovacuum%'

Postgres自動真空設定.SQLエディタ

Postgresの特定のテーブルのためにautovacuumを修正する方法

あなたが列の何百万を持っているならば、自動的に掃除して、毎分、データベースをreindexingすることは最適でないかもしれません.したがって、テーブルレベルを設定できます.テーブルレベルの設定を指定すると、グローバル設定をバイパスします.
たとえば、以下のクエリではautovacuumを設定しますSampleTable2 それが100以上の死んだタプルを持っているならば.

ALTER TABLE SampleTable2 SET (autovacuum_vacuum_scale_factor = 0, autovacuum_vacuum_threshold = 100)

真空対真空フル

我々が知っているように、完全な真空プロセスはオペレーティングシステムにスペースを取り戻します.しかし、完全な真空はその処理のためのテーブルの排他的ロックを必要とします、そして、それは他のすべての取引をブロックします.以下の表では、これらのプロセス間の違いを要約することができます.
真空対真空フル

結論

この記事では、以下を取り上げました.

Postgresの実装方法delete and update 文

使用VACUUM 玉子を取る

使用VACUUM FULL スペースをOSに戻す

個々のテーブルの自動バキュームの設定

これらの概念は,高いアベイラビリティを維持しながらデータベースのサーバコストを削減するのに有用である.上記の記事に示すクエリを使用すると、データベースの内部およびそのメモリ消費を検査できます.
場合は、直感的なインターフェイスと簡単にデータの可視化とSQLエディタをしたい.
ArcTypeによるデータ視覚化