Database JUNKY

MySQL,MariaDBを中心としたブログです

postgresql データを最適化(再編成)する方法

データを最適化する方法として、postgresqlは、vacuum という機能がございます。 まずデータの最適化とは何か?について説明します。 例えば、以下のようなデータが格納されているテーブルがあったと過程して説明いたします。

【現在のテーブル】

TAB1

Col1 Col2 Col3
1 A ABC
2 B ABC
3 C ABC
4 D ABC
5 E ABC
6 F ABC
7 G ABC
8 H ABC
9 I ABC

上記のTAB1テーブルに対して、下記、DELETE SQLを発行します。

  • DELETE FROM TAB1 WHERE COL1 = 3;
  • DELETE FROM TAB1 WHERE COL1 = 6;
  • DELETE FROM TAB1 WHERE COL1 = 2;

すると、テーブル的にどうなるかといいますと、以下のようになります。これがいわゆる虫食い状態といいます。 データベース全般がそうだと思っていますが、DELETEとは削除をするというか、実際は、nullで埋めるといったほうが 良いかもしれません。当然このままの状態ですと、SELECT等のSQLが発行された場合、必要な部分行までレコードを シークしますので無駄なオーバーヘッドがかかります。

【DELETE後のテーブル状態】

TAB1

Col1 Col2 Col3
1 A ABC
4 D ABC
5 E ABC
7 G ABC
8 H ABC
9 I ABC

この虫食い状態を再配置するために必要なコマンドが、vacuumというコマンドになります。このコマンドを発行しますと、 上記のようなテーブルが最適化され、以下のような状態になります。

【vacuum実行後のテーブル状態】

TAB1

Col1 Col2 Col3
1 A ABC
4 D ABC
5 E ABC
7 G ABC
8 H ABC
9 I ABC

上記を、踏まえた上で、vacuumの実行方法について記載いたします。

# su - dbuser $ vacuumdb qk VACUUM

これだけです。VACUUMというメッセージが出力されれば、正常に完了しております。簡単ですね。 ただし、vacuumdbは、かなりの負荷がかかり、通常運用で頻繁に実行すると、最悪関連するアプリケーションが 停止してしまうということがあるかもしれません。V8.1 以前を利用していた方は、アクセス負荷が低い時間を狙い、定期的にこの 処理を流していたのです、このあたりがかなり悩みどころだったのですが、V8.1以降をお使いの方はこの問題が解決され。 autovacuumという機能が新規で追加されております。ついでなので、autovacuumの設定にするにはどうしたらいいのか?についても 触れてみたいと思います。

  1. postgresqlの設定を編集します。 # vi /var/lib/pgsql/data/postgresql.conf
  2. 以下の行を探します。 #autovacuum = off                       # enable autovacuum subprocess?
  3. このように直します。 autovacuum = on # enable autovacuum subprocess?
  4. 以下の行を探します。 #stats_row_level = off
  5. このように直します。 stats_row_level = on
  6. 設定を反映させるため、postmasterを再起動します。 su - postgres -bash-3.2$ pg_ctl restart waiting for postmaster to shut down.... done postmaster stopped postmaster starting

以上で、autovacuumの設定は完了です。